Re: [HACKERS] Index Scans become Seq Scans after VACUUM ANALYSE
Hi All. I've been reading all the thread and I want to add a few points: You can set enable_seqscan=off in small or easy queries, but in large queries index can speed parts of the query and slow other, so I think it is neccesary if you want Postgres to become a Wide-used DBMS that the planner could be able to decide accuratelly, in the thread there is a point that might be useful, it will be very interesting that the planner could learn with previous executions, even there could be a warm-up policy to let planner learn about how the DB is working, this info could be stored with DB data, and could statistically show how use of index or seqscan works on every column of the DB. I think it will be useful hearing all users and not guiding only with our own experience, the main objective is to make a versatil DBMS, It's very easy to get down the need of improving indexes with single selects, but a lot of us are not doing single select, so I think that point needs to be heard. Regards ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Implement a .NET Data Provider
Le Dimanche 21 Avril 2002 02:11, Francisco Jr. a écrit : I'd like to know if there is already anybody working with something like this because I'm creating a new project at sourceforge.net and I don't want to overlap anywork already done :). Maybe you should try contact the ODBC list which is mainly working on Windows features / connectivity. Also, the ODBC team might open a CVS account for you on Postgresql.org. SourceForge does not allow projects to leave. Therefore, when your project is mature enough to be included in PostgreSQL main tree, there will still be garbage on Sourceforge in a Google search. Cheers, Jean-Michel POURE ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] [INTERFACES] sqlbang
Peter Eisentraut writes: [EMAIL PROTECTED] writes: The most reason for patch are paremeters, Parameters already exist: peter ~$ cat test.sql \echo :x1 \echo :x2 peter ~$ pg-install/bin/psql -f test.sql -v x1=foo -v x2=bar foo bar OK, positional parameters -- @BABOLO http://links.ru/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Fixups on variable.c
I've committed a bit more to variable.c to handle integer inputs to GUC parameters (string and float were already supported). I've included the cvs log message below. Further changes aren't precluded of course, but the code now supports string, integer, and floating point inputs to parameters (for those parameters which can accept them ;). - Thomas Convert GUC parameters back to strings if input as integers. Change elog(ERROR) messages to say that a variable takes one parameter, rather than saying that it does not take multiple parameters. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Documentation on page files
On Sun, Apr 21, 2002 at 07:28:32PM +1000, Martijn van Oosterhout wrote: http://svana.org/kleptog/pgsql/page.sgml.txt I don't know whatever SGML format this is using, so the layout is not great, but the information should be accurate. I used it to create a program to dump the datafiles directly without the postmaster :). Excellent - since this is a FRP (Frequently Requested Program) how do you feel about dumping it in contrib? Even if it's hardcoded for your particular table structure, it could serve as a starting point for some poor DBA who's got to recover from a lost xlog, for example. Ross ---(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] [RFC] Set Returning Functions
I've been reading past threads, studying backend code, reviewing Alex Pilosov's cursor foo patch (submitted last August/September, but never applied), and conversing off list with a few people regarding a possible implementation of Set Returning Functions (or SRF for short). Below is my proposal for how this might work. After discussion, and if there is no objection, I would like to work on this implementation with the hope that it could be in place for 7.3. Proposal for set returning functions (SRF): - The problem: - Currently the ability to return multiple row, multiple column result sets from a function is quite limited. In fact, it is not possible to return multiple columns directly. It is possible to work around this limitation, but only in a clumsy way (see contrib/dblink for an example). Alternatively refcursors may be used, but they have their own set of issues, not the least of which is they cannot be used in view definitions or exist outside of explicit transactions. The feature: - The desired feature is the ability to return multiple row, multiple column result sets from a function, or set returning functions (SRF) for short. Do we want this feature? - Based on the many posts on this topic, I think the answer to this is a resounding yes. How do we want the feature to behave? - A SRF should behave similarly to any other table_ref (RangeTblEntry), i.e. as a tuple source in a FROM clause. Currently there are three primary kinds of RangeTblEntry: RTE_RELATION (ordinary relation), RTE_SUBQUERY (subquery in FROM), and RTE_JOIN (join). SRF would join this list and behave in much the same manner. How do we want the feature implemented? (my proposal) - 1. Add a new table_ref node type: - Current nodes are RangeVar, RangeSubselect, or JoinExpr - Add new RangePortal node as a possible table_ref. The RangePortal node will be extented from the current Portal functionality. 2. Add support for three modes of operation to RangePortal: a. Repeated calls -- this is the existing API for SRF, but implemented as a tuple source instead of as an expression. b. Materialized results -- use a TupleStore to materialize the result set. c. Return query -- use current Portal functionality, fetch entire result set. 3. Add support to allow the RangePortal to materialize modes 1 and 3, if needed for a re-read. 4. Add a WITH keyword to CREATE FUNCTION, allowing SRF mode to be specified. This would default to mode a) for backward compatibility. 5. Ignore the current code which allows functions to return multiple results as expressions; we can leave it there, but deprecate it with the intention of eventual removal. - Thoughts/comments would be much appreciated. Thanks, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Simplifying OID lookups in the presence of namespaces
We are about to need to fix a fair number of places in client code
(eg, psql and pg_dump) that presently do things like
SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'foo');
This does not work reliably anymore because there could be multiple
relations named 'foo' in different namespaces. The sub-select to
get the relation OID will fail because it'll return multiple results.
The brute-force answer is
SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'foo'
AND relnamespace = (SELECT oid FROM pg_namespace
WHERE nspname = 'bar'));
But aside from being really ugly, this requires that the client code
know exactly which namespace contains the relation it's after. If
the client is relying on namespace search then it may not know that;
in fact, the client code very possibly isn't even aware of the exact
namespace search path it's using. I am planning to introduce an
informational function CURRENT_SCHEMAS() (or some such name) that
returns the current effective search path, probably as a NAME[] array.
But it looks really, really messy to write an SQL query that makes
use of such a function to look up the first occurrence of 'foo' in
the search path. We need to encapsulate the lookup procedure somehow
so that we don't have lots of clients reinventing this wheel.
We already have some functions that accept a text string and do a
suitable lookup of a relation; an example is nextval(), for which
you can presently write
nextval('foo') --- searches namespace path for foo
nextval('foo.bar') --- looks only in namespace foo
nextval('Foo.bar')--- quoting works for mixed-case names
Seems like what we want to do is make the lookup part of this available
separately, as a function that takes such a string and returns an OID.
We'd need such functions for each of the namespace-ified object kinds:
relations, datatypes, functions, and operators.
A variant of the idea of inventing functions is to extend the existing
datatype 'regproc' to do this, and invent also 'regclass', 'regtype',
'regoperator' datatypes to do the lookups for the other object kinds.
I proposed this in a different context last year,
http://archives.postgresql.org/pgsql-hackers/2001-08/msg00589.php
but it seemed too late to do anything with the idea for 7.2.
If we went with the datatype approach then we'd be able to write
queries like
SELECT * FROM pg_attribute WHERE attrelid = 'foo'::regclass;
or
SELECT * FROM pg_attribute WHERE attrelid = 'foo.bar'::regclass;
or for that matter you could do
SELECT * FROM pg_attribute WHERE attrelid = regclass('foo');
which'd be syntactically indistinguishable from using a function.
The datatype approach seems a little bit odder at first glance, but it
has some interesting possibilities with respect to implicit casting
(see above-referenced thread). So I'm inclined to go that route unless
someone's got an objection.
With a datatype, we also have outbound conversion to think of: so there
must be a function that takes an OID and produces a string. What I am
inclined to do on that side is emit an unqualified name if the OID
refers to a relation/type/etc that would be found first in the current
namespace search path. Otherwise, a qualified name (foo.bar) would be
emitted. This will have usefulness for applications like pg_dump, which
will have exactly this requirement (per discussion a few days ago that
pg_dump should not qualify names unnecessarily).
One question is what to do with invalid input. For example, if table
foo doesn't exist then what should 'foo'::regclass do? The existing
regproc datatype throws an error, but I wonder whether it wouldn't be
more useful to return NULL. Any thoughts on that?
Also, for functions and operators the name alone is not sufficient to
uniquely identify the object. Type regproc currently throws an error
if asked to convert a nonunique function name; that severely limits its
usefulness. I'm toying with allowing datatypes in the input string,
eg
'sum(bigint)'::regproc
but I wonder if this will create compatibility problems. In particular,
should the regproc and regoperator output converters include datatype
indicators in the output string? (Always, never, only if not unique?)
Doing so would be a non-backwards-compatible change for regproc.
We might avoid that complaint by leaving regproc as-is and instead
inventing a parallel datatype (say regfunction) that supports datatype
indications. But I'm not sure whether regproc is used enough to make
this an important concern.
Comments?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Implement a .NET Data
Thanks Jean. I will send a message to the ODBC list. At least I didn't create the project in sourceforge yet. I will try to get a cvs account at Postgresql.org as you said. :) Thanks very much!!! Francisco Jr. ___ Yahoo! Empregos O trabalho dos seus sonhos pode estar aqui. Cadastre-se hoje mesmo no Yahoo! Empregos e tenha acesso a milhares de vagas abertas! http://br.empregos.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ecpg/preproc.y is generating reduce/reduce conflicts
[tgl@rh1 preproc]$ make bison -y -d preproc.y conflicts: 2 reduce/reduce This is not good. 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] What is wrong with hashed index usage?
On Mon, 22 Apr 2002 15:04:22 -0700 Dann Corbit [EMAIL PROTECTED] wrote: Here is where a hashed index shines: To find a single item using a key, hashed indexes are enormously faster than a btree. That is typically speaking. I have not done performance benchmarks with PostgreSQL. Yes -- but in the benchmarks I've done, the performance different is not more than 5% (for tables with ~ 600,000 rows, doing lookups based on a PK with =). That said, my benchmarks could very well be flawed, I didn't spend a lot of time on it. If you'd like to generate some interest in improving hash indexes, I'd like to see some empirical data supporting your performance claims. Cheers, Neil -- Neil Conway [EMAIL PROTECTED] PGP Key ID: DB3C29FC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Documentation on page files
On Mon, Apr 22, 2002 at 11:14:36AM -0500, Ross J. Reedstrom wrote: On Sun, Apr 21, 2002 at 07:28:32PM +1000, Martijn van Oosterhout wrote: http://svana.org/kleptog/pgsql/page.sgml.txt I don't know whatever SGML format this is using, so the layout is not great, but the information should be accurate. I used it to create a program to dump the datafiles directly without the postmaster :). Excellent - since this is a FRP (Frequently Requested Program) how do you feel about dumping it in contrib? Even if it's hardcoded for your particular table structure, it could serve as a starting point for some poor DBA who's got to recover from a lost xlog, for example. Actually, it reads the table structure from the catalog. It also will find the right files to open. It reads files from both PG 6.5 and 7.2 although it shouldn't be too hard to make work for other versions. And if you people don't reorder the first few fields in pg_attribute, it will work for all future versions too. The dumping is more of an extra, the original idea was to check for errors in the datafiles. Hence the working name of pgfsck. At the moment the dumping dumps only tuples where xmax == 0 but I'm not sure if that's correct. It doesn't handle compressed tuples nor toasted ones, though thats more advanced really. And ofcourse outputing data in human readable format has to be added for each type. I only started writing it on Sunday, so let me give it a usable interface and I'll let people try it out. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Canada, Mexico, and Australia form the Axis of Nations That Are Actually Quite Nice But Secretly Have Nasty Thoughts About America ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] What is wrong with hashed index usage?
The benchmarks will depend mostly on the depth of the Btree. Hashes will be markedly faster only in the case(s) where descending into the tree to produce a matching leaf node would take longer than walking to the appropriate item in a hash. Most of the time until the btree gets deep they are nearly equivalent. When the tree ends up becoming many levels deep it can take longer to walk than the hash. Neil Conway wrote: On Mon, 22 Apr 2002 15:04:22 -0700 Dann Corbit [EMAIL PROTECTED] wrote: Here is where a hashed index shines: To find a single item using a key, hashed indexes are enormously faster than a btree. That is typically speaking. I have not done performance benchmarks with PostgreSQL. Yes -- but in the benchmarks I've done, the performance different is not more than 5% (for tables with ~ 600,000 rows, doing lookups based on a PK with =). That said, my benchmarks could very well be flawed, I didn't spend a lot of time on it. If you'd like to generate some interest in improving hash indexes, I'd like to see some empirical data supporting your performance claims. Cheers, Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Simplifying OID lookups in the presence of namespaces
Tom Lane wrote: A variant of the idea of inventing functions is to extend the existing datatype 'regproc' to do this, and invent also 'regclass', 'regtype', 'regoperator' datatypes to do the lookups for the other object kinds. I proposed this in a different context last year, http://archives.postgresql.org/pgsql-hackers/2001-08/msg00589.php but it seemed too late to do anything with the idea for 7.2. Interesting thread. It seems like the same basic facility could also support an enum datatype that people migrating from mysql are always looking for. One question is what to do with invalid input. For example, if table foo doesn't exist then what should 'foo'::regclass do? The existing regproc datatype throws an error, but I wonder whether it wouldn't be more useful to return NULL. Any thoughts on that? NULL makes sense. Also, for functions and operators the name alone is not sufficient to uniquely identify the object. Type regproc currently throws an error if asked to convert a nonunique function name; that severely limits its usefulness. I'm toying with allowing datatypes in the input string, eg 'sum(bigint)'::regproc but I wonder if this will create compatibility problems. In particular, should the regproc and regoperator output converters include datatype indicators in the output string? (Always, never, only if not unique?) I'd be inclined to include datatype always. If you don't, how can you use this for pg_dump, etc? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Simplifying OID lookups in the presence of namespaces
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: Also, for functions and operators the name alone is not sufficient to uniquely identify the object. Type regproc currently throws an error if asked to convert a nonunique function name; that severely limits its usefulness. I'm toying with allowing datatypes in the input string, eg 'sum(bigint)'::regproc but I wonder if this will create compatibility problems. In particular, should the regproc and regoperator output converters include datatype indicators in the output string? (Always, never, only if not unique?) I'd be inclined to include datatype always. If you don't, how can you use this for pg_dump, etc? pg_dump would probably actually prefer not having type info in the output string; it'll just have to strip it off in most places. But I don't have a good feeling for the needs of other applications, so I was asking what other people thought. If we supported both ways via two datatypes, we'd have all the bases covered; I'm just wondering if it's worth the trouble. regards, tom lane PS: interesting thought about enum ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] I am back
I was in Boston for a few days for a wedding. Never got time to be online. I am back now. I will read my email and apply outstanding patches tomorrow. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] make report
I'd like to implement *something* to help us collect information on what platforms actually have what features. This would be useful, for example, for figuring out whether any platforms are lacking 8 byte integers or are missing timezone infrastructure. I was thinking about something like make report which would mail the results of ./configure to, say, the ports mailing list. We could mention it in the text message printed at the end of the make cycle. Comments? Suggestions? - Thomas ---(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] make report
I'd like to implement *something* to help us collect information on what platforms actually have what features. This would be useful, for example, for figuring out whether any platforms are lacking 8 byte integers or are missing timezone infrastructure. I was thinking about something like make report which would mail the results of ./configure to, say, the ports mailing list. We could mention it in the text message printed at the end of the make cycle. Comments? Suggestions? Suggestion: Why not embed this information into the binary, and provide some way of extracting it. (There's a Linux kernel option that allows something similar, so it wouldn't be something unprecedented.) If all the config information is embedded in the binary, automatically, at compile time, then this allows the ability to be _certain_ that: - Oh, that was compiled with a really stupid set of compiler options; you'll have to recompile! - That was compiled without support for FOO, but with support for BAR. - Announcement, people: Look out for whether or not your distribution compiled PostgreSQL with proper support for 64 bit integers. Several distributions got this wrong with the 7.4.17 release, and you can see if it's OK by looking for LONG_LONG_REVISED in the embedded configuration information. [Downside: Announcement, script kiddies: If you find option UPDATE_DESCR_TABS=1 in the configuration information, then there's a very easy root exploit...] -- (reverse (concatenate 'string gro.gultn enworbbc)) http://www3.sympatico.ca/cbbrowne/x.html Rules of the Evil Overlord #176. I will add indelible dye to the moat. It won't stop anyone from swimming across, but even dim-witted guards should be able to figure out when someone has entered in this fashion. http://www.eviloverlord.com/ -- (concatenate 'string cbbrowne acm.org) http://www3.sympatico.ca/cbbrowne/spiritual.html Including a destination in the CC list that will cause the recipients' mailer to blow out is a good way to stifle dissent. -- from the Symbolics Guidelines for Sending Mail msg16196/pgp0.pgp Description: PGP signature
Re: [HACKERS] Documentation on page files
On Tue, 2002-04-23 at 01:29, Martijn van Oosterhout wrote: The dumping is more of an extra, the original idea was to check for errors in the datafiles. Hence the working name of pgfsck. At the moment the dumping dumps only tuples where xmax == 0 but I'm not sure if that's correct. AFAIK it is not. As Tom once explained me, it is ok for tuples xmax to be !=0 and still have a valid tuple. The validity is determined by some bits in tuple header. But I think the most useful behaviour should be to dump system fields too, so mildly knowledgeable sysadmin can import the dump and do the right thing afterwards (like restore data as it was before transaction nr 7000) - Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
