Re: [GENERAL] select * from users where user_id NOT in (select
Hello, thank you and sorry for asking a FAQ. I've fixed my problem now by: select user_id, username from phpbb_users where user_id not in (select ban_userid from phpbb_banlist where ban_userid is not null); but still your explanation feels illogical to me even though I know you're right... On 8/18/06, Stephan Szabo [EMAIL PROTECTED] wrote: When the subselect returns NULL for at least one row, you fall into this sort of case. x NOT IN (...) is equivalent to NOT(x IN (...)) which is NOT(x = ANY (...)) x = ANY (...) is basically defined as True if x = y is true for some y in the subselect False if x = y is false for all y in the subselect Unknown otherwise Since x = NULL is unknown and not true or false, you fall into the last case with your query and data. Regards Alex -- http://preferans.de ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dynamic partial index
In article [EMAIL PROTECTED], gustavo halperin [EMAIL PROTECTED] writes: Hello I'm interesting in a partial index for a rows that aren't older than 6 mounts, something like the sentence below: /CREATE INDEX name_for_the_index ON table (the_column_of_type_date) WHERE ( the_column_of_type_date (current_date - interval '6 month')::date );/ But this is not posible, I receive the next error: /ERROR: functions in index predicate must be marked IMMUTABLE/ So, what is the best solution for my problem? Use a fixed date 6 months ago, and regularly recreate the index. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] User can not more login
Am 2006-08-14 16:32:08, schrieb Chris: --password asks for YOUR password not the new one. SCHEISE!!! :-/ Thanks Michelle Konzack -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Dynamic Partial Index
Jeff Davis wrote: On Fri, 2006-08-18 at 00:19 +0300, gustavo halperin wrote: Create an index on the table, and then periodically move records into a separate archive table. Regards, Jeff Dave Thanks, but I have a question. If the table is a BIIG table, use your solution is still a good idea ?? What about to create a partial INDEX for the really current date (and not using the function current_date) and periodically dropped and created it with the current day again and again ?? If you continue to drop and recreate an index like that, it will need to scan the table during the creation of the index. That will take a while on a large table. You might be better off just using a normal index. To search the index only takes log(n) time. What problem are you currently having with a normal index? The downside of a normal index on a large table is that the index will grow large and consume space. I think the solution is to move seldom- accessed records to a separate archive table. That way, you don't ever have to scan the archive table unless you do a search in the archives. Regards, Jeff Davi OK, I become totally convinced now. Thank you, Gustavo ---(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] Migrating PostgreSQL database to MySQL/MS Access
well, since he wrote his app in vb.net he is probably using postgresql as a dumb data store, it might not be all that difficult. still, not a very smart move. so if you have functions on server side or triggers or rules... then you need some rewrite on the app... otherwise just to adapt the sql as necessary... the .net 'way' of database application development is to keep all the logic in the .net middleware. please note that I am completely opposed to this because it obfuscates good data management practices. however based on the op's comments I am guessing he is doing things the .net way. Not really. It's one of the ways. Another way that's pushed pretty hard with .Net is sticking your logic in stored procedures. The .Net tools from MS integrate very well with situations where all your logic is in stored procedures - both in SQL Server 7.0/2000 (which has only TSQL stored procs) and 2005 (which has TSQL and also CLR/.Net language independent stored procedures). The tools let you do it either way. Unfortunately a lot of people don't realize the gains to be had by choosing the right one. //Magnus ---(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] deploy postgre question
Hi We need to deploy postgreSQL with our new app The app is designed in Visual Studio 2005 and deployed in its own MSI. Our question is - is it possible to call the postgreSQL msi from our MSI or is it possible to merge PostGreSQL msi into ours. We use Wise for Windows 6 to create our MSI. Call, yes. See the page about silent install. Merge, no, not possible. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Pl/TCL: Is my Windows install missing scripts?
The Windows-PosgreSQL 8.1.4 package I downloaded from the PostgreSQL download site (right here) does not seem to contain the scripts: pltcl_delmod pltcl_listmod pltcl_loadmod Have I missed something, or can anyone else confirm? They're definitely not included there. Could you check if there are any other pltcl specific files missing, and then please open a bug report at the pgfoundry page (http://pgfoundry.org/projects/pginstaller). //Magnus ---(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] What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
Magnus, did you have a specific reason for choosing Europe/Dublin, or was it just alphabetically first? Europe/London looks at least marginally closer to what one would think GMT means: Does it have to be a specific city? I'd rather it just chose GMT. The fact that there is an entry for GMT Daylight Time means that Windows' idea of this time zone is not pure GMT. Or is the translation table entry a complete work of fiction? No, it's a work of a simplistic perlscript IIRC. It simply looked for the first match it could find, based on the list found in the registry (the whole concept is a bit of an ugly hack, but it's the best we could come up with). If there is a more fitting timezone for it, it should be changed. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL and Windows 2003 DFS Replication
Hi list ! I am currently deploying two servers (Windows 2003 R2) that will be used as file servers as well as PostgreSQL servers. One of the server will be the main server, the other one a backup server (no load-balancing, only an easy-recoverage solution). The goal is to be able to start working quickly after one of the server fails (after the main server fails actually, since the backup server is not used). I already configured a high-availability solution for the file server part by using the built-in DFS Replication service. I first thought I would use Slony-I to replicate changes to the main database on the backup server, but I then realized that I might use DFS Replication for that. The point is that I am not sure that it will work. Documentation about DFS Replication is not very talkative (IMHO), I have to little knowledge of PostgreSQL's file handling to know if it will work or not. I have compiled some informations about DFS Replication from Microsofts web site. Could you PostgreSQL gurus tell me whether using this replication mechanism is a good idea or not ? The main advantage for me is that I will not need to configure 2 replication systems (one for the files, on for the DBs). I would only need to maintain one of them ! To add to this thread, even if it's a bit late: It is *not* safe to use DFS/FRS replication for your PostgreSQL data directory. DFS is not synchronous (which means you could lose committed data or parts thereof), and it does *not* guarantee write order (which means your database will quite likely end up completely corrupt if you get a failover whenever anything is happening). * DFS Replication detects changes on the volume by monitoring the update sequence number (USN) journal, and DFS Replication replicates changes only after the file is closed. This is also a problem - PostgreSQL generally doesn't close its files until it's really necessary. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] select * from users where user_id NOT in (select
# [EMAIL PROTECTED] / 2006-08-18 10:00:20 +0200: On 8/18/06, Stephan Szabo [EMAIL PROTECTED] wrote: When the subselect returns NULL for at least one row, you fall into this sort of case. x NOT IN (...) is equivalent to NOT(x IN (...)) which is NOT(x = ANY (...)) x = ANY (...) is basically defined as True if x = y is true for some y in the subselect False if x = y is false for all y in the subselect Unknown otherwise Since x = NULL is unknown and not true or false, you fall into the last case with your query and data. I've fixed my problem now by: select user_id, username from phpbb_users where user_id not in (select ban_userid from phpbb_banlist where ban_userid is not null); but still your explanation feels illogical to me even though I know you're right... The confusion comes from mismatch between the meaning of NULL in languages like C where it means NONE, and SQL, where it's more like ANY/UNKNOWN. I believe it'll make sense once you buy the latter meaning. Since NULL means UNKNOWN, can you tell which ids from (1, 2, 3, 4) are ABSOLUTELY NOT in (1, UNKNOWN)? You can't, because you don't know what that UNKNOWN (IOW NULL) is. It is unknown whether an unknown value equals any other value: test=# select coalesce((1 = NULL)::int::text, 'UNKNOWN'); coalesce -- UNKNOWN (1 row) test=# select coalesce((NULL = NULL)::int::text, 'UNKNOWN'); coalesce -- UNKNOWN (1 row) Thus, given these data test=# create table a (id int); test=# create table b (id int); test=# insert into a values (1); test=# insert into a values (2); test=# insert into a values (3); test=# insert into a values (4); test=# insert into b values (1); test=# insert into b values (NULL); this query test=# select * from a where id not in (select * from b); must return an empty set, because the NULL in b might stand for any of the four values in a. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation
Magnus Hagander [EMAIL PROTECTED] writes: Does it have to be a specific city? I'd rather it just chose GMT. The fact that there is an entry for GMT Daylight Time means that Windows' idea of this time zone is not pure GMT. Or is the translation table entry a complete work of fiction? No, it's a work of a simplistic perlscript IIRC. It simply looked for the first match it could find, based on the list found in the registry (the whole concept is a bit of an ugly hack, but it's the best we could come up with). If there is a more fitting timezone for it, it should be changed. I guess the question is whether, when Windows is using this setting, it tracks British summer time rules or not. Would someone check? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connection string
On Tue, Aug 15, 2006 at 07:31:31PM -0600, Michael Fuhr wrote: Will that be a minor fix that can be backpatched or will it be invasive enough to be fixed only in HEAD? I'll submit a documentation I just fixed it and applied the patch to 8.0 and 8.1 too. Please test it. 7.4 is way more different so I'd prefer to not touch it anymore. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Inserting Data
Hi All I have a basic problem that I hope can be addressed. I need to insert data from one table into three other tables. I attempted the following format. CREATE OR REPLACE FUNCTION p_id_monitor()RETURNS "trigger" AS$$Begininsert into p_id.loops (monitor) Select p_id.devices.devices_id Where p_id.devices.device_number = library.devices.device_number and library.devices.type_ = 'mon' ;insert into p_id.settings (monitor) Select p_id.devices.devices_id Where p_id.devices.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; insert into p_id.alarms (monitor) Select p_id.devices.devices_id Where p_id.devices.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; Return Null ;End;$$LANGUAGE 'plpgsql' ;create trigger mon after insert on p_id.devices for each row execute procedure p_id_monitor() ; Unfortunately this gavemultiple results on the target tables. Is there a format that will give mea singleinsert for each original field without the need of creating three triggers??? Bob
Re: [GENERAL] Migrating PostgreSQL database to MySQL/MS Access
On 8/18/06, Magnus Hagander [EMAIL PROTECTED] wrote: the .net 'way' of database application development is to keep all the logic in the .net middleware. please note that I am completely opposed to this because it obfuscates good data management practices. however based on the op's comments I am guessing he is doing things the .net way. Not really. It's one of the ways. Another way that's pushed pretty hard with .Net is sticking your logic in stored procedures. The .Net tools from MS integrate very well with situations where all your logic is in stored procedures - both in SQL Server 7.0/2000 (which has only TSQL stored procs) and 2005 (which has TSQL and also CLR/.Net language independent stored procedures). The tools let you do it either way. Unfortunately a lot of people don't realize the gains to be had by choosing the right one. yes. in fact, iirc the ms team blew out the java pet shop performance demo by making use of stored procedures on the database. the .net stack can be used to make excellent database applications if used properly. however, most if not all the .net developers I have worked with professionaly (with exceptions from the asp.net world) are vb6 expatriates who do thick client designs. visual studio very much encourages this as does the entire ado.net stack which is my least favorite part of .net...imo its over designed and a solution in search of a problem. 2005 yukon is actually a nice database, second in my opinion to only postgresql in overall capabilities and general design. you can't deny though that ms encourages development of logic in the middle tier or 'business layer', whatever that means. merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connection string
On Fri, Aug 18, 2006 at 06:01:02PM +0200, Michael Meskes wrote:
On Tue, Aug 15, 2006 at 07:31:31PM -0600, Michael Fuhr wrote:
Will that be a minor fix that can be backpatched or will it be
invasive enough to be fixed only in HEAD? I'll submit a documentation
I just fixed it and applied the patch to 8.0 and 8.1 too. Please test
it.
It works with a double-quoted string but not with a single-quoted
string as the documentation mentions.
% cat foo.pgc
#include stdio.h
int main(void)
{
ECPGdebug(1, stderr);
EXEC SQL CONNECT TO 'tcp:postgresql://localhost/test';
EXEC SQL DISCONNECT;
return 0;
}
% ecpg foo.pgc
foo.pgc:5: ERROR: syntax error at or near 'tcp:postgresql://localhost/test'
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Query optimization and indexes
Suppose I have an index on 5 columns (A, B, C, D, E). If my WHERE clause is not in that order, will the optimizer reorder them as necessary and possible? WHERE A=1 AND C=3 AND B=2 AND E=5 AND D=4 Obviously it can't reorder them in all cases: WHERE A=1 AND (C=3 OR B=2) AND (E=5 OR D=4) If I don't specify columns in the WHERE clause, how much can it use the index? I think it is smart enough to use beginning columns: WHERE A=1 AND B=2 How about skipping leading columns? WHERE B=2 How about skipping intermediate columns? WHERE A=1 AND C=3 Or both, which is probably the same? WHERE B=2 AND D=4? -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(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] Query optimization and indexes
[EMAIL PROTECTED] writes: Suppose I have an index on 5 columns (A, B, C, D, E). If my WHERE clause is not in that order, will the optimizer reorder them as necessary and possible? Yes, the optimizer understands about commutativity/associativity of AND and OR ;-) If I don't specify columns in the WHERE clause, how much can it use the index? Before (if memory serves) 8.1, the planner would only consider leading index columns as potential indexscan qualifiers. So given where a = 5 and c = 4; only the a = 5 clause would be used with the index. As of 8.1 it will consider using nonconsecutive index columns, but if you think for a bit about the storage order of a btree, you'll realize that you really need leading columns to keep down the amount of the index that gets scanned. A lot of the time, such a plan will be rejected as apparently more expensive than a seqscan. (This is for btrees, I don't recall the state of play for GIST indexes exactly.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inserting Data
On 8/18/06, Bob Pawley [EMAIL PROTECTED] wrote: Unfortunately this gave multiple results on the target tables. Is there a format that will give me a single insert for each original field without the need of creating three triggers??? Bob try using old/new in your trigger functions. insert into table (targetfield) new.field; merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Inserting Data
On Fri, Aug 18, 2006 at 09:27:19AM -0700, Bob Pawley wrote: I need to insert data from one table into three other tables. I attempted the following format. [...] insert into p_id.loops (monitor) Select p_id.devices.devices_id Where p_id.devices.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; Style recommendation: add a FROM clause to these queries. Missing FROM clauses are nonstandard and can cause unexpected results. PostgreSQL 8.0 and earlier allow such queries by default but in 8.1 they're disabled by default. See the add_missing_from configuration setting: http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#GUC-ADD-MISSING-FROM Unfortunately this gave multiple results on the target tables. What do you mean by multiple results? Do you mean that each row inserted into p_id.devices causes multiple rows to be inserted into p_id.loops, p_id.settings, and p_id.alarms? The trigger function's query doesn't reference the new row that was inserted into p_id.devices; it joins the entire table against library.devices. Did you mean to do something like the following? INSERT INTO p_id.loops (monitor) SELECT NEW.devices_id FROM library.devices WHERE NEW.device_number = library.devices.device_number AND library.devices.type_ = 'mon'; -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PITR Questions
On Aug 9, 2006, at 10:31 PM, Matthew T. O'Connor wrote: Jim C. Nasby wrote: Take a look at http://pgfoundry.org/projects/pgpitrha/ I had already seen this however it says that this project has yet to release any files, so I thought it was a dead project. Am I missing something? No, the project hasn't released files (yet), but they are available in CVS. I'll try to at least get a tarball up in the next week. Also, note that in 8.1, you have to manually archive the last WAL file after pg_stop_backup(), or you backup is useless until that WAL file fills up on its own and is archived. Right, I was hoping to find someone who had well written and tested bash script or something that did this. I think the project does that, but I can't swear to it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Importance of re-index
On Aug 10, 2006, at 1:57 AM, John Sidney-Woollett wrote: Disagree. We only apply reindex on tables that see lots of updates... With our 7.4.x databases we vacuum each day, but we see real performance gains after re-indexing too - we see lower load averages and no decrease in responsiveness over time. Plus we have the benefit of reduced disk space usage. You may be getting temporary performance gains by shrinking the indexes to a level that's un-sustainable. As you update the table, it needs to create new index keys, which have to go somewhere. Also, if I had a dollar for everytime someone thought they were safe from bloat because they were vacuuming once a day, I'd be living on a beach somewhere. There's very few databases I've seen where vacuuming once a day is sufficient, so it's very likely that you are suffering fromm bloat. I think that the two things go hand in hand, although vacuum is the most important. John Jim C. Nasby wrote: And if you're vacuuming frequently enough, there shouldn't be that much need to reindex. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
