date:20060924

On Sat, Sep 23, 2006 at 11:47:59PM -0600, Michael Fuhr wrote:
 FOR i IN 0 .. (1  (aupper - alower + 1)) - 1 LOOP

To handle empty arrays this should be:

FOR i IN 0 .. COALESCE((1  (aupper - alower + 1)) - 1, 0) LOOP

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Restart after poweroutage

2006-09-24 Thread Richard Broersma Jr

 ...but for 
 yucks I decided to imagine I did not know.  What would I need to do to 
 figure this out.

 Maybe it is just a right 
 of passage that users of postgresql will just have to learn about this.


I would imagine that I am one of the yucks like you. ;-)  Anyway, everytime I 
see a useful (for a
yuck) email like this I always flag it for future reference.

Thanks,

Richard Broersma Jr.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] copy db1 to db2

2006-09-24 Thread Bobby Gontarski

Basically I need to copy db1 to db2 which I create manually. How do I do that, 
I tried pg_dump pg_restore but I get some errors with foreign key restraint...

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Restart after poweroutage

2006-09-24 Thread Martijn van Oosterhout

On Sun, Sep 24, 2006 at 10:11:00AM -0300, Jon Lapham wrote:
 I recently had another electrical power outage that left my machine 
 unable to restart postgresql.  I had previously reported this a while ago:

FWIW, I've crashed my machine a lot of times and never run into this
problem. However, I run Debian, maybe they do something different.

 Looking at $PGLOG, I discovered:
 FATAL:  pre-existing shared memory block (key 5432001, ID 65536) is 
 still in use
 HINT:  If you're sure there are no old server processes still running, 
 remove the shared memory block with the command ipcclean, ipcrm, or 
 just delete the file postmaster.pid.

This doesn't make sense to me. A reboot will absolutly kill any
existing shared memory blocks, how can it possibly be complaining about
it?

What does ipcs show after the failure to start postgres?

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] powerset?

2006-09-24 Thread Ben


Very nice, thanks!

On Sep 23, 2006, at 10:47 PM, Michael Fuhr wrote:


On Fri, Sep 22, 2006 at 11:38:12PM -0700, Ben wrote:

Does anybody have a stored proc they'd like to share (preferably pl/
pgsql) that generates the power set of an array?


Here's an attempt:

CREATE FUNCTION powerset(a anyarray) RETURNS SETOF anyarray AS $$
DECLARE
retval  a%TYPE;
alower  integer := array_lower(a, 1);
aupper  integer := array_upper(a, 1);
j   integer;
k   integer;
BEGIN
FOR i IN 0 .. (1  (aupper - alower + 1)) - 1 LOOP
retval := '{}';
j := alower;
k := i;

WHILE k  0 LOOP
IF k  1 = 1 THEN
retval := array_append(retval, a[j]);
END IF;

j := j + 1;
k := k  1;
END LOOP;

RETURN NEXT retval;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

Since this is a set-returning function you'd call it as follows:

SELECT * FROM powerset(ARRAY[1,2,3]);
 powerset
--
 {}
 {1}
 {2}
 {1,2}
 {3}
 {1,3}
 {2,3}
 {1,2,3}
(8 rows)

If you wrap the PL/pgSQL function with an SQL function then you
could call it from the SELECT list:

CREATE FUNCTION powerset2(anyarray) RETURNS SETOF anyarray AS $$
SELECT * FROM powerset($1);
$$ LANGUAGE sql IMMUTABLE STRICT;

CREATE TABLE foo (id serial PRIMARY KEY, a integer[]);
INSERT INTO foo (a) VALUES ('{1,2}');
INSERT INTO foo (a) VALUES ('{10,20,30}');

SELECT id, powerset2(a) FROM foo;
 id | powerset2
+
  1 | {}
  1 | {1}
  1 | {2}
  1 | {1,2}
  2 | {}
  2 | {10}
  2 | {20}
  2 | {10,20}
  2 | {30}
  2 | {10,30}
  2 | {20,30}
  2 | {10,20,30}
(12 rows)

Will that work for you?

--
Michael Fuhr



---(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] Restart after poweroutage

2006-09-24 Thread Tom Lane

Jon Lapham [EMAIL PROTECTED] writes:
 I recently had another electrical power outage that left my machine 
 unable to restart postgresql.  I had previously reported this a while ago:

 http://archives.postgresql.org/pgsql-general/2005-04/msg01286.php

 Anyway, because I have seen this problem before, I knew exactly what the 
 solution to the problem was (delete the postmaster.pid file),

As was pointed out to you in the discussion subsequent to that message,
this is not a good automatic response, and it should not be necessary at
all with a post-8.0 postmaster.

 FATAL:  pre-existing shared memory block (key 5432001, ID 65536) is 
 still in use

This is extremely odd, because a shared memory block could not possibly
have survived a reboot.  Too bad you have destroyed the evidence,
because I would like to know what really happened there.  Is it possible
that you have somehow managed to try to start the postmaster twice
during your system boot cycle?  If you do have two postmasters running
in that data directory right now, you are in deep trouble :-(

 I can reproduce this problem in v8.1.4 by these simple steps:

This is not reproducing the problem, this is merely demonstrating that
the postmaster will fail to overwrite a root-owned postmaster.pid file.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Restart after poweroutage

2006-09-24 Thread Joshua D. Drake




This doesn't make sense to me. A reboot will absolutly kill any
existing shared memory blocks, how can it possibly be complaining about
it?


PostgreSQL complains if it finds a postmaster.pid. As far as I can tell 
it doesn't have anything to do with shared memory except that we are 
tracking info in the postmaster.pid. Info that is no longer valid.


What would make sense to me, would be to read the pid file, check the 
process list. If the process that is in the pid file is not in the 
process list, we remove the pid file and start.


Sincerely,

Joshua D. Drake



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Restart after poweroutage

2006-09-24 Thread Jon Lapham


Tom Lane wrote:

Jon Lapham [EMAIL PROTECTED] writes:
I recently had another electrical power outage that left my machine 
unable to restart postgresql.  I had previously reported this a while ago:



http://archives.postgresql.org/pgsql-general/2005-04/msg01286.php


Anyway, because I have seen this problem before, I knew exactly what the 
solution to the problem was (delete the postmaster.pid file),


As was pointed out to you in the discussion subsequent to that message,
this is not a good automatic response, and it should not be necessary at
all with a post-8.0 postmaster.


Okay, yes, I forgot to mention that I also checked to make sure there 
was no postmaster running (via ps).




FATAL:  pre-existing shared memory block (key 5432001, ID 65536) is 
still in use


This is extremely odd, because a shared memory block could not possibly
have survived a reboot.  Too bad you have destroyed the evidence,
because I would like to know what really happened there.  Is it possible
that you have somehow managed to try to start the postmaster twice
during your system boot cycle?  If you do have two postmasters running
in that data directory right now, you are in deep trouble :-(


Ugh, I should have sent the email before fixing the problem.  Sorry 
about that.  If it happens again, I will not be so hasty.  Luckily (for 
you, not me) we have frequent power outages where this computer resides. 
 :)  Maybe it will happen again.


I do not *think* I am running 2 postmasters.

[EMAIL PROTECTED] ~]# service postgresql stop
Stopping postgresql service:   [  OK  ]
[EMAIL PROTECTED] ~]# ps -A | grep -i post
[EMAIL PROTECTED] ~]# service postgresql start
Starting postgresql service:   [  OK  ]
[EMAIL PROTECTED] ~]# ps -A | grep -i post
30760 ?00:00:00 postmaster
30762 ?00:00:00 postmaster
30764 ?00:00:00 postmaster
30765 ?00:00:00 postmaster
30766 ?00:00:00 postmaster

...is that normal to see 5 of them running?

I'm running just the standard (up to date) Fedora Core 5 version of 
postgresql, init scripts and all.



I can reproduce this problem in v8.1.4 by these simple steps:


This is not reproducing the problem, this is merely demonstrating that
the postmaster will fail to overwrite a root-owned postmaster.pid file.


Okay.

--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
 Jon Lapham  [EMAIL PROTECTED]Rio de Janeiro, Brasil
 Personal: http://www.jandr.org/
***-*--**---***---


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Restart after poweroutage

2006-09-24 Thread MaXX


Jon Lapham wrote:
[...]


I do not *think* I am running 2 postmasters.

[EMAIL PROTECTED] ~]# service postgresql stop
Stopping postgresql service:   [  OK  ]
[EMAIL PROTECTED] ~]# ps -A | grep -i post
[EMAIL PROTECTED] ~]# service postgresql start
Starting postgresql service:   [  OK  ]
[EMAIL PROTECTED] ~]# ps -A | grep -i post
30760 ?00:00:00 postmaster
30762 ?00:00:00 postmaster
30764 ?00:00:00 postmaster
30765 ?00:00:00 postmaster
30766 ?00:00:00 postmaster

is that normal to see 5 of them running?
Yes it is, and even more, depending on the number of clients currently 
connected...



akar# ps ax | grep postma
18626  ??  Is 0:36.48 /usr/local/bin/postmaster (postgres)
18627  ??  S  0:19.54 postmaster: logger process(postgres)
18629  ??  S  3:50.58 postmaster: writer process(postgres)
18630  ??  S  1:51.48 postmaster: stats buffer process(postgres)
18632  ??  I  7:17.39 postmaster: stats collector process(postgres)


18683  ??  I  0:47.33 postmaster: ipfwpglogger ipfw 
192.168.0.1(59189) idle (postgres)
18685  ??  I  0:00.94 postmaster: ipfwpglogger ipfw 
192.168.0.1(54261) idle (postgres)
49188  ??  I  0:04.37 postmaster: snortpguser ipfw 
192.168.0.1(56173) idle (postgres)
80722  ??  I  0:00.11 postmaster: pgsql template1 
192.168.0.250(49421) idle (postgres)
80723  ??  I  0:00.75 postmaster: pgsql ipfw 192.168.0.250(50624) 
idle (postgres)
80724  ??  I  0:01.67 postmaster: pgsql ipfw 192.168.0.250(60737) 
idle (postgres)

81216  ??  I  0:00.48 postmaster: perl ipfw [local] idle (postgres)
81253  ??  I  0:01.43 postmaster: webpguser ipfw [local] idle (postgres)

HTH,
--
MaXX

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] serial column




I need to develop a serial column that always starts at 1 
and is sequential evenafter deletes.

Any ideas???

Bob

Re: [GENERAL] serial column

On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:
 I need to develop a serial column that always starts at 1 and is
 sequential even after deletes.
  
 Any ideas???
  

Did you try the:

create table tbl
(
id SERIAL
);

or even with primary key...

create table tbl
(
id SERIAL primary key
);


-- 
Regards,
Gevik Babakhani




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] IF EXISTS

2006-09-24 Thread stevethames


Ok, thanks, Jeff.
This is not a critical problem.  Just annoying.  I'll wait for 8.2.
BTW, while I can see the reason for adding the IF EXISTS clause to the
language for checking the existence of objects, wouldn't it be easier to
simply provide the PL/PgSQL language for script loading?  Then it would be
possible to create scripts for database maintainance that could be run
periodically without having to make them functions stored in the database. 
Just a thought.
Cheers!


Jeff Davis-8 wrote:
 
 On Wed, 2006-09-20 at 14:18 -0700, stevethames wrote:
 I have a script that sets up my databases which I run whenever I change
 any
 of the functions.  It has a number of things it does that are unnecessary
 and cause errors.  I create some types, sequences, etc.  The error
 messages
 are irritating.  I'd like to do something like this:
 
 IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid')
   CREATE SEQUENCE SeqID;
 
 This works in other SQL languages like SQLServer and MySQL.  Is there
 anything like this in PostgreSQL?
 
 
 In 8.2, which is currently still in production, they have added the
 feature where you can do things like:
 
 DROP SEQUENCE IF EXISTS mysequence;
 CREATE SEQUENCE mysequence;
 
 Which makes writing SQL scripts much easier. They also made it work for
 other objects, like DROP TABLE IF EXISTS, etc.
 
 This might not help you, because 8.2 is still months away from being
 production quality. However, a beta isn't too far off and you may be
 interested to check it out. At least you know the code you want is
 already written :)
 
 I think the DROP IF EXISTS syntax makes more sense than CREATE IF NOT
 EXISTS, because normally the purpose of this type of thing is to reset
 your tables or sequences to the starting state.
 
 Regards,
   Jeff Davis
 
 
 
 
 ---(end of broadcast)---
 TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
 
 

-- 
View this message in context: 
http://www.nabble.com/IF-EXISTS-tf2308139.html#a6418758
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Table Inheritance / VARCHAR search question

2006-09-24 Thread Simon_Kelly

I would say that splitting the data will work ok if ( and only if ) you 
can remove some duplication of data and therefore reduce disk usage.  If 
it won't, it'll  not really save you anything, and it may increase disk 
space with the additional db overheads of another set of table meta 
information, indexes on the new table etc etc.

I have a similar width table with 7.5M rows ( no nulls ) and I get 
reasonable search times running on a 2GHz AMD64 with 2GB ram.

Varchars on disk ( if I remember correctly ) take little or no space when 
they are null.  ( A pg developer may need to comment for postgres )  It 
definitely is for Oracle ( and a good reason to rebuild tables regularly 
if you have a lot of inserts / updates and deletes going on ).

Cheers

Simon

[EMAIL PROTECTED] wrote on 20/09/2006 06:15:03 a.m.:

 Hi,
 
 I'm hoping someone on this list can save me some unnecessary 
 benchmarking today
 
 I have the  following table in my system
 
BIGSERIAL , INT , INT,  VARCHAR(32)
 
 There are currently 1M records , it will grow to be much much 
 bigger.  It's used as a search/dispatch table,  and gets the most 
 traffic on my entire app.
 
 I'm working on some new functionality, which will require the same 3 
 colums as above but with 3 new VARCHAR(32) columns
BIGSERIAL , INT , INT,  +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32)
 
 ie, the new  function shares the same  serial and the  the 2 INT columns
 
 I'm trying to get this to work efficiently on speed and on disk space.
 
 i've figured that my options are:
 
 a)one table with everything in it
pro:
   simple
possible con:
   when i had  something similar in mysql 4 years ago, i had to make 
 all the varchars chars , because speed was awful.  under this system, 
 80% of the 3 new VARCHAR fields will always be null, so that  disk 
 waste will be noticable.  thats only IF there is a speed issue with 
 VARCHAR searching.
 
 b) keep current table, create new table that inherits and has the 3 
 new fields
pro: simple
possible con:
   i can't find any documentation on how an inherit works behind the 
 scenes.  is the data cloned into the new table?  is there a join on 
 every search?  if this is constantly doing a join behind the  scenes, 
 thats probably not going to work for me
 
 c) move to a 3 table structure
table1- serial
table2 - current table, bigserial is not bigint
table3- bigint + 3 varchars
 
pro:
   obviously will work
con:
   a lot of restructuring
 
 i was going to have both table share a seqeunce, but then i 
 remembered that the  id is foreign keyed by other tables
 
 if anyone can offer a suggestion, i'd be greatly appreciative
 
 ---(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



Statement of confidentiality: This e-mail message and any accompanying
attachments may contain information that is IN-CONFIDENCE and subject to
legal privilege.
If you are not the intended recipient, do not read, use, disseminate,
distribute or copy this message or attachments.
If you have received this message in error, please notify the sender
immediately and delete this message.


*
This e-mail message has been scanned for Viruses and Content and cleared 
by the Ministry of Health's Content and Virus Filtering Gateway
*

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Connecting to PostgreSQL Server

2006-09-24 Thread Lukasz


Hello,

I installed a postgresql server on one of the machine in my network with 
an example IP 192.168.254.102. I have no problem to connect to the 
server from this machine, but when I want to have access to pgsql server 
from other pc with an example IP 192.168.254.105 it says that I have no 
access to database and it suggests adding this line to pg_hba.conf file:


host all all 192.168.0.0/24 md5

Even when I add it, the error is the same.

My pga_hba file looks like this:
# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# IPv4 local connections:
hostall all 127.0.0.1/32  md5
# IPv6 local connections:
#hostall all ::1/128   md5
host all all 192.168.0.0/24  md5

The beginning of the postgresql.conf:

listen_addresses = '*'  # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] how much free space in tables and index ?

2006-09-24 Thread maa1666

Hi,

When tuples are deleted, there remains free space in table and index
files.
Is it possible to know for each table and index how much free space it
contains ?

Thanks


---(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] Access to databas from the Internet

2006-09-24 Thread Lukasz


Shane Ambler napisal(a):
 On 20/9/2006 16:55, Lukasz [EMAIL PROTECTED] wrote:

 
  Shane Ambler napisal(a):
  On 19/9/2006 22:41, Lukasz [EMAIL PROTECTED] wrote:
 
  Hello,
 
  I would like to install a PostgreSQL. I know how to manage the database
  itself, creae databases, user, groups and so on. But I don't know how
  to allow other users, who are outside LAN to connect to database
  through Internet.
 
  For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local
  network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the
  computer with yyy.yyy... What and where I need to configure to have
  access to my database from Internet?
 
 
  I will assume that you want to allow normal psql client access and not
  through a web server.
 
  There is two places you will need to configure.
 
  One is your router - you will need to setup port forwarding . The default
  port for connecting to the PostgreSQL server is 5432 so the router will 
  need
  to forward any incoming requests on tcp port 5432 to tcp port 5432  at
  server address yyy.yyy.yyy.yyy (your PostgreSQL server address)
  If you have configured a different port then adjust accordingly.
  This is a common configuration option and shouldn't be hard to find.
 
  I will connect to my PostgreSQL by an Java applet, as also, from time
  to time, by PGAdmin.
 

 They will both connect the same as psql - through port 5432. If you wanted
 them to connect to a web server in your network to access the db then you
 would use port 80.

 --

 Shane Ambler
 [EMAIL PROTECTED]

 Get Sheeky @ http://Sheeky.Biz



 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

PostgreSQL finally installed, runs on Windows based server, everything
works great. I tried to give local and remote access to MS Access
database, but there was no good solution for me. I converted the
database to pgslq and now I'm a happy PostgreSQL user :)

Thank You guys for help.


---(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

[GENERAL] in failed sql transaction

2006-09-24 Thread Ralf Wiebicke


Hi all!

I just realized the following behaviour in postgresql: when I violate any 
constraint (unique constraint in my case) then the transaction is not usable 
anymore. Any other sql command returns a in failed sql transaction error. 
All other databases I used up to now just ignore the statement violating the 
constraint, but leave the transaction intact.

Is this intended behaviour or rather a bug? Or is there any way to switch on 
the behaviour I'd like to see?

Best regards,
Ralf.


-- 
Ralf Wiebicke
Softwareengineer

exedio GmbH
Am Fiebig 14
01561 Thiendorf
Deutschland

Telefon +49 (35248) 80-118
Fax +49 (35248) 80-199
[EMAIL PROTECTED]
www.exedio.com

---(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] Sun Java Studio Creator and PostgreSQL

2006-09-24 Thread Mike Chiarappa

Hello Poul,
take a look to thread:
http://forum.sun.com/jive/thread.jspa?forumID=123threadID=101711

It works for me !!! :o))

Mike


Jim Nasby ha scritto:

 On Sep 14, 2006, at 4:40 PM, Poul Møller Hansen wrote:
  I have some troubles getting Sun Java Studio Creator  Sun Java
  Application Server to work with
  PostgreSQL.
 
  Trying different syntaxes for the SQL statement (schema or no
  schema) in the session bean
  I get either the error: No columns in table: pmh.tablename or
  Cannot change transaction isolation level in the middle of a
  transaction
  when trying to delete, insert or update rows. Selecting is ok.
 
  The application works fine when using an Apache Derby database, so
  the problem is related to PostgreSQL
 
  I can see several postings on other mailing lists with the same
  problem, but haven's found any solution.

 Just because it works with Derby doesn't mean it's PostgreSQL's
 fault. There's plenty of things that work in certain databases that
 really shouldn't.

 In any case, you might try asking on pgsql-jdbc, since there's more
 java-heads over there. I'd also recommend turning query logging on so
 you can see exactly what commands are being sent to the server.
 --
 Jim Nasby[EMAIL PROTECTED]
 EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)



 ---(end of broadcast)---
 TIP 6: explain analyze is your friend


---(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] postgresql rising

2006-09-24 Thread tober


 Ron Johnson wrote:


It's a pack/herd mentality that serves the species very well, most
of the time.

 



Odd that you should state that, in light of your signature tag line.


- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
 



begin:vcard
fn:Berend Tober
n:Tober;Berend
email;internet:[EMAIL PROTECTED]
tel;work:860-767-0700 x118
tel;home:860-442-4103
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] column names in select don't exists in insert to

2006-09-24 Thread Johan


Michael Fuhr schreef:

 On Wed, Sep 20, 2006 at 02:29:16PM -0700, Johan wrote:
  I encountered a strange problem while trying to solve a bug. I use a
  postgresql 8.x database and a jdbc driver from
  postgresql-8.1dev-400.jdbc3.jar. The following is happening

 Any reason you're not using the latest, postgresql-8.1-407.jdbc3.jar?

  The table is created like
  create table test (
field1 int8 not null,
field2 int8 not null);
 
  if I do a
 
  select field1 from test;
 
  results are returned normal, no problems at all, but when i do a
 
  insert into test (field2, field1) values (1, 2);
 
  It complains that field1 doesn't exists.

 I can't reproduce this problem; could you post a complete test case?
 Do you see the problem if you execute the same statements in psql?

 --
 Michael Fuhr

 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster

Someone executed the query in psql for me and the problem seems to be
in a stored procedure triggered after update. This procedure complains
about the field.

Thanks for the help.

Johan


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?

2006-09-24 Thread Najib Abi Fadel

Hi again,How can i use connection pooling ? Should i use a software like PGPool ? Will the connection pooling boost considerably the performance ?Leonel adviced me to use persistent connections ? hos do i use that ?PS: I am using PHP for my applications.ThanksNajibTalha Khan [EMAIL PROTECTED] wrote: You should also consider using connection pooling inorder to attain better performance. Regards Talha Khan On 9/20/06, Najib Abi Fadel [EMAIL PROTECTED] wrote: I
have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database. Regards,Najib.Ben Trewern [EMAIL PROTECTED] wrote: The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer!Regards,Ben "Najib Abi Fadel" wrote in message
news:[EMAIL PROTECTED] Robin Ericsson wrote:On 9/18/06, Najib Abi Fadel wrote: Hi, i was searching for a load balancing solution for postgres, I found some ready to use software like PGCluster, Slony, pgpool and others. It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)--- TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I
need them for load balancing my database and thus making the queries faster. I have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to [EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates.

Do you Yahoo!? Next-gen email? Have it all with the all-new Yahoo! Mail.

Re: [GENERAL] Connecting to PostgreSQL Server

2006-09-24 Thread viniciusasousa


I am brazilian and have write dificility ,but code pg_hba is:
host all all 192.168.254.105/32  md5

and after reload configuration.


Belê!


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] serial column


Yes

But the only way of insuring that the serial starts at 1 and is sequential 
is to recreate the table.


I've tried creating and dropping the table but this generates other issues 
which I haven't been able to resolve.


Bob

- Original Message - 
From: Gevik Babakhani [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 2:00 PM
Subject: Re: [GENERAL] serial column



On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:

I need to develop a serial column that always starts at 1 and is
sequential even after deletes.
 
Any ideas???
 


Did you try the:

create table tbl
(
id SERIAL
);

or even with primary key...

create table tbl
(
id SERIAL primary key
);


--
Regards,
Gevik Babakhani




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] serial column

2006-09-24 Thread Ragnar

On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:
 I need to develop a serial column that always starts at 1 and is
 sequential even after deletes.

what exactly do you mean?

say you have rows where your
columns has values 1,2,3 and 4.

you now delete the row where
the value is 2.

what do you want to happen?

a) the rows with values 3 and 4
are changed tocontain 2 and 3 ?

b) the next 2 values to be inserted
to be 2 and then 5 ?

c) something else ?


gnari




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] serial column


Choice a.

I am using the numbers to identify devices.

If a device is deleted or replaced with another type of device I want the 
numbering to still be sequential.


Bob

- Original Message - 
From: Ragnar [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 2:24 PM
Subject: Re: [GENERAL] serial column



On sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:

I need to develop a serial column that always starts at 1 and is
sequential even after deletes.


what exactly do you mean?

say you have rows where your
columns has values 1,2,3 and 4.

you now delete the row where
the value is 2.

what do you want to happen?

a) the rows with values 3 and 4
are changed tocontain 2 and 3 ?

b) the next 2 values to be inserted
to be 2 and then 5 ?

c) something else ?


gnari







---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [GENERAL] serial column

The SERIAL is always sequential. SERIAL internally creates a SEQUENCE
and *binds* it to your table. even if you delete a record and insert a
new one , the sequence will continue to increment. however there will be
gaps between the values.

Isn't this the behavior you expect?


On Sun, 2006-09-24 at 14:19 -0700, Bob Pawley wrote:
 Yes
 
 But the only way of insuring that the serial starts at 1 and is sequential 
 is to recreate the table.
 
 I've tried creating and dropping the table but this generates other issues 
 which I haven't been able to resolve.
 
 Bob
 
 - Original Message - 
 From: Gevik Babakhani [EMAIL PROTECTED]
 To: Bob Pawley [EMAIL PROTECTED]
 Cc: Postgresql pgsql-general@postgresql.org
 Sent: Sunday, September 24, 2006 2:00 PM
 Subject: Re: [GENERAL] serial column
 
 
  On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:
  I need to develop a serial column that always starts at 1 and is
  sequential even after deletes.
   
  Any ideas???
   
  
  Did you try the:
  
  create table tbl
  (
  id SERIAL
  );
  
  or even with primary key...
  
  create table tbl
  (
  id SERIAL primary key
  );
  
  
  -- 
  Regards,
  Gevik Babakhani
  
  
  
  
  ---(end of broadcast)---
  TIP 3: Have you checked our extensive FAQ?
  
http://www.postgresql.org/docs/faq
 
 


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] serial column

It's the behavior I expect - but the gaps aren't acceptable.

Bob

- Original Message - 
From: Gevik Babakhani [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 2:42 PM
Subject: Re: [GENERAL] serial column

The SERIAL is always sequential. SERIAL internally creates a SEQUENCE
and *binds* it to your table. even if you delete a record and insert a
new one , the sequence will continue to increment. however there will be
gaps between the values.

Isn't this the behavior you expect?

On Sun, 2006-09-24 at 14:19 -0700, Bob Pawley wrote:

Yes

But the only way of insuring that the serial starts at 1 and is 
sequential

is to recreate the table.

I've tried creating and dropping the table but this generates other 
issues

which I haven't been able to resolve.

Bob

- Original Message - 
From: Gevik Babakhani [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 2:00 PM
Subject: Re: [GENERAL] serial column

 On Sun, 2006-09-24 at 13:50 -0700, Bob Pawley wrote:
 I need to develop a serial column that always starts at 1 and is
 sequential even after deletes.

 Any ideas???

 Did you try the:

 create table tbl
 (
 id SERIAL
 );

 or even with primary key...

 create table tbl
 (
 id SERIAL primary key
 );

 -- 
 Regards,

 Gevik Babakhani

 ---(end of 
 broadcast)---

 TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

---(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] serial column

2006-09-24 Thread Ragnar

On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote:
 Choice a.
 
 I am using the numbers to identify devices.
 
 If a device is deleted or replaced with another type of device I want the 
 numbering to still be sequential.

have you tried to implement ths using
triggers?

gnari



---(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] serial column

On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote:
 It's the behavior I expect - but the gaps aren't acceptable.
 
 Bob

Then using the SERIAL or SEQUENCE won't do you any good.

A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table

Another solution would be to have two triggers, one for delete and one
for insert plus an extra *reserve* table to reserve the deleted value.
The delete trigger would save the *deleted* values in the reserve table
and when a new record is inserted the insert trigger first would check
the reserve table for deleted values (that are stored by the delete
trigger) if a value exist then it would use that value or increment that
last value. 

However if you want to use a *no gap* sequence as a primary key, you
should be aware that you will destroy the integrity of you data.




-- 
Regards,
Gevik Babakhani




---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] serial column


Do you have a for instance??

Bob
- Original Message - 
From: Ragnar [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 3:10 PM
Subject: Re: [GENERAL] serial column



On sun, 2006-09-24 at 14:29 -0700, Bob Pawley wrote:

Choice a.

I am using the numbers to identify devices.

If a device is deleted or replaced with another type of device I want the
numbering to still be sequential.


have you tried to implement ths using
triggers?

gnari



---(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 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] how much free space in tables and index ?

On Fri, Sep 22, 2006 at 02:11:40AM -0700, [EMAIL PROTECTED] wrote:
 When tuples are deleted, there remains free space in table and index
 files.
 Is it possible to know for each table and index how much free space it
 contains ?

For tables see the contrib/pgstattuple module.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] serial column



A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table

I don't anticipate the table to be more than a few hundred rows - certainly 
fewer than 1,000.


Could you point to some documentation for regenerating a column's values 
other than manual??


Bob





- Original Message - 
From: Gevik Babakhani [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 3:19 PM
Subject: Re: [GENERAL] serial column



On Sun, 2006-09-24 at 14:49 -0700, Bob Pawley wrote:

It's the behavior I expect - but the gaps aren't acceptable.

Bob


Then using the SERIAL or SEQUENCE won't do you any good.

A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table

Another solution would be to have two triggers, one for delete and one
for insert plus an extra *reserve* table to reserve the deleted value.
The delete trigger would save the *deleted* values in the reserve table
and when a new record is inserted the insert trigger first would check
the reserve table for deleted values (that are stored by the delete
trigger) if a value exist then it would use that value or increment that
last value.

However if you want to use a *no gap* sequence as a primary key, you
should be aware that you will destroy the integrity of you data.




--
Regards,
Gevik Babakhani




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] how much free space in tables and index ?

2006-09-24 Thread Bruce Momjian

Michael Fuhr wrote:
 On Fri, Sep 22, 2006 at 02:11:40AM -0700, [EMAIL PROTECTED] wrote:
  When tuples are deleted, there remains free space in table and index
  files.
  Is it possible to know for each table and index how much free space it
  contains ?
 
 For tables see the contrib/pgstattuple module.

And that will work for indexes in 8.2.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://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] column names in select don't exists in insert to

On Thu, Sep 21, 2006 at 01:32:47AM -0700, Johan wrote:
 Someone executed the query in psql for me and the problem seems to be
 in a stored procedure triggered after update. This procedure complains
 about the field.

Were you able to fix the problem or are you still uncertain what
the problem is?  If the problem still exists then please post a
simple but complete test case, including the exact error message
you're getting.

-- 
Michael Fuhr

---(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] serial column

On Sun, 2006-09-24 at 15:29 -0700, Bob Pawley wrote:
 A possible solution for this would be to regenerate the entire column's
 values every time a record gets deleted starting form 1. but then again
 this would be very slow if you have a very large table
 
 I don't anticipate the table to be more than a few hundred rows - certainly 
 fewer than 1,000.
 
 Could you point to some documentation for regenerating a column's values 
 other than manual??
 
 Bob
 
 I am afraid there is no built-in way to do that.
perhaps you could create a function that:
step1: creates a sequence (with random name)
step2: update table set field=netval('random_seq_name');
step3: drop sequence...

-- 
Regards,
Gevik Babakhani




---(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] serial column

2006-09-24 Thread Tom Lane

Bob Pawley [EMAIL PROTECTED] writes:
 I am using the numbers to identify devices.
 If a device is deleted or replaced with another type of device I want the 
 numbering to still be sequential.

It sounds to me like you oughtn't be storing these numbers in the
database at all.  You just want to attach them at display time --- they
are certainly utterly meaningless as keys if they can change at any
moment.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] serial column

Thanks

I'll give that a try.

Bob
- Original Message - 
From: Gevik Babakhani [EMAIL PROTECTED]

To: Bob Pawley [EMAIL PROTECTED]
Cc: Postgresql pgsql-general@postgresql.org
Sent: Sunday, September 24, 2006 4:13 PM
Subject: Re: [GENERAL] serial column

On Sun, 2006-09-24 at 15:29 -0700, Bob Pawley wrote:

A possible solution for this would be to regenerate the entire column's
values every time a record gets deleted starting form 1. but then again
this would be very slow if you have a very large table

I don't anticipate the table to be more than a few hundred rows - 
certainly

fewer than 1,000.

Could you point to some documentation for regenerating a column's values
other than manual??

Bob

I am afraid there is no built-in way to do that.
perhaps you could create a function that:
step1: creates a sequence (with random name)
step2: update table set field=netval('random_seq_name');
step3: drop sequence...

--
Regards,
Gevik Babakhani

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] in failed sql transaction

On Sun, Sep 24, 2006 at 12:03:59PM +0200, Ralf Wiebicke wrote:
 I just realized the following behaviour in postgresql: when I violate any 
 constraint (unique constraint in my case) then the transaction is not usable 
 anymore. Any other sql command returns a in failed sql transaction error. 

Transactions are all-or-nothing: all statements must succeed or the
transaction fails (but see below regarding savepoints).

 All other databases I used up to now just ignore the statement violating the 
 constraint, but leave the transaction intact.

Which databases behave that way?  Does COMMIT succeed even if some
statements failed?

 Is this intended behaviour or rather a bug? Or is there any way to switch 
 on 
 the behaviour I'd like to see?

This is intended behavior.  You can use savepoints to roll back
part of a transaction so the transaction can continue after an
error.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[GENERAL] query rewrite rules for updateable views?

2006-09-24 Thread Markus Grabner


Hi!

As far as I understand, one can simulate updateable views in PostgreSQL by 
providing appropriate query rewrite rules. Is there any tool to automatically 
create these rules for a given set of table and view definitions?

Kind regards,
Markus


-- 
Markus Grabner - Computer Graphics and Vision
Graz University of Technology, Inffeldgasse 16a/II, 8010 Graz, Austria
Phone: +43/316/873-5041, Fax: +43/316/873-5050
WWW: http://www.icg.tu-graz.ac.at/Members/grabner

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] serial column

2006-09-24 Thread Adrian Klaver

On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote:
 Choice a.

 I am using the numbers to identify devices.

 If a device is deleted or replaced with another type of device I want the
 numbering to still be sequential.

 Bob

Do you have some other way of tracking a device? I am just trying to figure 
out how you know which device number 2 (as an example) you are looking at. I 
am assuming these devices exist as actual entities. So are these numbers 
applied to the actual device and if so are you going to be constantly 
renumbering them?

-- 
Adrian Klaver   
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] serial column

To some degree I don't care about the actual number other than roughly 
following the device ID.


At some point later in the design the numbers will be updated to project 
numbers and then frozen.


Bob


- Original Message - 
From: Adrian Klaver [EMAIL PROTECTED]

To: pgsql-general@postgresql.org
Cc: Bob Pawley [EMAIL PROTECTED]
Sent: Sunday, September 24, 2006 6:11 PM
Subject: Re: [GENERAL] serial column



On Sunday 24 September 2006 02:29 pm, Bob Pawley wrote:

Choice a.

I am using the numbers to identify devices.

If a device is deleted or replaced with another type of device I want the
numbering to still be sequential.

Bob

Do you have some other way of tracking a device? I am just trying to 
figure
out how you know which device number 2 (as an example) you are looking at. 
I

am assuming these devices exist as actual entities. So are these numbers
applied to the actual device and if so are you going to be constantly
renumbering them?

--
Adrian Klaver
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster




---(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] serial column