[HACKERS] Make length(char(n)) return 'true' length
The attached patch changes the existing behaviour of length(char(n)).
Currently, this is what happens:
template1=# select length('blah'::char(10));
length
10
(1 row)
With this patch:
template1=# select length('blah'::char(10));
length
4
(1 row)
This behaviour was proposed by Tom back in November last year. (I have
tried to handle multibyte correctly but probably haven't -- hence my email
hackers instead of patches).
The spec doesn't give us any insight (as far as I can tell) as to how we
should do it length(char(n)), but the above seems consistent with other
parts of the code (eg, comparison functions, concatenation).
SQL200X has these choice paragraphs for those who are interested:
length expression returns the length of a given character string,
as an exact numeric value, in characters or octets according to the
choice of function.
And:
the result is the number of explicit or implicit
char length units in char length expression, counted in
accordance with the definition of those units in the relevant
normatively referenced document.
I have no idea what the 'normatively referenced document' is, but grep-ing
through all of SQL200X, 99 and 92 didn't reveal anything too interesting.
GavinIndex: src/backend/utils/adt/varchar.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/utils/adt/varchar.c,v
retrieving revision 1.103
diff -2 -c -r1.103 varchar.c
*** src/backend/utils/adt/varchar.c 29 Nov 2003 19:51:59 - 1.103
--- src/backend/utils/adt/varchar.c 27 Jan 2004 06:18:33 -
***
*** 511,522
{
BpChar *arg = PG_GETARG_BPCHAR_P(0);
/* optimization for single byte encoding */
if (pg_database_encoding_max_length() = 1)
! PG_RETURN_INT32(VARSIZE(arg) - VARHDRSZ);
! PG_RETURN_INT32(
! pg_mbstrlen_with_len(VARDATA(arg), VARSIZE(arg) - VARHDRSZ)
! );
}
--- 511,530
{
BpChar *arg = PG_GETARG_BPCHAR_P(0);
+ int len;
+ char *str;
+ len = bcTruelen(arg);
+
/* optimization for single byte encoding */
if (pg_database_encoding_max_length() = 1)
! PG_RETURN_INT32(len);
! str = palloc(len);
! StrNCpy(str,VARDATA(arg),len);
!
! len = pg_mbstrlen_with_len(str, len);
! pfree(str);
!
! PG_RETURN_INT32(len);
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Most urgent
My server just had a bad crash... At reboot time, the pg_xlog filesystem didn't mount... The I have those errors at startup... is there anything can do to recover? Jan 27 13:24:11 server postgres[3469]: [3-1] LOG: checkpoint record is at 7/B7668 Jan 27 13:24:11 server postgres[3469]: [4-1] LOG: redo record is at 7/A08; undo record is at 0/0; shutdown FALSE Jan 27 13:24:11 server postgres[3469]: [5-1] LOG: next transaction ID: 21363395; next OID: 85839615 Jan 27 13:24:11 server postgres[3469]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Jan 27 13:24:11 server postgres[3469]: [7-1] LOG: redo starts at 7/A08 Jan 27 13:24:11 server postgres[3469]: [8-1] PANIC: could not read block 222 of relation 79389213: Périphérique ou adresse inexistants Jan 27 13:24:11 server postgres[3467]: [2-1] LOG: startup process (PID 3469) was terminated by signal 6 Jan 27 13:24:11 server postgres[3467]: [3-1] LOG: aborting startup due to startup process failure Jan 27 13:27:04 server postgres[3569]: [1-1] LOG: could not bind IPv6 socket: Réseau inaccessible Jan 27 13:27:04 server postgres[3569]: [1-2] HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. Jan 27 13:27:04 server postgres[3571]: [2-1] LOG: database system was interrupted while in recovery at 2004-01-27 13:24:11 MET Jan 27 13:27:04 server postgres[3571]: [2-2] HINT: This probably means that some data is corrupted and you will have to use the last backup for recovery. Jan 27 13:27:04 server postgres[3571]: [3-1] LOG: checkpoint record is at 7/B7668 Jan 27 13:27:04 server postgres[3571]: [4-1] LOG: redo record is at 7/A08; undo record is at 0/0; shutdown FALSE Jan 27 13:27:04 server postgres[3571]: [5-1] LOG: next transaction ID: 21363395; next OID: 85839615 Jan 27 13:27:04 server postgres[3571]: [6-1] LOG: database system was not properly shut down; automatic recovery in progress Jan 27 13:27:04 server postgres[3571]: [7-1] LOG: redo starts at 7/A08 Jan 27 13:27:04 server postgres[3571]: [8-1] PANIC: could not read block 222 of relation 79389213: Périphérique ou adresse inexistants Jan 27 13:27:04 server postgres[3569]: [2-1] LOG: startup process (PID 3571) was terminated by signal 6 Jan 27 13:27:04 server postgres[3569]: [3-1] LOG: aborting startup due to startup process failure -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(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] returning PGresult as xml
On Jan 25, 2004, at 3:07 AM, Brian Moore wrote: it's been said that converting a PGresult into xml is trivial and that's why it hasn't been done in the codebase as of yet. i have seen much code that writes xml, and many mistakes are made. most often improper escaping, or writing to a schema/DTD that has not been well-thought out. the transformation into xml is not difficult, but it does require attention to detail. The escaping, at any rate, is trivial if you use a proper API. It sounds like your code is not using any XML API, given that you have not mentioned adding dependencies to libpq and that you've mentioned your own hashtable algorithm. It would be much easier if you did so, though I imagine the additional dependency would mean it would not be accepted into libpq. PGresult num_rows='1' num_cols='2' col_desc num='0' type='int4' format='text' name='foo' / col_desc num='1' type='int4' format='text' name='bar' / row num='0' col num='0'1/col col num='1'2/col /row /PGresult How would you filter for a column in XSLT based on column name with this schema? It's certainly not trivial. I have similar code, and I included the column name as an attribute in each column element for this reason. I also used the java.sql type names rather than PostgreSQL ones, as my code is not specific to PostgreSQL. i would expect that integration would look something like exposing from libpq a function that looks something like: const char *PGresult_as_xml(PGresult *result, int include_dtd); Ugh. So it returns the whole thing as one big string? That won't hold up well if your resultset is large. A better way would be to pump out SAX events. This is what I did for three reasons: 1) The escaping becomes trivial, as mentioned above. In fact, not only does SAX escape things correctly, but it makes you explicitly specify that the string you're giving it is character data, an element name, an attribute name, an attribute value, etc, and handles everything properly based on that. So you'd really have to work to screw it up, unlike code that just does like printf(elem foo='%s' bar='%s'%s/elem, xml_attr_escape(foo_val), xml_attr_escape(bar_val), xml_char_escape(elem_val)); where it would be quite easy to lose track of what needs to be escaped how, what variables are already escaped, etc. 2) It can stream large result sets, provided that the next stage supports doing so. Certainly a raw SAX serializer would, also some XSLT stylesheets with Xalan, and STX/Joost is designed for streaming transformations. 3) If the next stage is a transformation, this makes it unnecessary to serialize and parse the data between. So the SAX way is faster. You're welcome to take a look at my code. I imagine it will not be directly useful to you, as it is written in Java, but I have a live example which puts this stuff to use. Designing an acceptable API and schema is always much easier when you see how it is put to use. http://www.slamb.org/projects/xmldb/ - my (so far poorly-named) xmldb project, which includes the org.slamb.xmldb.ResultSetProducer class to transform a java.sql.ResultSet to SAX events in my resultset schema. http://www.slamb.org/svn/repos/projects/xmldb/src/java/org/slamb/ xmldb/ResultSetProducer.java - source code for said class http://www.slamb.org/projects/mb/ - a message board which uses this code and some XSLT https://www.slamb.org/mb/ - a live example of said message board http://www.slamb.org/svn/repos/projects/mb/src/WEB-INF/xsl/ resultset.xsl - simple XSLT to take an arbitrary resultset and convert it to an HTML table http://www.slamb.org/svn/repos/projects/mb/src/WEB-INF/xsl/main.xsl - an example XSLT file that inherits this and then provides exceptions for a couple columns (not displaying the id column, instead including it as a hyperlink in the name column). Good luck. Scott Lamb ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] returning PGresult as xml
Scott Lamb wrote:
On Jan 25, 2004, at 3:07 AM, Brian Moore wrote:
PGresult num_rows='1' num_cols='2'
col_desc num='0' type='int4' format='text' name='foo' /
col_desc num='1' type='int4' format='text' name='bar' /
row num='0'
col num='0'1/col
col num='1'2/col
/row
/PGresult
How would you filter for a column in XSLT based on column name with
this schema? It's certainly not trivial. I have similar code, and I
included the column name as an attribute in each column element for
this reason.
Close to trivial if you set up a key on the col-desc elements, I should
think. Maybe something like:
xsl:key name=coldesc match=col-desc use=@num /
...
xsl:for-each select= key('coldesc',@num)/@name = 'colname'
...
Alternatively you can get there using the parent and preceding-sibling
axes, but it's less clear.
cheers
andrew
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recursive optimization of IN subqueries
Tom Lane writes In the second place, what the code is doing is dependent on an understanding of the semantics of IN; I'm not sure it's applicable to, say, WHERE outervar ANY (SELECT innervar FROM ...) and it's definitely not applicable to WHERE outervar ALL (SELECT innervar FROM ...) In particular, the optimization paths that involve unique-ifying the subselect output and then using it as the outer side of a join would definitely not work for these sorts of things. I'm not sure if I've understood you correctly in the section above. Are you saying that these types of queries don't have a meaningful or defined response? Or just that they wouldn't be very well optimized as a result of the unique-ifying code changes? Or have I just mis-read the thread... My understanding is that in ANSI SQL99, the expression expression ALL (subquery) - is TRUE when expression is greater than every value in the set of values returned by subquery. - is TRUE if subquery returns no values. The expression expression ANY (subquery) - is TRUE when expression is greater than at least one value of the set of values returned by subquery. - is FALSE if subsquery returns no values. (As supported by Oracle 9iv2 and Teradata v2r5.0.) Best regards, Simon ---(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] Recursive optimization of IN subqueries
Tom Lane writes In the second place, what the code is doing is dependent on an understanding of the semantics of IN; I'm not sure it's applicable to, say, WHERE outervar ANY (SELECT innervar FROM ...) and it's definitely not applicable to WHERE outervar ALL (SELECT innervar FROM ...) In particular, the optimization paths that involve unique-ifying the subselect output and then using it as the outer side of a join would definitely not work for these sorts of things. I'm not sure if I've understood you correctly in the section above. Are you saying that these types of queries don't have a meaningful or defined response? Or just that they wouldn't be very well optimized as a result of the unique-ifying code changes? Or have I just mis-read the thread... My understanding is that in ANSI SQL99, the expression expression ALL (subquery) - is TRUE when expression is greater than every value in the set of values returned by subquery. - is TRUE if subquery returns no values. The expression expression ANY (subquery) - is TRUE when expression is greater than at least one value of the set of values returned by subquery. - is FALSE if subsquery returns no values. (As supported by Oracle 9iv2 and Teradata v2r5.0.) Best regards, Simon ---(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] building plperl on 7.4.1
I tried to build plperl on 7.4.1,
On my system
perl -MConfig -e 'print $Config{ccdlflags}'
returns
-rdynamic -Wl,-rpath,/usr/lib/perl5/5.8.0/i386-linux-thread-multi/CORE
however the build ends up using
-rpath,$prefix/lib
Dave
--
Dave Cramer
519 939 0336
ICQ # 1467551
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] index scan with functional indexes
I'm curious what the result of a reverse index does on a table with url
like data, so I did the following
create function fn_strrev(text) returns text as 'return reverse($_[0])'
language 'plperl' with (iscachable);
create index r_url_idx on url( fn_strrev(url));
vacuum analyze;
explain select * from url where url like fn_strrev('%beta12.html');
QUERY PLAN
-
Seq Scan on url (cost=0.00..13281.70 rows=1 width=454)
Filter: ((url)::text ~~ 'lmth.21ateb%'::text)
Is it possible to get the planner to use an index scan ?
How?
the db is using locale 'C'
--
Dave Cramer
519 939 0336
ICQ # 1467551
---(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] Recursive optimization of IN subqueries
Simon Riggs wrote: Tom Lane writes In the second place, what the code is doing is dependent on an understanding of the semantics of IN; I'm not sure it's applicable to, say, WHERE outervar ANY (SELECT innervar FROM ...) and it's definitely not applicable to WHERE outervar ALL (SELECT innervar FROM ...) In particular, the optimization paths that involve unique-ifying the subselect output and then using it as the outer side of a join would definitely not work for these sorts of things. I'm not sure if I've understood you correctly in the section above. Are you saying that these types of queries don't have a meaningful or defined response? Or just that they wouldn't be very well optimized as a result of the unique-ifying code changes? Or have I just mis-read the thread... I think Tom is refering to the context of the specific optimization. The optimization we are discussing does nothing to correlated subqueries, and a uncorrolated subquery with ALL/ANY is actually a computed constant and not a join. -- Dennis
Re: [HACKERS] LWLock/ShmemIndex startup question
On Fri, 2004-01-23 at 00:21, Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
Andrew Dunstan wrote:
AFAIK the only target build environment for Windows right now is MinGW/gcc
If anyone knows how to get the M$ compilers to work nicely with our build
system that might be interesting, but probably at a later stage.
MS C compiler usage would require gmake and a whole host of other stuff
that doesn't seem worth doing.
Um, good point. Porting our Makefiles to anything but gmake seems
completely out of the question. So unless someone has a Windows build
environment that has gmake but not gcc, this is moot.
Tom, check out
http://archives.postgresql.org/pgsql-ports/2004-01/msg00017.php
Looking at the interix website, they seem to have gmake but no gcc,
which seems to fit into your scenario above. Incedentally they do
distribute a compiled PostgreSQL with their packages, though it's based
on 7.2 according to the author.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Recursive optimization of IN subqueries
My mistake then. Better to check than let a logical hole in Thanks for letting me know, Simon -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dennis Haney Sent: Tuesday, January 27, 2004 14:33 To: [EMAIL PROTECTED] Cc: 'Tom Lane'; [EMAIL PROTECTED] Subject: Re: [HACKERS] Recursive optimization of IN subqueries Simon Riggs wrote: Tom Lane writesIn the second place, what the code is doing is dependent on anunderstandingof the semantics of IN; I'm not sure it's applicable to, say, WHERE outervar ANY (SELECT innervar FROM ...)and it's definitely not applicable to WHERE outervar ALL (SELECT innervar FROM ...)In particular, the optimization paths that involve unique-ifying thesubselect output and then using it as the outer side of a join woulddefinitely not work for these sorts of things. I'm not sure if I've understood you correctly in the section above. Areyou saying that these types of queries don't have a meaningful ordefined response? Or just that they wouldn't be very well optimized as aresult of the unique-ifying code changes? Or have I just mis-read thethread... I think Tom is refering to the context of the specific optimization. The optimization we are discussing does nothing to correlated subqueries, and a uncorrolated subquery with ALL/ANY is actually a computed constant and not a join. -- Dennis
[HACKERS] Another optimizer question
Hi Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) select a.* from test1 a, (select id from test1 order by num) as b where a.id = b.id; There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? -- Dennis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Function call
I've been looking in the sql200x draft and there are no function calls with named arguments. Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I don't think it's very hard to add. I've been playing a little with it, making myself familiar with the code. Before making an implementation I just want to make sure that we really want an implementation. It's a neat feture, but it's not that very important so I wanted to bring it up. If one combine it with default values on parameters, then it's more useful since you easier can leave out arguments when you use the ident = expr syntax. Default values is also not in the standard. -- /Dennis Björklund ---(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] Most urgent
[EMAIL PROTECTED] writes: My server just had a bad crash... At reboot time, the pg_xlog filesystem didn't mount... So mount it... If you're trying to say that the xlog is irretrievably hosed, then see pg_resetxlog, and be prepared to spend some time checking to see if anything got corrupted. But first try to get your xlog back. 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] Function call
Dennis Bjorklund wrote: Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I like it very much, and I think mostly everyone else does, too. It's just a question of what syntax to use. Personally, I would be OK with =. The SQL standard already attaches a special meaning to - (object dereference, like in C), so I'd just avoid all arrows as operator names. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Most urgent
On Tue, 27 Jan 2004, Tom Lane wrote: Date: Tue, 27 Jan 2004 12:02:04 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] Most urgent [EMAIL PROTECTED] writes: My server just had a bad crash... At reboot time, the pg_xlog filesystem didn't mount... So mount it... Of course I did... As you can see from the logs I sent, it didn't help If you're trying to say that the xlog is irretrievably hosed, then see pg_resetxlog, and be prepared to spend some time checking to see if anything got corrupted. But first try to get your xlog back. Well, apparently, the fs itself was badly corrupted (should'nt happen, I'm using fsync + ODM mirroring) So I ended up reinstalling every thing and restart all db from backups. BTW, I still have statistics buffer full message (never very far from a reclycling of log file) maybe it's just a conincidence regards, tom lane -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Incorrect START TRANSACTION implementation
SQL99 part 2 clause 16.1 contains this note: NOTE 327 - The characteristics of a transaction begun by a start transaction statement are as specified in these General Rules regardless of the characteristics specified by any preceding set transaction statement. That is, even if one or more characteristics are omitted by the start transaction statement, the defaults specified in the Syntax Rules of this Subclause are effective and are not affected by any (preceding) set transaction statement. In other words, this should work: peter=# set session characteristics as transaction read only; SET peter=# start transaction; START TRANSACTION peter=# create table test (a int); ERROR: transaction is read-only because the default of a bare start transaction is read write. I propose that we change this to follow the standard and keep the traditional behavior for BEGIN only. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Recursive optimization of IN subqueries
Simon Riggs [EMAIL PROTECTED] writes: Tom Lane writes In particular, the optimization paths that involve unique-ifying the subselect output and then using it as the outer side of a join would definitely not work for these sorts of things. I'm not sure if I've understood you correctly in the section above. Are you saying that these types of queries don't have a meaningful or defined response? Or just that they wouldn't be very well optimized as a result of the unique-ifying code changes? I mean that if the unique-ifying implementation were used, it'd deliver the wrong answer (too many rows out). You could possibly carry through a set of extensions to check which kind of sub-SELECT was in use and not apply transformations that aren't correct, but it'd be a great deal more complexity for something of marginal value. As far as I've seen, people don't use inequalities in ANY/ALL subselects very much, and so I'm not excited about complicating the planner to support them better. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] index scan with functional indexes
Dave Cramer [EMAIL PROTECTED] writes:
create index r_url_idx on url( fn_strrev(url));
explain select * from url where url like fn_strrev('%beta12.html');
QUERY PLAN
-
Seq Scan on url (cost=0.00..13281.70 rows=1 width=454)
Filter: ((url)::text ~~ 'lmth.21ateb%'::text)
Is it possible to get the planner to use an index scan ?
Sure, but not that way. Try fn_strrev(url) like something.
You have to compare the indexed value to something...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Another optimizer question
On Tue, Jan 27, 2004 at 17:27:25 +0100, Dennis Haney [EMAIL PROTECTED] wrote: Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) Yes. It is important when a limit or distinct on clause is used in a subquery. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] index scan with functional indexes
same answer
davec=# show enable_seqscan;
enable_seqscan
off
(1 row)
davec=# explain analyze select * from url where fn_strrev(url) like
'%beta12.html';
QUERY PLAN
---
Seq Scan on url (cost=1.00..100013533.04 rows=503 width=454)
(actual time=3851.636..3851.636 rows=0 loops=1)
Filter: (fn_strrev((url)::text) ~~ '%beta12.html'::text)
Total runtime: 3851.712 ms
(3 rows)
On Tue, 2004-01-27 at 12:33, Tom Lane wrote:
Dave Cramer [EMAIL PROTECTED] writes:
create index r_url_idx on url( fn_strrev(url));
explain select * from url where url like fn_strrev('%beta12.html');
QUERY PLAN
-
Seq Scan on url (cost=0.00..13281.70 rows=1 width=454)
Filter: ((url)::text ~~ 'lmth.21ateb%'::text)
Is it possible to get the planner to use an index scan ?
Sure, but not that way. Try fn_strrev(url) like something.
You have to compare the indexed value to something...
regards, tom lane
--
Dave Cramer
519 939 0336
ICQ # 1467551
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index scan with functional indexes
On Tue, 27 Jan 2004, Dave Cramer wrote: same answer davec=# show enable_seqscan; enable_seqscan off (1 row) davec=# explain analyze select * from url where fn_strrev(url) like '%beta12.html'; That's still an unanchored like clause, besides I think that would get urls that begin with lmth.21ateb. I think the condition you want would be: fn_strrev(url) like 'lmth.21ateb%' ---(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] index scan with functional indexes
Dave Cramer [EMAIL PROTECTED] writes:
davec=# explain analyze select * from url where fn_strrev(url) like
'%beta12.html';
Don't you need the % at the right end to have an indexable plan?
I suspect that both of your tries so far are actually semantically
wrong, and that what you intend is
select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Another optimizer question
Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. If you are saying you don't want the sort to occur, why did you write it? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] index scan with functional indexes
Tried, all the suggestions
--dc--
davec=# explain analyze select * from url where fn_strrev(url) like
fn_strrev('%beta12.html');
QUERY PLAN
Seq Scan on url (cost=1.00..100013533.04 rows=503 width=454)
(actual time=1416.448..3817.221 rows=12 loops=1)
Filter: (fn_strrev((url)::text) ~~ 'lmth.21ateb%'::text)
Total runtime: 3817.315 ms
(3 rows)
davec=# explain analyze select * from url where fn_strrev(url) like
'lmth.21ateb%';
QUERY PLAN
Seq Scan on url (cost=1.00..100013533.04 rows=503 width=454)
(actual time=1412.181..3843.998 rows=12 loops=1)
Filter: (fn_strrev((url)::text) ~~ 'lmth.21ateb%'::text)
Total runtime: 3844.106 ms
(3 rows)
davec=# explain analyze select * from url where fn_strrev(url) like
'%lmth.21ateb';
QUERY PLAN
---
Seq Scan on url (cost=1.00..100013533.04 rows=503 width=454)
(actual time=3853.501..3853.501 rows=0 loops=1)
Filter: (fn_strrev((url)::text) ~~ '%lmth.21ateb'::text)
Total runtime: 3853.583 ms
(3 rows)
On Tue, 2004-01-27 at 13:02, Tom Lane wrote:
Dave Cramer [EMAIL PROTECTED] writes:
davec=# explain analyze select * from url where fn_strrev(url) like
'%beta12.html';
Don't you need the % at the right end to have an indexable plan?
I suspect that both of your tries so far are actually semantically
wrong, and that what you intend is
select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
regards, tom lane
--
Dave Cramer
519 939 0336
ICQ # 1467551
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Function call
Peter Eisentraut [EMAIL PROTECTED] writes: Dennis Bjorklund wrote: Thinking more about it, I'm not sure if it really is an important addition at all. I've got a number of requests for the feature. so there are people that want it, that much I know. I like it very much, and I think mostly everyone else does, too. It's just a question of what syntax to use. There are some pretty severe implementation problems that I haven't seen mentioned yet. In particular, how will you avoid individually trawling through every function with a matching name to try to match up the arguments? The index on proargtypes won't help you if you don't know what order the arguments are actually in. And I think the heuristics in func_select_candidate() that involve comparing matches at the same argument position will break down completely. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) regards, tom lane ---(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
Re: [HACKERS] Incorrect START TRANSACTION implementation
Peter Eisentraut [EMAIL PROTECTED] writes: SQL99 part 2 clause 16.1 contains this note: NOTE 327 - The characteristics of a transaction begun by a start transaction statement are as specified in these General Rules regardless of the characteristics specified by any preceding set transaction statement. That is, even if one or more characteristics are omitted by the start transaction statement, the defaults specified in the Syntax Rules of this Subclause are effective and are not affected by any (preceding) set transaction statement. In other words, this should work: peter=# set session characteristics as transaction read only; SET peter=# start transaction; START TRANSACTION peter=# create table test (a int); ERROR: transaction is read-only Are you sure you're reading that correctly? It says set transaction statement not set session characteristics statement. What use would SET SESSION CHARACTERISTICS have at all, if it's ignored by START TRANSACTION? It appears to me that the spec is saying that *this* should work: regression=# set transaction read only; SET regression=# start transaction; START TRANSACTION regression=# create table test (a int); CREATE TABLE ... which it does. 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] index scan with functional indexes
Dave Cramer [EMAIL PROTECTED] writes: Tried, all the suggestions Mph. It works for me... what PG version are you using exactly, and are you certain you've selected C locale? (Do you get LIKE optimization on plain indexes?) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] index scan with functional indexes
I'm using 7.4.1, the db was initdb --locale='C' and no I don't get them on plain indexes Dave On Tue, 2004-01-27 at 13:28, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: Tried, all the suggestions Mph. It works for me... what PG version are you using exactly, and are you certain you've selected C locale? (Do you get LIKE optimization on plain indexes?) regards, tom lane -- Dave Cramer 519 939 0336 ICQ # 1467551 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Extending SET SESSION AUTHORIZATION
Ezra Epstein [EMAIL PROTECTED] writes: I'd like to extend SET SESSION AUTHORIZATION to support a form which takes a password. Uh, a password? What purpose would that serve? For exactly the opposite usage: allowing a non-privileged user to take on a different authorization IFF a password is also supplied. This allows a user to use an existing connection (so, for example, connection pooling works) and not require a high priv'd account to then act as a specific (and specifically priv'd) user of the system. I do not think SET SESSION AUTH is a suitable replacement for logging in. For one thing, it doesn't apply per-user GUC settings. For another, using it this way in a pooling environment would be completely insecure --- what if you forget to log out, or your attempt to do so is dropped because it was inside a failed transaction block? Another objection to doing things this way is that it would just about force people to embed passwords into their SQL scripts, creating another serious source of insecurity. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] index scan with functional indexes
Dave Cramer [EMAIL PROTECTED] writes: I'm using 7.4.1, the db was initdb --locale='C' and no I don't get them on plain indexes Oh? If it's 7.4 then you can confirm the locale selection with show lc_collate and show lc_ctype (I think the first of these is what the LIKE optimization checks). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mixing threaded and non-threaded
On Fri, Jan 23, 2004 at 10:03:30PM -0500, Bruce Momjian wrote: Steve Atkins wrote: When I rebuilt libpq to use threads, I started seeing a bunch of weird failures in many of the older applications. The change in libpq meant that libpthread was being dynamically linked into the non-thread-aware applications, leading to some mutex deadlocks in their signal handlers, hanging those applications. There doesn't seem to be any tidy way to build and use both threaded and non-threaded libpq on the same system (LD_LIBRARY_PATH hacks aren't really viable for distributed code). Is there something I'm missing? No, there is not. We could compile two versions, and have you specify the threaded version only when you want it, but only some operating systems have that distinction, so then we would have to identical libraries on some platforms, and different ones on others, and that seemed pretty confusing. Of course, we can always revisit this. (If it's relevant, the OS in question is RedHat Linux, but I'm maintaining the same suite of apps on several other architectures.) This is interesting. I had not considered that libpq's calls to libpthread would cause problems. In fact, libpq shouldn't be doing anything special with pthread except for a few calls used in port/thread.c. Yes, libpqs use of actual use of pthread seems pretty harmless. However, the issue we always were worried about was that linking against libpthread would cause some unexpected thread calls in the application, and it looks like that is exactly what you are seeing. In fact, it sounds like it is the calls to allow synchronous signals to be delivered to the thread that generated them that might be the new change you are seeing. Exactly that, yes. My guess is that creating applications against the non-thread libpq and then replacing it with a threaded libpq is your problem. Yes. It seems to make no difference whether the application is rebuilt or not. It's pulling libpthread into a non-thread-aware application that's the problem. The only fix that would allow the non-threaded application to work with a thread-safe libpq would be to rewrite it to be a threaded application with a single active thread. I guess the question is whether you would like to have two libpq's and have to decide at link time if you wanted threading, or just have one libpq and make sure you recompile if you change the threading behavior of the library. We considered the later to be clearer. Recompiling doesn't neccesarily help unless the application is also rewritten. Also, if there are dozens of non-threaded applications using libpq on a system (possibly installed via rpms or equivalent) then replacing the system libpq could break something else. For now I'm just building and distributing two different libpqs and choosing between them with rpath hacks (yes, renaming one of them might be easier, but I'm specifying rpath explicitly anyway for other reasons). That seems to be working just fine for me. If there are multiple applications on the system using PostgreSQL we really don't want to break some of them if libpq is rebuilt to support a new one. Probably worth a mention in the documentation at least. Cheers, Steve ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Another optimizer question
Bruno Wolff III wrote: On Tue, Jan 27, 2004 at 17:27:25 +0100, Dennis Haney [EMAIL PROTECTED] wrote: Is it just me, or is there any way a sort could be relevant in a subquery? (except on queries containing volatile functions) Yes. It is important when a limit or distinct on clause is used in a subquery. Yup, but queries with those are also ignored, so I'm only talking about explicit 'order by' without any fancy stuff ;) -- Dennis
Re: [HACKERS] Mixing threaded and non-threaded
Steve Atkins wrote: My guess is that creating applications against the non-thread libpq and then replacing it with a threaded libpq is your problem. Yes. It seems to make no difference whether the application is rebuilt or not. It's pulling libpthread into a non-thread-aware application that's the problem. The only fix that would allow the non-threaded application to work with a thread-safe libpq would be to rewrite it to be a threaded application with a single active thread. Woh, as far as I know, any application should run fine with -lpthread, threaded or not. What OS are you on? This is the first I have heard of this problem. I guess the question is whether you would like to have two libpq's and have to decide at link time if you wanted threading, or just have one libpq and make sure you recompile if you change the threading behavior of the library. We considered the later to be clearer. Recompiling doesn't neccesarily help unless the application is also rewritten. Also, if there are dozens of non-threaded applications using libpq on a system (possibly installed via rpms or equivalent) then replacing the system libpq could break something else. Why? How would you rewrite it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Another optimizer question
Tom Lane wrote: Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. Then why spend time doing it at all? If you are saying you don't want the sort to occur, I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... -- Dennis
Re: [HACKERS] index scan with functional indexes -- solved
Interesting it works now, and the good news is it is *WAY* faster, this
might be able to speed up marc's doc search by orders of magnitude
this is searching 100536 rows
select * from url where fn_strrev(url) like fn_strrev('%beta12.html');
1.57ms
explain select * from url where url like '%beta12.html';
3310.38 ms
Dave
On Tue, 2004-01-27 at 13:48, Tom Lane wrote:
Dave Cramer [EMAIL PROTECTED] writes:
I'm using 7.4.1, the db was initdb --locale='C'
and no I don't get them on plain indexes
Oh? If it's 7.4 then you can confirm the locale selection with
show lc_collate and show lc_ctype (I think the first of these
is what the LIKE optimization checks).
regards, tom lane
--
Dave Cramer
519 939 0336
ICQ # 1467551
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pl/pgSQL versus pl/Python
Hello, With the new preload option is there any benefit/drawback to using pl/Python versus pl/pgSQL? And no... I don't care that pl/Python is now considered untrusted. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Mixing threaded and non-threaded
On Tue, Jan 27, 2004 at 02:07:44PM -0500, Bruce Momjian wrote: Steve Atkins wrote: My guess is that creating applications against the non-thread libpq and then replacing it with a threaded libpq is your problem. Yes. It seems to make no difference whether the application is rebuilt or not. It's pulling libpthread into a non-thread-aware application that's the problem. The only fix that would allow the non-threaded application to work with a thread-safe libpq would be to rewrite it to be a threaded application with a single active thread. Woh, as far as I know, any application should run fine with -lpthread, threaded or not. What OS are you on? This is the first I have heard of this problem. Linux/i386, RedHat 7.something, gcc 2.96. Not my favorite configuration, but nothing particularly odd. I guess the question is whether you would like to have two libpq's and have to decide at link time if you wanted threading, or just have one libpq and make sure you recompile if you change the threading behavior of the library. We considered the later to be clearer. Recompiling doesn't neccesarily help unless the application is also rewritten. Also, if there are dozens of non-threaded applications using libpq on a system (possibly installed via rpms or equivalent) then replacing the system libpq could break something else. Why? How would you rewrite it? No idea. I've not looked at exactly what's going on, yet. It's perfectly possible that the problem I'm seeing is actually a bug in the underlying code - but it's been used in heavy production use for two years without pthread, and deadlocked immediately when built with pthread, so it's the sort of bug that could be elsewhere. It's a very complex application, so I'd really need to reduce it to a test case to narrow it down. A hint, though, might be that it's a multiprocess application with a single master process that controls dozens of child processes. When the master shuts down it asks all the children to shut down, and then it deadlocks in the SIGCHILD handler. I'll burrow a bit deeper when I get some time. Cheers, Steve ---(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] Incorrect START TRANSACTION implementation
Tom Lane wrote: Are you sure you're reading that correctly? Not anymore... :-/ Sorry for the noise. I had remembered that in some context set transaction and set session characteristics were interchangeable, but apparently I got it all mixed up. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote: In particular, how will you avoid individually trawling through every function with a matching name to try to match up the arguments? I don't think you can avoid that. But it's just done once to find the oid of the real function, so if it's used multiple times in the same query it's not that bad. In most cases you don't have that many functions with the same name anyway. I've looked at the current code that finds the correct function and it looked doable. But, I wont know until I make the implementation. And don't hold your breath, I can't work all the time on this, so a couple of days work might take a couple of weeks. I have some code already, but it's not nearly done. func_select_candidate() that involve comparing matches at the same argument position will break down completely. I was planning to reorder the arguments before the matching according to the function prototype so I can reuse the old select_candidate (more or less, the reordering needs to be done for each matching). But if it's not that simple I will just have to solve it in some more difficult way. In any case, the currect semantics will stay the same. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) The ambigious part i've complained about to the people that have asked me for the feature. Now I've come to the conclusion that it doesn't really matter if it's ambigious. If I can't find one function that match then I'll just throw an error. There are still a lot of cases where it is useful and where there are no amiguities. About the speed, how many functions do you have with the same name. I don't think I've ever seen more then 10 or something. It should not be that slow to iterate over that a couple of times (I hope). It will never be as fast as a direct call, with the correct types of course. Of course I still like it to be fast, but it can never be as fast. -- /Dennis Björklund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Peter Eisentraut wrote: just a question of what syntax to use. Personally, I would be OK with =. That's also what I'm leaning towards now. As Greg suggested, just making = a special case as a function parameter. And if one want's to call a function with an expression containing a = one have to write foo((x=23)) and not foo(x=23). That's the current plan I have, I think it's implementable in a not so ugly way. If that works out the symbol is not stolen, I would just borrow it a little when it's the top level of an expression in a function call position. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mixing threaded and non-threaded
On Jan 27, 2004, at 1:16 PM, Steve Atkins wrote: A hint, though, might be that it's a multiprocess application with a single master process that controls dozens of child processes. When the master shuts down it asks all the children to shut down, and then it deadlocks in the SIGCHILD handler. It's not safe to do anything interesting in a SIGCHLD handler, unless you have pretty severe restrictions on when the signal can arrive. Take a look at http://www.opengroup.org/onlinepubs/007904975/functions/ xsh_chap02_04.html. It contains a list of all the async signal-safe functions in SUSv3. It's a pretty short list. Notably absent are pthread_mutex_*() and malloc() (and anything that uses them). Scott Lamb ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Question about indexes
How feasible would it be to have a btree index on ctid? I'm thinking it ought to work simply enough for the normal case of insert/delet/update, but I'm not completely certain how vacuum, vacuum full, and cluster would interact. You may think this would be utterly useless, but I have a cunning plan. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: On Tue, 27 Jan 2004, Tom Lane wrote: func_select_candidate() that involve comparing matches at the same argument position will break down completely. I was planning to reorder the arguments before the matching according to the function prototype so I can reuse the old select_candidate (more or less, the reordering needs to be done for each matching). You're not thinking this through. The reordering might be different for each candidate. func_select_candidate depends on having some notion of the same argument position, but what does that mean in such a case? There are also some difficult questions raised by schemas and search paths. s1.f1(text, text) masks s2.f1(text, text) if s1 appears before s2 in your search path. But does s1.f1(foo text, bar text) mask s2.f1(baz text, xyzzy text)? Does your answer change depending on whether the actual call has parameter names or not? For that matter, should f1(foo text, bar text) and f1(baz text, xyzzy text) be considered to be different function signatures that ought to be permitted to coexist in a single schema? If actual parameter names are going to affect resolution of search-path ambiguity, it's hard to argue that the parameter names aren't part of the signature. What might be the best compromise is to treat parameter names as documentation *only*, that is, we insist that the parameters have to appear in the declared order in any case. All we do with the names in a call (if supplied) is check that they match the function declaration after we have resolved which function is meant using the existing methods. (Adding default values would make overloaded functions an order of magnitude slower yet, not to mention outright ambiguous.) The ambigious part i've complained about to the people that have asked me for the feature. Now I've come to the conclusion that it doesn't really matter if it's ambigious. Yes it does. About the speed, how many functions do you have with the same name. Try select proname, count(*) from pg_proc group by 1 order by 2 desc; Note that the ones at the top are pretty popular in usage, not only in having lots of variants. I don't think it's acceptable to take major speed hits in parsing them, especially not if the hit occurs whether one uses the named-parameters feature or not ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Pl/Java 1.0.0.b now avaiable on Linux 386 and Cygwin
The Pl/Java project that I'm working on is progressing quite nicely. The beta release that I just uploaded to GBorg at http://gborg.postgresql.org/project/pljava/projdisplay.php has most of the functionality that I have intended for the first stable release. If you are interested, please take a look at the readme file and the user guide. http://gborg.postgresql.org/project/pljava/genpage.php?readme http://gborg.postgresql.org/project/pljava/genpage.php?userguide Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Function call
On Tue, 27 Jan 2004, Tom Lane wrote: speed hits in parsing them, especially not if the hit occurs whether one uses the named-parameters feature or not ... I'll read the rest of the mail more careful tomorrow moring, I just want to point out directly that for calls that doesn't use named arguments you get the exact same speed as before. Except for an extra if() to check if there are named arguments. I don't understand why you think that this will affect the current behaviour. That is more or less the only thing I feel sure about, that it should not hurt anything that we have today. -- /Dennis Björklund ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Another optimizer question
Dennis Haney kirjutas T, 27.01.2004 kell 21:08: Tom Lane wrote: Dennis Haney [EMAIL PROTECTED] writes: There is no constraint on the order of 'a', so why is pull_up_subqueries explicitly ignoring subqueries that contain an 'order by'? Because there would be no place to apply the sort operation. Then why spend time doing it at all? If you are saying you don't want the sort to occur, I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... And why is it written on the outer level of view. AFAIK any select from that view is also free to ignore it. --- Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Mixing threaded and non-threaded
Bruce Momjian wrote: Woh, as far as I know, any application should run fine with -lpthread, threaded or not. What OS are you on? This is the first I have heard of this problem. Perhaps we should try to figure out how other packages handle multithreaded/singlethreaded libraries? I'm looking at openssl right now, and openssl never links against libpthread: The caller is responsible for registering the locking primitives. -- Manfred ---(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] Write cache
Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(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] Extending SET SESSION AUTHORIZATION
Ezra Epstein [EMAIL PROTECTED] writes: I do not think SET SESSION AUTH is a suitable replacement for logging in. For one thing, it doesn't apply per-user GUC settings. For OK, what are GUC settings. Can SET SESSION AUTH be extended to do this as needed? Not very easily; it's not clear to me how you undo the previous settings taken from the other user, nor how you go back at RESET SESSION AUTH. (It's not so much that you don't know what settings are specified in pg_shadow, as that you don't know what might have been adopted if they'd not been there.) I am also concerned about whether layering such semantics onto SET SESSION AUTH wouldn't break its existing uses. Maybe you could declare by fiat that you don't care and users in this sort of environment don't get to have per-user GUC settings. If they are sharing a webapp front end then maybe they don't need 'em. I dunno how important it really is, but we'd have to think about the implications. another, using it this way in a pooling environment would be completely insecure --- what if you forget to log out, or your attempt to do so is dropped because it was inside a failed transaction block? Well, consider the alternative. A web user logs in to the web app, not to the DB. The web app connects to the DB as a user which has the union of ALL privs of each of the web users! This is the default mode of ALL production web apps. In other words, the alternative is an even bigger security hole No, the alternative is that the web app is responsible for managing security, which I think is the only reasonable place to put the responsibility if you intend to use shared connections. I find it simply illusory to think that a shared-connection setup is going to be secure if you don't have complete confidence in the front end. Basically what you're saying is that you're willing to trust the front end to ensure that user A can never do anything over user B's connection, but you're not willing to trust it to enforce security otherwise. That doesn't seem to hold water to me. Another issue with a SET SESSION AUTH extension of this kind is that it would force every multi-user installation to maintain password security whether they want it or not. In an environment where users do not normally use database passwords (perhaps they use IDENT auth instead) it's entirely likely that they'd not bother to select good passwords or guard them. In that case the option to get into someone else's account via SET SESSION AUTH becomes a security hole that people are unlikely to think to plug --- the old out of sight, out of mind problem. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Write cache
IDE or SCSI? Why do you think the WC is screwing you? Which driver(s)? LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Write cache
On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed Which driver(s)? Guess... ADPU320 LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Write cache
--On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANTTel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Write cache
On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(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] Function call
Dennis Bjorklund [EMAIL PROTECTED] writes: I'll read the rest of the mail more careful tomorrow moring, I just want to point out directly that for calls that doesn't use named arguments you get the exact same speed as before. Except for an extra if() to check if there are named arguments. I don't understand why you think that this will affect the current behaviour. It looked to me like you were talking about a major redesign of func_select_candidate and friends. I'll be interested to see how you do it without that. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Write cache
--On Tuesday, January 27, 2004 22:48:42 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANTTel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Another optimizer question
Hannu Krosing [EMAIL PROTECTED] writes: Dennis Haney kirjutas T, 27.01.2004 kell 21:08: I'm saying the sort makes no sense. So why even bother executing it? why did you write it? I believe the most common scenario would be that the subquery was expanded from a view... And why is it written on the outer level of view. AFAIK any select from that view is also free to ignore it. Indeed. If we decree that we can drop an ORDER BY in a subselect then there is no reason for anyone to write an ORDER BY in a view, because a view is exactly the same thing as a subselect. As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input values. (None of the SQL-standard ones are, of course, but we've frequently seen examples wherein it's convenient to build a user-defined aggregate that is ordering-sensitive.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Write cache
On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:55:49 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:48:42 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Write cache
--On Tuesday, January 27, 2004 23:01:45 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:55:49 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:48:42 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... SO, I consider these disks buggy or not acceptable for use. LER Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANTTel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 pgp0.pgp Description: PGP signature
Re: [HACKERS] Write cache
On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 16:02:40 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 23:01:45 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:55:49 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:48:42 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... SO, I consider these disks buggy or not acceptable for use. So do I... I have 6 disks... 250$ each... LER Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANTTel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: Have you searched our list
Re: [HACKERS] Write cache
--On Tuesday, January 27, 2004 23:03:56 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 16:02:40 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 23:01:45 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:55:49 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:48:42 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:45:20 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache --On Tuesday, January 27, 2004 22:43:34 +0100 [EMAIL PROTECTED] wrote: On Tue, 27 Jan 2004, Larry Rosenman wrote: Date: Tue, 27 Jan 2004 15:38:30 -0600 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: Write cache IDE or SCSI? SCSI Why do you think the WC is screwing you? Because after a sys crash, the most used filesystems (databases) are screwed what VxFS mount options are you using? Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. That also would explain the fantastic write performance I have compared to read... SO, I consider these disks buggy or not acceptable for use. So do I... I have 6 disks... 250$ each... What kind of warranty? How Old? LER Which driver(s)? Guess... ADPU320 Hrm. LER --On Tuesday, January 27, 2004 22:34:46 +0100 [EMAIL PROTECTED] wrote: Because I've lost a lot of data using postgresql (and I know for sure this should'nt happen) I've gone a bit further reading documentations on my disks and... I have FUJITSU MAP3367NC 36G 1 rpm disks, those disk have a write cache of 8Mb, if someone could tell me hox to turn it off... Could'nt find it in the docs and this could very well explain why a busy database crashes every time Thanks for your help -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - --- --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] --- --- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANTTel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- --- - Make your life a dream, make your dream a reality. (St Exupery) -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] - - Make your life a dream, make your dream a reality. (St Exupery) -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail:
Re: [HACKERS] Another optimizer question
As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input Not to mention our workaround for Max and min (ORDER BY LIMIT) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Another optimizer question
Rod Taylor [EMAIL PROTECTED] writes: As a more direct response, there *are* reasons for people to put ORDER BY in a subselect and expect it to be honored. The typical example that's been discussed several times in the archives is that you want to use an aggregate function that is sensitive to the ordering of its input Not to mention our workaround for Max and min (ORDER BY LIMIT) Right, although one could reasonably expect that an optimization to drop ORDER BY wouldn't drop it if there were a LIMIT there as well. The planner knows perfectly well that those two clauses interact. The cases that are relevant are where the planner could not realize that dropping the ORDER BY would change the results in an unwanted way. The aggregate function example is interesting because the planner doesn't know whether an aggregate function is order-sensitive or not. (We could imagine extending pg_aggregate and CREATE AGGREGATE to tell that, if we were determined to drop ORDER BY in subselects whenever possible. But I'm not sure that that's the only relevant issue.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Question about indexes
Greg Stark [EMAIL PROTECTED] writes: How feasible would it be to have a btree index on ctid? Why would you want one? Direct access by ctid beats out an index lookup every time. In any case, vacuum and friends would break such an index entirely. regards, tom lane ---(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
Re: [HACKERS] Question about indexes
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: How feasible would it be to have a btree index on ctid? Why would you want one? Direct access by ctid beats out an index lookup every time. Of course. But as I mentioned, I have a cunning plan. If you have two indexes (a,ctid) and (b,ctid) and do a query where a=1 and b=2 then it would be particularly easy to combine the two efficiently. If specially marked btree indexes -- or even all btree indexes -- implicitly had ctid as a final sort order after all the index column, then it would esentially obviate the need for bitmap indexes. They wouldn't have the space advantage, but they would be possible to combine using arbitrary boolean expressions without looking at the actual tuples. This is essentially what is in the TODO about using bitmaps, but without having to do any extra sorts. This would only really be an advantage for particularly wide tables where the combination of boolean clauses narrows the result set down a lot more than any one clause. In any case, vacuum and friends would break such an index entirely. That was what I was afraid of. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Write cache
[EMAIL PROTECTED] wrote: Nothing special... And any option I could use would'nt change a thing: the cache is on the disk itself... I did'nt look physicaly yet but according to the docs there's no way to disable it lik I always did on IBM... I was forced to buy those disk (more expensive, not better) define not special? ISTM that the driver should force it out to the disk, unless the disk is lying to the driver, or the driver is buggy. That's exactly what I think: the DISK has a write cache so it's lying to the controler saying data is written when it's only in the disk cache. This would be the first time a SCSI disk lies about its write caching. There are plenty of low-cost (i.e. IDE) disks out there having a hidden write cache, but AFAIK a generic SCSI tool is usable to enable/disable the write cache. I'd be quite surprised if your disks wouldn't allow disabling write caching, because SCSI disks are usually targeted towards professional usage. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Question about indexes
Greg Stark [EMAIL PROTECTED] writes: If you have two indexes (a,ctid) and (b,ctid) and do a query where a=1 and b=2 then it would be particularly easy to combine the two efficiently. If specially marked btree indexes -- or even all btree indexes -- implicitly had ctid as a final sort order after all the index column, then it would esentially obviate the need for bitmap indexes. I don't think so. You are thinking only of exact-equality queries --- as soon as the WHERE clause describes a range of index entries, the readout wouldn't be sorted by ctid anyway. Combining indexes via a bitmap intermediate step (which is not really the same thing as bitmap indexes, IIUC) seems like a more robust approach than relying on the index entries to be in ctid order. But if we did want to sort indexes that way, we could do it today, I think. The ctid is already stored in index entries (it is the payload remember...) and we could use it as a tiebreaker when determining insertion position. This doesn't have the problems that putting ctid into the user columns would do, because the system knows about that ctid as being special; the difficulty with ctid in the user columns is the code not knowing that it'd need to change on a tuple move. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pl/pgSQL versus pl/Python
On 01/27/04:04/2, Joshua D. Drake wrote: With the new preload option is there any benefit/drawback to using pl/Python versus pl/pgSQL? And no... I don't care that pl/Python is now considered untrusted. Feature-wise I'm not exactly sure how pl/Python matches up against pl/pgSQL, but I'd bet on pl/pgSQL being more mature, as pl/Python was rather rough the last time I looked at its source(7.3.x). No docs yet, and parts are immature as well, but if you're not afraid of playing with some dirty source, you can checkout my Python PL project on gborg. Once I get it cleaned up and throw in a few more implementation details(oh yes, the fun ones =\), I'll make a more formal announcement on hackers to get some criticisms and pointers before a formal release.(I announced intent about 3-4 months ago and have been working on it infrequently since then.) gborg.postgresql.org/project/postgrespy (use cvs, co the modules pl and if) If you are in no particular hurry, I would suggest waiting until this is done(may be a while still). It should be quite an improvement to pl/Python in many aspects. Regards, James William Pye ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Write cache
Andreas Pflug [EMAIL PROTECTED] writes: This would be the first time a SCSI disk lies about its write caching. There are plenty of low-cost (i.e. IDE) disks out there having a hidden write cache, but AFAIK a generic SCSI tool is usable to enable/disable the write cache. A SCSI disk shouldn't lie about write completion in any case; there's no need to because the interface spec is inherently multi-threaded (unlike IDE). See past discussions. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: 7.5 change documentation (was Re: [HACKERS] cache control?)
Bruce Momjian wrote Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: If the TODO-list-with-dash isn't the correct place to have looked, is there another list of committed changes for the next release? We tend to rely on the CVS commit logs as the definitive source. You can pull the info from the CVS server (I use cvs2cl.pl to format the results nicely), or read the archives of pgsql-committers. In theory there should be a section at the head of release.sgml mentioning the major changes done-so-far, but for various reasons this hasn't gotten installed in the 7.5 branch yet. (Look at the CVS versions during 7.4 development to see how we did it last time.) As far as the ARC change goes, I believe Jan still considers it a work-in-progress, so it may not be appropriate to list yet anyway. (Jan, where are you on that exactly?) Do we need such a list? (I'd be happy to compile and maintain this if it agreed that it is a good idea to have such a document or process as separate from TODO - I'll be doing this anyway before I pass further comments!) If you wanted to go through the existing 7.5 commits and write up a new done-so-far section, it'd save someone else (like me or Bruce) from having to do it sometime soon ... Doesn't Robert Treat's News Bits list all the major changes weekly? That would b e a good source. Bruce - The excellent work that both you and Robert do is a slightly different view to what I had in mind - I agree they are all aspects of the same information. I'm posting a first output of this now, so we can discuss whether such a thing is useful, and or whether it can ever be all of useful/accurate/timely. I'll happily add this to the HEAD of release.sgml, though lets agree the content/direction first, before I spend time on a more formal publication mechanism. Best Regards, Simon Riggs ---(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] 7.5 change documentation
POSTGRESQL: Summary of Changes since last release (7.4.1) -- 26 Jan 2004 This is a summary of most changes since code versions marked 7_4_1, rather than a weekly news bulletin, a summary of desired future items, or the definitive list of what's in any particular release. The intention is to help everybody understand what's coming and what might be affected, though most importantly, where you might add value to the community as a designer, developer, tester, technical author or advocate. So far in this release dev cycle, major functionality will effect - PERFORMANCE - OPTIMIZER/EXECUTOR - ROBUSTNESS - SECURITY Other code changes are summarised and their major impacts noted. These notes cover major changes and are not guaranteed complete, or even fully tested. Many additional patches to the latest full release have been submitted and these are appreciated just as much, even though they have *mostly* more isolated effects. Documentation changes continue, though aren't described here, neither are client side utilities/interfaces. Nothing mentioned here is DEFINITELY in 7.5 or any future release; testing of everything mentioned here is encouraged and appreciated, for regression, performance and robustness. There is not yet a CVS branch specifically for any later release than 7_4_1; these changes are not yet even guaranteed to build into a consistent release when taken together. Description of changes is designed to highlight benefit and impact, as well as identifying specific areas of code change and potential knock-on effects. MAJOR FUNCTIONALITY PERFORMANCE - Major new memory buffer cache algorithm has now been implemented using the Adaptive Replacement Cache algorithm. The implementation should have positive benefit for everybody's workload, since ARC will adapt to a variety of situations and has been designed to allow Vacuum to avoid interfering with user applications. (Jan) src/backend/buffer - New performance profiling of Intel CPU has allowed new spinlock code to achieve performance/throughput gains of up to 10% using DBT-2 (OLTP) workloads. Further gains to follow? (Manfred Spraul/Tom) src/backend/storage/lmgr/s_lock.c - TIP 9 now needs changing! Cross-data-type comparisons are now indexable by btrees. All the existing cross-type comparison operators (int2/int4/int8 and float4/float8) have appropriate support. (Tom) Implications for user defined types and indices also? [HACKERS] 8-Nov-03 - All operations on TEMP relations are no longer logged in WAL, nor are they involved in checkpoints, thus improving performance. (Tom) - Index performance improved when scanning highly non-unique indices; will greatly improve performance of cursor/fetch logic. B-tree's initial-positioning-strategy code has been improved so that we start scan at first entry, rather than reading in all entries that share that index value before we begin to scan. (Tom, after Dimitry Tkach) - Heap access code is now faster when using compressed columns in-line; previous assumption was that all compressed columns were also toasted (Tom) - Optimized calling performance for dynamically loaded C functions. Hash table added to cache lookups of 'C'-language functions. Some limited testing suggests that this puts the lookup speed for external functions just about on par with built-in functions. (Tom) OPTIMIZER/EXECUTOR IMPROVEMENTS - Genetic Optimizer usage has been re-analyzed; geqo defaults have now been set to more effective values which are expected to significantly improve plan selection for complex multi-way joins ( 10-way). geqo_effort setting now offers an easy 1..10 setting (like IBM DB2), that allows this to be controlled realistically by user/DBA. New heuristic added to significantly reduce number of join plans attempted before geqo begins. (Tom) - Avoid redundant unique-ification step on subqueries where the result is already known to be unique (i.e. it is a SELECT DISTINCT ... subquery, IN subqueries that use UNION/INTERSECT/EXCEPT (without ALL)). Also set join_in_selectivity correctly. (Tom) - Avoid redundant projection step when scanning a table that we need all the columns from. In case of SELECT INTO, we have to check that the hasoids flag matches the desired output type, too. (Tom) - Repair mis-estimation of indexscan CPU costs. When an indexqual contains a run-time key (that is, a nonconstant expression compared to the index variable), the key is evaluated just once per scan, but we were charging costs as though it were evaluated once per visited index entry. (Tom) - Avoid planner failure for cases involving Cartesian products inside IN (sub-SELECT) constructs. (Tom) ROBUSTNESS - Local buffer manager is no longer used for newly-created non-TEMP relations; a new non-TEMP relation goes through the shared bufmgr and thus will participate normally in checkpoints. TEMP relations use the local buffer manager throughout their lifespan. (Tom) -
Re: [HACKERS] Disaster!
Tom Lane wrote: Okay ... Chris was kind enough to let me examine the WAL logs and postmaster stderr log for his recent problem, and I believe that I have now achieved a full understanding of what happened. The true bug is indeed somewhere else than slru.c, and we would not have found it if slru.c had had less-paranoid error checking. [SNIP] Clap. Clap. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] postgresql.org reverse lookup fail
Hi, We have serious problems past 4 days in receiving mail lists from postgresql.org. Subscribers living in the jp domain are receiving via a relay host named powergres.sra.co.jp (this is an authorized relay host for jp domain, and this configuration has been approved by Marc). After checking the log on powergres.sra.co.jp, it turns out that reverse look up for postgresql.org has been failed and this make it impossible to receive those mails from postgresql.org. Anyone can take care of this problem? -- Tatsuo Ishii ---(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] postgresql.org reverse lookup fail
looking into it ... seems somewhere upstream changed their DNS config to not pull our reverse information ... just had someone else point it out to me as well :( I've removed the jp relay temporarily, since it is failing, and hope to have it resolved within the next 24hrs ... Thanks ... On Wed, 28 Jan 2004, Tatsuo Ishii wrote: Hi, We have serious problems past 4 days in receiving mail lists from postgresql.org. Subscribers living in the jp domain are receiving via a relay host named powergres.sra.co.jp (this is an authorized relay host for jp domain, and this configuration has been approved by Marc). After checking the log on powergres.sra.co.jp, it turns out that reverse look up for postgresql.org has been failed and this make it impossible to receive those mails from postgresql.org. Anyone can take care of this problem? -- Tatsuo Ishii ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Question about indexes
Tom Lane [EMAIL PROTECTED] writes:
I don't think so. You are thinking only of exact-equality queries ---
as soon as the WHERE clause describes a range of index entries, the
readout wouldn't be sorted by ctid anyway.
But then even bitmap indexes would fail in that way too, or at least have a
lot of extra cost that would have to be taken into account based on the number
of values in the range.
Combining indexes via a bitmap intermediate step (which is not really
the same thing as bitmap indexes, IIUC) seems like a more robust
approach than relying on the index entries to be in ctid order.
I would see that as the next step, But it seems to me it would be only a small
set of queries where it would really help enough to outweigh the extra work of
the sort. Whereas if the ctid is already pre-sorted then the extra cost is
fairly low. Sort of like the difference in cost between a merge join where
both sides have to be sorted and a merge join where both sides are pre-sorted.
But if we did want to sort indexes that way, we could do it today,
I think. The ctid is already stored in index entries (it is the
payload remember...) and we could use it as a tiebreaker when
determining insertion position. This doesn't have the problems that
putting ctid into the user columns would do, because the system knows
about that ctid as being special; the difficulty with ctid in the user
columns is the code not knowing that it'd need to change on a tuple move.
That's exactly what I was thinking. I just don't know how badly it would
complicate the vacuum{,full}/cluster code and whether those are the only cases
to worry about.
Note that the space saving of bitmap indexes is still a substantial factor.
Using btree indexes the i/o costs of doing multiple index scans plus a table
scan of the relevant pages would still be quite substantial. So this doesn't
completely obviate the need for bitmap indexes, but I think it would remove a
lot of the pressure from people who just need them to handle a few select
queries.
--
greg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgresql.org reverse lookup fail
Hi, We have serious problems past 4 days in receiving mail lists from postgresql.org. Subscribers living in the jp domain are receiving via a relay host named powergres.sra.co.jp (this is an authorized relay host for jp domain, and this configuration has been approved by Marc). After checking the log on powergres.sra.co.jp, it turns out that reverse look up for postgresql.org has been failed and this make it impossible to receive those mails from postgresql.org. Anyone can take care of this problem? -- Tatsuo Ishii Here is the more detailed info: It seems reverse look up for svr1.postgresql.org fails. Due to this sendmail denies to receive mails relayed by svr1.postgresql.org (200.46.204.71). Jan 25 04:09:55 powergres sendmail[9622]: i0OJ9qY09622: ruleset=check_rcpt, arg1=[EMAIL PROTECTED], relay=[200.46.204.71], reject=550 5.7.1[EMAIL PROTECTED]... Relaying denied. IP name lookup failed [200.46.204.71] -- Tatsuo Ishii ---(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
Re: [HACKERS] postgresql.org reverse lookup fail
It seems reverse look up for svr1.postgresql.org fails. Due to this sendmail denies to receive mails relayed by svr1.postgresql.org (200.46.204.71). Jan 25 04:09:55 powergres sendmail[9622]: i0OJ9qY09622: ruleset=check_rcpt, arg1=[EMAIL PROTECTED], relay=[200.46.204.71], reject=550 5.7.1[EMAIL PROTECTED]... Relaying denied. IP name lookup failed [200.46.204.71] As a temporary measure you could set the hostname/domain name for this IP in /etc/hosts (provided sendmail is configured to use the local resolver, then DNS). Gavin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Question about indexes
Greg Stark [EMAIL PROTECTED] writes: Combining indexes via a bitmap intermediate step (which is not really the same thing as bitmap indexes, IIUC) seems like a more robust approach than relying on the index entries to be in ctid order. I would see that as the next step, But it seems to me it would be only a small set of queries where it would really help enough to outweigh the extra work of the sort. What sort? The whole point of a bitmap is that it makes it easy to visit the tuples in heap order. You scan the index, you set the appropriate bits in the bitmap, and then you scan the bitmap and go to the heap tuples that have their bits set. If you are using multiple indexes you can AND or OR their results at the bitmap phase before you go to the heap. An implementation of this kind would not produce tuples in index order, so if you have an ORDER BY to satisfy then you end up doing an explicit sort after you have the tuples. It would be up to the planner to consider this cost versus the advantages of being able to use multiple indexes; we'd certainly want to keep the existing scan mechanism as an available alternative. But if the query is suited to multiple indexes I suspect it'd be a win pretty often. Note that the space saving of bitmap indexes is still a substantial factor. I think you are still confusing what I'm talking about with a bitmap index, ie, a persistent structure on-disk. It's not that at all, but a transient structure built in-memory during an index scan. I'm a little dubious that true bitmap indexes would be worth building for Postgres. Seems like partial indexes cover the same sorts of applications and are more flexible. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
