Re: [HACKERS] pg_dump -C option
Added to TODO: * Have pg_dump -C dump database location and encoding information Jim Buttafuoco writes: will do. While you're at it, at least the encoding parameter should be saved as well. Take a peek at what pg_dumpall saves. Jim Buttafuoco writes: I am working a some patches to the code and I noticed that pg_dump -C database doesn't provide the database location information in the dump file. Is this correct? Your observation is correct, but the behaviour is not. Feel free to send a patch. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Suitable Driver ?
On Thu, 11 Oct 2001, Balaji Venkatesan wrote: Now i need to install DBD For PGSQL .Is this the driver i have to work on for pgsql ?. Or do I have any other option to connect to pgsql from perl . Indeed i've found out an other way to use Pg driver provided by PGSQL to interface perl with pgsql. You need DBD::Pg, which is a DBD driver for postgres. I need to exactly know the difference between use Pg ; and use DBI ; Need to which one is proceeding towards correct direction under what circumstances. You need use DBI; and use DBD::Pg; Pg by itself is slightly lower-level module that is similar to C interface to postgresql. when I tried to install DBD-Pg-0.93.tar.gz under Linux i get Configuring Pg Remember to actually read the README file ! please set environment variables POSTGRES_INCLUDE and POSTGRES_LIB ! I need to know what these varibles POSTGRES_INCLUDE and POSTGRES_LIB should point to ... To location of your installed postgres includes' and libraries For example: export POSTGRES_INCLUDE=/usr/local/pgsql/include export POSTGRES_LIB=/usr/local/pgsql/lib -alex ---(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] Suitable Driver ?
Balaji Venkatesan [EMAIL PROTECTED] writes: I have installed latest DBI from www.cpan.org Now i need to install DBD For PGSQL .Is this the driver i have to work on for pgsql ?. If you want to use DBI then you should get the DBD::Pg driver from CPAN. (Yes, it is on CPAN, even though their index page about DBD modules didn't list it last time I looked.) I need to exactly know the difference between use Pg ; and use DBI ; Need to which one is Pg is a older stand-alone driver; it's not DBI-compatible, and it's got nothing to do with DBD::Pg. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump oid problems
Tom, Thanks for the prompt reply. Following is the postgresql log output: DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = '3527162388'::oid DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT c.relname FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE i.indrelid = 3527162388AND i.indisprimary ERROR: dtoi4: integer out of range DEBUG: AbortCurrentTransaction DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) The 3527162388AND is exactly as shown in the log, with no space between the value and the AND, I guess this is the problem, wherever it's being generated in the code. HTH Thanks, Steve Tom Lane wrote: steve [EMAIL PROTECTED] writes: When trying to pg_dump on 7.1.2 ( 7.1.3) I get the following error message: bash-2.04$ pg_dump dwh getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range Several of my tables have very large OIDs (over 4 billion in some cases Hmm. Okay, I think I can see how over-2-gig OIDs might lead to that error message, but that doesn't really help in tracking down the specific location of the problem. Could you run pg_dump after doing export PGOPTIONS=-d2 so that its queries get sent to the postmaster log? Then looking at the log to see the last couple of queries before the failure should tell us. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump oid problems
steve [EMAIL PROTECTED] writes: DEBUG: query: SELECT c.relname FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE i.indrelid = 3527162388AND i.indisprimary ERROR: dtoi4: integer out of range The 3527162388AND is exactly as shown in the log, with no space between the value and the AND, I guess this is the problem, wherever it's being generated in the code. That's evidently coming from line 2346 of src/bin/pg_dump/pg_dump.c: WHERE i.indrelid = %s Try changing it to WHERE i.indrelid = '%s'::oid (Problem seems to be solved already in 7.2devel) 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] Deadlock? idle in transaction
i've had similar problems before. Looks like some thing is in a transaction, blocked on something else. Then vacuum comes in, locks half the tables, and then gets stuck on a table that the transaction has modified. Now most of your other transactions will block forever. Then the connection limit for postgres will be hit. Then you can't connect to postgres at all. Basically, its a death spiral starting from something in a transaction blocking forever on an external command. Nothing postgres itself can do about. Of course, this is just my conjecture based on the info provided. -rchit -Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 11, 2001 2:29 AM To: PostgreSQL Hacker Subject: [HACKERS] Deadlock? idle in transaction A customer's machine hangs from time to time. All we could find so far is that postgres seems to be in state idle in transaction: postgres 19317 0.0 0.3 8168 392 ?SOct05 0:00 /usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data postgres 19983 0.0 0.8 8932 1020 ?SOct05 0:01 postgres: postgres rabatt 192.168.50.222 idle in transaction postgres 21005 0.0 0.0 34844 ?SOct06 0:00 /usr/lib/postgresql/bin/psql -t -q -d template1 postgres 21014 0.0 0.7 8892 952 ?SOct06 0:01 postgres: postgres rabatt [local] VACUUM waiting postgres 21833 0.0 0.4 3844 572 ?SOct06 0:00 /usr/lib/postgresql/bin/pg_dump rabatt postgres 21841 0.0 1.2 9716 1564 ?SOct06 0:00 postgres: postgres rabatt [local] COPY waiting postgres 22135 0.0 0.9 8856 1224 ?SOct06 0:00 postgres: postgres rabatt 192.168.50.223 idle in transaction waiting I'm not sure what's happening here and I have no remote access to the machine myself. Any idea what could be the reason for this? There may be some client processes running at the time the dump and the vacuum commands are issued that have an open transaction doing nothing. That is the just issued a BEGIN command. Thinking about it run some inserts at the very same time, although that's not likely. Any hints are appreciated. Thanks in advance. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Deadlock? idle in transaction
Michael Meskes [EMAIL PROTECTED] writes: A customer's machine hangs from time to time. All we could find so far is that postgres seems to be in state idle in transaction: You evidently have some client applications holding open transactions that have locks on some tables. That's not a deadlock --- at least, it's not Postgres' fault. The VACUUM is waiting to get exclusive access to some table that's held by one of these clients, and the COPY is probably queued up behind the VACUUM. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] FAQ error
Bruce Momjian wrote:
$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
Is this correct Perl? I don't see a nextval() function in Perl. Can
you call SQL server-side functions natively from Perl?
Ofcourse not. This can be counted as 'pseudo-code'...
A correct implementation using DBI (and DBD::Pg) would be
$newSerialID = $dbh-selectrow_array (q{select
nextval('person_id_seq')});
$dbh-do (qq{INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise
Pascal')});
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
It is more complicated than you think
-- The Eighth Networking Truth from RFC 1925
---(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] FAQ error
Bruce Momjian writes:
Our FAQ, item 4.16.2 has:
$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
Is this correct Perl?
No. I always thought it was pseudo code. I think it's fine.
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Btree index ignored on SELECT until VACUUM ANALYZE
When adding an index to a (quite large, ~2 million rows) table PostgreSQL continues to do sequential lookups until VACUUM ANALYZE is run. Naturally performance is poor. The CREATE INDEX statement takes considerable time. Seen with 7.1.3 on Intel Linux (RedHat 7.0 7.1 and Solaris 2.6. In the example below the data file (8 MB) can be found at: http://services.csl.co.uk/postgresql/obs.gz Consider the session below: lkind@elsick:~% createdb obs_test CREATE DATABASE lkind@elsick:~% psql obs_test obs_test=# CREATE TABLE obs (setup_id INTEGER, time REAL, value REAL, bad_data_flag SMALLINT); CREATE obs_test=# COPY obs FROM '/user/lkind/obs'; COPY obs_test=# SELECT COUNT(*) FROM obs; count - 1966593 (1 row) obs_test=# CREATE UNIQUE INDEX obs_idx ON obs USING BTREE(setup_id, time); CREATE obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118; NOTICE: QUERY PLAN: Seq Scan on obs (cost=0.00..42025.90 rows=197 width=14) EXPLAIN obs_test=# VACUUM ANALYZE obs ; VACUUM obs_test=# EXPLAIN SELECT * FROM obs WHERE setup_id = 300 AND time = 118; NOTICE: QUERY PLAN: Index Scan using obs_idx on obs (cost=0.00..9401.60 rows=1 width=14) EXPLAIN obs_test=# \q ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_result -list
I found a non-existent option -list described in the doc of libpgtcl's pg_result procedure. Shall we remove it from the docs? Yes, removed. Thanks. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Deadlock? idle in transaction
A customer's machine hangs from time to time. All we could find so far is that postgres seems to be in state idle in transaction: postgres 19317 0.0 0.3 8168 392 ?SOct05 0:00 /usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data postgres 19983 0.0 0.8 8932 1020 ?SOct05 0:01 postgres: postgres rabatt 192.168.50.222 idle in transaction postgres 21005 0.0 0.0 34844 ?SOct06 0:00 /usr/lib/postgresql/bin/psql -t -q -d template1 postgres 21014 0.0 0.7 8892 952 ?SOct06 0:01 postgres: postgres rabatt [local] VACUUM waiting postgres 21833 0.0 0.4 3844 572 ?SOct06 0:00 /usr/lib/postgresql/bin/pg_dump rabatt postgres 21841 0.0 1.2 9716 1564 ?SOct06 0:00 postgres: postgres rabatt [local] COPY waiting postgres 22135 0.0 0.9 8856 1224 ?SOct06 0:00 postgres: postgres rabatt 192.168.50.223 idle in transaction waiting I'm not sure what's happening here and I have no remote access to the machine myself. Any idea what could be the reason for this? There may be some client processes running at the time the dump and the vacuum commands are issued that have an open transaction doing nothing. That is the just issued a BEGIN command. Thinking about it run some inserts at the very same time, although that's not likely. Any hints are appreciated. Thanks in advance. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] syslog by default?
Bruce Momjian [EMAIL PROTECTED] writes: OK, that makes sense. My only question is how many platforms _don't_ have syslog. If it is only NT and QNX, I think we can live with using it by default if it exists. There seems to be a certain amount of confusion here. The proposal at hand was to make configure set up to *compile* the syslog support whenever possible. Not to *use* syslog by default. Unless we change the default postgresql.conf --- which I would be against --- we will still log to stderr by default. Given that, I'm not sure that Peter's argument about losing functionality is right; the analogy to readline support isn't exact. Perhaps what we should do is (a) always build syslog support if possible, and (b) at runtime, complain if syslog logging is requested but we don't have it available. Did we decide to compile in syslog support by default? I thought so. -- 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 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] psql and security
Patch applied. Thanks Tatsuo and Tom. Tatsuo Ishii [EMAIL PROTECTED] writes: As you can see, psql reconnect as any user if the password is same as foo. Of course this is due to the careless password setting, but I think it's better to prompt ANY TIME the user tries to switch to another user. Comments? Yeah, I agree. Looks like a simple change in dbconnect(): /* * Use old password if no new one given (if you didn't have an old * one, fine) */ if (!pwparam oldconn) pwparam = PQpass(oldconn); to /* * Use old password (if any) if no new one given and we are * reconnecting as same user */ if (!pwparam oldconn PQuser(oldconn) userparam strcmp(PQuser(oldconn), userparam) == 0) pwparam = PQpass(oldconn); regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_client_encoding
Tatsuo, Did you ever commit this new function? I just tried a 'select pg_client_encoding()' and it told me that there was no such function. This was on sources that I pulled and built two days ago. I was planning on changing the JDBC code to use this function instead of getdatabaseencoding(). Sorry for the delay. I have just added pg_client_encoding() which returns client side encoding name. Also, what names will this new function return (the old character set names like getdatabaseencoding still does, or the new names)? The old ones. To make sure, here are the encoding names list currently supported. encodingwhat pg_client_encoding/alias getdatabaseencoding returns ASCII SQL_ASCII UTF-8 UNICODE UTF_8 MULE-INTERNAL MULE_INTERNAL ISO-8859-1 LATIN1 ISO_8859_1 ISO-8859-2 LATIN2 ISO_8859_2 ISO-8859-3 LATIN3 ISO_8859_3 ISO-8859-4 LATIN4 ISO_8859_4 ISO-8859-5 ISO_8859_5 ISO-8859-6 ISO_8859_6 ISO-8859-7 ISO_8859_7 ISO-8859-8 ISO_8859_8 ISO-8859-9 LATIN5 ISO_8859_9 ISO-8859-10 ISO_8859_10 LATIN6 ISO-8859-13 ISO_8859_13 LATIN7 ISO-8859-14 ISO_8859_14 LATIN8 ISO-8859-15 ISO_8859_15 LATIN9 ISO-8859-16 ISO_8859_16 EUC-JP EUC_JP EUC-CN EUC_CN EUC-KR EUC_KR EUC-TW EUC_TW Shift_JIS SJISSHIFT_JIS Big5BIG5 Windows1250 WIN1250 Windows1251 WIN KOI8-R KOI8KOI8R IBM866 ALT thanks, --Barry Tatsuo Ishii wrote: Hi, I'm going to add a new function pg_client_encoding returning the current client side encoding name. I know there is a similar functionality already there in PostgreSQL (show client_encoding) but it's pain to handle notice message by a program. Also note that JDBC driver and maybe some other APIs use getdatabaseencoding, but I think it's not adequate for FE APIs to know actual encoding passed to FE side, since an encoding conversion might be made in BE side. For example, if PGCLIENTENCODING is set to SJIS before starting postmaster, the actual encoding passed to FE would be SJIS even the database encoding is EUC_JP. Comments? -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Glitch in handling of postmaster -o options
Bruce Momjian [EMAIL PROTECTED] writes: Would someone give me a status on this? I don't think we need any code changes. If we decide to deprecate -o (or anything else), it's just a documentation change. So we can argue about it during beta ... If we notify of the impending deprecation now, to actually occur in 7.3, would we be best intoducing alternative option names somewhere in the 7.2 beta cycle so people writing scripts for 7.2 can use the new names and know their scripts will work into the future? The alternative option names already exist, in the form of GUC variables. For example, --sort-mem=NNN could replace -S NNN. 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] Unicode combining characters
Can I ask about the status of this? Hi all, while working on a new project involving PostgreSQL and making some tests, I have come up with the following output from psql : lang | length | length | text| text --+++---+--- isl | 7 | 6 | _l_ta | _leit isl | 7 | 7 | _l_ta | _litum isl | 7 | 7 | _l_ta | _liti_ isl | 5 | 4 | ma_ur| mann isl | 5 | 7 | ma_ur| m_nnum isl | 5 | 5 | ma_ur| manna isl | 5 | 4 | _ska| -a_i [the misalignment is what I got, it's not a copy-paste error] This is pasted from a UTF-8 xterm running psql under a UTF-8 locale, querying a database created with -E UNICODE (by the way, these are icelandic words :) ). What you see above is misleading, since it's not possible to see that '_', '_', '_' and '_' are using combining marks, while '_' is not. As a reminder, a combining mark in Unicode is that _ is actually encoded as a + ' (where ' is the acute combining mark). Encoded in UTF-8, it's then 61 cc 81 [UTF16: 0061 0301], instead of c3 a1 [UTF16: 00E1]. The length fields are what is returned by length(a.text) and length(b.text). So, this shows two problems : - length() on the server side doesn't handle correctly Unicode [I have the same result with char_length()], and returns the number of chars (as it is however advertised to do), rather the length of the string. - the psql frontend makes the same mistake. I am using version 7.1.3 (debian sid), so it may have been corrected in the meantime (in this case, I apologise, but I have only recently started again to use PostgreSQL and I haven't followed -hackers long enough). = I think fixing psql shouldn't be too complicated, as the glibc should be providing the locale, and return the right values (is this the case ? and what happens for combined latin + chinese characters for example ? I'll have to try that later). If it's not fixed already, do you want me to look at this ? [it will take some time, as I haven't set up any development environment for postgres yet, and I'm away for one week from thursday]. = regarding the backend, it may be more complex, as the underlaying system may not provide any UTF-8 locale to use (!= from being UTF-8 aware : an administrator may have decided that UTF-8 locales are useless on a server, as only root connections are made, and he wants only the C locale on the console - I've seen that quite often ;) ). This brings me to another subject : I will need to support the full Unicode collation algorithm (UCA, as described in TR#10 [1] of the Unicode consortium), and I will need to be able to sort according to locales which may not be installed on the backend server (some of which may not even be recognised by GNU libc, which supports already more than 140 locales -- artificial languages would be an example). I will also need to be able to normalise the unicode strings (TR#15 [2]) so that I don't have some characters in legacy codepoints [as 00E1 above], and others with combining marks. There is today an implementation in perl of the needed functionality, in Unicode::Collate and Unicode::Normalize (which I haven't tried yet :( ). But as they are Perl modules, the untrusted version of perl, plperlu, will be needed, and it's a pity for what I consider a core functionality in the future (not that plperlu isn't a good thing - I can't wait for it ! - but that an untrusted pl language is needed to support normalisation and collation). Note also that there are a lot of data associated with these algorithms, as you could expect. I was wondering if some people have already thought about this, or already done something, or if some of you are interested in this. If nobody does anything, I'll do something eventually, probably before Christmas (I don't have much time for this, and I don't need the functionality right now), but if there is an interest, I could team with others and develop it faster :) Anyway, I'm open to suggestions : - implement it in C, in the core, - implement it in C, as contributed custom functions, - implement it in perl (by reusing Unicode:: work), in a trusted plperl, - implement it in perl, calling Unicode:: modules, in an untrusted plperl. and then : - provide the data in tables (system and/or user) - which should be available across databases, - load the data from the original text files provided in Unicode (and other as needed), if the functionality is compiled into the server. - I believe the basic unicode information should be standard, and the locales should be provided as contrib/ files to be plugged in as needed. I can't really accept a solution which would rely on the underlaying libc, as it may not provide the necessary locales (or maybe, then, have a way to override the collating tables by user
Re: [HACKERS] Proposal: new GUC paramter
Tatsuo Ishii [EMAIL PROTECTED] writes: This is really annoying since: o these code fragments actually controls the optimization efforts for subqueries and views, not related to GEQO at all. So using GEQO parameters for this kind of purpose seems abuse for me. But GEQO_RELS is directly related to the maximum number of FROM-clause entries that we want to try to handle by exhaustive search. So I think it's not completely unreasonable to use it for this additional purpose. Still, if you want to do the work to create another GUC parameter, I won't object. This is a tough call. The GEQO value is used here to indicate a table list that is very long and needs GEQO processing, so there is some relationship. If we get to a point where the number of tables is too large, we do have problems. However, the GEQO setting is set to the point where we want GEQO to take over from the standard optimizer. If GEQO was to be improved, this value would be decreased but the point at which you would want to stop increasing the target list probably would be the same. The GEQO/2 is clearly just a ballpark estimate. I can see the value as a separate config parameter, but I can also see it as something that may be confusing to users and 1% of people will want to change it. In fact, interestingly, even if GEQO is off, GEQO_THRESHHOLD can be changed by users wishing to pull more of their subqueries into their target list. I started thinking of some more complex comparison we could do, such as determining if: 2 * (factorial(rels_in_upper_query) + factorial(rels_in_subquery)) factorial(rels_in_upper_query + factorial(rels_in_subquery) but this doesn't seem to generate good decisions. I have applied the following documentation patch to at least document the current behavior. -- 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 Index: doc/src/sgml/runtime.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v retrieving revision 1.89 diff -c -r1.89 runtime.sgml *** doc/src/sgml/runtime.sgml 2001/10/09 18:46:00 1.89 --- doc/src/sgml/runtime.sgml 2001/10/11 21:08:59 *** *** 719,725 this many FROM items involved. (Note that a JOIN construct counts as only one FROM item.) The default is 11. For simpler queries it is usually best to use the ! deterministic, exhaustive planner. /para /listitem /varlistentry --- 719,727 this many FROM items involved. (Note that a JOIN construct counts as only one FROM item.) The default is 11. For simpler queries it is usually best to use the ! deterministic, exhaustive planner. This parameter also controls ! how hard the optimizer will try to merge subquery ! literalFROM/literal clauses into the upper query. /para /listitem /varlistentry ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] optimizer question
Reinoud van Leeuwen [EMAIL PROTECTED] writes: I have a table that contains almost 8 milion rows. The primary key is a sequence, so the index should have a good distribution. Why does the optimizer refuse to use the index for getting the maximum value? The optimizer has no idea that max() has anything to do with indexes. You could try something like select * from tab order by foo desc limit 1; Can we consider doing this optimization automatically? -- 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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] CLUSTER TODO item
Can I get a status on this? On Sun, 23 Sep 2001, Tom Lane wrote: Note: I'm not convinced that relfilenode and pg_class.oid are each used in exactly the right spots. Once we have cases where they can differ, we may well find some bugs to flush out. But that needs to happen anyway, so don't let it dissuade you from doing CLUSTER the right way. I think I may have broken stuff. I'm not sure. I've fiddled a fair bit but I'm still segfaulting in the storage manager. It might be because I'm heap_creating and then just stealing relfilenode - I don't know. Anyway, a patch is attached which clusters and then recreates the indexes - but then segfaults. Am I going about this all wrong? Thanks Gavin Content-Description: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- 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 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] CLUSTER TODO item
Bruce Momjian [EMAIL PROTECTED] writes: Can I get a status on this? It's not gonna happen for 7.2, I think ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Glitch in handling of postmaster -o options
Bruce Momjian [EMAIL PROTECTED] writes: Would someone give me a status on this? I don't think we need any code changes. If we decide to deprecate -o (or anything else), it's just a documentation change. So we can argue about it during beta ... If we notify of the impending deprecation now, to actually occur in 7.3, would we be best intoducing alternative option names somewhere in the 7.2 beta cycle so people writing scripts for 7.2 can use the new names and know their scripts will work into the future? The alternative option names already exist, in the form of GUC variables. For example, --sort-mem=NNN could replace -S NNN. I don't think we can remove -o behavior during beta because it will affect people using -S in startup scripts. I just wanted to know if I should record this on the TODO list. Added to TODO: * Remove behavior of postmaster -o after making postmaster/postgres flags unique -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Glitch in handling of postmaster -o options
Bruce Momjian [EMAIL PROTECTED] writes: I don't think we can remove -o behavior during beta because it will affect people using -S in startup scripts. That was *not* the proposal under discussion. The proposal was to warn people in the 7.2 documentation that we plan to remove -o in 7.3. AFAICS there is no backwards-compatible way to clean up these switches, and so the best bet is to make an incompatible change --- after suitable warning. 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] Glitch in handling of postmaster -o options
Would someone give me a status on this? --- Hi all, There seem to be a few namespace conflicts for the options of postgres and postmaster. The one's I could identify from the man pages are : -i -N -o -p -S -s If we are going to deprecate -o, then we'll need to make sure we also introduce replacement names where these conflicts are. This way, in the future -o can be treated like a 'no-option' and everything would work. If we notify of the impending deprecation now, to actually occur in 7.3, would we be best intoducing alternative option names somewhere in the 7.2 beta cycle so people writing scripts for 7.2 can use the new names and know their scripts will work into the future? ??? Regards and best wishes, Justin Clift Tom Lane wrote: Marko Kreen [EMAIL PROTECTED] writes: I am suggesting this. [ code snipped ] Okay, that would mean that -o '-S nnn' still works, but -o -F doesn't. But ... the thing is, there is no reason for -o to exist anymore other than backwards compatibility with existing startup scripts. -o doesn't do anything you can't do more cleanly and sanely with GUC options (--sort_mem, etc). So, I don't really see much value in keeping it if you're going to break one of the more common usages --- which I'm sure -o -F is. Since the problem I identified is not likely to bite very many people, my vote is not to try to apply a code solution now. I think we should leave the code alone, and instead document in 7.2 that -o is deprecated (and explain what to do instead), with the intention of removing it in 7.3. Giving people a release cycle's worth of notice seems sufficient. Possibly we could also take this opportunity to deprecate -S and the other options that are standing in the way of unified command line options for postmasters and backends. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- 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 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] extract(timezone_hour) funny business
Plus or minus? Is there a standard for this? We are printing date/time using Posix conventions, which are opposite from the SQL conventions for setting time zone (which we don't yet support, since it is fundamentally useless ;) I apparently implemented one, and you expect the other. peter=# select extract(timezone_hour from timestamp '2001-10-10 01:04:54.965162+03'); --- -2 Big problem. Not really. The timestamp you have specified is read in and internalized as a gmt value, then is rewritten using your current time zone settings. afaict the time zone on an input value should not persist with the value itself, so the info does not carry far enough forward to be used for an output routine. Note that I did not implement time with time zone this way, but rather used a persistant time zone. I *think* that this should be taken out, but further discussion is welcome. The reference books are distressingly unclear or obviously incorrect on this topic, presumably in the interests of remaining lucid. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] EXTRACT broken
Just updated...
peter=# SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
ERROR: Timestamp with time zone units 'dow' not recognized
This is documented to work.
Ah, I broke this with some recent additions to implement more ISO
conventions (I changed the behavior of the date/time parser so that it
does not willingly ignore unrecognized fields).
I see the problem and the solution, but am in the middle of a few
changes to SET code and can't test at the moment. Hopefully I'll get
this fixed in the next couple of days, and if not I'll get it done early
next week.
Would you like to add some tests to the regression suite? Clearly this
isn't covered there...
peter=# SELECT EXTRACT(DOW FROM TIME '20:38:40');
ERROR: Interval units 'dow' not recognized
The expression is nonsensical, but so is the result.
Hmm. Why is the result nonsensical? day of week does not have meaning
for intervals, so it should not be recognized, right?
It is the same result as saying
SELECT timestamp_part('yabadabadoo', time '20:38:40');
- Thomas
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
