Re: [GENERAL] Oracle outer join syntax
On Wed, 2006-04-05 at 14:46 +0200, Stefan Nobis wrote: Is there any way (or working solution) to extend PostgreSQL to accept Oracles outer join syntax with '(+)'? Not AFAIK, and there are no plans to add support that I'm aware of. EnterpriseDB claim to have pretty good Oracle compatibility, so I'd imagine they support this syntax, although their online documentation doesn't mention it -- www.enterprisedb.com -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] index scan backward plan question
On Tue, 2006-03-21 at 16:58 -0500, Kevin Murphy wrote: I have a table for which PG 8.3 is guessing wrong about a plan when the result set gets large. There is no such thing as PG 8.3. - Index Scan Backward using merged_weight_date_idx on merged (cost=0.00..31295593.98 rows=141839 width=229) (actual time=3.888..10380.783 rows=500 loops=1) The mis-estimation of the result set of the index scan is clearly a problem -- have you run ANALYZE recently? Try re-running ANALYZE and retrying EXPLAIN ANALYZE. Otherwise, please provide the queries that trigger the problem and the relevant schema definitions. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] understanding post gres indexes
On Sun, 2006-03-05 at 08:50 +0530, IYENGAR SURESH PARTHASARATHY wrote: i want to fully understand the functioning of postgres indexes with respect to the code. See src/backend/access/ in the source tree, specifically the nbtree/, hash/, gist/ and index/ subdirectories -- each directory has a README with some additional information. Personally I find the hash index code easiest to understand, although it has the fewest features. -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is the pg_locks been used?
On Thu, 2006-02-23 at 17:12 -0300, Carlos Henrique Reimer wrote: When the pg_locks view is used the internal lock manager data structures are momentarily locked and that is why I would like to know if some application is reading the pg_locks view and how many times. Is there a way to discover it? AFAIK there is no easy way to determine this information. You could probably patch Postgres to record the info fairly easy: one way would be to allocate a small block of shared memory and an LWLock to protect it, and then have pg_lock_status() acquire the lock and increment a counter. Then add a new function to retrieve the current value of the counter. You could even do it without modifying the backend proper: change the definition of the pg_locks view to invoke a set-returning PL/PgSQL function. That function would increment a counter stored in some table, and then construct and return the normal pg_locks result set. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Sun, 2006-02-26 at 12:08 -0500, Tom Lane wrote: We'd consider removing these features if they were actually blocking support of some spec-required behavior ... but since they don't, it's quite unlikely they'll ever be removed. Right; there are plenty of places in which PostgreSQL extends the standard. If you're concerned about writing standard-compliant applications, merely removing the places where we have historical syntax variants is probably going to be of little help. I think a better approach would be to introduce the concept of SQL dialects, similar to --std=... in GCC or SQL modes in MySQL 5. That would help people who want to write standard-compliant applications while not inconveniencing those who don't care. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How do I use the backend APIs
On Fri, 2006-02-17 at 11:34 +0800, Qingqing Zhou wrote: AFAIK there is no such API for this purpose. The reason is that to access BTree, you have to setup complex enough environment to enable so. For example, the buffer pool support, the WAL support etc. So though exporting such API is easy to do, there is no pratical usage of it. Well, if the API is going to be invoked by C UDFs, it could assume that the environment has been appropriately initialized. I think it would be possible to provide such an API (although it would take a considerable amount of work). However, I don't see the point -- why would an application want to use the API? SQL is much more flexible. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Create User
On Fri, 2006-01-20 at 09:16 +0100, DB Subscriptions wrote: BEGIN CREATE USER NEW.userid WITH PASSWORD NEW.pword IN GROUP NEW.groupe; RETURN new; END; You can't use PL/PgSQL variables in DDL commands. Try using EXECUTE: EXECUTE 'CREATE USER ' || NEW.userid || '...'; -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Text field performance
On Tue, 2006-01-17 at 15:01 -0800, Glen Parker wrote: We're still on 7.4 (sorry, probly should have mentioned that). Does that documentation apply to the 7.4 series as well? AFAIK, there haven't been any major changes to TOAST since 7.4, so most of that documentation should be applicable. Maybe an easier question is, can we expect a TOAST performance increase when upgrading to 8.1? A lot of performance improvements have been made since since 7.4, but I don't believe any of them have affected TOAST in particular. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Create one prepared function
On Sun, 2006-01-08 at 00:12 +, Marcos José Setim wrote: I'd like that create functions in plpgsql with prepared SQL and plan saved, to that the Postgresl increase the performance of executions. This is possible? plpgsql internally caches query plans the first time a function is invoked in a given session, so there is probably no (performance) reason to do it by hand. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Performance Low Using the Prepare and Execute
On Sat, 2006-01-07 at 20:38 +, Marcos José Setim wrote: I want to use the Prepare and Execute resources of PostgreSQL to increment the performance of my SQL's. $sSQL = 'INSERT INTO teste (nome) VALUES( ? )'; $oDB-Prepare( $sSQL ); The PREPARE documentation states:[1] Prepared statements have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, for example, if the query involves a join of many tables or requires the application of several rules. If the statement is relatively simple to plan and rewrite but relatively expensive to execute, the performance advantage of prepared statements will be less noticeable. Since an INSERT ... VALUES without a subselect or any applicable rules requires very little parsing, planning, or rewriting time, PREPARE/EXECUTE is unlikely to improve performance. -Neil [1] http://developer.postgresql.org/docs/postgres/sql-prepare.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL crashing
[EMAIL PROTECTED] wrote: PostgreSQL runs for a while but always eventually (30min - 2hrs) crashes. Dec 20 17:14:57 server4 kernel: postmaster: page allocation failure. order:0, mode:0xd0 Dec 20 17:14:57 server4 kernel: [c0143271] __alloc_pages+0x2e1/0x2f7 This looks like a kernel or hardware issue, not a problem with PostgreSQL itself. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Sun supporting PostgreSQL
On Fri, 2005-11-18 at 11:00 +0100, Wolfgang Keller wrote: Given the focus of Sun on fault-tolerance etc., one of THE projects that they should definitely sponsor is http://gborg.postgresql.org/project/pgreplication/projdisplay.php AFAIK pgreplication is no longer active. However, the Slony II project is based on the same theoretical foundation (Kemme's Postgres-R work), and is under active development. The website is http://www.slony2.org; Gavin's talk is worth reading. We should have more information on the details of the design available soon. -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Transaction IDs not the same in same transaction?
On Sun, 2005-23-10 at 16:35 -0700, Steve V wrote: Which library is GetTopTransactionId() available in? It's defined in the backend executable, as is GetCurrentTransactionId(). A similar wrapper function to the one shown here: http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php should work. But I agree with the other folks in this thread who have questioned whether this is a good idea: backend APIs are known to change significantly between releases, and making assumptions about how they behave seems like asking for trouble to me. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL on Dual Processors, Dual-Core AMD Chips
On Tue, 2005-18-10 at 22:21 -0500, Tony Caduto wrote: From what i understand Postgresql will scale with more cpus, but not in the same way as threaded server would. Threading isn't really relevant. PostgreSQL currently forks a new process for each client connection, and each process can be scheduled on a different CPU. If PostgreSQL used threads and allocated one thread per client connection, each thread could be scheduled on a different CPU (assuming we used kernel threads). So either way the number of concurrent connections would need to exceed the number of CPUs to scale effectively. Now, rather than dedicating a single process/thread to each connection, another approach would be to share the work of query processing among threads/processes differently. For example, we could perform a large sort operation by splitting the input data among N threads/processes, which could then do the sort in parallel. Postgres currently doesn't do this, but it doesn't have much to do with threads vs. processes per se (threads might make this somewhat easier to implement, though). -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] unsigned types
On Mon, 2005-17-10 at 12:25 -0500, Jim C. Nasby wrote: So, if you have a bunch of int2's all next to each other in a table, they will happily just consume 2 bytes. The issue comes when you try and mix them with other fields randomly, since many other fields require int alignment. We could improve on this by reordering fields on-disk to reduce alignment/padding requirements, during CREATE TABLE. We'd need to be sure to present the same column order back to the client application, of course, but that should be possible. The notion of a physical column number (on-disk position of the column) as well as a logical column numer (position of the column in the table -- e.g. in SELECT * expansion) would also make it easy to implement column reordering in ALTER TABLE, which has been requested a few times. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] unsigned types
On Sat, 2005-15-10 at 16:42 -0400, jeff sacksteder wrote: It occurs to me that I don't know how to define unsigned integer datatypes. I'm making a schema to describe network packets and I need columns to contain values from 0-255, etc. I can't seem to find any documentation on this. What's the best prectice for this situation? You can use a signed type with a CHECK constraint to restrict the column's value to positive integers. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Equivalent queries and the planner
On Fri, 2005-14-10 at 09:43 -0400, John D. Burger wrote: I believe these queries are exactly equivalent, but I presume the planner doesn't know that. explain select gazPlaceID from gazPlaces where gazPlaceID not in (select gazPlaceID from gazContainers); explain select gazPlaceID from gazPlaces except select gazPlaceID from gazContainers; Yeah, query optimization for set operations is currently quite primitive; the above transformation is not yet implemented. In general, there are lots of ways to express the same abstract information need in SQL, and I assumed that there were some set of (probably incomplete) equivalencies encoded somewhere. Is this so? I don't know of a canonical list of planner transformations. There are some presentations on planner internals that touch on this, which is better than nothing: http://neilc.treehou.se/optimizer.pdf http://conferences.oreillynet.com/presentations/os2003/lane_tom.pdf -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PostgreSQL's bug tracker
On Tue, 2005-11-10 at 14:43 +0200, Martijn van Oosterhout wrote: My personal favourite bug-tracker is debbugs, as used by the Debian Project. You can submit bugs by email, they get forwarded to maintainers (which can be a mailing list) via email. When they reply, the reply is also stored with the bug. Bugs can be tagged. AFAIK you can subscribe to bugs so if anything is added or altered you are told about it. I think debbugs is fairly close to what we'd need, for reasons stated earlier: http://archives.postgresql.org/pgsql-hackers/2005-05/msg01156.php (I think Bugzilla is *completely* the wrong tool for the Postgres development model.) I've heard vague comments from Debian people that the debbugs code is kind of evil, although I haven't confirmed that myself. Writing a system like this from scratch would not be much work, anyway... -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreSQL Gotchas
On Thu, 2005-06-10 at 12:07 -0600, Aly S.P Dharshi wrote: http://sql-info.de/postgresql/postgres-gotchas.html Any comments from folks on the list ? SELECT column alias, ...: this is a known issue. AFAIK it is not easy to solve. Unquoted object names fold to lower case: this is intentional, both because the developers prefer this behavior and because it is consistent with the behavior of prior PostgreSQL versions. Implicit FROM item and unintended cross joins: fixed in 8.1, as the gotcha notes. COUNT(*) very slow: this is a known issue -- see the -hackers archives for many prior discussions. MVCC makes this hard to solve effectively (whether applications should actually be using COUNT(*) on large tables with no WHERE clause is another matter...) -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CLUSTER equivalent
Kevin Murphy wrote: I just wanted to confirm that the COPY command always stores data in the table in the order in which it appears in the import file. This is not the case -- depending on the content of the FSM, the newly added rows might be distributed throughout the table. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Questions about varchar NOT NULL default = char(1)
Emi Lu wrote:
Greetings,
If one column col1 is defined as :
col1 varchar(1) not null default ''
Does it means that col1's definition is equal to
col1 char(1) not null default ''
Not quite; for example,
neilc=# create table t1 (x char(1) not null);
CREATE TABLE
neilc=# create table t2 (x varchar(1) not null);
CREATE TABLE
neilc=# insert into t1 values ('');
INSERT 0 1
neilc=# insert into t2 values ('');
INSERT 0 1
neilc=# select octet_length(x) from t1;
octet_length
--
1
(1 row)
neilc=# select octet_length(x) from t2;
octet_length
--
0
(1 row)
Put it another way, will char '' be saved as char(1) or char '' does not
use space at all?
I'm not sure what you mean.
-Neil
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] buffer manager
Alvaro Herrera wrote: IIRC Neil Conway posted a patch to make 8.0.2 use LRU instead of ARC, when the whole patent issue arised. http://archives.postgresql.org/pgsql-patches/2005-01/msg00253.php -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What happens when wal fails?
Tom Lane wrote: Joseph Shraibman jks@selectacast.net writes: If I put the pg_xlog directory on its own disk, then that disk fails, does that mean the postgres is hosed or does it just mean that postgres no longer safe from a power outage? The latter. The WAL is actually write-only during normal operation. Well, data loss is certainly possible. Suppose a power failure caused the machine to go down and (for whatever reason) also resulted in losing the disk on which the WAL is stored. Since recovery will not be possible, there will probably be data corruption. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] data Transfer rate priority on Postgres ?
[EMAIL PROTECTED] wrote: Does Postgres have any kind of configuration that determines the speed of data transfer to the clients? No. Is there any kind of connection priority? No, although it is possible to crudely set priorities via OS-level tools like nice(1). -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] indexes are farked
Scott Marlowe wrote: You might want to schedule analyzes to run every thirty minutes or every hour. I doubt that is necessary or wise. Rerunning ANALYZE should only be necessary when the distribution of your data changes significantly -- e.g. after a bulk load or deletion of a lot of content. IMHO In most circumstances, running ANALYZE once a day is more than sufficient. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Transaction Handling in pl/pgsql?
Joshua D. Drake wrote: If you are using savepoints you can rollback to a specific point of a parent transaction. Although you can't use savepoints (explicitly) in functions. PL/PgSQL exceptions (which are actually implemented internally via savepoints) can be used to achieve a similar effect. -Neil ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Standalone Parser for PL/pgSQL
Alvaro Herrera wrote: I don't think you can use just plpgsql's parser. The problem is that it relies on the main backend parser to figure out anything it doesn't understand. I think it depends on what kind of information you want to extract from a PL/PgSQL function definition. The PL/PgSQL parser handles the structure of the PL/PgSQL function definition itself, but it does not parse expressions or SQL queries. Those are essentially treated as strings that are later handed to the main SQL machinery to be parsed and evaluated. If you're content to treat expressions and SQL queries as opaque strings, you shouldn't need to concern yourself with the main SQL parser. The main parser depends (at least) on the List handling and memory handling. So your simple standalone parser will have to contain both things at least. The PL/PgSQL parser also depends on these, although to a lesser degree. -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] To Postgres or not
Vivek Khera wrote: The first sentence rules out MySQL, so the second sentence should read So that leaves Postgres. Your problem is solved ;-) (If you are accustomed to Oracle, you are probably expecting an ACID database, which rules out MySQL too). Does MySQL with InnoDB not qualify as an ACID-compliant database? -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] out of memory problem in CVS
laser wrote: SELECT url,sum(ct) as ctperkw from ctrraw group by url order by ctperkw desc limit 1000; and the query run out of memory, the log file attached. Have you run ANALYZE recently? You might be running into the well-known problem that hashed aggregation can consume an arbitrary amount of memory -- posting the EXPLAIN for the query would confirm that. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to know if a TRANSACTION isn't completed
[EMAIL PROTECTED] wrote: Is there a similar command in Postgresql so that the client application can find out if there's an unresolved transaction before it starts a new one? See PQtransactionStatus() in libpq; if you're using a different language interface, it should provide some means to get the same information. http://developer.postgresql.org/docs/postgres/libpq-status.html -Neil ---(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: [GENERAL] OIDS
Tino Wildenhain wrote: Google or your favourite search engine helps :-) http://www.postgresql.org/files/documentation/books/aw_pgsql/node71.html is among the first results. Unfortunately those docs are quite out of date. This page is better: http://developer.postgresql.org/docs/postgres/datatype-oid.html (This describes the behavior that will be the default in 8.1) Specifically: Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various system tables The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postgres 8.0.1 configure failed
Dinesh Pandey wrote: ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl configure: error: *** Could not execute a simple test program. This may be a problem *** related to locating shared libraries. Check the file 'config.log' *** for the exact reason. Sounds like good advice to me -- what does config.log say? -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
Alvaro Herrera wrote: No, we don't have SQLERRM support yet. If you were asking about getting the messages from RAISE EXCEPTION, I'm afraid there's no way to get it in the EXCEPTION clause. If you want to contribute it, patches are welcome ... Actually, Pavel Stehule sent in a patch for this a few days ago, which I'll be reviewing and apply to HEAD shortly -- it will be in 8.1. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] So maybe SQLERRM? Sb knows how to check it?
Alvaro Herrera wrote: Huh, I meant a patch for getting the error message from RAISE EXCEPTION. Does Pavel's patch address that too? Yes. (I just posted a revised patch to -patches, I'll apply it later tonight.) -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Things I learned about PG8 on AIX5.3 with XLC compiler
Mohan, Ross wrote: 1) Many (many!) uninitialized variables in code. Optimizers don't do well with this. Um, what? 2) Not clear (to me, a nonprogrammer) whether this is GNU C, ANSI C, Postgres C, or what the overall coding protocol is. Postgres is mostly ANSI C89, with limited use of GNU C extensions (which should be limited to inside #ifdef __GNUC__ blocks, AFAIK). -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: MD5 computation (was: RE: [GENERAL] For Tom Lane)
On Wed, 2005-06-01 at 08:22 +0200, Philippe Lang wrote: What is the best way to calculate an MD5 Sum for a set of rows in a table, on a Postgresql server? The md5() builtin function. contrib/pgcrypto is available if you need more sophisticated hashing / encryption. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CREATE TEMP TABLE AS SELECT/ GET DIAGNOSTICS
On Tue, 2005-05-31 at 15:43 -0400, Tom Lane wrote: OK, next question: is this a bug fix we should back-patch into 7.4, or just change it in HEAD? I agree with Alvaro: fix it in HEAD, but don't backport the change to 8.0 or 7.4. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] getting lock information
Himanshu Baweja wrote: is there any other better way by which i can get a list of locks acquired and waited for during entire run of my application Hacking the backend would be the easiest route, I think. Why do you need this information -- what are you trying to do? -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Exception Handling in C-Language Functions?
Felix E. Klee wrote: I have the created a C-Language function (code is below). Now, I wonder: How do I handle exceptions, for example if malloc cannot assign the necessary memory? Do palloc and pfree handle such a case cleanly? Yes -- they will roll back the current transaction on if there is no memory available. You can catch the error via PG_TRY() in 8.0, although in the case of OOM there isn't probably a lot your exception handler could do... -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] guids / bytea and index use ?
Paul Newman wrote: We are currently using a 32byte varchar for our primary keys. We tried to reduce this down to 16 bytes but varchar didn't seem to store this correctly. In what way was it not stored correctly? The size limit should not significantly affect varchar behavior, other than bounding its maximum size of course. I'd like to use bytea instead so we could use 16bytes, but are indexes used properly ? Sure. Does anyone have any other suggestions on how to store guids ? http://gborg.postgresql.org/project/pguuid/projdisplay.php is out there; I haven't used it personally, though. Is there a reason you can't use an int8? -Neil ---(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: [GENERAL] Table and index size
Dan Black wrote: How can i calculate table and index size on hard disk? See contrib/dbsize in the PostgreSQL source tarball. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Tom Lane wrote: Performance? I'll run some benchmarks tomorrow, as it's rather late in my time zone. If anyone wants to post some benchmark results, they are welcome to. I disagree completely with the idea of forcing this behavior for all datatypes. It could only be sensible for fairly wide values; you don't save enough to justify the lossiness otherwise. I think it would be premature to decide about this before we see some performance numbers. I'm not fundamentally opposed, though. [ BTW, posting patches to pgsql-general seems pretty off-topic. ] Not any more than discussing implementation details is :) But your point is well taken, I'll send future patches to -patches. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] SECURITY RELEASES: 7.2.8 - 7.3.10 - 7.4.8 - 8.0.3
Marc G. Fournier wrote: Please note that the security issues were those already reported by Tom Lane, as well as a manual fix for them. These releases are mainly to ensure that those installing and/or upgrading existing installations have those fixes automatically. Note that if you're upgrading within a release series (e.g. 8.0.x to 8.0.3) without a dump and reload, you will _not_ get the necessary system catalog changes automatically. Tom's earlier mail describes the procedure needed to correct the system catalog: http://www.postgresql.org/about/news.315 -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Bruce Momjian wrote: Is there a TODO anywhere in this discussion? If so, please let me know. There are a couple: - consider changing hash indexes to keep the entries in a hash bucket sorted, to allow a binary search rather than a linear scan - consider changing hash indexes to store each key's hash value in addition to or instead of the key value. You should probably include a pointer to this discussion as well. (I'd like to take a look at implementing these if I get a chance.) -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Hash index vs. b-tree index (PostgreSQL 8.0)
Ying Lu wrote: May I know for simple = operation query, for Hash index vs. B-tree index, which can provide better performance please? I don't think we've found a case in which the hash index code outperforms B+-tree indexes, even for =. The hash index code also has a number of additional issues: for example, it isn't WAL safe, it has relatively poor concurrency, and creating a hash index is significantly slower than creating a b+-tree index. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Christopher Petrilli wrote: This being the case, is there ever ANY reason for someone to use it? Well, someone might fix it up at some point in the future. I don't think there's anything fundamentally wrong with hash indexes, it is just that the current implementation is a bit lacking. If not, then shouldn't we consider deprecating it and eventually removing it. I would personally consider the code to be deprecated already. I don't think there is much to be gained b removing it: the code is pretty isolated from the rest of the tree, and (IMHO) not a significant maintenance burden. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Jim C. Nasby wrote: Having indexes that people shouldn't be using does add confusion for users, and presents the opportunity for foot-shooting. Emitting a warning/notice on hash-index creation is something I've suggested in the past -- that would be fine with me. Even if there is some kind of advantage (would they possibly speed up hash joins?) No, hash joins and hash indexes are unrelated. -Neil ---(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: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Jim C. Nasby wrote: No, hash joins and hash indexes are unrelated. I know they are now, but does that have to be the case? I mean, the algorithms are fundamentally unrelated. They share a bit of code such as the hash functions themselves, but they are really solving two different problems (disk based indexing with (hopefully) good concurrency and WAL logging vs. in-memory joins via hashing with spill to disk if needed). Like I said, I don't know the history, so I don't know why we even have them to begin with. As I said, the idea of using hash indexes for better performance on equality scans is perfectly valid, it is just the implementation that needs work. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Tom Lane wrote: On the other hand, once you reach the target index page, a hash index has no better method than linear scan through all the page's index entries to find the actually wanted key(s) I wonder if it would be possible to store the keys in a hash bucket in sorted order, provided that the necessary ordering is defined for the index keys -- considering the ubiquity of b+-trees in Postgres, the chances of an ordering being defined are pretty good. Handling overflow pages would be tricky: perhaps we could store the entries in a given page in sorted order, but not try to maintain that order for the hash bucket as a whole. This would mean we'd need to do a binary search for each page of the bucket, but that would still be a win. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [GENERAL] Hash index vs. b-tree index (PostgreSQL
Tom Lane wrote: I have a gut reaction against that: it makes hash indexes fundamentally subservient to btrees. I wouldn't say subservient -- if there is no ordering defined for the index key, we just do a linear scan. However: what about storing the things in hashcode order? Ordering uint32s doesn't seem like any big conceptual problem. Hmm, my memory of the hash code is a bit fuzzy. Do I understand correctly? - we only use some of the bits in the hash to map from the hash of a key to its bucket - therefore within a bucket, we can still distinguish most of the non-equal tuples from one another by comparing their full hash values - if we keep the entries in a bucket (or page, I guess -- per earlier mail) sorted by full hash value, we can use that to perform a binary search Sounds like a good idea to me. How likely is it that the hash index will be sufficiently large that we're using most of the bits in the hash just to map hash values to buckets, so that the binary search won't be very effective? (At this point many of the distinct keys in each bucket will be full-on hash collisions, although sorting by the key values themselves would still be effective.) I think that efficient implementation of this would require explicitly storing the hash code for each index entry, which we don't do now, but it seems justifiable on multiple grounds --- besides this point, the search could avoid doing the data-type-specific comparison if the hash code isn't equal. Another benefit is that it would speed up page splits -- there would be no need to rehash all the keys in a bucket when doing the split. -Neil ---(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: [GENERAL] 'prepare' is not quite schema-safe
Vlad wrote: ok, since there is no gurantee that server-side prepared query is still active, pergaps postgresql interface library provide way to check if a prepared before query still alive prior runing exec I'm not sure I quite follow you -- in some future version of the backend in which prepared queries are invalidated, this would be invisible to the client. The client wouldn't need to explicitly check for the liveness of the prepared query, they could just execute it -- if necessary, the backend will re-plan the query before executing it. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 'prepare' is not quite schema-safe
Tom Lane wrote: That's what it is supposed to do. It would hardly be possible to prepare a query at all if we had to wait till EXECUTE to find out which tables it was supposed to use. An alternative would be to flush dependent plans when the schema search path is changed. In effect this would mean flushing *all* prepared plans whenever the search path changes: we could perhaps keep plans that only contain explicit namespace references, but that seems fragile. Flushing all plans might well be a cure that is worth than the disease, at least for a lot of users. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] temp tables ORACLE/PGSQL
Dennis Sacks wrote: The disadvantage is, you'll have to have some process for deleting old data from the table, as it will stay around and it will bite you when you get the same pg_backend_pid() again down the road. Rather than use pg_backend_id(), why not just assign session IDs from a sequence? You would still get the problem of stale session data so you'd probably still want a periodic cleaner process, but you won't need to worry about session ID collision. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] oid wraparound
Hubert Fröhlich wrote: Those days, we had PostgreSQL 7.1 and 7.2, and we had to be careful oids approaching 2^32 (2.14 billion) Now, we have 8.0. What does the situation look like? With the default settings, there is exactly the same risk of OID wraparound as in earlier releases. However, you can set the default_with_oids configuration parameter to false to significantly reduce OID consumption, to the point that you probably won't need to worry about it. It will mean that tables will not have OIDs by default, so you should specify WITH OIDS when creating tables that need OIDs if necessary (although think twice before doing this, as there are only a few good reasons to use OIDs in user tables). (This setting will default to false in 8.1) -Neil ---(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: [GENERAL] plpgsql default arguments
Pavel Stehule wrote: CREATE OR REPLACE FUNCTION foo(integer, integer) RETURNS integer AS $$ BEGIN RETURN $1 + $2; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION foo(integer) RETURNS integer AS $$ BEGIN RETURN foo($1,10); -- 10 is default value END; $$ LANGUAGE plpgsql; Note that if you define the short cut function in SQL (the second one above that supplies the default argument), you can take advantage of function inlining. Granted, it's probably not a huge win, but if all the function does is return the result of evaluating another function, it need not be pl/pgsql anyway. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] psql vs perl prepared inserts
Dawid Kuroczko wrote:
For a test you might want to try also this approach (both from perl and
from psql):
$dbh-do('PREPARE sth_tim (int,inet,boolean,timestamptz) AS INSERT
INTO timestamps VALUES ($1,$2,$3,$4)');
$sth_tim = $dbh-prepare(EXECUTE sth_tim(?,?,?,?));
...and later execute it. (and likewise with psql). If you'll see gain in speed
with perl it means your DBD::Pg wasn't using server side prepared
statements.
The intent of prepared statements is to reduce the overhead of running
the parser, rewriter and planner multiple times for a statement that is
executed multiple times. For an INSERT query without any sub-selects
that is not rewritten by any rules, the cost to parse, rewrite and plan
the statement is trivial. So I wouldn't expect prepared statements to be
a big win -- you would gain a lot more from batching multiple inserts
into a single transaction, and more still from using COPY.
-Neil
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] using limit with delete
Chris Smith wrote: I'm trying to use a limit clause with delete, but it doesn't work at the moment It isn't in the SQL standard, and it would have undefined behavior: the sort order of a result set without ORDER BY is unspecified, so you would have no way to predict which rows DELETE would remove. delete from table where x='1' limit 1000; You could use a subquery to achieve this: DELETE FROM table WHERE x IN (SELECT x FROM table ... ORDER BY ... LIMIT ...); -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Regarding niladic functions
Chandra Sekhar Surapaneni wrote: Can we write our own niladic functions in 8.0.0? I want to write a niladic function similar to current_timestamp, but I did not find any information in the documentation. If you mean a function without any arguments, it is trivial: CREATE FUNCTION foo() RETURNS ... AS ...; SELECT foo(); If you mean a function that doesn't take any arguments and can be invoked without an empty set of parentheses (like current_timestamp), there isn't a way to define such a function via SQL. You could probably hack the SQL parser to add support for specific functions like this, as is done for current_timestamp and friends. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Clearing locks
Edwin New wrote: I have encountered a situation where a java process is dying but leaving locks active. If the connection to PostgreSQL is severed (e.g. the client actually disconnects), the current transaction will be rolled back and any held locks will be released. So it seems that the problem is that when the client dies, it is not actually disconnecting from PostgreSQL, and is in the midst of a transaction that has acquired some locks. Perhaps this is due to buggy connection pooling software that does not rollback a connection's transaction before putting it back into the connection pool? Without more information it's tough to be sure. FYI, you can examine the status of the lock manager via the pg_locks system view: http://www.postgresql.org/docs/8.0/static/monitoring-locks.html How can I, as DBA, clear a lock / roll back an incomplete transaction without access to the connection that created the lock? Well, you can always kill the backend process -- that will abort its transaction and release any locks it holds. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Copression
Stanislaw Tristan wrote: It's a possible to compress traffic between server and client while server returns query result? It's a very actually for dial-up users. What is solution? You could use an SSH tunnel with compression to achieve this. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] function body error checking issues
Bruce Momjian wrote: Syntax error reporting has been improved in our code so 8.1 might be better and catching such errors. Yes, current sources catches this at definition-time: % psql -f test.sql psql:test.sql:21: ERROR: syntax error at or near EXCEPTIONRATIO_OUT at character 1 QUERY: EXCEPTIONRATIO_OUT = 0 CONTEXT: SQL statement in PL/PgSQL function get_ratio near line 13 psql:test.sql:21: LINE 1: EXCEPTIONRATIO_OUT = 0 psql:test.sql:21: ^ If folks have more suggestions for improving pl/pgsql compile-time error checking, speak up. I'm also planning to implement trivially-dead-code detection (like statements that follow a RETURN, and so on), although that's not in HEAD yet. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need to check palloc() return value?
On Tue, 2005-02-15 at 20:13 -0700, Michael Fuhr wrote: Do user-defined functions need to check palloc()'s return value, or does return guarantee success? It guarantees success. -Neil ---(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: [GENERAL] PL/pgSQL bug: FOUND variable is not updated correct
On Tue, 2005-02-08 at 18:11 -0500, Tom Lane wrote: Vitaly Belman [EMAIL PROTECTED] writes: Doing an EXECUTE for a query which returns results still yields 'f' for the FOUND variable. This is not a bug. Read the list of statements that update FOUND. EXECUTE is not one of them. See also previous discussion on this topic: http://archives.postgresql.org/pgsql-bugs/2004-10/msg1.php -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] create temp table and on commit in 7.3.3
On Wed, 2005-02-09 at 10:48 +0530, Antony Paul wrote: Hi all, This is giving error in 7.3.3. CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS; ERROR: parser: parse error at or near ON at character 51 Is this supported. No. Looking at the manual would have made it pretty obvious this was added in 7.4 -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Applications that leak connections
Paul Tillotson wrote: Does anyone know a safe way to shutdown just one backend Sending it a SIGTERM via kill(1) should be safe. -Neil ---(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: [GENERAL] cmin increments by 2 except in 7.4?
On Tue, 2005-02-01 at 01:53 -0500, Tom Lane wrote: /* * Increment command counter between queries, but not after the * last one. */ if (planlist_item != NULL) CommandCounterIncrement(); but planlist_item will *never* be NULL here. Should be testing lnext(planlist_item), I think. Neil? Indeed :( One-liner attached, and applied to HEAD and REL8_0_STABLE. -Neil Index: src/backend/tcop/pquery.c === RCS file: /var/lib/cvs/pgsql/src/backend/tcop/pquery.c,v retrieving revision 1.89 diff -c -r1.89 pquery.c *** src/backend/tcop/pquery.c 31 Dec 2004 22:01:16 - 1.89 --- src/backend/tcop/pquery.c 1 Feb 2005 23:11:40 - *** *** 1033,1039 * Increment command counter between queries, but not after the * last one. */ ! if (planlist_item != NULL) CommandCounterIncrement(); /* --- 1033,1039 * Increment command counter between queries, but not after the * last one. */ ! if (lnext(planlist_item) != NULL) CommandCounterIncrement(); /* ---(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: [GENERAL] cmin increments by 2 except in 7.4?
On Tue, 2005-02-01 at 18:37 -0500, Tom Lane wrote: Do you think it's worth groveling through the other uses of forboth() for the same type of error? I just checked the other uses of forboth(), and didn't notice any errors. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [pgsql-advocacy] MySQL worm attacks Windows servers
Josh Berkus wrote: If you know of a PostgreSQL package, from any source, that installs with trust on network ports, please notify Core (and Core only, please). Why only -core? -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] changing sort_mem on the fly?
Jim C. Nasby wrote: I'd really like to see an improvement in how sort_mem/work_mem is handled. So would I :) (I think it's well known that the current system is not optimal.) Do you have any thoughts on how to improve it? -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] changing sort_mem on the fly?
Tom Lane wrote: The correct place for a sysadmin to limit memory usage would be in the ulimit settings the postmaster starts under. Of course, Neil's argument still holds in general: anyone who can write arbitrary queries is not going to have any difficulty in soaking up unreasonable amounts of resources. Trying to restrict that would probably make the system less useful rather than more so. I'm not sure if I agree that there's no potential for implementing better resource limits/quotas in PG in the future, I was just pointing out that it would require a lot more work to prevent resource consumption by malicious users than merely limiting who is allowed to set sort_mem/work_mem. If you could implement per-user/per-connection limits on things like processor usage or disk space consumption, I think that would be useful to some users (e.g. people offering PG in a web hosting environment). -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] changing sort_mem on the fly?
On Thu, 2005-01-27 at 20:03 -0600, Jim C. Nasby wrote: Yes, there is a risk allowing ad-hoc settings; you can starve the machine for memory. A malicious user who can execute SELECT queries can already consume an arbitrary amount of memory -- say, by disabling GEQO and self-joining pg_class to itself 50 times. I'm not sure that letting users modify sort_mem/work_mem actually increases the risk from malicious users. Restricting this parameter to superusers only would also be a hit to usability. -Neil ---(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: [GENERAL] Tablespaces and primary keys
On Mon, 2005-01-24 at 21:03 -0600, Wes wrote: Implicit indexes created by a constraint do not appear to honor the default tablespace. The index gets created in the null tablespace. I took pg_dumpall output and modified the schema to place everything in specific table spaces. When the statement: ALTER TABLE ONLY addresses ADD CONSTRAINT addresses_pkey PRIMARY KEY (address_key); is executed, this results in an implicitly created index: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index addresses_pkey for table addresses However, it ignores my previous command: SET default_tablespace = indexes; and creates the index without a tablespace. I can't repro this (with current sources): neilc=# create tablespace foo location '/tmp/foo'; CREATE TABLESPACE neilc=# set default_tablespace = 'foo'; SET neilc=# create table xyz (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index xyz_pkey for table xyz CREATE TABLE Time: 45.838 ms neilc=# \d xyz Table public.xyz Column | Type | Modifiers +-+--- a | integer | not null Indexes: xyz_pkey PRIMARY KEY, btree (a) Tablespace: foo neilc=# \d xyz_pkey Index public.xyz_pkey Column | Type +- a | integer primary key, btree, for table public.xyz Tablespace: foo neilc=# alter table xyz drop constraint xyz_pkey; ALTER TABLE neilc=# alter table xyz add constraint xyz_pkey2 primary key (a); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index xyz_pkey2 for table xyz ALTER TABLE neilc=# \d xyz_pkey2 Index public.xyz_pkey2 Column | Type +- a | integer primary key, btree, for table public.xyz Tablespace: foo -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pl/pgsql oddity
Richard Huxton wrote: You want elsif - plpgsql isn't a hugely sophisticated language and its parser is having trouble there. I'm guessing the parser is somehow putting the elseif branch under the initial then so it never gets executed. Indeed; the parser thinks an unrecognized keyword indicates the beginning of a SQL statement: since the PL/PgSQL parser and the SQL parser are completely separate, we need to do some guessing about what constitutes a legal SQL statement. See the more detailed diagnose of the problem here: http://archives.postgresql.org/pgsql-bugs/2004-11/msg00297.php There's a patch in that thread that provides better PL/PgSQL error checking (which results in flagging this kind of code as invalid at compile time). Some form of that patch will be in 8.1, as well as other nice stuff like warning for unreachable code. Tom also suggested just adding 'elseif' as an alternative for 'elsif'. That sounds like it would be worth doing. Congratulations - I think you've found a bug. You can report it formally via the bugs mailing list No need, this is a known issue. -Neil ---(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: [GENERAL] pl/pgsql oddity
On Thu, 2004-12-16 at 11:09 -0500, Tom Lane wrote:
I think we should go ahead and do that for 8.0. I'm getting tired of
reading reports that stem from this mistake (I think this is the third
one in the past month ...). I can't see any real downside to accepting
both spellings, can you?
I agree this is pretty harmless. I've applied the attached trivial patch
to HEAD.
-Neil
Index: doc/src/sgml/plpgsql.sgml
===
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.51
diff -c -r1.51 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 13 Dec 2004 18:05:08 - 1.51
--- doc/src/sgml/plpgsql.sgml 17 Dec 2004 03:37:41 -
***
*** 1475,1481
para
literalIF/ statements let you execute commands based on
! certain conditions. applicationPL/pgSQL/ has four forms of
literalIF/:
itemizedlist
listitem
--- 1475,1481
para
literalIF/ statements let you execute commands based on
! certain conditions. applicationPL/pgSQL/ has five forms of
literalIF/:
itemizedlist
listitem
***
*** 1490,1495
--- 1490,1498
listitem
paraliteralIF ... THEN ... ELSIF ... THEN ... ELSE//
/listitem
+ listitem
+ paraliteralIF ... THEN ... ELSEIF ... THEN ... ELSE//
+ /listitem
/itemizedlist
/para
***
*** 1633,1638
--- 1636,1648
/programlisting
/para
/sect3
+
+ sect3
+ titleliteralIF-THEN-ELSEIF-ELSE//title
+
+ para
+literalELSEIF/ is an alias for literalELSIF/.
+ /para
/sect2
sect2 id=plpgsql-control-structures-loops
Index: src/pl/plpgsql/src/scan.l
===
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/scan.l,v
retrieving revision 1.37
diff -c -r1.37 scan.l
*** src/pl/plpgsql/src/scan.l 13 Sep 2004 01:45:32 - 1.37
--- src/pl/plpgsql/src/scan.l 17 Dec 2004 03:08:22 -
***
*** 144,149
--- 144,150
default { return K_DEFAULT; }
diagnostics { return K_DIAGNOSTICS; }
else { return K_ELSE; }
+ elseif { return K_ELSIF; }
elsif { return K_ELSIF; }
end{ return K_END;}
exception { return K_EXCEPTION; }
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Running functions that return void in psql
Eric Brown wrote: I've got quite a few plpgsql functions that insert, update or delete. They're all declared to return void. All other functions, I can just run 'select f(...);' from psql to test them. I don't understand how to test these ones that return void from psql. neilc=# create function xyz() returns void as 'begin return; end;' language 'plpgsql'; CREATE FUNCTION neilc=# select xyz(); xyz - (1 row) -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Performance differences 7.1 to 7.3
On Mon, 2004-12-13 at 17:43 -0500, Jimmie H. Apsey wrote: Hello all, I have just loaded Postgresql 7.3.6-7 onto a new server on the recommendation of Tom Lane. It is part of Red Hat AS 3. I have Postgresql 7.1.3-5 running on Red Hat AS 2.1. I have a simple view from which I select on both systems. The 7.3.6-7 version requires 18+ seconds to do a select from a particular view. The 7.1.3-5 version requires 3+ seconds to select from the same view. Have you run ANALYZE recently? If so, take a look at the query plans produced by 7.1 and 7.3 (Using EXPLAIN and EXPLAIN ANALYZE). Likely the planner is making an incorrect decision -- EXPLAIN should help you figure out why. You can also post the EXPLAIN / EXPLAIN ANALYZE output to the list and someone can give you some advice. For more info: http://www.postgresql.org/docs/7.4/static/performance-tips.html#USING-EXPLAIN -Neil ---(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: [GENERAL] Performance suggestions?
On Wed, 2004-12-15 at 10:22 +1100, Harvey, Allan AC wrote: I have a small table about 20 rows, a constant, that is receiving about 160 updates per second. The table is used to share gathered data to other process asynchronously. After 5 min it is 12 updates per second. Performance returns after a vacuum analyse. It should be quite feasible to VACUUM this table frequently (once per minute or even more often). Or should I just stick to saving the data, inserts seem to go on and on, and use a different IPC method. MVCC isn't really ideal for this kind of situation, but without knowing more about your application it's difficult to say whether switching to another IPC method would be a better choice. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Corrupt RTREE index
On Tue, 2004-12-14 at 14:12 -0600, Scott Marlowe wrote: IS this same issue true for hash or GiST indexes? Yes, it is: currently, only btree indexes are WAL safe. (I spent some time recently looking into adding page-level concurrency and WAL to GiST, but I haven't had a chance to finish that work -- it is quite a big job...) -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] VACUUM FULL [ANALYZE] problem
On Tue, 2004-12-14 at 17:15 -0800, Tim Vadnais wrote: My question is: What is stand-alone mode? http://www.postgresql.org/docs/7.4/static/app-postgres.html -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] disabling OIDs?
On Sun, 2004-12-12 at 20:25 -0800, Lonni J Friedman wrote: OK, thanks. So is there any real benefit in doing this in a generic (non-dspam) sense, or is it just a hack that wouldn't be noticable? Any risks or potential problems down the line? It saves 4 bytes per row; depending on alignment and padding considerations, that may or may not equate to disk space savings. Other than the inability to use OIDs on the table, there is no real risks to doing this -- I'm planning to advocate making WITHOUT OIDS the default in PostgreSQL 8.1+. You can get this behavior in 8.0 by setting the default_with_oids config variable to false. -Neil ---(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: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: I seem to remember hearing that the memory limit on certain operations, such as sorts, is not enforced (may the hackers correct me if I am wrong); rather, the planner estimates how much a sort might take by looking at the statistics for a table. If the statistics are wrong, however, the sort doesn't actually stay within sort memory, and so the process consumes a very large amount of memory, much more than the sort_mem configuration parameter should allow it to. AFAIK this is not the case. sort_mem defines the in-memory buffer used _per_ sort operation. The problem you may be referring to is that multiple concurrent sort operations (possibly within a single backend) will each consume up to sort_mem, so the aggregate memory usage for sort operations may be significantly higher than sort_mem. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
On Mon, 2004-12-06 at 22:19 -0300, Alvaro Herrera wrote: AFAIK this is indeed the case with hashed aggregation, which uses the sort_mem (work_mem) parameter to control its operation, but for which it is not a hard limit. Hmmm -- I knew we didn't implement disk-spilling for hashed aggregation, but I thought we had _some_ sane means to avoid consuming a lot of memory if we got the plan completely wrong. AFAICS you are right, and this is not the case :-( We definitely ought to fix this. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
On Mon, 2004-12-06 at 23:55 -0500, Tom Lane wrote: Bear in mind that the price of honoring sort_mem carefully is considerably far from zero. I'll do some thinking about disk-based spilling for hashed aggregation for 8.1 The issue with the hash code is that it sets size parameters on the basis of the estimated input row count; the memory usage error factor is basically inversely proportional to the error in the planner's row estimate. Right. But I don't think it's acceptable to consume an arbitrary amount of memory to process a query, even if we only do that when the planner makes a mistake (regrettably, planner mistakes occur with some regularity). As a quick hack, what about throwing away the constructed hash table and switching to hashing for sorting if we exceed sort_mem by a significant factor? (say, 200%) We might also want to print a warning message to the logs. This assumes that aggregation-by-sorting can be used in a superset of the cases where aggregation-by-hashing can be used, and that the semantics of both nodes are the same; I believe both conditions hold. And of course, performance will likely suck -- but (a) since the planner has guessed wrong performance is probably going to suck anyway (b) it is better than running the machine OOM. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Column n.nsptablespace does not exist error
On Tue, 2004-11-30 at 17:54 +1100, Johan Wehtje wrote: I am getting the error Column n.nsptablespace does not exist in my application when I connect using my Administrative tool. This only happens with Version 8, but it does crash my application, does anyone have any ideas ? You need to upgrade your admin tool -- that column was removed from the system catalogs in beta5. See: http://archives.postgresql.org/pgsql-hackers/2004-11/msg00987.php -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Index work around?
On Thu, 2004-11-25 at 10:24 +0100, Bjørn T Johansen wrote: do I still need to use ::int8 to make it use indexes in 8.0 as I need in 7.x? That should no longer be necessary. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What is alias_list_srl() ?
On Thu, 2004-11-25 at 15:00 -0500, Fred Fung wrote: I am running PostgreSQL 7.4.5 and I notice the following 2 sets of error messages generated by the postmaster everything I do a query through my frontend application program The source of the errors is your frontend application, not PostgreSQL. ERROR: relation serialreg does not exist Your application is submitting a query that references a table (serialreg) that does not exist. ERROR: syntax error at or near MODE at character 10 Without seeing the query that produces this, it's difficult to say what the problem is. Try enabling statement logging and reporting the query that causes the error. ERROR: function alias_list_srl() does not exist Again, your application is trying to invoke a user-defined function that does not exist, so this is a problem with your application (or your configuration), not PostgreSQL itself. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Can't get planner to use multicolumn index on large
Ulrich Meis wrote: CREATE TABLE data.question_result ( id bigserial PRIMARY KEY, trial_idbigint NOT NULL REFERENCES data.trial(id), question_id bigint REFERENCES content.question(id), two more columns, ); mydb=# explain analyze select * from data.question_result where trial_id=1 and question_id=2; This is a well-known optimizer deficiency. You need to single-quote the numeric literals or cast them to the type of the column, or else you won't get index scans for non-int4 columns. In other words: explain analyze select * from data.question_result where trial_id='1' and question_id='2' This is fixed in 8.0 -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to handle larger databases?
On Mon, 2004-11-22 at 08:59 -0500, Geoffrey wrote: So that would say the previous statements are not accurate? That is, there's no problem with using a varchar? Right; there is no reason to prefer CHAR(n) over VARCHAR(n), unless you need whitespace padding. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID's
Peter Eisentraut wrote: There are certainly ways to handle this. But no one has seriously proposed getting rid of OIDs and presented a plan for fixing all the other holes that move would leave. Right; I certainly have no intention of trying to remove OIDs any time soon. However, I _will_ be proposing that we set default_with_oids to false by default in 8.1, per previous discussion on pgsql-hackers. Among other things, this will mean that CREATE TABLE will not include OIDs by default: if you want OIDs on a particular table, you can either specify WITH OIDS explicitly or change the default_with_oids configuration parameter. -Neil ---(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: [GENERAL] oid size on 64 bit machine
On Thu, 2004-11-11 at 14:34 -0500, Tom Lane wrote: Geoffrey [EMAIL PROTECTED] writes: Do I get more oids on a 64 bit machine? No. Nor will OIDs ever be 64 bit, I'd hazard to guess. In any case using OIDs in applications is a bad idea -- you probably should not be using them at all, let alone using more than 2^32 of them. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Using EXECUTE in same manner as SELECT
Andrew Lazarus wrote: Is there some clear reason why I can't using EXECUTE of a PREPAREd query as I can a SELECT statement in CREATE TABLE AS, INSERT, FROM clause, sub-selects, etc.? If not, wouldn't this be a very useful change? You can already do CREATE TABLE AS ... EXECUTE. I agree being able to use EXECUTE in more situations would be a good thing. Patches are welcome. I'm not sure there's a lot of value in adding support for EXECUTE in subqueries, since you would still need to do parsing, rewriting and planning for the rest of the query; also, the fact that the plan for the EXECUTE has already been generated would limit the optimizer's ability to pullup subqueries and so forth. -Neil ---(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: [GENERAL] Reasoning behind process instead of thread based
I don't see the big difference between what Marco is suggesting and user threads -- or to be more precise, I think user threads and event-based programming are just two sides of the same coin. A user thread just represents the state of a computation -- say, a register context and some stack. It is exactly that *state* that is passed to a callback function in the event-based model. The only difference is that with user threads the system manages context for you, whereas the event-based model lets the programmer manage it. Which model is better is difficult to say. Martijn van Oosterhout wrote: 1. non-blocking is nice, but lots of OSes (eg POSIX) don't support it on disk I/O unless you use a completely different interface. We could implement I/O via something like POSIX AIO or a pool of worker threads that do the actual I/O in a synchronous fashion. But yeah, either way it's a major change. 2. If one of your 'processes' decides to do work for half an hour (say, a really big merge sort), you're stuck. It would be relatively easy to insert yield points into the code to prevent this from occurring. However, preemptive scheduling would come in handy when running foreign code (e.g. user-defined functions in C). I honestly don't think you could really do a much better job of scheduling than the kernel. I think we could do better than the kernel by taking advantage of domain-specific knowledge, I'm just not sure we could beat the kernel by enough to make this worth doing. BTW, I think this thread is really interesting -- certainly more informative than a rehash of the usual processes vs. threads debate. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Strange count(*) implementation?
Henk Ernst Blok wrote: I assume(d) the more expensive statistics (e.g., value distribution info) are updated only when outdated too much or on request (manual vacuum). They are only updated on request -- i.e. when an ANALYZE is issued. So if explain can get the most recent count, why not use it in the count as well if you know the statistics are still acurate? Aside from the issue of stale statistics, there is another problem: optimizer statistics are designed to be approximations. They are not necessarily precise, even if ANALYZE has just been run (for example, pg_class.reltuples is stored as a floating point number). A practical problem is that aggregates like count() are implemented via a general-purpose API; there is currently no provision for bypassing the API in certain special case scenarios. See here for more info: http://developer.postgresql.org/docs/postgres/functions-aggregate.html -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to connect with postmaster through TCP socket
On Mon, 2004-10-25 at 22:59, Deepa K wrote: Hi, I am using postgresql 7.1.3 in RedHatLinux 7.2. Note that PostgreSQL 7.1.3 is quite old -- you should consider upgrading. Can anyone tell me how to connect with postmaster through TCP socket (it is started with -i option) using libpq from an external application. (written in C) http://www.postgresql.org/docs/7.4/static/libpq.html#LIBPQ-CONNECT -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL Security Release(s) for 7.2, 7.3 and 7.4
Marc G. Fournier wrote: In order to address a recent security report from iDefence, we have released 3 new point releases: 7.2.6, 7.3.8 and 7.4.6 Assuming you're referring to the make_oidjoins_check bug, I don't think it is accurate to bill these as security releases. As the 7.4.6 release notes plainly state: --- # Avoid using temp files in /tmp in make_oidjoins_check This has been reported as a security issue, though it's hardly worthy of concern since there is no reason for non-developers to use this script anyway. --- That said, the fix for the clog bug is reason enough to make the point releases, and reason enough for users to upgrade. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL Security Release(s) for 7.2, 7.3 and 7.4
On Mon, 2004-10-25 at 00:43, Tom Lane wrote: He's not. There were two other recent security reports, which core kept to ourselves until the release could be made. Ah, ok -- fair enough. Are those additional security fixes mentioned in the release notes? -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CREATE TEMPORARY TABLE AS ... ON COMMIT?
On Thu, 2004-10-21 at 06:40, Thomas F.O'Connell wrote: Is the ON COMMIT syntax available to temporary tables created using the CREATE TABLE AS syntax? No, but it should be. There's a good chance this will be in 8.1 If not, is there a way to drop such a table at the end of a transaction? DROP TABLE :) -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.0.0beta3 vacuum analyze
On Mon, 2004-10-18 at 14:49, Ed L. wrote: I *think* I'm seeing vacuum analyze queries launched automatically on an 8.0.0beta3 (unless I have a rogue autovac running that I haven't spotted). Is this something new in 8.0 and to be expected? No. #vacuum_cost_delay = 0# 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits Vacuum cost delay affects the way that VACUUM behaves, not the frequency with which it is executed. -Neil ---(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: [GENERAL] Numeric user names
On Wed, 2004-10-13 at 06:31, Ed Stoner wrote: I am unable to use the CREATE USER command with numeric user names (i.e. CREATE USER 35236 WITH PASSWORD '1234';). Is this a limitation or a problem somewhere with how I have things configured? Is there are workaround? A username is an identifier; per the docs, SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). So it's a limitation. I don't know of an easy workaround. Why do you need numeric usernames? -Neil ---(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: [GENERAL] capacity of datatype text
On Wed, 2004-10-13 at 14:58, John Ossmann wrote: I'm not sure where to find it exactly, but does anyone know how much data a column of type text in a postgres DB can hold? There is no limit on what text itself can contain. However, a field of any data type can contain at most 1GB (compressed -- Postgres will do this compression automatically). See: http://www.postgresql.org/docs/faqs/FAQ.html#4.5 -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
