Re: [GENERAL] pg_dumpall and version confusion
--- Original Message --- From: Joshua D. Drake [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 15/03/07, 23:51:18 Subject: Re: [GENERAL] pg_dumpall and version confusion If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. I can't even begin to imagine how difficult that would be on Windows! /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Column does not exist when trying to insert data.
Parthan SR wrote:
I get the following error in the browser, whent he fields for unit and
returnedas are left blank and hence becomes None.
I also tried to have some string value such as 'nos' and 'not reqd' for
'unit' and 'requiredas' but still it get a similar error
saying column 'nos' does not exist.
*ProgrammingError*: ERROR: column none does not exist INSERT INTO
orderitem
(item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas)
VALUES (6,10.00,None,39,1,1,2007-3-16,20-03-2007,None)
args = ('ERROR: column none does not exist\n\nINSERT INT...,
10.00,None,39,1,1,2007-3-16,20-03-2007,None)',)
You're looking for Null rather than None.
Oh, and your dates need to be quoted too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is This A Set Based Solution?
Bruno Wolff III schrieb:
On Mon, Mar 12, 2007 at 11:15:01 -0700,
Stefan Berglund [EMAIL PROTECTED] wrote:
I have an app where the user makes multiple selections from a list. I
can either construct a huge WHERE clause such as SELECT blah blah FROM
foo WHERE (ID = 53016 OR ID = 27 OR ID = 292 OR ID = 512) or I could
alternatively pass the string of IDs ('53016,27,292,512') to a table
returning function which TABLE is then JOINed with the table I wish to
query instead of using the unwieldy WHERE clause. The latter strikes me
as a far more scalable method since it eliminates having to use dynamic
SQL to construct the ridiculously long WHERE clause which will no doubt
ultimately bump up against parser length restrictions or some such.
How big is huge?
If the list of IDs is in the 1000s or higher, then it may be better to
load the data into a temp table and ANALYSE it before running your query.
Otherwise, for smaller lists the IN suggestion should work well in recent
versions.
Show me a user which really clicks on 1000 or more checkboxes on a
webpage or similar ;)
I'd think around 20 values is plenty.
Regards
Tino
---(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] simple coordinate system
On 3/15/07, Webb Sprague [EMAIL PROTECTED] wrote: http://www.postgresql.org/docs/8.2/static/datatype-geometric.html Have you looked at these yet? If not, you asked your question prematurely and should have read the docs. If so, in what respect do they not work for you? Yes, I've looked at those, I was thinking that point looked like a good type, but it's only 2d, so maybe I need a hint on how to use this in a 3d environment. -- regards, Robin ---(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] Practical question.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/15/07 22:14, louis gonzales wrote: Hello List, I want to write a statement-level trigger - one that happens once per statement - such that, immediately after an insert into a table(which gets a unique integer value as an ID from a defined sequence, being the primary key on the table), a new table is created with foreign key constraint on that unique ID. So if you insert 10,000 records into T, you then have 10,000 new tables? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF+nDLS9HxQb37XmcRAkrOAJ4pKzY/TVlm3K0aqiEY7ukhCfNZjwCfa5C7 mukqcoAmU2/OYr4QFVxjt6k= =sHA3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Dump a function
Hello, I'm working a lot with psql and plpgsql, I have a project based on psql and for the developpement I often need to dump a function, it is not really possible with psql because you don't have correctly the parameter. So I've made a patch for pg_dump which permit to dump all the functions or only one. It makes my development easier. Do you think it is useful ? How can I post this patch ? Is it a code review ? Kind Regards, D.
Re: [GENERAL] exception handling in plperlu
On Thu, Mar 15, 2007 at 10:06:06PM -0400, Jasbinder Singh Bali wrote: How about using a try catch block? isn't that more efficient that eval? Umm, eval is perl's equivalent of try/catch. There is no other way. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] pg_dumpall and version confusion
Dave Page [EMAIL PROTECTED] writes: --- Original Message --- From: Joshua D. Drake [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 15/03/07, 23:51:18 Subject: Re: [GENERAL] pg_dumpall and version confusion If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. I can't even begin to imagine how difficult that would be on Windows! As difficult as a new .bat file? -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is This A Set Based Solution?
Tino Wildenhain [EMAIL PROTECTED] writes: Show me a user which really clicks on 1000 or more checkboxes on a webpage or similar ;) I'd think around 20 values is plenty. On the other hand, show me a page with 1000 or more checkboxes to be clicked at once and I'd show a developer / designer that needs a new career... :-) -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dumpall and version confusion
Jorge Godoy wrote: Dave Page [EMAIL PROTECTED] writes: --- Original Message --- From: Joshua D. Drake [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 15/03/07, 23:51:18 Subject: Re: [GENERAL] pg_dumpall and version confusion If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. I can't even begin to imagine how difficult that would be on Windows! As difficult as a new .bat file? Thats the easy bit. The difficult bit is building the statically linked utilities with ssl, gettext and kerberos support. We found when porting PostgreSQL in the first place that for many of the supporting libraries, Windows is an afterthought and where on unix there might be static and dynamic builds, on Windows once dynamic is done, static doesn't matter. Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Column does not exist when trying to insert data.
On Thursday 15 March 2007 9:52 pm, Parthan SR wrote:
Hello,
My table 'orderitems' looks like this
create table orderitem (
id serial not null primary key,
item integer not null,
quantity numeric(6,2) not null,
unit varchar(10),
conference integer not null,
seller integer not null,
incharge integer not null,
orderdate date not null,
duedate date not null,
returnedas varchar,
);
I get all the values from a form, validate them and try to insert into
my postgres database table.
The integers in the above table are foreign key refernces (which I
have done using constraints).
The fields 'unit' and 'returnedas' are optional. I use python-psycopg
to handle the DB part.
When I execute the following statement..
INSERT INTO orderitem
(item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
) VALUES (%d,%f,%s,%d,%d,%d,%s,%s,%s) %
(params['item'],params['quantity'],params['unit'],
params['conference'],params['seller'],params['incharge'],params['orderdate'
],params['duedate'],params['returnedas'])
To make it make work here I had to use pyformat formatting. To illustrate-
VALUES(%(item)s,%(quantity)s,%(unit)s,%(conference)s,%(seller)s,%(incharge)s,
%(orderdate)s,%(duedate)s,% (returnedas)),
{'item':params['item'],'quantity':params['quantity'],'unit':params['unit'],'conference':
['conference'],'seller':params['seller'],'incharge':params['incharge'],
'orderdate':params[orderdate'],'duedate':params['duedate'],'returnedas':params['returnedas']}
params['conference'],params['seller'],params['incharge'],params['orderdate'
],params['duedate'],params['returnedas']
I get the following error in the browser, whent he fields for unit and
returnedas are left blank and hence becomes None.
I also tried to have some string value such as 'nos' and 'not reqd' for
'unit' and 'requiredas' but still it get a similar error
saying column 'nos' does not exist.
*ProgrammingError*: ERROR: column none does not exist INSERT INTO
orderitem
(item,quantity,unit,conference,seller,incharge,orderdate,duedate,returnedas
) VALUES (6,10.00,None,39,1,1,2007-3-16,20-03-2007,None)
args = ('ERROR: column none does not exist\n\nINSERT INT...,
10.00,None,39,1,1,2007-3-16,20-03-2007,None)',)
Where am I getting wrong ?
This is probably a psycopg issue. If the solution I suggested above does not
work you may want to take up the problem on the psycopg list.
--
Adrian Klaver
[EMAIL PROTECTED]
---(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] pg_dumpall and version confusion
Dave Page wrote: --- Original Message --- From: Joshua D. Drake [EMAIL PROTECTED] To: Dave Page [EMAIL PROTECTED] Sent: 15/03/07, 23:51:18 Subject: Re: [GENERAL] pg_dumpall and version confusion If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. I can't even begin to imagine how difficult that would be on Windows! Well supporting Windows in general is difficult ;), but perhaps it would only be hard the first time because the build process could be automated? J /D ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Lifecycle of PostgreSQL releases
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Erik Jones) transmitted: On Mar 14, 2007, at 6:17 PM, CAJ CAJ wrote: Hello, What is the lifecycle of a 8.0/8.1/8.2 releases? With 8.3 scheduled to be released in July, what will be the status of the 7.4.x branch? We are planning pg upgrades from 8.0.x/7.4.x to 6.2.x and were wondering if it's worth waiting for the 8.3 release. I really hope you meant upgrades to 8.2.x. And, no, it's not worth waiting. Upgrade at the soonest available opportunity, expecially the 7.4.x servers. I hope so too; if performance isn't a direct issue, and doing upgrades is seriously inconvenient, it might well be worth waiting for 8.3. We've got some little databases around here and there which don't *NEED* upgrading from any perspective other than that we'd loosely prefer to be on modern versions of PostgreSQL. For a small web contacts database, or for a lightly loaded MRTG-like application, an upgrade may not actually be very valuable. -- select 'cbbrowne' || '@' || 'gmail.com'; http://linuxdatabases.info/info/lisp.html I'm sure that nobody here would dream of misusing the Arpanet. It's as unthinkable as fornication, or smoking pot. -- RMS ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Practical question.
Actually, there will be creation of 2 new tables for each insert on 'primary' table, so for 10K records, we would have 20K tables. Those tables each will never grow more than a few records each. Is it better to have 1 monolithic table and have to search it, or small individual tables but many of them? Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/15/07 22:14, louis gonzales wrote: Hello List, I want to write a statement-level trigger - one that happens once per statement - such that, immediately after an insert into a table(which gets a unique integer value as an ID from a defined sequence, being the primary key on the table), a new table is created with foreign key constraint on that unique ID. So if you insert 10,000 records into T, you then have 10,000 new tables? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF+nDLS9HxQb37XmcRAkrOAJ4pKzY/TVlm3K0aqiEY7ukhCfNZjwCfa5C7 mukqcoAmU2/OYr4QFVxjt6k= =sHA3 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Email:[EMAIL PROTECTED] WebSite: http://www.linuxlouis.net Open the pod bay doors HAL! -2001: A Space Odyssey Good morning starshine, the Earth says hello. -Willy Wonka ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PgSql on Vista?
Actually the only reason we have an email disclaimer is for CSIA compliance. We know they have a highly dubious legal standing, but, hey, it's what the auditors require. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Konrad Neuwirth [mailto:[EMAIL PROTECTED] Sent: Friday, March 16, 2007 6:46 AM To: Brandon Aiken Subject: Re: [GENERAL] PgSql on Vista? I find it ironic that this -- to my mind, very on the spot -- comment comes with one of the mostly useless signature that there for ... plausible deniability. :-) Konrad On 3/16/07, Brandon Aiken [EMAIL PROTECTED] wrote: It was clear to me after 20 minutes of actually trying to use the OS that UAC was a gimmick designed to supply plausible deniability for the fact that Windows XP suffered so many problems with malware. They can simply ask were you using UAC? every time someone complains that their box got infected with some rootkit of some sort instead of actually having to secure their OS intelligently. They know darn well that everyone who uses the OS will turn it off. The silly thing is the boy-who-cried-wolf and nothing more. Frankly, I like Windows Server 2003's default of not allowing you to execute any program you got from elsewhere without explicitly unblocking it. It works just as well, IMX, although the error message you get when you forget about it is useless. B Aiken From: [EMAIL PROTECTED] on behalf of Dave Page Sent: Thu 3/15/2007 5:24 PM To: Paul Lambert Cc: Arkan; pgsql-general@postgresql.org Subject: Re: [GENERAL] PgSql on Vista? Paul Lambert wrote: After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Doesn't the security center keep popping up to point out that it's turned off? Regards Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(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] Lifecycle of PostgreSQL releases
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Joshua D. Drake) transmitted: There is zero question that 8.2 is faster than 7.4 *but* if 7.4 isn't slow for them... Note, that I meant no reason for him to upgrade 7.4 *right now*. He could wait for 8.3. (I think he should get off 7.4 in general) He could wait for 8.4 as well, as it will be probably faster and have more features than 8.3. Following your reasoning, one could wait essentially forever. You have got to be kidding. There is quite a bit of difference between 3 months and 17 months. From the persons email, he obviously has an array of production machines. This isn't hack fest 2000, just load up whatever. My professional opinion, and frankly the opinion we are telling our customers (except those that will explicitly benefit from something in 8.2) is to wait for 8.3. At Afilias, we're mostly thru our upgrades from 7.4 to 8.1; while I'm running buildfarm on 8.2, there's only one case where I'm presently considering an 8.2 upgrade (the app *would* specifically benefit), and I'm expecting that we won't bother much with the 8.2 branch. We had a particular challenge that some apps got stuck on 7.4 for excessively long because of JDBC customization; that held back upgrades. But now that that is unstuck, I'm still not pushing to schedule 8.2 upgrades for everything just because it's now more possible; the upgrade process involves quite a lot of work, enough that I think I'd rather skip to the next model. I daresay that 8.1 was everything I was hoping for; the performance improvements are looking really good. I was living with 7.4; 8.1 is plenty better and I think I can probably live with that for a year before saying oh, that's not good enough - I want 8.3!!! I'm not stuck on that answer; there's one system I *do* want to put on 8.2. But I'm mostly inclined to wait for 8.3 for most of my further upgrade needs. -- cbbrowne,@,gmail.com http://linuxdatabases.info/info/ Save the whales. Collect the whole set. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PgSql on Vista?
Brandon Aiken wrote: Actually the only reason we have an email disclaimer is for CSIA compliance. We know they have a highly dubious legal standing, but, hey, it's what the auditors require. google CSIA turns up a bunch of stuff. what is it exactly? -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Konrad Neuwirth [mailto:[EMAIL PROTECTED] Sent: Friday, March 16, 2007 6:46 AM To: Brandon Aiken Subject: Re: [GENERAL] PgSql on Vista? I find it ironic that this -- to my mind, very on the spot -- comment comes with one of the mostly useless signature that there for ... plausible deniability. :-) Konrad On 3/16/07, Brandon Aiken [EMAIL PROTECTED] wrote: It was clear to me after 20 minutes of actually trying to use the OS that UAC was a gimmick designed to supply plausible deniability for the fact that Windows XP suffered so many problems with malware. They can simply ask were you using UAC? every time someone complains that their box got infected with some rootkit of some sort instead of actually having to secure their OS intelligently. They know darn well that everyone who uses the OS will turn it off. The silly thing is the boy-who-cried-wolf and nothing more. Frankly, I like Windows Server 2003's default of not allowing you to execute any program you got from elsewhere without explicitly unblocking it. It works just as well, IMX, although the error message you get when you forget about it is useless. B Aiken From: [EMAIL PROTECTED] on behalf of Dave Page Sent: Thu 3/15/2007 5:24 PM To: Paul Lambert Cc: Arkan; pgsql-general@postgresql.org Subject: Re: [GENERAL] PgSql on Vista? Paul Lambert wrote: After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Doesn't the security center keep popping up to point out that it's turned off? Regards Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Practical question.
louis gonzales wrote: Actually, there will be creation of 2 new tables for each insert on 'primary' table, so for 10K records, we would have 20K tables. Those tables each will never grow more than a few records each. Is it better to have 1 monolithic table and have to search it, or small individual tables but many of them? 20k tables sounds insane. I am not sure why you wouldn't want just one table. I mean, you are saying a *few* records, so you are talking what 100k records in a single table instead? 100k is nothing. Joshua D. Drake Joshua D. Drake Ron Johnson wrote: On 03/15/07 22:14, louis gonzales wrote: Hello List, I want to write a statement-level trigger - one that happens once per statement - such that, immediately after an insert into a table(which gets a unique integer value as an ID from a defined sequence, being the primary key on the table), a new table is created with foreign key constraint on that unique ID. So if you insert 10,000 records into T, you then have 10,000 new tables? ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_dump warning format?
On Mar 15, 2007, at 9:08 PM, Tom Lane wrote: Erik Jones [EMAIL PROTECTED] writes: Basically I need a way for my script to know if it needs to abort because there were issues with the dump before it follows through with the drop db... Well, just checking the exit status from pg_dump ought to give you at least a go/no-go indicator. If you want to be significantly more paranoid, you could try the theory that *any* output to stderr is indicative of a problem. Thanks. I guess I just wasn't sure as to whether or not anything other than bad messages were written to stderr. Given the intent of stderr you wouldn't think so, but I've seen cases where it was so. erik jones [EMAIL PROTECTED] sofware developer 615-296-0838 emma(r)
Re: [GENERAL] Practical question.
louis gonzales wrote: Is it better to have 1 monolithic table and have to search it, or small individual tables but many of them? Ron Johnson wrote: Yes, 1 large table. This is what RDMS were designed for. ---(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] simple coordinate system
Robin Ericsson [EMAIL PROTECTED] writes: Yes, I've looked at those, I was thinking that point looked like a good type, but it's only 2d, so maybe I need a hint on how to use this in a 3d environment. Yeah, the built-in geometric types are all 2D. If you need 3D, perhaps PostGIS can help --- otherwise you're on your own :-(. But adding a new datatype to PG isn't hard, if you can hack C at all. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PgSql on Vista?
It's about $20,000 cheaper than ISO (ISA? IEC? One of those TLAs.) certification. Industrial engineering. CSIA is industrial control certification: http://www.controlsys.org/ -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, March 16, 2007 10:19 AM To: Brandon Aiken Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PgSql on Vista? Brandon Aiken wrote: Actually the only reason we have an email disclaimer is for CSIA compliance. We know they have a highly dubious legal standing, but, hey, it's what the auditors require. google CSIA turns up a bunch of stuff. what is it exactly? -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: Konrad Neuwirth [mailto:[EMAIL PROTECTED] Sent: Friday, March 16, 2007 6:46 AM To: Brandon Aiken Subject: Re: [GENERAL] PgSql on Vista? I find it ironic that this -- to my mind, very on the spot -- comment comes with one of the mostly useless signature that there for ... plausible deniability. :-) Konrad On 3/16/07, Brandon Aiken [EMAIL PROTECTED] wrote: It was clear to me after 20 minutes of actually trying to use the OS that UAC was a gimmick designed to supply plausible deniability for the fact that Windows XP suffered so many problems with malware. They can simply ask were you using UAC? every time someone complains that their box got infected with some rootkit of some sort instead of actually having to secure their OS intelligently. They know darn well that everyone who uses the OS will turn it off. The silly thing is the boy-who-cried-wolf and nothing more. Frankly, I like Windows Server 2003's default of not allowing you to execute any program you got from elsewhere without explicitly unblocking it. It works just as well, IMX, although the error message you get when you forget about it is useless. B Aiken From: [EMAIL PROTECTED] on behalf of Dave Page Sent: Thu 3/15/2007 5:24 PM To: Paul Lambert Cc: Arkan; pgsql-general@postgresql.org Subject: Re: [GENERAL] PgSql on Vista? Paul Lambert wrote: After install completes you can turn it back on... if you want - personally I leave it off, it's an incredibly annoying feature. Doesn't the security center keep popping up to point out that it's turned off? Regards Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ** LEGAL DISCLAIMER ** Statements made in this e-mail may or may not reflect the views and opinions of Wineman Technology, Inc. or its employees. This e-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] exception handling in plperlu
just wondeng why doesn't it let me put my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask; host=192.168.0.120; port=5432;, , ); in eval says Global symbol $dbh requires explicit package name at line where ever dbh is used Jas On 3/16/07, Martijn van Oosterhout kleptog@svana.org wrote: On Thu, Mar 15, 2007 at 10:06:06PM -0400, Jasbinder Singh Bali wrote: How about using a try catch block? isn't that more efficient that eval? Umm, eval is perl's equivalent of try/catch. There is no other way. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFF+oqkIB7bNG8LQkwRApyEAJ413cmp0bIuasEceWhXgTgSE6NOFACeNijH ZHknBQrtHqg30xL8Wh219Ik= =Xhhm -END PGP SIGNATURE-
Re: [GENERAL] simple coordinate system
On 3/16/07, Tom Lane [EMAIL PROTECTED] wrote: Robin Ericsson [EMAIL PROTECTED] writes: Yes, I've looked at those, I was thinking that point looked like a good type, but it's only 2d, so maybe I need a hint on how to use this in a 3d environment. Yeah, the built-in geometric types are all 2D. If you need 3D, perhaps PostGIS can help --- otherwise you're on your own :-(. But adding a new datatype to PG isn't hard, if you can hack C at all. My hope was that there was something between standard PostgreSQL and PostGIS as I didn't want to bring in the whole PostGIS into my application. But probably it's worth it anyways. -- regards, Robin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] exception handling in plperlu
On Mar 16, 2007, at 10:54 , Jasbinder Singh Bali wrote: just wondeng why doesn't it let me put my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask; host=192.168.0.120; port=5432;, , ); in eval says Global symbol $dbh requires explicit package name at line where ever dbh is used There is a mailing list for DBD::Pg: http://gborg.postgresql.org/mailman/listinfo/dbdpg-general/ But your problem in not related to the driver, rather it's a Perl issue. Your variable is defined within the block, so its scope is the eval block (my implies lexical scoping). If you define it outside the block and assign inside the block, then it will work. Cheers, M
Re: [GENERAL] exception handling in plperlu
You'll want something like this:
my $dbh = eval { DBI-connect();};
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jasbinder Singh
Bali
Sent: Friday, March 16, 2007 9:55 AM
To: Martijn van Oosterhout; Tom Lane; pgsql-general@postgresql.org
Subject: Re: [GENERAL] exception handling in plperlu
just wondeng why doesn't it let me put
my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask; host=192.168.0.120;
port=5432;, , );
in eval
says
Global symbol $dbh requires explicit package name at line where ever
dbh is used
Jas
On 3/16/07, Martijn van Oosterhout kleptog@svana.org wrote:
On Thu, Mar 15, 2007 at 10:06:06PM -0400, Jasbinder Singh Bali wrote:
How about using a try catch block?
isn't that more efficient that eval?
Umm, eval is perl's equivalent of try/catch. There is no other way.
Have a nice day,
--
Martijn van Oosterhoutkleptog@svana.org
http://svana.org/kleptog/
From each according to his ability. To each according to his ability
to litigate.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (GNU/Linux)
iD8DBQFF+oqkIB7bNG8LQkwRApyEAJ413cmp0bIuasEceWhXgTgSE6NOFACeNijH
ZHknBQrtHqg30xL8Wh219Ik=
=Xhhm
-END PGP SIGNATURE-
Re: [GENERAL] exception handling in plperlu
On Fri, Mar 16, 2007 at 10:54:49AM -0400, Jasbinder Singh Bali wrote: just wondeng why doesn't it let me put my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask; host=192.168.0.120; port=5432;, , ); in eval says Global symbol $dbh requires explicit package name at line where ever dbh is used Well, eval starts a new scope so the $dbh is only defined within that block. From the errors I assume you tried the other statements outside that scope? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] exception handling in plperlu
Jasbinder Singh Bali [EMAIL PROTECTED] writes:
just wondeng why doesn't it let me put
my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask; host=192.168.0.120; port=5432;,
, );
in eval
says
Global symbol $dbh requires explicit package name at line where ever dbh is
used
The my() variable goes out of scope when the eval {} is done. Declare
the variable outside the eval {}:
my $dbh;
eval { $dbh = connect(...) };
eval { $dbh-prepare(...:) };
-Doug
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dumpall and version confusion
Joshua D. Drake wrote: other deficiencies let's not add to complexity by having an --output-version. If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. Joshua D. Drake I am afraid that I have to agree with Tom here. Pg_dump has plenty of Well for me that would not be a option. I use object pascal and can't statically link C code into my apps. Doing the statically linked thing would also bloat any resulting binary. I don't think the whole --output-version thing would be a good idea either, but it would be a simple matter to add some logic for the GRANT ON SEQUENCE, which seems to be the only thing that is really causing problems at least between 8.1 and 8.2. The old way of granting permissions on sequences still works on 8.2 right? If so then maybe a switch to disable GRANT ON SEQUENCE would do the trick. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_dumpall and version confusion
Tony Caduto [EMAIL PROTECTED] writes: I don't think the whole --output-version thing would be a good idea either, but it would be a simple matter to add some logic for the GRANT ON SEQUENCE, which seems to be the only thing that is really causing problems at least between 8.1 and 8.2. You mean it's the only thing you've run into. There are a *lot* of changes in the output of different pg_dump versions. Even focusing on the narrow question of sequence changes between 8.1 and 8.2, what of the change from emitting SERIAL to emitting a separate CREATE SEQUENCE and ALTER SEQUENCE OWNED BY? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] exception handling in plperlu
Douglas == Douglas McNaught [EMAIL PROTECTED] writes:
Douglas my $dbh;
Douglas eval { $dbh = connect(...) };
Since eval returns its value (or undef if $@ has the error), you can shorten
this to:
my $dbh = eval { DBI-connect(...) };
and now either look at $@ (if you want to distinguish exception vs normal) or
just defined $dbh (if you want to know if you have a useful handle or not).
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
merlyn@stonehenge.com URL:http://www.stonehenge.com/merlyn/
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Bug in CREATE/DROP TABLESPACE command
On Windows Server 2003, if you create a tablespace to a location that doesn't exist, then try to remove that tablespace, you get an error that pg_tblspc/# does not exist. It appears that postgres created the tablespace internally, but not the folder. When you try to drop the tablespace, the folder doesn't exist and it reports an error. This sounds like two interacting bugs: 1) The tablespace should not have been created because the symlink could not be created. 2) It should be possible to remove a tablespace even if the symlink has already been deleted manually. The workaround is to create a pg_tblsc/# directory then do the drop. Where do I submit this bug? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [SQL] PostgreSQL to Oracle
On Thu, 2007-03-15 at 16:20 -0400, Robert Treat wrote: Do you find [HSODBC] works well? I've used it from some older Oracle instances connecting back into PostgreSQL and the results I had have been flakey at best. For us too. We tried in early 2006 with Oracle 8 via hsodbc to postgresql 7.4.6 and had several problems. From what I remember: - Oracle complained about SQLError when using unixODBC 3.0. This necessitated using 2.5. http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/0291.htm - We had type conversion problems. I can't remember many details of this, but I *think* they may have been text/varchar and timestamp/date. We don't use blobs. - It took our admins a fair bit of time to get working. It probably didn't help that Oracle was running on AIX. - The real nail in the coffin seemed to be that there was no way to push predicates to the remote side. This mean that a remote relation was materialized locally in order to join with local data and that we'd never get reasonable performance for cross-database joins (which, perhaps, it's not designed to do). I'm particularly curious about this last problem. Without pushing predicates to the remote side, it's hard for me to imagine that HSODBC would be terribly useful for anything but building a local warehouse. This seems so odd that I think I must be wrong, but I couldn't find any documentation that to support or discourage cross-database joins. Can someone out there enlighten me? What's the intent for HSODBC and what are its limitations? Can one access remote server side functions via HSODBC (eg, sqlplus select col1,plpgsqlfx(col1) from remotetable)? Thanks, Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [SQL] PostgreSQL to Oracle
On Fri, Mar 16, 2007 at 10:18:56AM -0700, Reece Hart wrote: On Thu, 2007-03-15 at 16:20 -0400, Robert Treat wrote: Do you find [HSODBC] works well? I've used it from some older Oracle instances connecting back into PostgreSQL and the results I had have been flakey at best. - The real nail in the coffin seemed to be that there was no way to push predicates to the remote side. This mean that a remote relation was materialized locally in order to join with local data and that we'd never get reasonable performance for cross-database joins (which, perhaps, it's not designed to do). DBI-Link has a way to push predicates to the remote side, but you have to do it manually because DBI-Link is a user-space application, i.e. it doesn't have access to PostgreSQL's planner. Let me know if you have questions on how this works :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(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] [SQL] PostgreSQL to Oracle
On 3/16/07, David Fetter [EMAIL PROTECTED] wrote: DBI-Link has a way to push predicates to the remote side, ... As Ezequias asked about migrating an application, I'm not quite sure why we're discussing this. Using HSODBC to move data permanently is quite good assuming you have no data type issues. -- Jonah H. Harris, Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 3rd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.2.3 VACUUM Timings/Performance
Hi All, Okay, I'm getting a little further now. I'm about to create entries in the pg_autovacuum system tables. However, I'm a little confused as to how I go about finding out the OID value of the tables. The pg_autovacuum table requires the OID of the table you want to create settings for (vacrelid). Can anyone shed some light on how I can extract the OID of the table? Also, what happens if you create a table without OID's, are you still able to add it's details in the pg_autovacuum table if there is no OID associated with a table? Name Type References Description vacrelid oid pg_class.oid The table this entry is for enabled bool If false, this table is never autovacuumed vac_base_thresh integer Minimum number of modified tuples before vacuum vac_scale_factor float4 Multiplier for reltuples to add to vac_base_thresh anl_base_thresh integer Minimum number of modified tuples before analyze anl_scale_factor float4 Multiplier for reltuples to add to anl_base_thresh vac_cost_delay integer Custom vacuum_cost_delay parameter vac_cost_limit integer Custom vacuum_cost_limit parameter freeze_min_age integer Custom vacuum_freeze_min_age parameter freeze_max_age integer Custom autovacuum_freeze_max_age parameter Thanks Bruce Bruce McAlister [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi Tom, Thanks for the suggestion. It's been a while since I replied to this as I had to go and do some further investigation of the docs with regards the autovacuum daemons configuration. According to the documentation, the formula's for the vacuum and analyze are as follows: Vacuum vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples Analyze analyze threshold = analyze base threshold + analyze scale factor * number of tuples My current settings for autovacuum are as follows: # - Cost-Based Vacuum Delay - vacuum_cost_delay = 200 # 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 #--- # AUTOVACUUM PARAMETERS #--- autovacuum = on # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on autovacuum_naptime = 1min # time between autovacuum runs autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 250# min # of tuple updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze autovacuum_freeze_max_age = 2 # maximum XID age before forced vacuum # (change requires restart) autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay autovacuum_vacuum_cost_limit= -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit Thus to make the autovacuum more aggressive I am thinking along the lines of changing the following parameters: autovacuum_vacuum_threshold = 250 autovacuum_analyze_threshold = 125 The documentation also mentions that when the autovacuum runs it selects a single database to process on that run. This means that the particular table that we are interrested in will only be vacuumed once every 17 minutes, assuming we have 18 databases and the selection process is sequential through the database list. From my understanding of the documentation, the only way to work around this issue is to manually update the system catalog table pg_autovacuum and set the pg_autovacuum.enabled field to false to skip the autovacuum on tables that dont require such frequent vacuums. If I do enable this feature, and I manually run a vacuumdb from the command line against that particular disabled table, will the vacuum still process the table? I'm assuming too, that the best tables to disable autovacuum on will be ones with a minimal amount of update/delete queries run against it. For example, if we have a table that only has inserts applied to it, it is safe to assume that that table can safely be ignored by autovacuum. Do you have any other suggestions as to which tables generally can be excluded from the autovacuum based on the usage
[GENERAL] planning issue
if you have time, could you offer advice on this: i'm doing a database cleanup right now -- 1.4M records -- and each query is taking 1 second i can't really wait 2 weeks for this to finish , so I'm hoping that someone will be able to help out the issue is that the planner keeps doing a sequential scan, despite the fact that the requesite columns are indexed. hoping someone may be able to offer advice:. SELECT * FROM table_a WHERE id != 10001 AND ( ( field_1 ilike '123' ) OR ( field_2 ilike 'abc' ) ) QUERY PLAN - Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346) Filter: ((id 10001) AND (((field_1)::text ~~* '123'::text) OR ((field_2)::text ~~* 'abc'::text))) however, i have the following indexes: table_a__pkey PRIMARY KEY, btree (id) table_a__idx__field_1 btree (field_1) table_a__idx__field_2 btree (field_2) can anyone offer advice to help me use the indexes on this ? // Jonathan Vanasco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] planning issue
QUERY PLAN
-
Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
Filter: ((id 10001) AND (((field_1)::text ~~* '123'::text) OR
((field_2)::text ~~* 'abc'::text)))
however, i have the following indexes:
table_a__pkey PRIMARY KEY, btree (id)
table_a__idx__field_1 btree (field_1)
table_a__idx__field_2 btree (field_2)
can anyone offer advice to help me use the indexes on this ?
create a function lower index and instead of calling ilike call ~
lower('123')
Joshua D. Drake
// Jonathan Vanasco
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---(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 8.2.3 VACUUM Timings/Performance
On Mar 16, 2007, at 2:06 PM, Bruce McAlister wrote: Hi All, Okay, I'm getting a little further now. I'm about to create entries in the pg_autovacuum system tables. However, I'm a little confused as to how I go about finding out the OID value of the tables. The pg_autovacuum table requires the OID of the table you want to create settings for (vacrelid). Can anyone shed some light on how I can extract the OID of the table? Also, what happens if you create a table without OID's, are you still able to add it's details in the pg_autovacuum table if there is no OID associated with a table? SELECT oid FROM pg_class where relname='table_name'; The WITH/WITHOUT OIDS clause of CREATE TABLE refers to whether or not to create oids for the rows of the table, not the table it itself. Tables always get an oid. erik jones [EMAIL PROTECTED] software developer 615-296-0838 emma(r)
[GENERAL] Oracle-PostgreSQL Coexistence/Interoperability (segue from PostgreSQL to Oracle)
On Fri, 2007-03-16 at 14:20 -0400, Jonah H. Harris wrote: As Ezequias asked about migrating an application, I'm not quite sure why we're discussing this. Using HSODBC to move data permanently is quite good assuming you have no data type issues. I missed the OP and therefore the link to app migration. However, HSODBC and its limitations were mentioned and therefore this discussion seems germane. No matter... I've posted this as a new thread. Perhaps you've implicitly answered my question -- you apparently think HSODBC's primary utility is to move data permanently. That's fine, but far short of my desire to have a efficient way to expose schema and data in a postgresql database to oracle's clients -- in real time, with pg functions, sane type conversions, and reasonable join performance. I appreciate the challenges of the latter for an optimizer, but it's technically possible (and, psst, could be very lucrative). I'm not trying to permanently migrate anything, but rather I'm trying to coexist seamlessly with Oracle and lessen the barriers to transitioning to postgresql (or v/v). Interoperability is really the only option when one has to contend with the integration of multiple database apps/schema from multiple developers and with Oracle-specific dependencies, such as I do. Pragmatically speaking, you can't just migrate such a tangle wholesale -- if there's no other route, it'll never happen (and, sniffle, I'll be inextricably bound to Oracle forever). -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.
Re: [GENERAL] planning issue
On Fri, 2007-03-16 at 12:17 -0700, Joshua D. Drake wrote:
QUERY PLAN
-
Seq Scan on table_a (cost=0.00..22779.68 rows=1 width=346)
Filter: ((id 10001) AND (((field_1)::text ~~* '123'::text) OR
((field_2)::text ~~* 'abc'::text)))
however, i have the following indexes:
table_a__pkey PRIMARY KEY, btree (id)
table_a__idx__field_1 btree (field_1)
table_a__idx__field_2 btree (field_2)
can anyone offer advice to help me use the indexes on this ?
create a function lower index and instead of calling ilike call ~
lower('123')
To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( lower(field_1) = '123' )
OR
( lower(field_2) = 'abc' )
)
That should be able to use your indexes correctly.
Regards,
Jeff Davis
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] planning issue
On Mar 16, 2007, at 3:48 PM, Jeff Davis wrote: To clarify a little: No clarifcation needed. Joshua Drake's suggestion made perfect sense and I was able to implement in 2 seconds. works like a charm! ETA 2 weeks - 30mins Thanks to all. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Possible planner bug?
I have two indexes defined on syslog_p: syslog_p_severity_ts_idx btree (severity, ts) syslog_p_ts_where_severity_1_idx btree (ts) WHERE severity = 1 The planner chooses what looks to me like a very strange plan for this query: = explain analyze select * from syslog where severity=1; QUERY PLAN --- Bitmap Heap Scan on syslog_p (cost=60404.31..303245.13 rows=1441595 width=180) (actual time=19229.159..64908.742 rows=1403715 loops=1) Recheck Cond: (severity = 1) - BitmapAnd (cost=60404.31..60404.31 rows=134693 width=0) (actual time=19102.728..19102.728 rows=0 loops=1) - Bitmap Index Scan on syslog_p_ts_where_severity_1_idx (cost=0.00..18783.83 rows=1441595 width=0) (actual time=339.103..339.103 rows=1405315 loops=1) - Bitmap Index Scan on syslog_p_severity_ts_idx (cost=0.00..40899.43 rows=1441595 width=0) (actual time=18659.069..18659.069 rows=1619356 loops=1) Index Cond: (severity = 1) Total runtime: 67261.257 ms (7 rows) Why look at both indexes and AND them together? If the tuple is alive, and has severity=1, it will be in the partial index. The only reason I can think is if the table is not VACUUMed frequently enough, then maybe it would find fewer dead tuples. Regards, Jeff Davis ---(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] Lifecycle of PostgreSQL releases
On Mar 15, 2007, at 10:22 AM, Alvaro Herrera wrote: He could wait for 8.4 as well, as it will be probably faster and have more features than 8.3. Following your reasoning, one could wait essentially forever. H... precisely the reason my cell phone hasn't been replaced in a long time :-) I'm evaluating whether to upgrade from 8.1 to 8.2 still... but the jump from a 7.4 to 8.2 is to me a no-brainer once you've ironed out the minor issues with syntax pickyness that 8.x imposes on some sloppy queries that worked with 7.4 smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Creation of a read-only role.
Hello. When we start using of any replication system (e.g. Slony) we need to create a read-only role for access the database. This role must be able to read anything, but should NOT be able to INSERT, UPDATE or DELETE for all database objects. Overall, we need 3 roles: 1. Administrator: can do anything with a database (by default this user is already exists - postgres). 2. Read-only: can only read. Runs on all slave nodes. 3. Read-write: can write, but cannot change the database schema. Runs on master node only. Is any way to easily create and maintain these standard roles? Now I have written a stored procedure which iterates over the pg_catalog and runs a lot of REVOKE GRANT commands, but it seems to be not an universal solution, because: 1. I have to re-run this procedure after I change the database schema. (Very bad item! Can we avoid it?) 2. It looks like a broot-force method, and nothing said about it in the Slony documentation (strange). 3. In MySQL (e.g.) there is a one-command way to create these three roles. Again, these 3 roles seems to be a de-facto standard for replication systems, but I found nothing about this question in the Google.
Re: [GENERAL] Lifecycle of PostgreSQL releases
[EMAIL PROTECTED] (Alvaro Herrera) writes: Joshua D. Drake escribió: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Erik Jones wrote: I really hope you meant upgrades to 8.2.x. And, no, it's not worth waiting. Upgrade at the soonest available opportunity, expecially the 7.4.x servers. I don't really agree with this. If he is running 7.4.16 there very well may be zero compelling reason for him to upgrade. Really? There are any number of anecdotal reports of massive speed improvements between 7.x and various 8.x versions. Not to mention a few feature improvements. There is zero question that 8.2 is faster than 7.4 *but* if 7.4 isn't slow for them... Note, that I meant no reason for him to upgrade 7.4 *right now*. He could wait for 8.3. (I think he should get off 7.4 in general) He could wait for 8.4 as well, as it will be probably faster and have more features than 8.3. Following your reasoning, one could wait essentially forever. That *is* true for the case of the really lightly used database where there isn't any particularly compelling reason to look to upgrade. - If it's providing results fast enough for the users, then they have no reason to be demanding an upgrade. - If it has enough functionality to support the queries they're running, again, there's no good reason to demand an upgrade The only reason to feel forced to upgrade is that at some point, the old version essentially falls out of support. I'd feel uncomfortable, today, about having any 7.3 databases around, from that perspective, and I'd certainly be inclined to leap them up, probably to 8.2. My discomfort level is such that I'd want to do that now, before 8.3 appears. I fully expect that once 8.3 is around, interest in supporting 7.4 will also begin to dwindle, and that's a good enough reason to want to get off 7.4, not now, but soon enough. Now, *eventually* Josh will want to upgrade to a new Saab, because the old one will start getting expensive to maintain (fundamentally because more and more bits of it will start aging noticeably). My Honda Civic needs a bit of work now; in a couple years, the cost of maintaining it may grow, and I'll want to get something new. *Eventually*, I'll want to upgrade to a new cell phone because the old one will be scratched up and the battery will cease to hold a decent charge. But that's not true yet. We don't need upgrades on these things *instantly* because of the huge value of the utility of the upgraded features; we're using the features modestly enough that we can afford to wait a while. On the other hand, I have some apps where I'm quite looking forward to 8.3 because I expect to want to use 8.3's improvements in speed and functionality Pretty Early in its release cycle. Both scenarios can certainly occur. -- cbbrowne,@,linuxdatabases.info http://www3.sympatico.ca/cbbrowne/multiplexor.html Rules of the Evil Overlord #206. When my Legions of Terror park their vehicle to do reconnaissance on foot, they will be instructed to employ The Club. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible planner bug?
Jeff Davis [EMAIL PROTECTED] writes: The planner chooses what looks to me like a very strange plan for this query: Exactly which PG version is this? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Practical question.
On 3/16/07, louis gonzales [EMAIL PROTECTED] wrote: I want to write a statement-level trigger - one that happens once per statement - such that, immediately after an insert into a table(which gets a unique integer value as an ID from a defined sequence, being the primary key on the table), a new table is created with foreign key constraint on that unique ID. hi, i think what you;re trying to do is wrong - having that many tables simply cannot work properly. additionally - i think you're misinformed. the kind of action you would like to trigger on is not per statement but per row. example: insert into table x (field) select other_field from other_table; if this insert would insert 10 records - once per statement trigger would be called only once. but anyway - what you're proposing will lead to many, many problems. (plus it will never scale correctly). depesz ---(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] exception handling in plperlu
On 3/16/07, Jasbinder Singh Bali [EMAIL PROTECTED] wrote:
just wondeng why doesn't it let me put
my $dbh=DBI-connect(dbi:Pg:dbname=dbunmask;
host=192.168.0.120; port=5432;, , );
in eval
you dont need to put -connect in eval.
just set connect option 'raiseerror' to 0 (and printerror as well). in
this situation it will simply set errorcode. no eval{} required.
depesz
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PgSql on Vista?
Hi, Am Donnerstag, 15. Mär 2007, 15:38:56 +0100 schrieb A. Kretschmer: am Wed, dem 14.03.2007, um 1:24:01 -0700 mailte Arkan folgendes: have anybody installed PgSQL on Windows Vista? [...] Someone write this about vista: 1. Disable UAC 2. EnableAdministrator account 3. Login as administrator Sorry. I am bored to write this and you are bored to read this: starting with or turning to Postgres is a good opportunity to convince your customer to use an OS rather than a relegating makeshift. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
