Re: [GENERAL] RPM compat-postgresql-libs-4-2 for IA-64

2007-02-20 Thread DANTE Alexandra

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

2007-02-20 Thread Devrim GUNDUZ
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?

2007-02-20 Thread Csaba Nagy
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

2007-02-20 Thread Rafa Comino

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

2007-02-20 Thread Teodor Sigaev

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

2007-02-20 Thread Rafa Comino

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

2007-02-20 Thread Tomasz Ostrowski
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

2007-02-20 Thread Merlin Moncure

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

2007-02-20 Thread Adam Rich

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

2007-02-20 Thread Tom Lane
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

2007-02-20 Thread Bruce Momjian

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

2007-02-20 Thread Ian Harding

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

2007-02-20 Thread Marek Lewczuk

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

2007-02-20 Thread Magnus Hagander
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?

2007-02-20 Thread Ian Harding

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.

2007-02-20 Thread Guido Neitzer

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

2007-02-20 Thread Bruce Momjian
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

2007-02-20 Thread Scott Marlowe
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

2007-02-20 Thread CAJ CAJ

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

2007-02-20 Thread Merlin Moncure

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 ?

2007-02-20 Thread Ezequias Rodrigues da Rocha

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

2007-02-20 Thread Alvaro Herrera
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

2007-02-20 Thread Stefan Kaltenbrunner
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

2007-02-20 Thread Frank Miles

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?

2007-02-20 Thread Robert Fitzpatrick
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?

2007-02-20 Thread Scott Marlowe
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

2007-02-20 Thread Shane Ambler

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

2007-02-20 Thread Magnus Hagander
 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

2007-02-20 Thread Bruce Momjian
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

2007-02-20 Thread Tony Caduto

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

2007-02-20 Thread Magnus Hagander
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

2007-02-20 Thread Tom Lane
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

2007-02-20 Thread Magnus Hagander
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

2007-02-20 Thread Dave Page
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

2007-02-20 Thread Karl O. Pinc

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

2007-02-20 Thread Magnus Hagander
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

2007-02-20 Thread Dave Page
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

2007-02-20 Thread Andrej Ricnik-Bay

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

2007-02-20 Thread Bruce Momjian

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.

2007-02-20 Thread Andrej Ricnik-Bay

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

2007-02-20 Thread Yonatan Ben-Nes

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

2007-02-20 Thread Rhys Stewart

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

2007-02-20 Thread Karl O. Pinc


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?

2007-02-20 Thread Ron Mayer
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

2007-02-20 Thread Stephan Szabo
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?

2007-02-20 Thread Tomi N/A

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

2007-02-20 Thread Robert Fitzpatrick
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?

2007-02-20 Thread Andrej Ricnik-Bay

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

2007-02-20 Thread David Legault

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.

2007-02-20 Thread Jan Wieck

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

2007-02-20 Thread gustavo halperin

 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

2007-02-20 Thread Jim Nasby

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

2007-02-20 Thread Paul Lambert
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)?

2007-02-20 Thread dave crane
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

2007-02-20 Thread Jaime Casanova

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)

2007-02-20 Thread Tony Caduto


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

2007-02-20 Thread Tony Caduto



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 ?

2007-02-20 Thread Tom Lane
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

2007-02-20 Thread Ron Johnson
-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

2007-02-20 Thread Adam Rich

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

2007-02-20 Thread Tom Lane
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

2007-02-20 Thread Ron Johnson
-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

2007-02-20 Thread Stefan Kaltenbrunner
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