Re: [GENERAL] RPM compat-postgresql-libs-4-2 for IA-64
Hello Devrim, hello List, No problem to share with you the RPM. Yesterday afternoon, I built the 8.2.3 RPM for RHEL4-AS and IA-64. I propose to work off-list with you Devrim, so you could take the RPM and check them. Is it OK ? Regards, Alexandra Devrim GUNDUZ wrote: Hello, On Mon, 2007-02-19 at 16:49 +0100, DANTE Alexandra wrote: Where can I find the RPM compat-postgresql-libs-4-2 for RHEL4-AS and IA-64 ? Those libs are extracted from 8.1.X RPMs and put together to form an RPM package. If you can build and send us 8.1.8 RPMs, I can build and upload that compat RPM. Also, I'd be happy if you again share 8.2.3 RPMs with us :) Regards, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RPM compat-postgresql-libs-4-2 for IA-64
Hi, On Tue, 2007-02-20 at 09:14 +0100, DANTE Alexandra wrote: I propose to work off-list with you Devrim, so you could take the RPM and check them. Is it OK ? Ok for me :) Regards, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] How to force disconnections from a Perl script?
On Tue, 2007-02-20 at 03:43, Tom Lane wrote: Nonsense. pg_stat_activity + kill -TERM should solve this problem reasonably well. Some of us don't trust kill -TERM 100%, which is why it's not currently exposed as a standard function, but if you're using a reasonably recent PG release it's probably safe. Oh, OK... so pg_stat_activity shows all connections now ? Or it was always like that ? For some reason I thought it will only show connections where a transaction is in progress. Idle can mean there is nothing executing at all... are those shown too ? Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Having performance problems with TSearch2
I have problems with queries over tsearch index.
I have a table of books, with 120 registers. I have created an GIST
index over the title and subtitle,
CREATE INDEX idxts2_titsub_idx ON public.libros USING gist
(idxts2_titsub);
My problems started when i execute my queries.
For example, i execute a simple query like this one:
explain analyze
SELECT isbn, titulo
FROM libros
WHERE idxts2_titsub @@ to_tsquery('default_spanish',
to_ascii('sevilla'))
ORDER BY titulo
LIMIT 10;
This query take more than 10 secods, and i think this is too much for such
an easy query.
Every night, i execute a VACUUM ANALYZE over my data base.
The query plan of this query, is the next one:
QUERY PLAN
Limit (cost=4725.18..4725.20 rows=10 width=56) (actual time=
17060.826..17061.078 rows=10 loops=1)
- Sort (cost=4725.18..4728.23 rows=1223 width=56) (actual time=
17060.806..17060.874 rows=10 loops=1)
Sort Key: titulo
- Bitmap Heap Scan on libros (cost=45.28..4662.46 rows=1223
width=56) (actual time=10831.530..16957.667 rows=2542 loops=1)
Filter: (idxts2_titsub @@ '''sevilla'''::tsquery)
- Bitmap Index Scan on idxts2_titsub_idx
(cost=0.00..45.28rows=1223 width=0) (actual time=
10830.051..10830.051 rows=2586 loops=1)
Index Cond: (idxts2_titsub @@ '''sevilla'''::tsquery)
Total runtime: 17062.665 ms
I have no idea what is happening. Why the Bitmap Index Scan and the Bitmap
Heap Scan cost so much time?
I have a 2GB RAM memory Server.
Thanks every body for your healp and sorry for my English
Re: [GENERAL] Having performance problems with TSearch2
Use GIN index instead of GiST I have a table of books, with 120 registers. I have created an GIST index over the title and subtitle, -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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
[GENERAL] Having performance problems with TSearch2
I have problems with queries over tsearch index.
I have a table of books, with 120 registers. I have created an GIST
index over the title and subtitle,
CREATE INDEX idxts2_titsub_idx ON public.libros USING gist
(idxts2_titsub);
My problems started when i execute my queries.
For example, i execute a simple query like this one:
explain analyze
SELECT isbn, titulo
FROM libros
WHERE idxts2_titsub @@ to_tsquery('default_spanish',
to_ascii('sevilla'))
ORDER BY titulo
LIMIT 10;
This query take more than 10 secods, and i think this is too much for such
an easy query.
Every night, i execute a VACUUM ANALYZE over my data base.
The query plan of this query, is the next one:
QUERY PLAN
Limit (cost=4725.18..4725.20 rows=10 width=56) (actual time=
17060.826..17061.078 rows=10 loops=1)
- Sort (cost=4725.18..4728.23 rows=1223 width=56) (actual time=
17060.806..17060.874 rows=10 loops=1)
Sort Key: titulo
- Bitmap Heap Scan on libros (cost=45.28..4662.46 rows=1223
width=56) (actual time=10831.530..16957.667 rows=2542 loops=1)
Filter: (idxts2_titsub @@ '''sevilla'''::tsquery)
- Bitmap Index Scan on idxts2_titsub_idx
(cost=0.00..45.28rows=1223 width=0) (actual time=
10830.051..10830.051 rows=2586 loops=1)
Index Cond: (idxts2_titsub @@ '''sevilla'''::tsquery)
Total runtime: 17062.665 ms
I have no idea what is happening. Why the Bitmap Index Scan and the Bitmap
Heap Scan cost so much time?
I have a 2GB RAM memory Server.
Thanks every body for your healp and sorry for my English
Re: [GENERAL] Advisory on possibly insecure security definer functions
On Wed, 14 Feb 2007, Peter Eisentraut wrote: By installing functions or operators with appropriate signatures in other schemas, users can then redirect any function or operator call in the function code to implementations of their choice [snip] The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. This fix is not enough in certain common configurations. I've sent a proof of concept to securityatpostgresql.org, but I won't disclose it before I'm allowed to by security team. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(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] boolean operator on interval producing strange results
On 2/19/07, Tom Lane [EMAIL PROTECTED] wrote:
Merlin Moncure [EMAIL PROTECTED] writes:
# select ((now() - '1 day'::interval)::timestamp - now()) 0;
?column?
--
f -- looks busted to me
(1 row)
If you'd casted to timestamptz then I'd agree this is busted.
As-is, it might have something to do with your timezone setting,
which you didn't mention?
show timezone reports us/eastern in both cases. also, i don't really
see how this matters, since we are comparing '-1 days'::interval with
0 in both cases. in fact:
# show timezone;
TimeZone
US/Eastern
(1 row)
# select ('-1 days'::interval) 0;
?column?
--
f
(1 row)
as it happens, after months and months of faithful service, this
machine decided to dump core last night. so, we are scheduling some
downtime + yum update. (my previous mail was wrong, production was the
non-updated box). this is the only environmental difference I can
think of. At the very least I can report back if this fixes the
problem.
merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] boolean operator on interval producing strange results
All of these statements produce 'f' for me as well, via 8.2.1 on RHEL 4.
select ((now() - '1 day'::interval)::timestamp - now()) 0;
select ((now() - '1 day'::interval)::timestamptz - now()) 0;
select ('-1 days'::interval) 0;
But all of these return 't':
select ((now() - '1 day'::interval)::timestamp - now()) '0'::interval;
select ((now() - '1 day'::interval)::timestamptz - now())
'0'::interval;
select ('-1 days'::interval) '0'::interval;
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure
Sent: Tuesday, February 20, 2007 8:30 AM
To: Tom Lane
Cc: postgres general
Subject: Re: [GENERAL] boolean operator on interval producing strange
results
On 2/19/07, Tom Lane [EMAIL PROTECTED] wrote:
Merlin Moncure [EMAIL PROTECTED] writes:
# select ((now() - '1 day'::interval)::timestamp - now()) 0;
?column?
--
f -- looks busted to me
(1 row)
If you'd casted to timestamptz then I'd agree this is busted.
As-is, it might have something to do with your timezone setting,
which you didn't mention?
show timezone reports us/eastern in both cases. also, i don't really
see how this matters, since we are comparing '-1 days'::interval with
0 in both cases. in fact:
# show timezone;
TimeZone
US/Eastern
(1 row)
# select ('-1 days'::interval) 0;
?column?
--
f
(1 row)
as it happens, after months and months of faithful service, this
machine decided to dump core last night. so, we are scheduling some
downtime + yum update. (my previous mail was wrong, production was the
non-updated box). this is the only environmental difference I can
think of. At the very least I can report back if this fixes the
problem.
merlin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---(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] boolean operator on interval producing strange results
Merlin Moncure [EMAIL PROTECTED] writes: On 2/19/07, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: # select ((now() - '1 day'::interval)::timestamp - now()) 0; ?column? -- f -- looks busted to me (1 row) If you'd casted to timestamptz then I'd agree this is busted. As-is, it might have something to do with your timezone setting, which you didn't mention? show timezone reports us/eastern in both cases. Oooohhh ... it's not timezone, it's locale. EXPLAIN, when used correctly, shows how the system is interpreting this, and it's not what you think: regression=# explain select 1 where ((now() - '1 day'::interval)::timestamp - now()) 0; QUERY PLAN --- Result (cost=0.02..0.03 rows=1 width=0) One-Time Filter: ((now() - '1 day'::interval))::timestamp without time zone)::timestamp with time zone - now()))::text '0'::text) (2 rows) Still another demonstration of why implicit casts to text are evil :-( Try putting the '0' in quotes. (And drop the useless explicit cast to timestamp while you're at it.) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Password issue revisited
Added to TODO for Win32: o Check .pgpass file permissions --- Shane Ambler wrote: Michael Schmidt wrote: Fellow PostgreSQL fans, 1. I don't see that this would pose a major security risk. In fact, in applications where the user enters the password for each session, the password need never be saved to disk, which seems a definite security advantage. Some folks have noted that .pgpass is a plain text file, hence it could be vulnerable. Yes it is a plain text file but if you want to use it then you need to ensure the security is sufficient on the file or it won't be used. As per the manual - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) So this security feature should be something that gets added to the windows version. But otherwise the security of the user's account that has a .pgpass file is the decider on whether it is vulnerable. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] indexes across multiple tables
On 2/18/07, Chris [EMAIL PROTECTED] wrote:
Toby Tremayne wrote:
Hi all,
I'm just experimenting with tsearch2 - I have it all working fine but I
was wondering if there's a way to create indexes containing vector
columns from multiple tables? Or if not, how do people usually manage
this kind of issue?
Postgres doesn't support multi-table indexes so there's no way tsearch2
would be able to.
What exactly are you trying to achieve?
--
Probably best to join the tables, then simply do the search in the
WHERE clause. Something like
select p.partname, s.subassemblyname
from part p join subassembly s
on p.partid = s.partid
where p.partidx @@ to_tsquery('Some happy string')
or s.subidx @@ to_tsquery('Some happy string')
That's how I do it, anyway...
- Ian
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Warning TupleDesc reference leak
Hello, after upgrade to 8.2 version, PostgreSQL throws following warnings: WARNING: TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f60ad0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x4203d908 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fdc410 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fbb568 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42044bf0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42038e60 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41feebc0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fa0018 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fd9c30 (16425,-1) still referenced What it means ? Thanks ML ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Password issue revisited
Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? //Magnus On Tue, Feb 20, 2007 at 09:49:00AM -0500, Bruce Momjian wrote: Added to TODO for Win32: o Check .pgpass file permissions --- Shane Ambler wrote: Michael Schmidt wrote: Fellow PostgreSQL fans, 1. I don't see that this would pose a major security risk. In fact, in applications where the user enters the password for each session, the password need never be saved to disk, which seems a definite security advantage. Some folks have noted that .pgpass is a plain text file, hence it could be vulnerable. Yes it is a plain text file but if you want to use it then you need to ensure the security is sufficient on the file or it won't be used. As per the manual - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) So this security feature should be something that gets added to the windows version. But otherwise the security of the user's account that has a .pgpass file is the decider on whether it is vulnerable. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How would you handle updating an item and related stuff all at once?
On 2/17/07, Rick Schumeyer [EMAIL PROTECTED] wrote: This may be bad design on my part, but... Not at all. Very common scenario I have three tables of interest...Account, Employee, and AccountEmployeeRelation. There is a many-to-many relationship between accounts and employees. The join table also contains a column indicating what role the employee plays on this account. My interface is a web app (I'm trying out Ruby on Rails). On the edit account screen I want to edit account attributes AND be able to add/delete employees in one form. The gui part seems to work. BUT, when I update I'm not sure how to handle updating the AccountEmployeeRelation table. During the update, relations may have been added or deleted, and existing relations may have been changed. It seems to me the easiest thing to do is delete all the relations for the account and create all new ones with the data submitted from the form. This seems wasteful, but the alternative would be a pain. Or is this really the best way? I tried a bunch of cleverness where I checked for existence and updated if required, etc but came back to just zapping them all and inserting. As long as it's done in a transaction and there are not too many, it's fine. It doesn't eat any more space and eats less cycles than doing it the hard way. Thanks for any advice. You're welcome! Completely off topic, (but not worth a separate post) I have been forced to use a little bit of mysql lately...did you know that if you use transaction and foreign key syntax with myisam tables, it does not complain...it just silently ignores your requests for transactions and foreign key checks. Yikes! I had incorrectly assumed I would get an error message indicating that transactions are not supported. Oh well. Sorry about that. Nuff said 8^/ - Ian ---(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] Database performance comparison paper.
Am 19.02.2007 um 17:49 schrieb Jan Wieck: Oh, this one wasn't about raw speed of trivial single table statements like all the others? No, it wasn't. They also tested the insert performance of a system without foreign keys and without transactions (MySQL MyISAM) against systems with foreign key handling and transactions. It would be more or less the same, if you compare copy against insert performance on PostgreSQL and state that insert should be as fast as copy without saying why. Btw: these guys claim to be database consultants. cug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password issue revisited
Magnus Hagander wrote: Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? OK, I added a comment to fe-connect.c explaining why we don't need to check the permissions of .pgpass, and removed the TODO. Thanks. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Warning TupleDesc reference leak
On Tue, 2007-02-20 at 08:55, Marek Lewczuk wrote: Hello, after upgrade to 8.2 version, PostgreSQL throws following warnings: WARNING: TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) still referenced I don't know what that means, but which 8.2 version? 8.2.3 is the latest 8.2 version. If you're not running that update to that first and see if it helps. ---(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] Write errors in postgres log
Hello Tom, Thanks for the response. My replies inline... On 2/18/07, Tom Lane [EMAIL PROTECTED] wrote: CAJ CAJ [EMAIL PROTECTED] writes: We have 2 servers running postgres database 8.0.3 serving a web application. You do realize we are up to 8.0.12 in that branch? You're missing nearly two years worth of bug fixes. Yes we're aware of it. The web-application is from a 3rd party vendor and comes bundled with postgresql 8.0.3. Is there a specific fix in the recent releases that might have fixed the data corruption I described? We can then request the vendor to upgrade their software stack including Pg. I'm also concerned about the security fixes as well. ERROR: xlog flush request 2/66B19020 is not satisfied --- flushed only to 2/5F8F95A2 ... CONTEXT: writing block 754 of relation 1663/17230/17443 Looks a bit ugly --- might be worth looking at that block with pg_filedump to see what the extent of the corruption is. Will try pg_filedump and let you know what happens. 1. pg_reset_xlog did not work. Define did not work. What did you do exactly, and what results did you get? I apologize for the lack of information. I will get that to you as soon as I can. In brief, ran pg_resetxlog which identified the last good WAL address. Postgres successfully recovered at start but.pg_dump ran into similar errors. 2. Recent backups are corrupted as well. It's not possible for a pg_dump backup to be affected by this problem. How exactly are you making your backups, and what happens when you try to use them? We shutdown the database and make a copy of the pgdata directory. pg_dump/pg_restore takes a long time to be used for backups. We are exploring the PITR method (a little too late), Since the data corruption goes way back, our recent backup is corrupted as well (we see the same errors when we restore the old pgdata backup) I appreciate your response and feel free to ask for any information that might help. Thanks
Re: [GENERAL] boolean operator on interval producing strange results
On 2/20/07, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: On 2/19/07, Tom Lane [EMAIL PROTECTED] wrote: Merlin Moncure [EMAIL PROTECTED] writes: # select ((now() - '1 day'::interval)::timestamp - now()) 0; ?column? -- f -- looks busted to me (1 row) If you'd casted to timestamptz then I'd agree this is busted. As-is, it might have something to do with your timezone setting, which you didn't mention? show timezone reports us/eastern in both cases. Oooohhh ... it's not timezone, it's locale. EXPLAIN, when used correctly, shows how the system is interpreting this, and it's not what you think: regression=# explain select 1 where ((now() - '1 day'::interval)::timestamp - now()) 0; QUERY PLAN --- Result (cost=0.02..0.03 rows=1 width=0) One-Time Filter: ((now() - '1 day'::interval))::timestamp without time zone)::timestamp with time zone - now()))::text '0'::text) (2 rows) Still another demonstration of why implicit casts to text are evil :-( Try putting the '0' in quotes. (And drop the useless explicit cast to timestamp while you're at it.) you are correct once again, production was recently updated and locale was not properly set. The sql in question was already fixed, it was sloppy and I was just curious what was going on. completely agree regarding implicit casts...evil! (especially on types like interval) merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Have anyone this man e-mail ?
No I didn't. I just need an expert in Slony-I My application needs critically an slony-I master to run correctly. In my incursions Slony-I appears quite difficult. I need someone to make a configuration step-by-step (in msn messenger) configuration. The documentation of Robert does not work for me (I have some douts). My best regards... Ezequias 2007/2/16, Vivek Khera [EMAIL PROTECTED]: On Feb 16, 2007, at 12:46 PM, Ezequias Rodrigues da Rocha wrote: Hi list, I am looking for this guy for some help with Slony-I. Then why don't you send Robert a direct email? He's not that hard to find with google. Or perhaps ask your question here; there are lots of smart folks here, some of which may even do windows. Obviously last month when you posted this exact same query you didn't get a response... ---(end of broadcast)--- TIP 6: explain analyze is your friend -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(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] Warning TupleDesc reference leak
Marek Lewczuk wrote: Hello, after upgrade to 8.2 version, PostgreSQL throws following warnings: WARNING: TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) still referenced Are there C functions, or anything interesting which we should know about your database? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Warning TupleDesc reference leak
Marek Lewczuk wrote: Hello, after upgrade to 8.2 version, PostgreSQL throws following warnings: WARNING: TupleDesc reference leak: TupleDesc 0x42051d90 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41f60ad0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x4203d908 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fdc410 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fbb568 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42044bf0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x42038e60 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41feebc0 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fa0018 (16425,-1) still referenced WARNING: TupleDesc reference leak: TupleDesc 0x41fd9c30 (16425,-1) still referenced What it means ? there is at least one known cause for that though that is fixed in 8.2.3 and involves plpgsql and subtransactions/exception blocks - what version are you running exactly ? Stefan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] open source - content management system - that uses PostGreSQL
One nice CMS package -- it doesn't force you to use Postgresql, but that is (IIRC) the default -- and python: http://www.djangoproject.com/ HTH-- -frank ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Complex search advice?
I want to be able to build complex search and reporting capabilities in to our PHP5 application. We want to be able to save searches for later use. We also want to build queries from virtually any field in certain tables from our PHP app. I hope to do as much within postgresql as possible. Then I start looking for how to enumerate field names, etc. Before I spend countless hours on seeing if some of my ideas will work and coding them, I hoped to receive some guidance here as to where I should start and possibly what are the elements of something like this? Will anything in contrib help? -- Robert ---(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] Complex search advice?
On Tue, 2007-02-20 at 11:26, Robert Fitzpatrick wrote: I want to be able to build complex search and reporting capabilities in to our PHP5 application. We want to be able to save searches for later use. We also want to build queries from virtually any field in certain tables from our PHP app. I hope to do as much within postgresql as possible. Then I start looking for how to enumerate field names, etc. Before I spend countless hours on seeing if some of my ideas will work and coding them, I hoped to receive some guidance here as to where I should start and possibly what are the elements of something like this? Will anything in contrib help? Depending on the scale of your dataset, you might wanna look into tsearch2, it's a full text search engine that lets you search very large text data sets quickly. If you're not going to have hundreds of thousands of records to search through, then you might be able to just roll your own using standard searches. If by enumerate field names you mean how to ask postgresql what field names it has on a table, the easy easy way in PHP is to run a query like select * from table limit 1 then use pgsql_fetch_assoc() to grab the first row and iterate through the keys you get back. If you already know the column names, then you can use an array to store the ones you want to search on and iterate over that. There's lots of examples on the net for how to do this, and look at www.phpbuilder.com for forums discussing just this. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password issue revisited
Bruce Momjian wrote: Magnus Hagander wrote: Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. I believe that .pgpass on *nix won't be used if it is readable by anyone except the current user. From the docs - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) I would think that if they are using FAT filesystem (which is only partially supported for developers benefit) then they can't use pgpass. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? I would say the same as the *nix version - if it is readable or writable by anyone except the current user it is potentially at risk, the current user connecting to pgsql is the only use for this file. Which I believe is the whole point of the TODO entry, stop anyone using the pgpass file without proper security. The other thing to consider is that pgpass is the file referenced by PGPASSFILE - the user can set this to point to a file anywhere on any drive available. It is users who only think they know what they are doing that create and modify it by hand and then kick up a fuss when it causes trouble. If we want the windows clients to be used then I do think that the security decisions should not be dropped for windows clients. OK, I added a comment to fe-connect.c explaining why we don't need to check the permissions of .pgpass, and removed the TODO. Thanks. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Password issue revisited
The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. The only case when it's not protected by default is if you're usnig FAT filesystem, in which case there is nothing you can do about it anyway. On unix, the file will often be created in outside-readable mode by default, depending on how your OS is set up. I believe that .pgpass on *nix won't be used if it is readable by anyone except the current user. No, root can always read it. On unix, there is one root. On windows, the concept of administrator is less clear. From the docs - The permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. (The file permissions are not currently checked on Microsoft Windows, however.) I would think that if they are using FAT filesystem (which is only partially supported for developers benefit) then they can't use pgpass. If they are using FAT, the obviously don't care about the security of the system anyway, so it's not a problem, IMHO. So we only have to care about people who use NTFS. So to reach a situation where the file lives in an unprotected directory, you must actively open up the directory in question. Which is hidden from default view, so you really need to know what you're doing to get there. Not to mention it's a pain to define what permissions are ok and what are not. We're talking ACLs and not filemodes - so how do you decide which accounts are ok to have access, and which are not? I would say the same as the *nix version - if it is readable or writable by anyone except the current user it is potentially at risk, the current user connecting to pgsql is the only use for this file. Which I believe is the whole point of the TODO entry, stop anyone using the pgpass file without proper security. Again, it's a lot harder to actually define it on Windows. What if your user has access only through a group? What about DENY permissions. Things like that. The other thing to consider is that pgpass is the file referenced by PGPASSFILE - the user can set this to point to a file anywhere on any drive available. That's a very valid point though, didn't think about that. Still doesn't take away the how part, though, but it does take away part of the why part. //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] Password issue revisited
Tom Lane wrote: Michael Schmidt [EMAIL PROTECTED] writes: ... Regarding how I concluded that PGPASSFILE was deprecated for pg_dump, I offer the following. 1. The documentation for pg_dump in the manual (Section VI) includes a section labeled Environment. This lists PGDATABASE, PGHOST, PGPORT, and PGUSER. It also says default connection parameters but there is no hyperlink or reference to another manual section to explain/define this term. Yeah. There is a link down in See Also but the incomplete Environment section of these man pages seems misleading. Rather than try to maintain complete lists in each of the client-application man pages, I propose we remove those sections completely, and just rely on the See Also links to section 29.12. I think we can conclude that adding libpq in the See Also section of the documentation isn't sufficient. I have removed that mention, and added this text to the bottom of the Environment section for each utility: +This utility, like most other productnamePostgreSQL/ utilities, +also uses the environment variables supported by xref +linkend=libpq-envars endterm=libpq. I have backpatched this to 8.2.X. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password issue revisited
Magnus Hagander wrote: Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. Is there truly such a thing on a windows PC? All it takes is one Virus or Malware to gain access to the PC and anything stored in the user profile is easy picking. The virus and malware creators may not know about the pg_pass file now, but they will eventually. What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. I work at a large financial institution and if the auditors knew about the pg_pass being plain text, they would pretty much ban it's use. Anytime a password is sitting on a non encrypted file system, regardless of it's permissions it is potentially at risk. -- Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Password issue revisited
Tony Caduto wrote: Magnus Hagander wrote: Are we sure we want to do this? (Sorry, didn't notice this thread last time) The default on *all* windows versions since NT 4.0 (which is when the directory we use was added) will put this file in a protected directory. Is there truly such a thing on a windows PC? All it takes is one Virus or Malware to gain access to the PC and anything stored in the user profile is easy picking. The virus and malware creators may not know about the pg_pass file now, but they will eventually. What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. I work at a large financial institution and if the auditors knew about the pg_pass being plain text, they would pretty much ban it's use. Anytime a password is sitting on a non encrypted file system, regardless of it's permissions it is potentially at risk. If we wanted to do that, we could use the Windows API that's available to do this. The idea with the pgpass flie is to have it compatible with the unix version. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Password issue revisited
Magnus Hagander [EMAIL PROTECTED] writes: Tony Caduto wrote: What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. If we wanted to do that, we could use the Windows API that's available to do this. The idea with the pgpass flie is to have it compatible with the unix version. More to the point, that's far outside the scope of this project. Use a PAM auth module that you like, or Kerberos or whatever. I'm way past tired of let's put yet another authentication technology in libpq requests. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Password issue revisited
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Tony Caduto wrote: What about having a wallet type system where the user can create a pass phrase to protect a generated key that would get loaded once per session. That is how KDE allows users to store passwords. If we wanted to do that, we could use the Windows API that's available to do this. The idea with the pgpass flie is to have it compatible with the unix version. More to the point, that's far outside the scope of this project. Use a PAM auth module that you like, or Kerberos or whatever. I'm way past tired of let's put yet another authentication technology in libpq requests. Just to make things clear, this wouldn't be about another auth method. Windows has an API to store arbitrary passwords in a secure way. At least it does in XP+, not sure if it was in 2000. Not saying it's a good idea, but it's not another auth tech for libpq. //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] Password issue revisited
Magnus Hagander wrote: Just to make things clear, this wouldn't be about another auth method. Windows has an API to store arbitrary passwords in a secure way. At least it does in XP+, not sure if it was in 2000. Would it really solve Tony's problem though? I'm not familiar with the API you're thinking of, but do be useful to us it must be able to give the unencrypted passwords back to us, and therefore anything else pretending to be us. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Views: having a rule call a function vs. using a before trigger
Hi,
Postgresql 8.1.
I'm trying to come up with a generic way
of inserting into a view, particularly regards
error testing and the generation of complicated
foreign keys. I don't seem to be having much luck.
(I also want to update and delete, but haven't gotten
that far.)
I thought that, for inserts at least, I could write
a rule that called a function and have the function
do anything complicated, raise exceptions, etc.
But I get the error message shown below.
It occurs to me that I might be able to get
a BEFORE trigger declared on the view to
work, but that seems a bit unusual and
I was wondering if it'd even be supported.
I'd prefer to stay in the land of the sane,
so if there's just no generic technique
that lets me execute arbitrary code when
inserting/updating/deleting into views,
well, I'll just have to scale back. But I want
to explore all the options.
The basic idea for inserts is to have a rule
on the view supporting the insert that does:
DO INSTEAD
INSERT INTO underlying_table (c1, c2, ...)
SELECT n.c1, ... FROM viewinsertfunc(NEW.*)
AS n (c1, c2, ...);
viewinsertfunc() would do all the work of
error checking, foreign key generation, etc.
It would also do all the necessary inserting
into all underlying tables. Finally it
would return a SETOF record that would always
be empty. That way the DO INSTEAD INSERT
would never actaully do any inserting
and the function could do all the work,
but the INSTEAD would still be an INSERT
and thus appropriate return codes would
be supplied to any clients.
The implimentation below is a slightly modified
version of this. The function actually
returns a row to be inserted, just because
this is a simple case where there's only
one table underlying the view and nothing
much complicated is going on. In this
case I could probably get away without
having a function at all and just
use constraints on the view for error
checking and use COALESCE to come up
with the right values. I'm presenting
the simple case but I've got other views
where coming up with the right data values
involves looking at other tables and
I'd really like a function to handle
the data generation.
-
The error message I get when I try to create the rule is:
ERROR: function expression in FROM may not refer to other relations
of same query level
-
The table:
Table babase.interact_data
Column | Type|
Modifiers
+---+-
iid| integer | not null default
nextval('interact_data_iid_seq'::regclass)
sid| integer |
act| character(2) | not null
date | date | not null
start | time(0) without time zone |
stop | time(0) without time zone |
-
The view:
View babase.interact
Column | Type| Modifiers
--+---+---
iid | integer |
sid | integer |
act | character(2) |
date | date |
jdate| integer |
start| time(0) without time zone |
startspm | double precision |
stop | time(0) without time zone |
stopspm | double precision |
View definition:
SELECT interact_data.iid, interact_data.sid, interact_data.act,
interact_data.date, julian(interact_data.date) AS jdate,
interact_data.start, spm(interact_data.start) AS startspm,
interact_data.stop, spm(interact_data.stop) AS stopspm
FROM interact_data
ORDER BY interact_data.iid;
-
The rule:
CREATE OR REPLACE RULE interact_insert
AS ON insert
TO interact
DO INSTEAD
INSERT INTO interact_data (iid, sid, act, date, start, stop)
SELECT n.iid, n.sid, n.act, n.date, n.start, n.stop
FROM _interact_insert(NEW.*)
AS n (iid INT
, sid INT
, act CHAR(2)
, date DATE
, start TIME(0)
, stop TIME(0));
-
The function:
CREATE OR REPLACE FUNCTION
_interact_insert(this_row interact)
RETURNS interact_data
LANGUAGE plpgsql
AS $$
-- Handle inserts into the interact view.
--
-- GPL_notice(` --', `2007', `Karl O. Pinc [EMAIL PROTECTED]')
--
-- Syntax: _interact_insert(this_row)
--
-- Input:
-- this_row A the interact row to insert.
--
-- Returns:
-- A interact_data row to insert.
--
-- Remarks:
-- You'd think the either-data-or-computed-value
Re: [GENERAL] Password issue revisited
Dave Page wrote: Magnus Hagander wrote: Just to make things clear, this wouldn't be about another auth method. Windows has an API to store arbitrary passwords in a secure way. At least it does in XP+, not sure if it was in 2000. Would it really solve Tony's problem though? I'm not familiar with the API you're thinking of, but do be useful to us it must be able to give the unencrypted passwords back to us, and therefore anything else pretending to be us. yeah, but it pops up a GUI notification for you. It's what IE uses to store things like passports. It's also used, IIRC, by the new RDP client that's available, and a few more. Did a quick check, and it's XP/2003 only. See http://msdn2.microsoft.com/en-us/library/aa302353.aspx. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Password issue revisited
Magnus Hagander wrote: Dave Page wrote: Magnus Hagander wrote: Just to make things clear, this wouldn't be about another auth method. Windows has an API to store arbitrary passwords in a secure way. At least it does in XP+, not sure if it was in 2000. Would it really solve Tony's problem though? I'm not familiar with the API you're thinking of, but do be useful to us it must be able to give the unencrypted passwords back to us, and therefore anything else pretending to be us. yeah, but it pops up a GUI notification for you. It's what IE uses to store things like passports. It's also used, IIRC, by the new RDP client that's available, and a few more. Did a quick check, and it's XP/2003 only. See http://msdn2.microsoft.com/en-us/library/aa302353.aspx. That would break all the non-interactive apps that we recommend using pgpass with to prevent storing passwords in even less secure places. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Syncing postgres data with Pocket PC
On 2/20/07, Justin Dearing [EMAIL PROTECTED] wrote: Hello, I need a way to sync a postgres view with a table on a Windows CE device. The table will be read only on the mobile device. I am seeking to replace an access database that syncs a table with a pocket pc table via active sync. I would really like to use postgres for the desktop side of things, but need to be able to syn change to the database with pocket PCs via active sync. I'd be asking the people who wrote active sync, then, not the postgres-community. It's the chore of the provider of the interoperability product to get this done via a driver or even just ODBC. Just my two cents, though. Cheers, Andrej ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] encode, lower and 0x8a
I have updated the encode() documentation to not mention ASCII, and to be more specific about what 'escape' does. Backpatched to 8.2.X. --- Michael Fuhr wrote: On Thu, Jan 25, 2007 at 02:28:38PM -0500, Michael Artz wrote: Perhaps my understanding of the 'encode' function is incorrect, but I was under the impression that I could do something like: SELECT lower(encode(bytes, 'escape')) FROM mytable; as it sounded like (from the manual) that 'encode' would return valid ASCII, with all the non-ascii bytes hex escaped. The documentation for encode() does give that impression: Encode binary string to ASCII-only representation. Supported types are: base64, hex, escape. However, the source code for esc_encode() in src/backend/utils/adt/encode.c says and does otherwise: * Only two characters are escaped: * \0 (null) and \\ (backslash) When I have the byte 0x8a, however, I get the error: ERROR: invalid byte sequence for encoding UTF8: 0x8a Since encode() returns text and doesn't escape non-ASCII characters, all of the original binary data will be treated as though it's text in the database's encoding. If the data contains byte sequences that aren't valid in that encoding then you get the above error. I have the sneaking suspicion that I am missing something, so please correct me if I am wrong. If I am wrong, is there a better way to lowercase all the ascii characters in a bytea string? What are you trying to do? What is the binary data and why are you treating it (or part of it) as though it's text? Do you want the end result to be text with escape sequences or do you want to convert it back to bytea? Something like this might work: SELECT lower(textin(byteaout(bytes))) FROM mytable; To turn the result back into bytea: SELECT decode(lower(textin(byteaout(bytes))), 'escape') FROM mytable; -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.368 diff -c -c -r1.368 func.sgml *** doc/src/sgml/func.sgml 20 Feb 2007 18:18:05 - 1.368 --- doc/src/sgml/func.sgml 20 Feb 2007 19:48:37 - *** *** 1356,1363 /entry entrytypetext/type/entry entry ! Encode binary data to acronymASCII/acronym-only representation. Supported types are: literalbase64/, literalhex/, literalescape/. /entry entryliteralencode( E'123\\000\\001', 'base64')/literal/entry entryliteralMTIzAAE=/literal/entry --- 1356,1365 /entry entrytypetext/type/entry entry ! Encode binary data to different representation. Supported types are: literalbase64/, literalhex/, literalescape/. + literalEscape/ merely outputs null bytes as literal\000/ and + doubles backslashes. /entry entryliteralencode( E'123\\000\\001', 'base64')/literal/entry entryliteralMTIzAAE=/literal/entry ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database performance comparison paper.
On 2/21/07, Guido Neitzer wrote: It would be more or less the same, if you compare copy against insert performance on PostgreSQL and state that insert should be as fast as copy without saying why. Btw: these guys claim to be database consultants. Guess one should consider oneself lucky not to be their customer, then, since they seem to base their decisions on thin air and personal preference... cug Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] invalid input syntax for integer: NULL
Hi everyone,
I'm trying to write a PL/pgSQL function which execute an insert, I encounter
a problem when I try to insert NULL value into an integer field.
The following code is for reproducing:
CREATE TABLE test(
bh INT8
);
CREATE OR REPLACE FUNCTION testinsertion(intornull bigint) RETURNS text AS
$$
DECLARE
BEGIN
RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull, 'NULL')||')';
END;
$$ LANGUAGE plpgsql;
When I run: SELECT testinsertion(5); OR SELECT testinsertion(NULL);
ERROR: invalid input syntax for integer: NULL
CONTEXT: SQL statement SELECT 'INSERT INTO test (bh) VALUES ('||COALESCE(
$1 , 'NULL')||')'
PL/pgSQL function testinsertion line 4 at return
And if I try to change the COALESCE second value at the function to NULL
(instead of 'NULL') it works if a value is being passed to the integer field
but doesn't work if a NULL Is passed:
SELECT testinsertion(5);
testinsertion
--
INSERT INTO test (bh) VALUES (5)
(1 row)
SELECT testinsertion(NULL);
testinsertion
---
(1 row)
Thanks a lot in advance,
Yonatan Ben-Nes
Re: [GENERAL] Installing support for python on windows
resurrection Ok so i am having trouble installing plpython, and found this thread. Howevre, after adding postgresql/bin to the path and the python lib directory to the path i still get: createlang: language installation failed: ERROR: could not load library C:/Pro gram Files/PostgreSQL/8.2/lib/plpython.dll: The specified module could not be f ound. so maybe im doing the path thing wrong? i used : C:\Program Files\PostgreSQL\8.2\binset PATH=C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbem;C:\Python24\Lib;c:\Program Files\PostggreSQL\8.2\bin;c:\Program Files\PostggreSQL\8.2\lib so if that is correct what else am i missing? On 1/2/07, novnov [EMAIL PROTECTED] wrote: Whew, python lang support just installed for me too. After reading more, it seems that while there is some broken dependency re DWMAPI.dll on windowsxp systems, it does not affect most programs. DWMAPI.dll is shown as missing by 'depends' but that exposes delay-loaded problems that may have little real world impact. The best thread on the matter seems to be http://episteme.arstechnica.com/eve/forums/a/tpc/f/99609816/m/494009191831. I'd not tried createlang since fixing the basic python and postgres path issues, and had assumed that the remaining problem exposed by 'depends' was a show stopper. I wonder if it should be included in the docs for installing langs that on windows postgresql\bin and python24 and python24\lib need to be in the path? And that the current windows installer dll requires python 2.4? Thanks Adrian for all of your assistance. Adrian Klaver wrote: On Monday 01 January 2007 6:24 pm, novnov wrote: Thanks, the depends tools looks very handy, surprising I'd not heard of it before. I found that the postgresql\bin dir must be added to the path. Also, I had python 2.5 installed, and plpython apparently needs python 2.4. I've installed that and added to the path, but there is another dependency missing inside of the python stack, DWMAPI.dll. Googling DWMAPI.dll gets a mixed bag, but I think that it might be part of IE6, and not IE7 (I have IE7). Maybe the current plpython does not work unless IE6 is installed, because plpython needs python 2.4, which needs IE 6??? I installed with python 2.5 and IE7 with no problem. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- View this message in context: http://www.nabble.com/Installing-support-for-python-on-windows-tf2902841.html#a8135655 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid input syntax for integer: NULL
On 02/20/2007 03:45:55 PM, Yonatan Ben-Nes wrote:
Hi everyone,
I'm trying to write a PL/pgSQL function which execute an insert, I
encounter
a problem when I try to insert NULL value into an integer field.
RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull,
'NULL')||')';
And if I try to change the COALESCE second value at the function to
NULL
(instead of 'NULL') it works if a value is being passed to the
integer field
but doesn't work if a NULL Is passed:
NULL, without the quotes, is the proper way to write
NULL as a literal value. When you put quotes around it it's a
string. So that's why you get a type exception.
COALESCE chooses the first value that's not NULL. So if you
pass it NULL you may as well not supply the second argument.
You probably want a plpgsql IF statement or a CASE expression.
Karl [EMAIL PROTECTED]
Free Software: You don't pay back, you pay forward.
-- Robert A. Heinlein
---(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] Priorities for users or queries?
Bruce Momjian wrote: Hard to argue with that. Is it a strong enough argument to add a TODO? I'm thinking some sort of TODO might be called for. Perhaps two TODOs? * Use the OS's priority features to prioritize backends (and document that it might work better with OS's that support priority inheritance). * Investigate if postgresql could develop an additional priority mechanism instead of using the OS's. Ron Mayer wrote: Magnus Hagander wrote: ... quite likely to suffer from priority inversion ... CMU paper... tested PostgreSQL (and DB2) on TPC-C and TPC-W ...found that...I/O scheduling through CPU priorities is a big win for postgresql. http://www.cs.cmu.edu/~bianca/icde04.pdf Setting priorities seems a rather common request, supposedly coming up every couple months [5]. The paper referenced [1] suggests that even with naive schedulers, use of CPU priorities is very effective for CPU and I/O intensive PostgreSQL workloads. If someone eventually finds a workload that does suffer worse performance due to priority inversion, (a) they could switch to an OS and scheduler that supports priority inheritance; (b) it'd be an interesting case for a paper rebutting the CMU one; and (c) they don't have to use priorities. If a user does find he wants priority inheritance it seems Linux[1], BSD[2], some flavors of Windows[3], and Solaris[4] all seem to be options; even though I've only seen PostgreSQL specifically tested for priority inversion problems with Linux (which did not find problems but found additional benefit of using priority inheritance). [1] Linux with Priority inheritance showing benefits for PostgreSQL http://www.cs.cmu.edu/~bianca/icde04.pdf [2] BSD priority inheritance work mentioned: http://www.freebsd.org/news/status/report-july-2004-dec-2004.html [3] Windows priority inheritance stuff: http://msdn2.microsoft.com/en-us/library/aa915356.aspx [4] Solaris priority inheritance stuff http://safari5.bvdep.com/0131482092/ch17lev1sec7 http://www.itworld.com/AppDev/1170/swol-1218-insidesolaris/ [5] Tom suggests that priorities are a often requested feature. http://svr5.postgresql.org/pgsql-performance/2006-05/msg00463.php ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] invalid input syntax for integer: NULL
On Tue, 20 Feb 2007, Yonatan Ben-Nes wrote:
Hi everyone,
I'm trying to write a PL/pgSQL function which execute an insert, I encounter
a problem when I try to insert NULL value into an integer field.
The following code is for reproducing:
CREATE TABLE test(
bh INT8
);
CREATE OR REPLACE FUNCTION testinsertion(intornull bigint) RETURNS text AS
$$
DECLARE
BEGIN
RETURN 'INSERT INTO test (bh) VALUES ('||COALESCE(intornull, 'NULL')||')';
I think you'd need something like
COALESCE(CAST(intornull AS TEXT), 'NULL')
in order to make that work. You want the output to effectively be a string
which contains the int to be concatenated with the other strings or the
string 'NULL' to be concatentated with the other strings.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] can't stop the postmaster?
This is probably a question more appropriate on a gentoo mailing list, but I'll ask anyway as it obviously has to do with postgresql and I've a feeling someone will probably know: can anyone explain what is it that happens here when I try to stop the postmaster? What can I do about it? # /etc/init.d/postgresql stop * Stopping PostgreSQL ... start-stop-daemon --stop --pidfile /var/lib/postgresql/data/postmaster.pid --retry -TERM//-INT//-QUIT --oknodo /sbin/start-stop-daemon: invalid schedule item (must be [-]signal-name, -signal-number, timeout or `forever' Try `/sbin/start-stop-daemon --help' for more information.[ !! ] TIA, t.n.a. ---(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
[GENERAL] Crosstab
I am trying to use the crosstab function of the contrib tablefunc.
Reading the README, I believe I am supposed to be using crosstab(sql, N)
for my situation and wondering if the SQL can be based on a view? I have
this view created that gives me each sales rep and their total number of
units sold and total revenue for each month:
CREATE VIEW public.view_pick1 (
rep,
month,
units,
revenue)
AS
SELECT users.user_login AS rep, date_part('month'::text,
current_clients.start_date) AS month, count(companies.company_id) AS
units, sum(companies.company_revenue) AS revenue
FROM ((companies JOIN current_clients ON ((companies.company_id =
current_clients.client_id))) JOIN users ON ((companies.company_sales_rep =
users.user_id)))
GROUP BY users.user_login, date_part('month'::text, current_clients.start_date)
ORDER BY users.user_login, date_part('month'::text, current_clients.start_date);
Trying to make a crosstab, let's say just for units, this is what I'm
attempting, which is wrong of course, can someone enlighten me as this
is my first crosstab.
select * from crosstab('select rep, month, units from view_pick1 order by
1,2;', 12) AS view_pick1(rep varchar, jan double precision, feb double
precision, mar double precision, apr double precision, may double precision,
jun double precision, jul double precision, aug double precision, sep double
precision, oct double precision, nov double precision, dec double precision);
Error is: ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601
Not sure what that means, I tried to match up the view field types with
the returned fields. My sql produces the following after which is what I
would like to get. Am I even going about this correctly?
rep | month | units
--+---+---
aespinal | 5 | 4
aespinal | 6 | 3
asmith | 1 | 1
athranow | 1 | 5
athranow | 2 | 1
athranow | 3 | 2
athranow | 4 | 1
repjan feb mar apr may jun etc...
-+-+-+-+-+-+-+-
aespinal4 3
asmith 1
athranow5 1 2 1
Thanks for the help!
--
Robert
---(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] can't stop the postmaster?
On 2/21/07, Tomi N/A [EMAIL PROTECTED] wrote: # /etc/init.d/postgresql stop * Stopping PostgreSQL ... start-stop-daemon --stop --pidfile /var/lib/postgresql/data/postmaster.pid --retry -TERM//-INT//-QUIT --oknodo /sbin/start-stop-daemon: invalid schedule item (must be [-]signal-name, -signal-number, timeout or `forever' Try `/sbin/start-stop-daemon --help' for more information.[ !! ] Impossible to answer w/o knowing what gentoo's /etc/init.d/postgresql looks like or what start-stop-daemon is or does ... you're right, it *is* a gentoo question. Cheers, Andrej ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] REVOKE ALL
Hello, Is there a way to revoke all privileges of a role without actually specifying the whole list of items. Like if a role has privileges on FUNCTIONs, is there a REVOKE all FUNCTIONS. Is there a way to check if it has a GRANT in a particular type (CONNECT, FUNCTION, TRIGGER) before calling the REVOKE command? Also, if I do a GRANT CONNECT ON DATABASE X TO Y, will Y be able to connect to other databases if I haven't given him permission to do so (what is the default value when a role is created since roles are global)? Thanks David
Re: [GENERAL] Database performance comparison paper.
On 2/20/2007 3:51 PM, Andrej Ricnik-Bay wrote: On 2/21/07, Guido Neitzer wrote: It would be more or less the same, if you compare copy against insert performance on PostgreSQL and state that insert should be as fast as copy without saying why. Btw: these guys claim to be database consultants. Guess one should consider oneself lucky not to be their customer, then, since they seem to base their decisions on thin air and personal preference... As the original author of the PHP TPC-W implementation you can find on pgfoundry, I know pretty good what it takes to make MySQL perform about as good as PostgreSQL under a real benchmarking scenario. I implemented all the database access parts basically two times. Once for PostgreSQL as an experienced DB developer would do it, once turning half the queries upside down in a horribly unintuitive way to give MySQL+InnoDB clues how to do it. Of course did I NOT run any of those tests using MyISAM. In the end, both implementations performed more or less the same, measured at the HTTP interface. What the PHP+PG implementation did more elegantly in SQL, the PHP+My implementation had to do with more PHP code. And that is where all those crappy wannabe-benchmarks just fail to make sense to me. They measure some common denominator SQL statements at an abstracted DB API level. That is just nonsense. It doesn't matter how fast a specific index scan or a specific insert or update operation by itself is. What matters is how many parallel simulated users of a well defined business application the System Under Test (middleware plus database) can support within the responsetime constraints. All that said, what really scares me is that these clowns apparently don't even know the system of their preference. No serious DB consultant would even bother testing anything using MyISAM any more. It is a table handler only considered for disposable data. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] postgresql vs mysql
Hello I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? Thank you, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ //| _ _ | \\/ `/ `.|| /~\\ \//~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ |||| ] \ | o|o | / ] Y |||| Y [ \___\_--_ /_/__/ | \_|l,--.l|_/ | /.-\() /--.\ | ' ` | `--(__)' \ (/~`----'~\) / U// U / \ `-_-__-_-'/ \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/`.`' / [ /__`--'__\ |`-'| /\(__,-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ ^\ /^ | ` ( \\ _\ -__- /_ ,-\ ,-~~-. \ `:.___,/ (___\/___) (/()`---' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Out of memory on vacuum analyze
On Feb 19, 2007, at 1:19 PM, Jeff Davis wrote: You told PostgreSQL that you have 900MB available for maintenance_work_mem, but your OS is denying the request. Try *lowering* that setting to something that your OS will allow. That seems like an awfully high setting to me. 900MB isn't that unreasonable if you're building indexes on a restore or something similar. I have run into issues when trying to set it much over 1G, though... on various OSes and platforms. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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
[GENERAL] Error upgrading on W2K
I have postgres running on W2K, version 8.2.1 which I am upgrading to 8.2.3 but when I run the upgrade I get an error as follows: The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2803. The install seemed to continue fine after this point and when I check in psql I have 8.2.3 installed and the databases appear to be operational. Any thoughts on what the error might have been and if I need to check anything in particular to verify correct install? Cheers, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] number of tables limited over time (not simultaneous)?
We've settled upon a method for gathering raw statistics from widely
scattered data centers of creating one sequence per-event, per minute.
Each process (some lapp, some shell, some python, some perl etc) can
call a shell script which calls ssh-psql to execute a nextval('event')
sequence. Periodically (every 2-10 minutes, depending on other factors)
Another process picks up the value and inserts it into a permanent home.
We're only talking a few 7-10k calls per minute, but going to this from
a query that does an update has saved a *huge* amount of overhead.
If I needed to a periodic dump and restore would only take a minute.
This data is highly transient. More frequently than biweekly or so
would be annoying though.
Aside from security concerns, did we miss something? Should I be
worried we're going through ~60,000 sequences per day?
TIA,
dave
---(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 vs mysql
On 2/20/07, gustavo halperin [EMAIL PROTECTED] wrote: Hello I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? in MySQL if you have tables MyISAM they will ignore all rollback commands you exexute, so you will have inconsistencies in databases... worst, if you have tables MyISAM and tables InnoDB the first will ignore all rollback commands and the laters won't... of course the legendary speed in mysql can be obtained if you use tables MyISAM :( some other issues (some of them had been resolved in 5.x i don't know wich ones) http://sql-info.de/mysql/gotchas.html one last thing mysql team doesn't afraid to change behaviours between minor releases, look at this thread http://archives.postgresql.org/pgsql-general/2005-12/msg00487.php http://dev.mysql.com/doc/refman/5.1/en/join.html (Join Processing Changes in MySQL 5.0.12) -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Nice article on Unicode and it's encodings (utf8, utf16 and utf32)
http://developersoven.blogspot.com/ -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
one last thing mysql team doesn't afraid to change behaviours between minor releases, look at this thread That is so true, all the differences between minor versions made creating Lightning Admin for MySQL a pain in the rear... After I did the port I really appreciated how clean PostgreSQL is. -- Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Have anyone this man e-mail ?
Ezequias Rodrigues da Rocha [EMAIL PROTECTED] writes: I just need an expert in Slony-I This is the wrong forum for discussing Slony ... you will be much more likely to find experts in the Slony lists: http://gborg.postgresql.org/mailman/listinfo/slony1-general regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/20/07 15:25, gustavo halperin wrote: Hello I have a friend that ask me why postgresql is better than mysql. I personally prefer posgresql, but she need to give in her work 3 or 4 strong reasons for that. I mean not to much technical reasons. Can you give help me please ? The only reason I'd need is that MySQL (even InnoDB) lets you accidentally insert intrinsically bad data. According to the official v5 docs, it's the app programmer's fault if s/he tries to insert 35-Feb-2007 into the database. MySQL will purposefully convert it to '-00-00'. http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J 98mWmmEqtFKGaDX4ZvU87J4= =EPxL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql vs mysql
I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively.
mysql use test
Database changed
mysql create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql select version();
+-+
| version() |
+-+
| 5.0.27-standard |
+-+
1 row in set (0.00 sec)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/20/07 15:25, gustavo halperin wrote:
Hello
I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?
The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '-00-00'.
http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF29nmS9HxQb37XmcRAsD9AJ4gGKaCz5gTQD879DBvsay6nHU8+wCfSj3J
98mWmmEqtFKGaDX4ZvU87J4=
=EPxL
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(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 vs mysql
Adam Rich [EMAIL PROTECTED] writes:
I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively.
Really?
[EMAIL PROTECTED] ~]$ mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.32 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql insert into test values ('35-Feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql select * from test;
++
| td |
++
| -00-00 |
++
1 row in set (0.00 sec)
mysql
Note that this case is *not* testing whether mysql knows that
February has less than 31 days.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresql vs mysql
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/20/07 23:59, Adam Rich wrote:
I'm not apologizing for their past mistakes.. But the issue
you cite is no longer true:
As of 5.0.2, the server requires that month and day values
be legal, and not merely in the range 1 to 12 and 1 to 31,
respectively.
Only if you set sql modes STRICT_TRANS_TABLES and STRICT_ALL_TABLES
(which *still* allow bogus dates like 2007-02-00!!) or TRADITIONAL.
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
It's (finally) a step in the right direction, but is really only a
pretty please, since SQL modes are session-time changeable.
mysql use test
Database changed
mysql create table test ( td DATE );
Query OK, 0 rows affected (0.01 sec)
mysql insert into test values ('35-Feb-2007');
ERROR 1292 (22007): Incorrect date value: '35-Feb-2007' for column 'td'
at row 1
mysql select version();
+-+
| version() |
+-+
| 5.0.27-standard |
+-+
1 row in set (0.00 sec)
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ron Johnson
Sent: Tuesday, February 20, 2007 11:35 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql
On 02/20/07 15:25, gustavo halperin wrote:
Hello
I have a friend that ask me why postgresql is better than mysql.
I personally prefer posgresql, but she need to give in her work 3 or 4
strong reasons for that. I mean not to much technical reasons. Can you
give help me please ?
The only reason I'd need is that MySQL (even InnoDB) lets you
accidentally insert intrinsically bad data. According to the
official v5 docs, it's the app programmer's fault if s/he tries to
insert 35-Feb-2007 into the database. MySQL will purposefully
convert it to '-00-00'.
http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html
- ---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
- ---(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
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF2+SQS9HxQb37XmcRAqh4AJwJz41yaTzIkqcAIr1wi7gK7J1QPACgvl07
fVNXVeoJo4vWhbIeGWM5MWs=
=Px12
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Out of memory on vacuum analyze
Jim Nasby wrote: On Feb 19, 2007, at 1:19 PM, Jeff Davis wrote: You told PostgreSQL that you have 900MB available for maintenance_work_mem, but your OS is denying the request. Try *lowering* that setting to something that your OS will allow. That seems like an awfully high setting to me. 900MB isn't that unreasonable if you're building indexes on a restore or something similar. I have run into issues when trying to set it much over 1G, though... on various OSes and platforms. versions before 8.2 have some issues(mostly reporting bogus errors) with very large settings for maintenance_work_mem. 8.2 and up are behaving more sanely but I don't think they can actually make anything better with values in the GB range. Have you actually measured a performance improvment going beyond 250-350MB(that seemed about to be the sweet spot last I tested) or so for index creation and friends ? Stefan ---(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
