Re: [HACKERS] CREATE INDEX and HOT - revised design
Ühel kenal päeval, N, 2007-03-22 kell 23:30, kirjutas Pavan Deolasee: On 3/22/07, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: When CREATE INDEX starts, it acquires ShareLock on the table. At this point we may have one or more HOT-update chains in the table. Tuples in this chain may be visible to one or more running transactions. The fact that we have ShareLock on the table means that all tuples in the chain except the one at the head either RECENTLY_DEAD or were UPDATEd by the same transaction that is now running CREATE INDEX. This logic seems pretty questionable to me in view of the recent VACUUM FULL bug fixes. We now know that an update chain can contain tuples that appear DEAD but are later than ones that are RECENTLY_DEAD. How are you defining a HOT chain exactly --- will it be just a part of an update chain that consists only of contiguous tuples that appear live or RECENTLY_DEAD to the CREATE INDEX transaction? HOT-chain is something which contains contiguous tuples that share the same index keys for all the existing indexes and share a single index entry. It does not really matter if there are RECENTLY_DEAD tuples that appear before a DEAD tuple because from VACUUM FULL bug fix we know that they are DEAD too. My argument is that its enough to index only the LIVE tuple which is at the end of the chain if we don't use the new index for queries in transactions which were started before CREATE INDEX. You mean, which were started before CREATE INDEX completes ? Just wait for all concurrent transactions to complete before marking the index as usable in plans. I am proposing to do that by storing an xid in the pg_index row. I don't think it is a good idea to store xid's anywhere but in xmin/xmax columns, as doing so would cause nasty xid wraparound problems. Instead you should wait, after completeing the index , for all concurrent transactions to end before you mark the index as usable for queries, similar to the way CREATE INDEX CONCURRENTLY does. A special case is where a tuple is UPDATEd multiple times by the same transaction which is also creating the index, in which case there are more than one LIVE versions of the tuple. But again we are safe by indexing only the latest version because all other versions would be invisible (even to us) once CREATE INDEX commits. In fact, the serializable transactions started before CREATE INDEX can not anyway see the index so all this is done to handle read-committed transactions. You are laboring under an illusion that system catalog accesses are MVCC. SnapshotNow does not behave that way: the system can see the new index as soon as it's committed. (It had better, since it has to start updating the index immediately, whether it's safe to scan it or not.) I'm not sure whether that's fundamental to your argument or not, but it's certainly wrong. Oh, thanks for pointing that out. But thats certainly not fundamental to the argument as you probably already guessed. The xid still controls the usage of index for query planning, somewhat similar to isindvalid flag for CREATE INDEX CONCURRENTLY. Xids are unstable and will come back to bite you after 2G transactions. Why not just use the isindvalid flag ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CREATE INDEX and HOT - revised design
Ühel kenal päeval, N, 2007-03-22 kell 07:09, kirjutas Andrew Dunstan: Pavan Deolasee wrote: What I am hearing from many users is that its probably not such a nice thing to put such restriction. Thats fair. It really helps to think about a solution once you know what is acceptable and what is not. That's likely to be the reaction for almost any restriction you can imagine. Performance improvements are great, but you can't ask people for whom current performance is adequate to pay a price in functionality for them. An easy solution would be to not enable HOT by default, so people who dont want to pay the price of no in-transaction CREATE INDEX for HOT improvements don't have to. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(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: [HACKERS] CREATE INDEX and HOT - revised design
Ühel kenal päeval, K, 2007-03-21 kell 14:06, kirjutas Merlin Moncure: On 3/21/07, Florian G. Pflug [EMAIL PROTECTED] wrote: Pavan Deolasee wrote: On 3/21/07, Merlin Moncure [EMAIL PROTECTED] wrote: On 3/21/07, Pavan Deolasee [EMAIL PROTECTED] wrote: It seems much simpler to me do something like this. But important question is whether the restriction that CREATE INDEX can not be run in a transaction block is acceptable ? yikes -- this is huge, huge price to pay, IMHO. Think about DDL that implies index creation such as adding unique constraint to a table, many postgresql users (including me) take advantage of that in update systems to production databases. I didn't understand that quite well. How does it help to run CREATE INDEX inside a transaction ? The problem is not so much CREATE INDEX per se, but other DDL commands that implicitly create an index, for example adding a PRIMARY KEY to a table. What I would really like is an official way to promote an UNIQUE index to Primary Key, so that PK could also be added without locking the table for long periods. Some applications that I have written would fail badly if CREATE INDEX was disallowed inside a transaction - mostly, because they use plpgsql functions to manipulate database objects, and disallowing CREATE INDEX inside a transaction presumably means disallowing it from inside stored procedures. we don't have stored procedures :( only functions. Maybe your problem will be fixed once we get proper stored procedures, which can begin and commit/rollback transactions inside their code. speaking with pavan off list he seems to think that only 'create index' is outside transaction, not the other ddl flavors of it because they are generally acquiring a excl lock. so, in that sense it is possibly acceptable to me although still a pretty tough pill to swallow (thinking, guc time). It would also preclude ever integrating vanilla 'create index' to create table command, fwiw. I don't think that any of the HOT restrictions apply when creating an index on an empty table. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/23/07, Hannu Krosing [EMAIL PROTECTED] wrote: My argument is that its enough to index only the LIVE tuple which is at the end of the chain if we don't use the new index for queries in transactions which were started before CREATE INDEX. You mean, which were started before CREATE INDEX completes ? Just wait for all concurrent transactions to complete before marking the index as usable in plans. I thought there will be objections to that approach since it changes the CREATE INDEX behavior and may also lead to new deadlock conditions. I am proposing to do that by storing an xid in the pg_index row. I don't think it is a good idea to store xid's anywhere but in xmin/xmax columns, as doing so would cause nasty xid wraparound problems. May be we can use VACUUM to cleanup the xid. If xid is less than OldtestXmin, the index is visible to all the transactions and can be marked as InvalidTransactionId. Oh, thanks for pointing that out. But thats certainly not fundamental to the argument as you probably already guessed. The xid still controls the usage of index for query planning, somewhat similar to isindvalid flag for CREATE INDEX CONCURRENTLY. Xids are unstable and will come back to bite you after 2G transactions. Why not just use the isindvalid flag ? Who would set the flag to true ? Unless of course we are waiting in CREATE INDEX. But that seems to be less acceptable to me. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] tsearch_core for inclusion
http://www.sigaev.ru/misc/tsearch_core-0.41.gz
http://mira.sai.msu.su/~megera/pgsql/ftsdoc/
Changes
1) added command
ALTER FULLTEXT MAPPING ON cfgname [FOR lexemetypename[, ...]] REPLACE
olddictname TO newdictname;
2) added operator class for text and varchar
CREATE INDEX idxname ON tblname USING GIN ( textcolumn );
3) changed definition of @@ operation: {tsvector|varchar|text} @@ {text|tsquery}
SELECT * FROM tblname WHERE textcolumn @@ text;
We have two questions:
1. pg_catalog schema, if not explicitly specified in search_path, implicitly
placed as the first schema to browse. To what extent it is intentioned ?
2. At present, visibility of FTS objects conforms to the standard PostgreSQL
rule and defined by search_path variable.
For given schema and server's locale, it's possible to have several FTS
configurations, but the only one (with special flag enabled)
could be used as default. Current (active) FTS configuration contains
in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration
is looked in search_path to match server's locale with default flag enabled.
By default, the first visible schema is the pg_catalog, so that system FTS
objects always mask users. To change that, one need explicitly specify
pg_catalog in the search_path.
This can confuse people, especially unexperienced users. Imagine, she creates
public.fts configuration for ru_RU.UTF-8 locale and enabled it as default.
CREATE FULLTEXT CONFIGURATION public.fts LIKE pg_catalog.russian_utf8 AS
DEFAULT;
but with default search_path default configuration will be still
pg_catalog.russian_utf8 and she should redefine search_path to use
public.fts. Then, she can creates index for simple (without creating
tsvector column) search on TEXT column
CREATE INDEX pgweb_idx ON pgweb USING gin(body);
Notice, there is no way to specify fts configuration, so CREATE INDEX will use
pg_catalog.russian_utf8 configuration and, consequently, specific dictionaries,
stop-words, etc. Next time, she should remember about search_path, else she will
be very confused, because pg_catalog.russian_utf8 will be used in
SELECT title FROM pgweb WHERE body @@ plainto_tsquery('create table');
Of course, there are several ways to avoid such kind of errors, but
we want to minimize this possible source of confusions and ask community
if it's worth to make user-created fts configuration to be visible prior to
system configurations in pg_catalog, if pg_catalog was not *explicitly*
specified in the search_path ?
--
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
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee wrote: There is a slight hole in that SERIALIZABLE transactions won't be able to use any indexes they build during their transaction, since they may need to be able to see prior data, but I don't think anybody is going to complain about that restriction. Anyone? Oh, I did not see that. If there are no HOT-chains in the table, we can set the xid to InvalidTransactionId so that the index is usable immediately after it is created in the current transaction, as well as those transactions which were started before CREATE INDEX. We can possibly further improve it by checking if there are no HOT-chains except those created by this transaction and set xid to InvalidTransactionId. IMO with that we shall address most of the use cases. There are few which might still get impacted, but even for them there won't be any correctness problem. Why exactly can't a SERIALIZABLE transaction use the index it created itself? If you add a pointer to the root of all HOT update chains where either the HEAD is alive, or some tuple is visible to the transaction creating the index, shouldn't this be sufficient for using the index in the creating transaction? greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch_core for inclusion
Teodor Sigaev wrote: For given schema and server's locale, it's possible to have several FTS configurations, but the only one (with special flag enabled) could be used as default. Current (active) FTS configuration contains in GUC variable tsearch_conf_name. If it's not defined, then FTS configuration is looked in search_path to match server's locale with default flag enabled. Isn't the real problem that only _one_ configuration per locale should be marked as DEFAULT at any time, no matter what schema it is in? Having one DEFAULT configuration per schema per locale will necessarily cause confusion if search_path is not set carefully I think. greetings, Florian Pflug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] CREATE INDEX and HOT - revised design
On 3/23/07, Florian G. Pflug [EMAIL PROTECTED] wrote: Why exactly can't a SERIALIZABLE transaction use the index it created itself? If you add a pointer to the root of all HOT update chains where either the HEAD is alive, or some tuple is visible to the transaction creating the index, shouldn't this be sufficient for using the index in the creating transaction? Tuples in the HOT-chain may not share the same index keys with respect to the new index being built (they share the same keys for all existing indexes though). So we don't know which index key to use while building the index. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee wrote: On 3/23/07, Florian G. Pflug [EMAIL PROTECTED] wrote: Why exactly can't a SERIALIZABLE transaction use the index it created itself? If you add a pointer to the root of all HOT update chains where either the HEAD is alive, or some tuple is visible to the transaction creating the index, shouldn't this be sufficient for using the index in the creating transaction? Tuples in the HOT-chain may not share the same index keys with respect to the new index being built (they share the same keys for all existing indexes though). So we don't know which index key to use while building the index. Ah, of course - thanks for pointing that out. greetings, Florian Pflug ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] CREATE INDEX and HOT - revised design
Hannu Krosing wrote: I don't think it is a good idea to store xid's anywhere but in xmin/xmax columns, as doing so would cause nasty xid wraparound problems. Instead you should wait, after completeing the index , for all concurrent transactions to end before you mark the index as usable for queries, similar to the way CREATE INDEX CONCURRENTLY does. We already discussed having VACUUM FREEZE deal with the pg_index xid column. I don't see how having CREATE INDEX wait for all completed transactions helps us from a usability perspective. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] CREATE INDEX and HOT - revised design
Pavan Deolasee wrote: Xids are unstable and will come back to bite you after 2G transactions. Why not just use the isindvalid flag ? Who would set the flag to true ? Unless of course we are waiting in CREATE INDEX. But that seems to be less acceptable to me. Agreed, and we have the problem of crash recovery if we do that. The xid seems to handle that automatically. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Hi,
we have found that psql in PostgreSQL 8.2.3
has problems connecting to the server
running on Solaris 10/Sun SPARC.
$ uname -a
SunOS dev-machine 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V440
It seems that somehow the system provided
GCC 3.4.3 miscompiles timestamptz_send()
and it segfaults. The default function looks like this:
Datum
timestamptz_send(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
StringInfoData buf;
pq_begintypsend(buf);
#ifdef HAVE_INT64_TIMESTAMP
pq_sendint64(buf, timestamp);
#else
pq_sendfloat8(buf, timestamp);
#endif
PG_RETURN_BYTEA_P(pq_endtypsend(buf));
}
GDB indicates crash at the last line.
No matter how I unrolled the function calls,
the indicated crasher line was always the one
before:
pq_sendfloat8(buf, timestamp);
I must be a stack corruption somehow.
I also unrolled pq_sendfloat8() so the function looks like this:
Datum
timestamptz_send(PG_FUNCTION_ARGS)
{
TimestampTz timestamp = PG_GETARG_TIMESTAMPTZ(0);
StringInfoData buf;
bytea *byteap;
union
{
float8 f;
int64 i;
} swap;
uint32 n32;
pq_begintypsend(buf);
#ifdef HAVE_INT64_TIMESTAMP
pq_sendint64(buf, timestamp);
elog(NOTICE, timestamptz_send() HAVE_INT64_TIMESTAMP after
pq_sendint64);
#else
swap.f = (float8)timestamp;
elog(NOTICE, timestamptz_send() int64: %lld, swap.i);
/* High order half first, since we're doing MSB-first */
#ifdef INT64_IS_BUSTED
/* don't try a right shift of 32 on a 32-bit word */
n32 = (swap.i 0) ? -1 : 0;
elog(NOTICE, timestamptz_send() INT64_IS_BUSTED high 32: %d, n32);
#else
n32 = (uint32) (swap.i 32);
elog(NOTICE, timestamptz_send() high 32: %d, n32);
#endif
n32 = htonl(n32);
elog(NOTICE, timestamptz_send() htonl high 32: %d, n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
/* Now the low order half */
n32 = (uint32) swap.i;
elog(NOTICE, timestamptz_send() low 32: %d, n32);
n32 = htonl(n32);
elog(NOTICE, timestamptz_send() htonl low 32: %d, n32);
appendBinaryStringInfo(buf, (char *) n32, 4);
elog(NOTICE, timestamptz_send() pq_sendfloat8);
#endif
byteap = (bytea *) buf.data;
elog(NOTICE, timestamptz_send() buf-data = %p, byteap);
Assert(buf.len = VARHDRSZ);
VARATT_SIZEP(byteap) = buf.len;
PG_RETURN_BYTEA_P(byteap);
}
Th crashing line according to GDB is now the elog() call after:
swap.f = (float8)timestamp;
This is a simple explicit type cast which shouldn't cause problems,
however it is the one that somehow corrupts something on the stack
and causes the segfault upon entering the function at the next
statement.
As a workaround, we recompiled PostgreSQL 8.2.3 with
--enable-integer-datetimes
and the client can connect to the server now, after initdb.
I tried to exercise calling timestamptz_send() but creating a table
with float8 field, INSERTing and SELECTing works, too.
Both textual and binary COPY FROM and COPY TO work, too.
Either these exercises didn't call pq_sendfloat8() or it
doesn't cause problems elsewhere, only in timestamptz_send().
--
--
Zoltán Böszörményi
Cybertec Geschwinde Schönig GmbH
http://www.postgresql.at/
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[HACKERS] Documentation access problems.
I'm new to PostgreSQL, having done a little work with MySQL in the past. Part of the reason for changing to PostgreSQL is some of the differences but to understand them I need docs of course. Now there are plenty of books, but I can't find any to examine in local bookshops (pretty poor for a town with two Universities) and I'm having problems with the PDF docs because of my eyesight. The PDFs are of high quality in terms of effort and content, but I can't get the text large enough to see and yet scroll smoothly enough under Windows XP on a fairly new machine (3GHz Pentium with 2GB RAM). I've explored the accessibility features of Acrobat Reader 8 and when trying to get it to read to me, it starts reading a dialog box that doesn't appear on the screen, and then locks up. Using Narrator through Windows Accessibility is really odious, with almost no control over what is happening. The Adobe Reader says that the accessibility features of PDF documents are not in place for this doc, (I'm presently reading http://www.postgresql.org/files/documentation/pdf/8.2/postgresql-8.2-A4.pdf;) and I think if they were I might be able to make it reflow the text so I can have large print and fit it on the screen. However, I don't know anything about the production of PDFs in practice, so don't know what is really possible here. So I'm not in a position to ask for some of the more advanced features. I don't know what the document production system is, but I was hoping that it is all under program control, and thus it would be relatively easy for someone to say Double the print size throughout and all the pagination, numbering, contents and indices would sort themselves out. [OK, things are never THAT easy :-), but in principle...] So could someone generate a large print edition of the docs so I don't have to fight screen readers and magnifiers to access this stuff, please? Changing the fonts to sans-serif might be another option, but I imagine that what with code, emphasized text, normal text, and other typographical conventions this would be more difficult to get right than making the print bigger. If this is too difficult, I have found the Web versions, but don't know how much (if anything) is lost in making the documents fit HTML. Thank you, Hugh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Documentation access problems.
Hugh Sasse wrote: If this is too difficult, I have found the Web versions, but don't know how much (if anything) is lost in making the documents fit HTML. I suggest you read the HTML pages. The information is the same. In fact, I think the question is how much is lost in making the documents fit PDF, rather than HTML. There are some tables that are a bit messed up in PDF, that look perfectly well in HTML. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Zoltan Boszormenyi wrote: Hi, we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. $ uname -a SunOS dev-machine 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V440 It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. The default function looks like this: Can you send me how you compiled Postgres (configure switches, LDFLAGS ...) and is possible get core file? Did you try compile with different optimalization flags or did you try sun studio compiler? Zdenek ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
Hugh Sasse [EMAIL PROTECTED] writes: ... I have found the Web versions, but don't know how much (if anything) is lost in making the documents fit HTML. Nothing --- the HTML version is what I invariably consult. So if you have decent reader tools for HTML, by all means go with that. regards, tom lane ---(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: [HACKERS] Documentation access problems.
Tom Lane wrote: Hugh Sasse [EMAIL PROTECTED] writes: ... I have found the Web versions, but don't know how much (if anything) is lost in making the documents fit HTML. Nothing --- the HTML version is what I invariably consult. So if you have decent reader tools for HTML, by all means go with that. me too /aolmode I do think though that there is a good case for producing PDFs for sight impaired people, on pgfoundry if not as part of our standard docs production. cheers andrew (whose grandmother was legally blind for many years) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Documentation access problems.
me too /aolmode I do think though that there is a good case for producing PDFs for sight impaired people, on pgfoundry if not as part of our standard docs production. It should be standard docs imo. PDF is a heck of a lot easier to read if you have a good PDF reader. Not to mention print. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Documentation access problems.
Joshua D. Drake [EMAIL PROTECTED] writes: It should be standard docs imo. PDF is a heck of a lot easier to read if you have a good PDF reader. Just out of curiosity, what would that be? I've used both Acrobat and Preview, and I do not like either. (As to the original point, I'm all for fixing the PDF accessibility features Hugh mentioned, but I'm afraid it may be a research project to find out how/whether our document production tools can do that.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Zoltan Boszormenyi [EMAIL PROTECTED] writes: we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. ... It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. I find it fairly hard to believe that timestamptz_send would be invoked at all while using psql, much less during initial connection. psql doesn't do any binary-output requests. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Documentation access problems.
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: It should be standard docs imo. PDF is a heck of a lot easier to read if you have a good PDF reader. Just out of curiosity, what would that be? I've used both Acrobat and Preview, and I do not like either. I use Evince personally. I used to like Acrobat but it has gotten really bloated. However the best one I have ever seen is kpdf, but I have never been able to get KDE stable for me (I am not interested in a thread on this ;)). The big thing for me, is a single document, zero clicks, that is searchable. PDF and plain text are the only thing that give me that. If you are really zealous you can even use Beagle (which I don't) to preindex the PDF for you for easy searching. (As to the original point, I'm all for fixing the PDF accessibility features Hugh mentioned, but I'm afraid it may be a research project to find out how/whether our document production tools can do that.) Yeah possibly. Sincerely, Joshua D. Drake regards, tom lane -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Zdenek Kotala írta: Zoltan Boszormenyi wrote: Hi, we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. $ uname -a SunOS dev-machine 5.10 Generic_118833-36 sun4u sparc SUNW,Sun-Fire-V440 It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. The default function looks like this: Can you send me how you compiled Postgres (configure switches, LDFLAGS ...) and is possible get core file? This was the configure line: ./configure --prefix=/export/local/postgresql/postgresql-8.2.3 --with-includes=/usr/local/include --with-libraries=/usr/local/lib/ I added --enable-debug --enable-depend --enable-cassert to get sensible gdb report after that. The problem was that the server had problems after psql connected with these commands: $ psql -l -h dev-machine -p 5477 -U user psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. $ psql -h dev-machine -p 5477 -U user template1 psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. If the user doesn't have permissions in e.g. pg_hba.conf then I get the correct permission denied error. If the user can connect then some statement inside psql causes segfault in the server. Compiled with debug info, I got this from gdb on the core file: $ gdb /.../pgsql/bin/postgres /.../data/core ... Program terminated with signal 11, Segmentation fault. #0 0x0021c8a0 in timestamptz_send (fcinfo=0x1) at timestamp.c:461 461 PG_RETURN_BYTEA_P(pq_endtypsend(buf)); (gdb) I described my experiments, compiling with --enable-integer-datetimes fixed the issue. Did you try compile with different optimalization flags or did you try sun studio compiler? No, and no. Sun Studio isn't installed, only gcc. Zdenek -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Documentation access problems.
Am Freitag, 23. März 2007 15:15 schrieb Hugh Sasse: The PDFs are of high quality in terms of effort and content, but I can't get the text large enough to see How large would you need it to be? I can zoom both the PDF and the HTML so that an n is 5mm high. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Crash bug in 8.2.3 on Solaris 10/Sparc
Tom Lane írta: Zoltan Boszormenyi [EMAIL PROTECTED] writes: we have found that psql in PostgreSQL 8.2.3 has problems connecting to the server running on Solaris 10/Sun SPARC. ... It seems that somehow the system provided GCC 3.4.3 miscompiles timestamptz_send() and it segfaults. I find it fairly hard to believe that timestamptz_send would be invoked at all while using psql, much less during initial connection. psql doesn't do any binary-output requests. regards, tom lane Then please explain this miracle. Anyway, your comment makes my suspicion about the correctness of GCC-3.4.3 on Solaris 10/sparc more founded now. :-) -- -- Zoltán Böszörményi Cybertec Geschwinde Schönig GmbH http://www.postgresql.at/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Documentation access problems.
Peter Eisentraut wrote: Am Freitag, 23. März 2007 15:15 schrieb Hugh Sasse: The PDFs are of high quality in terms of effort and content, but I can't get the text large enough to see How large would you need it to be? I can zoom both the PDF and the HTML so that an n is 5mm high. I wonder if we can have a stylesheet option that says, Large Print? I believe there is actually a Large Print standard /me googles http://www.access-board.gov/sec508/standards.htm Although it doesn't talk about specific font sizes etc.. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation access problems.
On Fri, 23 Mar 2007, Peter Eisentraut wrote: Am Freitag, 23. M?rz 2007 15:15 schrieb Hugh Sasse: The PDFs are of high quality in terms of effort and content, but I can't get the text large enough to see You trimmed that -- it is large enough if I can put up with non-smooth scrolling. It seems to need to be bigger than the other fonts I use: I think the Times in Adobe comes out with the thin strokes really thin. How large would you need it to be? I can zoom both the PDF and the HTML so that an n is 5mm high. It's a variable function of my vision, lighting, but I usually use 24 point on VDUs, In this terminal (because Lucida Console doesn't have thin strokes, the n's are about 5 mm high, but I'd like them bigger if possible. I'm not the limiting case, a former colleague liked her text about 2cm tall. Thank you, Hugh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Documentation access problems.
Hugh Sasse wrote: It's a variable function of my vision, lighting, but I usually use 24 point on VDUs, In this terminal (because Lucida Console doesn't have thin strokes, the n's are about 5 mm high, but I'd like them bigger if possible. I'm not the limiting case, a former colleague liked her text about 2cm tall. Would fixed width font help you? Thank you, Hugh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
On Fri, 23 Mar 2007, Joshua D. Drake wrote: Hugh Sasse wrote: It's a variable function of my vision, lighting, but I usually use 24 point on VDUs, In this terminal (because Lucida Console doesn't have thin strokes, the n's are about 5 mm high, but I'd like them bigger if possible. I'm not the limiting case, a former colleague liked her text about 2cm tall. Would fixed width font help you? That's almost the right question :-) but it's not the width of the characters which would help, it is the width of the narrowest strokes in the characters. I don't have a clue what typographers would call that. I don't need to push this as far as Helvetica or Ariel, where all the strokes are the same width, though that would help some people, including some with dyslexia. Also one needs fixed-with and proportional fonts as part of the semantic information in the document. I suppose the question to ask now is: what is the current production system for the PDFs? If that is known then we can see what variables can be adjusted within reason. I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). Hugh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Documentation access problems.
Hugh Sasse wrote: On Fri, 23 Mar 2007, Joshua D. Drake wrote: Hugh Sasse wrote: It's a variable function of my vision, lighting, but I usually use 24 point on VDUs, In this terminal (because Lucida Console doesn't have thin strokes, the n's are about 5 mm high, but I'd like them bigger if possible. I'm not the limiting case, a former colleague liked her text about 2cm tall. Would fixed width font help you? That's almost the right question :-) but it's not the width of the characters which would help, it is the width of the narrowest strokes in the characters. I don't have a clue what typographers would call that. Maybe a completely different typeface like Gentium may be helpful? I suppose the question to ask now is: what is the current production system for the PDFs? If that is known then we can see what variables can be adjusted within reason. I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). We use openjade. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
On Fri, 23 Mar 2007, Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: It should be standard docs imo. PDF is a heck of a lot easier to read if you have a good PDF reader. Just out of curiosity, what would that be? I've used both Acrobat and Preview, and I do not like either. I use Evince personally. I used to like Acrobat but it has gotten really bloated. However the best one I have ever seen is kpdf, but I have never been able to get KDE stable for me (I am not interested in a thread on this ;)). I'm not familiar enough with these to comment. Meanwhile I have phoned adobe (because I couldn't see where to report this bug. They told me that it could be due to a bug in the document [which surprised me -- anything that fundamental ought to yield an error message[ but there are no tools (like lint for C) for testing things like this. I said that it looks OK, but they said it could still have problems. So I've tried other PDFs and in general they don't crash in the same way. Nonetheless, Adobe Reader should not crash in my opinion, so I have raised this: https://www.adobe.com/cfusion/support/index.cfm?event=casedetailid=0200194921loc=en_us quote When attempting to read the PosgreSQL documentation (http://www.postgresql.org/files/documentation/pdf/8.2/postgresql-8.2-A4.pdf) I found the text either too small or large enough but the scrolling was jerky, and thus annoying to use. So I attempt to select Rea d Out Loud - begin reading (ctrl-Y) off the View menu. I then get a small blue bar appearing in the bottom right corner of the windwo, wich a red circle to the right which has a red cross in it. This looks like a close button in windows but it is round and not on the blue bar. Then the reader starts reading a dial ogue which is not visible on the screen. After a while the reader crashes and I must then kill it with the task manager. I have tried a few other documents now, and they seem to be working ok, however the way this fails is not really optimal :-) Maybe something more useful can be done in the case caused by this document. Thank you. Hugh /quote [There are more typos in there than I thought!] If they can't fix the crash they may be able to tell us something useful. Thank you, Hugh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
On Fri, 23 Mar 2007, Alvaro Herrera wrote: Hugh Sasse wrote: On Fri, 23 Mar 2007, Joshua D. Drake wrote: Hugh Sasse wrote: It's a variable function of my vision, lighting, but I usually use 24 point on VDUs, In this terminal (because Lucida Console doesn't have thin strokes, the n's are about 5 mm high, but I'd like them bigger if possible. I'm not the limiting case, a former colleague liked her text about 2cm tall. Would fixed width font help you? That's almost the right question :-) but it's not the width of the characters which would help, it is the width of the narrowest strokes in the characters. I don't have a clue what typographers would call that. Maybe a completely different typeface like Gentium may be helpful? Google gave me: http://scripts.sil.org/cms/scripts/page.php?site_id=nrsiitem_id=Gentium and there are samples which are more readable in PDF. The samples are on A5 rather than A4. I wonder if the PostgreSQL docs were output to A$ whether that might help me, because I'd be able to double the size before lines flowed off the screen? Thanks for this info about Gentium -- I rather like it. I suppose the question to ask now is: what is the current production system for the PDFs? If that is known then we can see what variables can be adjusted within reason. I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). We use openjade. Ah, so the HTML is the source, the DSSSL is used (about which I know nothing) to get (to something to get to) the PDF. It would take me a while to reach the stage where I could contribute anything useful in this area, alas. Thank you, Hugh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Documentation access problems.
Hugh Sasse wrote: I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). There is no reason we cannot produce several versions of the docs. It doesn't have to be one size fits all. cheers andrew ---(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: [HACKERS] Documentation access problems.
The samples are on A5 rather than A4. I wonder if the PostgreSQL docs were output to A$ whether that might help me, because I'd be able to double the size before lines flowed off the screen? Thanks for this info about Gentium -- I rather like it. I suppose the question to ask now is: what is the current production system for the PDFs? If that is known then we can see what variables can be adjusted within reason. I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). We use openjade. Ah, so the HTML is the source, No, docbook is the source of which you apply DSSSL to to generate PS, PDF, HTML, XML, Latex etc.. Joshua D. Drake the DSSSL is used (about which I know nothing) to get (to something to get to) the PDF. It would take me a while to reach the stage where I could contribute anything useful in this area, alas. Thank you, Hugh -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Documentation access problems.
On Fri, 23 Mar 2007, Andrew Dunstan wrote: Hugh Sasse wrote: I'd like to improve it for me and others in my position without making it typographically hideous for fully sighted people :-). There is no reason we cannot produce several versions of the docs. It doesn't have to be one size fits all. Agreed, but it is nice to be able discuss paper docs with other people. And we don't want to frighten the horses if someone hits the wrong link for the download. cheers andrew Hugh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Documentation access problems.
I wrote : Ah, so the HTML is the source, On Fri, 23 Mar 2007, Joshua D. Drake wrote: No, docbook is the source of which you apply DSSSL to to generate PS, PDF, HTML, XML, Latex etc.. OK, well I need to become familiar with docbook for other projects, so I may be able to contribute later. Thank you. Joshua D. Drake Hugh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Documentation access problems.
Hugh Sasse wrote: I wrote : Ah, so the HTML is the source, On Fri, 23 Mar 2007, Joshua D. Drake wrote: No, docbook is the source of which you apply DSSSL to to generate PS, PDF, HTML, XML, Latex etc.. OK, well I need to become familiar with docbook for other projects, so I may be able to contribute later. Thank you. My pleasure. Start with Docbook simple, less to learn. :) Joshua D. Drake Joshua D. Drake Hugh -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] EXISTS optimization
I'm posting this to performance in case our workaround may be of benefit to someone with a similar issue. I'm posting to hackers because I hope we can improve our planner in this area so that a workaround is not necessary. (It might make sense to reply to one group or the other, depending on reply content.) We are converting from a commercial database (which shall remain unnamed here, due to license restrictions on publishing benchmarks). Most queries run faster on PostgreSQL; a small number choose very poor plans and run much longer. This particular query runs on the commercial product in 6.1s first time, 1.4s cached. In PostgreSQL it runs in about 144s both first time and cached. I was able to use an easy but fairly ugly rewrite (getting duplicate rows and eliminating them with DISTINCT) which runs on the commercial product in 9.2s/3.0s and in PostgreSQL in 2.0s/0.7s. Here are the tables: Table public.TranHeader Column | Type | Modifiers ---+--+--- tranNo| TranNoT| not null countyNo | CountyNoT | not null acctPd| DateT | not null date | DateT | not null isComplete| boolean | not null tranId| TranIdT| not null tranType | TranTypeT | not null userId| UserIdT| not null workstationId | WorkstationIdT | not null time | TimeT | Indexes: TranHeader_pkey PRIMARY KEY, btree (tranNo, countyNo) TranHeader_TranAcctPeriod UNIQUE, btree (acctPd, tranNo, countyNo) TranHeader_TranDate UNIQUE, btree (date, tranNo, countyNo) Table public.TranDetail Column |Type| Modifiers -++--- tranNo | TranNoT | not null tranDetailSeqNo | TranDetailSeqNoT | not null countyNo| CountyNoT| not null acctCode| AcctCodeT| not null amt | MoneyT | not null assessNo| TranIdT | caseNo | CaseNoT | citnNo | CitnNoT | citnViolDate| DateT| issAgencyNo | IssAgencyNoT | partyNo | PartyNoT | payableNo | PayableNoT | rcvblNo | RcvblNoT | Indexes: TranDetail_pkey PRIMARY KEY, btree (tranNo, tranDetailSeqNo, countyNo) TranDetail_TranDetCaseNo UNIQUE, btree (caseNo, tranNo, tranDetailSeqNo, countyNo) TranDetail_TranDetPay UNIQUE, btree (payableNo, tranNo, tranDetailSeqNo, countyNo) TranDetail_TranDetRcvbl UNIQUE, btree (rcvblNo, tranNo, tranDetailSeqNo, countyNo) TranDetail_TranDetAcct btree (acctCode, citnNo, countyNo) Table public.Adjustment Column | Type | Modifiers -+---+--- adjustmentNo| TranIdT | not null countyNo| CountyNoT | not null date| DateT | not null isTranVoided| boolean | not null reasonCode | ReasonCodeT | not null tranNo | TranNoT | not null adjustsTranId | TranIdT | adjustsTranNo | TranNoT | adjustsTranType | TranTypeT | explanation | character varying(50) | Indexes: Adjustment_pkey PRIMARY KEY, btree (adjustmentNo, countyNo) Adjustment_AdjustsTranId btree (adjustsTranId, adjustsTranType, tranNo, countyNo) Adjustment_AdjustsTranNo btree (adjustsTranNo, tranNo, countyNo) Adjustment_Date btree (date, countyNo) Admittedly, the indexes are optimized for our query load under the commercial product, which can use the covering index optimization. explain analyze SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006-01-01' AND H.countyNo = 66 AND A.countyNo = 66 AND EXISTS ( SELECT 1 FROM TranDetail D WHERE D.tranNo = H.tranNo AND D.countyNo = H.countyNo AND D.caseNo LIKE '2006TR%' ) ; Nested Loop (cost=182.56..72736.37 rows=1 width=46) (actual time=6398.108..143631.427 rows=2205 loops=1) Join Filter: ((H.tranId)::bpchar = (A.adjustmentNo)::bpchar) - Bitmap Heap Scan on Adjustment A (cost=182.56..1535.69 rows=11542 width=22) (actual time=38.098..68.324 rows=12958 loops=1) Recheck Cond: (((date)::date '2006-01-01'::date) AND ((countyNo)::smallint = 66)) - Bitmap Index Scan on Adjustment_Date (cost=0.00..179.67 rows=11542 width=0) (actual time=32.958..32.958 rows=12958 loops=1) Index Cond: (((date)::date '2006-01-01'::date) AND ((countyNo)::smallint = 66)) - Index Scan using TranHeader_pkey on TranHeader H
Re: [HACKERS] Documentation access problems.
Joshua D. Drake wrote: The big thing for me, is a single document, zero clicks, that is searchable. PDF and plain text are the only thing that give me that. If you are really zealous you can even use Beagle (which I don't) to preindex the PDF for you for easy searching. Lots of projects publish their HTML docs in two formats: One Big HTML file with everything; Broken up into many HTML files that link to each other. This would allow you you have one big searchable document. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Documentation access problems.
Matthew T. O'Connor wrote: Joshua D. Drake wrote: The big thing for me, is a single document, zero clicks, that is searchable. PDF and plain text are the only thing that give me that. If you are really zealous you can even use Beagle (which I don't) to preindex the PDF for you for easy searching. Lots of projects publish their HTML docs in two formats: One Big HTML file with everything; Broken up into many HTML files that link to each other. This would allow you you have one big searchable document. Agreed. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] Documentation access problems.
Matthew T. O'Connor matthew@zeut.net writes: Lots of projects publish their HTML docs in two formats: One Big HTML file with everything; Broken up into many HTML files that link to each other. This would allow you you have one big searchable document. The key word there being big ;-) ... I don't have any problem with making such a version available on the website, but I don't think shipping two versions of the HTML docs in our tarballs is reasonable. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: Lots of projects publish their HTML docs in two formats: One Big HTML file with everything; Broken up into many HTML files that link to each other. This would allow you you have one big searchable document. The key word there being big ;-) ... I don't have any problem with making such a version available on the website, but I don't think shipping two versions of the HTML docs in our tarballs is reasonable. I think having the single HTML file version available on our web site is enough. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Documentation access problems.
Bruce Momjian wrote: Tom Lane wrote: Matthew T. O'Connor matthew@zeut.net writes: Lots of projects publish their HTML docs in two formats: One Big HTML file with everything; Broken up into many HTML files that link to each other. This would allow you you have one big searchable document. The key word there being big ;-) ... I don't have any problem with making such a version available on the website, but I don't think shipping two versions of the HTML docs in our tarballs is reasonable. I think having the single HTML file version available on our web site is enough. Agreed. ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] [COMMITTERS] pgsql: We no longer need to palloc the VacuumStmt node; keeping it on
[EMAIL PROTECTED] (Alvaro Herrera) writes: We no longer need to palloc the VacuumStmt node; keeping it on the stack is simpler. If you're going to do that, you should at least set the nodeTag so that the struct appears valid to onlookers. A memset wouldn't be out of place either to make sure that any uninitialized fields behave sanely. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] EXISTS optimization
Kevin Grittner [EMAIL PROTECTED] writes: explain analyze SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006-01-01' AND H.countyNo = 66 AND A.countyNo = 66 AND EXISTS ( SELECT 1 FROM TranDetail D WHERE D.tranNo = H.tranNo AND D.countyNo = H.countyNo AND D.caseNo LIKE '2006TR%' ) ; The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses indexed access to the TranHeader and then to Adjustment. If you want that, try rewriting the EXISTS to an IN: AND (H.tranNo, H.countyNo) IN ( SELECT D.tranNo, D.countyNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' ) We don't currently try to flatten EXISTS into a unique/join plan as we do for IN. I seem to recall not doing so when I rewrote IN planning because I didn't think it would be exactly semantically equivalent, but that was awhile ago. Right at the moment it seems like it ought to be equivalent as long as the comparison operators are strict. regards, tom lane ---(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: [HACKERS] [COMMITTERS] pgsql: We no longer need to palloc the VacuumStmt node; keeping it on
Tom Lane wrote: [EMAIL PROTECTED] (Alvaro Herrera) writes: We no longer need to palloc the VacuumStmt node; keeping it on the stack is simpler. If you're going to do that, you should at least set the nodeTag so that the struct appears valid to onlookers. A memset wouldn't be out of place either to make sure that any uninitialized fields behave sanely. Done, thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] EXISTS optimization
On Fri, Mar 23, 2007 at 4:49 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: Kevin Grittner [EMAIL PROTECTED] writes: explain analyze SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006- 01- 01' AND H.countyNo = 66 AND A.countyNo = 66 AND EXISTS ( SELECT 1 FROM TranDetail D WHERE D.tranNo = H.tranNo AND D.countyNo = H.countyNo AND D.caseNo LIKE '2006TR%' ) ; The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses indexed access to the TranHeader and then to Adjustment. If you want that, try rewriting the EXISTS to an IN: AND (H.tranNo, H.countyNo) IN ( SELECT D.tranNo, D.countyNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' ) Nice. I get this: explain analyze SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006- 01- 01' AND H.countyNo = 66 AND A.countyNo = 66 AND (H.tranNo, H.countyNo) IN ( SELECT D.tranNo, D.countyNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' ) ; Nested Loop (cost=27.76..36.38 rows=1 width=46) (actual time=92.999..200.398 rows=2209 loops=1) Join Filter: ((H.tranNo)::integer = (A.tranNo)::integer) - Nested Loop (cost=27.76..32.08 rows=1 width=50) (actual time=92.970..176.472 rows=2209 loops=1) - HashAggregate (cost=27.76..27.77 rows=1 width=6) (actual time=92.765..100.810 rows=9788 loops=1) - Index Scan using TranDetail_TranDetCaseNo on TranDetail D (cost=0.00..27.66 rows=20 width=6) (actual time=0.059..60.967 rows=46301 loops=1) Index Cond: (((caseNo)::bpchar = '2006TR'::bpchar) AND ((caseNo)::bpchar '2006TS'::bpchar) AND ((countyNo)::smallint = 66)) Filter: ((caseNo)::bpchar ~~ '2006TR%'::text) - Index Scan using TranHeader_pkey on TranHeader H (cost=0.00..4.30 rows=1 width=46) (actual time=0.006..0.006 rows=0 loops=9788) Index Cond: (((H.tranNo)::integer = (D.tranNo)::integer) AND ((H.countyNo)::smallint = 66)) Filter: ((tranType)::bpchar = 'A'::bpchar) - Index Scan using Adjustment_pkey on Adjustment A (cost=0.00..4.28 rows=1 width=22) (actual time=0.008..0.009 rows=1 loops=2209) Index Cond: (((H.tranId)::bpchar = (A.adjustmentNo)::bpchar) AND ((A.countyNo)::smallint = 66)) Filter: ((date)::date '2006-01-01'::date) Total runtime: 201.306 ms That's the good news. The bad news is that I operate under a management portability dictate which doesn't currently allow that syntax, since not all of the products they want to cover support it. I tried something which seems equivalent, but it is running for a very long time. I'll show it with just the explain while I wait to see how long the explain analyze takes. explain SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006- 01- 01' AND H.countyNo = 66 AND A.countyNo = 66 AND H.tranNo IN ( SELECT D.tranNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' AND D.countyNo = H.countyNo ) ; Nested Loop (cost=0.00..181673.08 rows=1 width=46) Join Filter: ((H.tranId)::bpchar = (A.adjustmentNo)::bpchar) - Seq Scan on Adjustment A (cost=0.00..2384.27 rows=11733 width=22) Filter: (((date)::date '2006-01-01'::date) AND ((countyNo)::smallint = 66)) - Index Scan using TranHeader_pkey on TranHeader H (cost=0.00..15.27 rows=1 width=46) Index Cond: (((H.tranNo)::integer = (A.tranNo)::integer) AND ((H.countyNo)::smallint = 66)) Filter: (((tranType)::bpchar = 'A'::bpchar) AND (subplan)) SubPlan - Index Scan using TranDetail_TranDetCaseNo on TranDetail D (cost=0.00..27.66 rows=20 width=4) Index Cond: (((caseNo)::bpchar = '2006TR'::bpchar) AND ((caseNo)::bpchar '2006TS'::bpchar) AND ((countyNo)::smallint = ($0)::smallint)) Filter: ((caseNo)::bpchar ~~ '2006TR%'::text) We don't currently try to flatten EXISTS into a unique/join plan as we do for IN. I seem to recall not doing so when I rewrote IN planning because I didn't think it would be exactly semantically equivalent, but that was awhile ago. Right at the moment it seems like it ought to be equivalent as
Re: [HACKERS] EXISTS optimization
On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote: We don't currently try to flatten EXISTS into a unique/join plan as we do for IN. I seem to recall not doing so when I rewrote IN planning because I didn't think it would be exactly semantically equivalent, but that was awhile ago. Right at the moment it seems like it ought to be equivalent as long as the comparison operators are strict. Wasn't it due to the fact that IN needs to scan through all possibilites anyway because of its interaction with NULL, whereas EXISTS can stop at the first row? That would mean the subquery to be materialised would not be equivalent if it called any non-immutable functions. It's also much less clear to be a win in the EXISTs case. But then, that's a costs issue the planner can deal with... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] EXISTS optimization
I don't understand -- TRUE OR UNKNOWN evaluates to TRUE, so why would the IN need to continue? I'm not quite following the rest; could you elaborate or give an example? (Sorry if I'm lagging behind the rest of the class here.) -Kevin Martijn van Oosterhout kleptog@svana.org 03/23/07 5:26 PM On Fri, Mar 23, 2007 at 05:49:42PM -0400, Tom Lane wrote: We don't currently try to flatten EXISTS into a unique/join plan as we do for IN. I seem to recall not doing so when I rewrote IN planning because I didn't think it would be exactly semantically equivalent, but that was awhile ago. Right at the moment it seems like it ought to be equivalent as long as the comparison operators are strict. Wasn't it due to the fact that IN needs to scan through all possibilites anyway because of its interaction with NULL, whereas EXISTS can stop at the first row? That would mean the subquery to be materialised would not be equivalent if it called any non-immutable functions. It's also much less clear to be a win in the EXISTs case. But then, that's a costs issue the planner can deal with... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] [HACKERS] EXISTS optimization
On Fri, Mar 23, 2007 at 5:26 PM, in message [EMAIL PROTECTED], Kevin Grittner [EMAIL PROTECTED] wrote: I tried something which seems equivalent, but it is running for a very long time. I'll show it with just the explain while I wait to see how long the explain analyze takes. explain SELECT A.adjustmentNo, A.tranNo, A.countyNo, H.date, H.userId, H.time FROM Adjustment A JOIN TranHeader H ON (H.tranId = A.adjustmentNo AND H.countyNo = A.countyNo AND H.tranNo = A.tranNo) WHERE H.tranType = 'A' AND A.date DATE '2006- 01- 01' AND H.countyNo = 66 AND A.countyNo = 66 AND H.tranNo IN ( SELECT D.tranNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' AND D.countyNo = H.countyNo ) ; explain analyze results: Nested Loop (cost=0.00..181673.08 rows=1 width=46) (actual time=42224.077..964266.969 rows=2209 loops=1) Join Filter: ((H.tranId)::bpchar = (A.adjustmentNo)::bpchar) - Seq Scan on Adjustment A (cost=0.00..2384.27 rows=11733 width=22) (actual time=15.355..146.620 rows=13003 loops=1) Filter: (((date)::date '2006-01-01'::date) AND ((countyNo)::smallint = 66)) - Index Scan using TranHeader_pkey on TranHeader H (cost=0.00..15.27 rows=1 width=46) (actual time=74.141..74.141 rows=0 loops=13003) Index Cond: (((H.tranNo)::integer = (A.tranNo)::integer) AND ((H.countyNo)::smallint = 66)) Filter: (((tranType)::bpchar = 'A'::bpchar) AND (subplan)) SubPlan - Index Scan using TranDetail_TranDetCaseNo on TranDetail D (cost=0.00..27.66 rows=20 width=4) (actual time=0.039..58.234 rows=42342 loops=13003) Index Cond: (((caseNo)::bpchar = '2006TR'::bpchar) AND ((caseNo)::bpchar '2006TS'::bpchar) AND ((countyNo)::smallint = ($0)::smallint)) Filter: ((caseNo)::bpchar ~~ '2006TR%'::text) Total runtime: 964269.555 ms ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] EXISTS optimization
Kevin Grittner [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] wrote: If you want that, try rewriting the EXISTS to an IN: AND (H.tranNo, H.countyNo) IN ( SELECT D.tranNo, D.countyNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' ) That's the good news. The bad news is that I operate under a management portability dictate which doesn't currently allow that syntax, since not all of the products they want to cover support it. Which part of it don't they like --- the multiple IN-comparisons? I tried something which seems equivalent, but it is running for a very long time. AND H.tranNo IN ( SELECT D.tranNo FROM TranDetail D WHERE D.caseNo LIKE '2006TR%' AND D.countyNo = H.countyNo ) No, that's not gonna accomplish a darn thing, because you've still got a correlated subquery (ie, a reference to outer H) and so turning the IN into a join doesn't work. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Time to package 8.2.4
Hello, We have had several customers get bit by the 8.2.3 stats collector bug. It is also starting to get reported in areas such as IRC. The really bad thing about this bug is that you won't know what is wrong unless you know where to look, PostgreSQL will just appear slow and tying up resources. Can we please package 8.2.4 and get it out the door? Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 1: 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
Re: [PERFORM] [HACKERS] EXISTS optimization
On Fri, Mar 23, 2007 at 6:04 PM, in message [EMAIL PROTECTED], Peter Kovacs [EMAIL PROTECTED] wrote: On 3/23/07, Kevin Grittner [EMAIL PROTECTED] wrote: [...] That's the good news. The bad news is that I operate under a management portability dictate which doesn't currently allow that syntax, since not all of the products they want to It doesn't really touch the substance, but I am curious: are you not even allowed to discriminate between products in your code like: if db is 'postresql' then ... else ... ? What would be the rationale for that? Anybody who's not curious about that should skip the rest of this email. Management has simply given a mandate that the software be independent of OS and database vendor, and to use Java to help with the OS independence. I have to admit that I am the architect of the database independence solution that was devised. (The choice of Java for the OS independence has been very successful. We have run our bytecode on HP-UX, Windows, Sun Solaris, and various flavors of Linux without having to compile different versions of the bytecode. Other than when people get careless with case sensitivity on file names or with path separators, it just drops right in and runs. For the data side, we write all of our queries in ANSI SQL in our own query tool, parse it, and generate Java classes to run it. The ANSI source is broken down to lowest common denominator queries, with all procedural code covered in the Java query classes. So we have stored procedures which can be called, triggers that fire, etc. in Java, issuing SELECT, INSERT, UPDATE, DELETE statements to the database. This allows us to funnel all DML through a few primitive routines which capture before and after images and save them in our own transaction image tables. We use this to replicate from our 72 county databases, which are the official court record, to multiple central databases, and a transaction repository, used for auditing case activity and assisting with failure recovery. The problem with burying 'if db is MaxDB', 'if db is SQLServer', 'if db is PostgreSQL' everywhere is that you have no idea what to do when you then want to drop in some different product. We have a plugin layer to manage known areas of differences which aren't handled cleanly by JDBC, where the default behavior is ANSI-compliant, and a few dozen to a few hundred lines need to be written to modify that default support a new database product. (Of course, each one so far has brought in a few surprises, making the plugin layer just a little bit thicker.) So, to support some new syntax, we have to update our parser, and have a way to generate code which runs on all the candidate database products, either directly or through a plugin layer. If any of the products don't support multi-value row value constructors, I have a hard time seeing a good way to cover that with the plugin. On the subject issue, I'm pretty sure it would actually be less work for me to modify the PostgreSQL optimizer to efficiently handle the syntax we do support than to try to bend row value constructors to a syntax that is supported on other database products. And, by the way, I did take a shot on getting them to commit to PostgreSQL as the long-term solution, and relax the portability rules. No sale. Perhaps when everything is converted to PostgreSQL and working for a while they may reconsider. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
