[GENERAL] Modifying an existing table to use an ENUM instead of an int

[Mike Christensen]

Hi all -

I have an existing table that looks like this:

CREATE TABLE orders
(
  --Bunch of stuff you don't care about
  orderstate integer NOT NULL,
  --Etc
)

with a bunch of data in it.  I've now created this new data type:

CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
'Error', 'Cancelled');

I want to change the type of "orderstate" from integer to
OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
etc.

I could create a new column, copy all the data over, then delete the
old column, but I suspect there's some cool way to go about doing
this.  Thanks!

Mike

PS - This is totally shameless, but if you cook or your spouse does,
I'd totally appreciate it if you could take a short survey to help us
develop this website we're working on.  We're hoping to get about a
thousand responses so I have to plug it everywhere :)  This URL is
http://survey.kitchenpc.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Modifying an existing table to use an ENUM instead of an int

[Mike Christensen]

Ok, I'm convinced this should work but it does not:

CREATE FUNCTION ConvertIntToOrderStateEnum(state integer) RETURNS
OrderStateEnum AS
$BODY$
SELECT CASE WHEN $1=0 then 'Preview'::OrderStateEnum
   WHEN $1=1 then 'InQueue'::OrderStateEnum
   WHEN $1=2 then 'Ordered'::OrderStateEnum
   WHEN $1=3 then 'Error'::OrderStateEnum
   WHEN $1=4 then 'Cancelled'::OrderStateEnum ELSE NULL END;
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

ALTER TABLE orders ALTER orderstate TYPE OrderStateEnum
USING ConvertIntToOrderStateEnum(OrderState);

I get the error:

ERROR: operator does not exist: orderstateenum >= integer
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.

On Mon, Jun 7, 2010 at 12:11 AM, Mike Christensen  wrote:
> Hi all -
>
> I have an existing table that looks like this:
>
> CREATE TABLE orders
> (
>  --Bunch of stuff you don't care about
>  orderstate integer NOT NULL,
>  --Etc
> )
>
> with a bunch of data in it.  I've now created this new data type:
>
> CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
> 'Error', 'Cancelled');
>
> I want to change the type of "orderstate" from integer to
> OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
> etc.
>
> I could create a new column, copy all the data over, then delete the
> old column, but I suspect there's some cool way to go about doing
> this.  Thanks!
>
> Mike
>
> PS - This is totally shameless, but if you cook or your spouse does,
> I'd totally appreciate it if you could take a short survey to help us
> develop this website we're working on.  We're hoping to get about a
> thousand responses so I have to plug it everywhere :)  This URL is
> http://survey.kitchenpc.com/
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Modifying an existing table to use an ENUM instead of an int

[Mike Christensen]

Doh!  I suck, I had a CHECK constraint on that column to make sure the
value was between 0 and 4 (the whole reason I was switching to an
enum).  Once I dropped the constraint, the ALTER TABLE worked
perfectly.

I guess I have to gripe about the error message though, it should tell
me there was a problem enforcing an existing constraint on the column.

Mike

On Mon, Jun 7, 2010 at 12:40 AM, Mike Christensen  wrote:
> Ok, I'm convinced this should work but it does not:
>
> CREATE FUNCTION ConvertIntToOrderStateEnum(state integer) RETURNS
> OrderStateEnum AS
> $BODY$
> SELECT CASE WHEN $1=0 then 'Preview'::OrderStateEnum
>   WHEN $1=1 then 'InQueue'::OrderStateEnum
>   WHEN $1=2 then 'Ordered'::OrderStateEnum
>   WHEN $1=3 then 'Error'::OrderStateEnum
>   WHEN $1=4 then 'Cancelled'::OrderStateEnum ELSE NULL END;
> $BODY$
> LANGUAGE 'sql' IMMUTABLE STRICT;
>
> ALTER TABLE orders ALTER orderstate TYPE OrderStateEnum
> USING ConvertIntToOrderStateEnum(OrderState);
>
> I get the error:
>
> ERROR: operator does not exist: orderstateenum >= integer
> SQL state: 42883
> Hint: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
>
> On Mon, Jun 7, 2010 at 12:11 AM, Mike Christensen  wrote:
>> Hi all -
>>
>> I have an existing table that looks like this:
>>
>> CREATE TABLE orders
>> (
>>  --Bunch of stuff you don't care about
>>  orderstate integer NOT NULL,
>>  --Etc
>> )
>>
>> with a bunch of data in it.  I've now created this new data type:
>>
>> CREATE TYPE OrderStateEnum AS ENUM ('Preview', 'InQueue', 'Ordered',
>> 'Error', 'Cancelled');
>>
>> I want to change the type of "orderstate" from integer to
>> OrderStateEnum, and cast 0 to Preview, 1 to InQueue, 2 to Ordered,
>> etc.
>>
>> I could create a new column, copy all the data over, then delete the
>> old column, but I suspect there's some cool way to go about doing
>> this.  Thanks!
>>
>> Mike
>>
>> PS - This is totally shameless, but if you cook or your spouse does,
>> I'd totally appreciate it if you could take a short survey to help us
>> develop this website we're working on.  We're hoping to get about a
>> thousand responses so I have to plug it everywhere :)  This URL is
>> http://survey.kitchenpc.com/
>>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WINDOWS : PostgreSQL 8.4 Server Start Error

[Zery]

Craig,

Nothing is usefull or barely i understand :-)

In pg_log i only find this :
2010-06-07 14:11:40 ICT LOG:  could not receive data from client: An
operation was attempted on something that is not a socket.


2010-06-07 14:11:40 ICT LOG:  incomplete startup packet

In event viewer i found :

: Waiting for server startup..
and
: Timed out waiting for server startup

I have removed PowerDVD and still it doesn't solve the problem, this
the second time it happen, the first time i couldn't figure out what's
the problem, so I reinstalled Windows, this time i found the problem
is crashing with PowerDVD, but i'm avoiding to reinstall Windows.

I read some other way is to manage Firewall, i did turn off the
firewall, still it dosn't solve either.

Any other way??


> On 6/7/10, Craig Ringer  wrote:
>> On 07/06/10 08:34, Zery wrote:
>>> All,
>>>
>>>
>>> I'm a newbie in postgresql, a few days ago i install postgresql
>>> 8.4.3.1 and it works fine, yesterday i install cyberlink powerdvd 9
>>> and after that my postgresql server cannot start, it give the
>>> following error info :
>>>
>>> " The postgresql-8.4 - PostgreSQL Server 8.4 service is starting
>>>   The postgresql-8.4 - PostgreSQL Server 8.4 service could not be
>>> started.
>>>
>>>   The service did not report an error
>>>   ."
>>
>> Look in the Windows event log for details on why it might be failing to
>> start. You can find Event Viewer in Adminstrative Tools in the Control
>> Panel.
>>
>> Also look at the PostgreSQL log files in
>>   C:\Program Files\postgresql\8.4\data\pg_log
>> (I think that path is right, by default anyway) and see if there's
>> anything useful there.
>>
>> I wouldn't be too surprised to find that PowerDVD has installed
>> something it shouldn't into C:\WINDOWS\SYSTEM32.
>>
>> --
>> Craig Ringer
>>
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Random Weighted Result Ordering

[Dimitri Fontaine]

Eliot Gable  writes:

> I have a set of results that I am selecting from a set of tables which I want 
> to return in a random weighted order for each priority group returned. Each 
> row has a
> priority column and a weight column. I sort by the priority column with 1 
> being highest priority. Then, for each distinct priority, I want to do a 
> weighted random
> ordering of all rows that have that same priority. I select the set of rows 
> and pass it to a custom-built function that does the ordering. I have tested 
> both the
> prioritize and the random weighted ordering functions and they do exactly 
> what I want them to do for ordering the data that I send them.
>
> The problem comes from the fact that I tried to make them generalized. They 
> take an array of a small complex type which holds just an arbitrary ID, the 
> priority,
> and the weight. The output is the same information but the rows are in
> the correct order. 

I'd try having the function return just numbers in the right order, then
use that in the ORDER BY. To have those numbers, you'd still need to
join with the result of the function, tho.

Hope this helps you already, I don't have time to go deeper in the
subject!

Regards,
-- 
dim

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Do ODBC - Posgresql supports refcursor?

[Ravi Katkar]

Hi List,


I am trying to execute a function which returns a refcursor, from ODBC client,
But ODBC client could not able to recognize the refcursor hence could not able 
to retrieve the resultset.
I am working with Posgresql 8.4 version.


Do ODBC - Posgresql supports refcursor?

Do we have user guide or document on ODBC guide which can help.


Thanks,
Ravi Katkar

[GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[Amber]

Hi ,
The installer has an error dealing with directory separator char on windows
platform, it seems the installer causes the server looking for configuration
file path with /, it should be \.  Hoping Enterprisedb engineers can see
this message.

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[Dave Page]

On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
> Hi ,
> The installer has an error dealing with directory separator char on windows
> platform, it seems the installer causes the server looking for configuration
> file path with /, it should be \.  Hoping Enterprisedb engineers can see
> this message.

/ works just fine on Windows.

What is the actual problem you are seeing, as opposed to the perceived cause?

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] WINDOWS : PostgreSQL 8.4 Server Start Error

[Kenichiro Tanaka]

Hi.

At an environment(Ver8.4.2),a simmilar error happened and we could work 
around

setting postgresql.conf

client_min_messages = log
to
client_min_messages = notice .


I'm not sure that your error is same,
but can you try this?

//Your message is following
//2010-06-07 14:11:40 ICT LOG:  incomplete startup packet
//So I guess that you set client_min_messages = log or higher.


(2010/06/07 16:49), Zery wrote:

Craig,

Nothing is usefull or barely i understand :-)

In pg_log i only find this :
2010-06-07 14:11:40 ICT LOG:  could not receive data from client: An
operation was attempted on something that is not a socket.


2010-06-07 14:11:40 ICT LOG:  incomplete startup packet

In event viewer i found :

: Waiting for server startup..
and
: Timed out waiting for server startup

I have removed PowerDVD and still it doesn't solve the problem, this
the second time it happen, the first time i couldn't figure out what's
the problem, so I reinstalled Windows, this time i found the problem
is crashing with PowerDVD, but i'm avoiding to reinstall Windows.

I read some other way is to manage Firewall, i did turn off the
firewall, still it dosn't solve either.

Any other way??


   

On 6/7/10, Craig Ringer  wrote:
 

On 07/06/10 08:34, Zery wrote:
   

All,


I'm a newbie in postgresql, a few days ago i install postgresql
8.4.3.1 and it works fine, yesterday i install cyberlink powerdvd 9
and after that my postgresql server cannot start, it give the
following error info :

" The postgresql-8.4 - PostgreSQL Server 8.4 service is starting
   The postgresql-8.4 - PostgreSQL Server 8.4 service could not be
started.

   The service did not report an error
   ."
 

Look in the Windows event log for details on why it might be failing to
start. You can find Event Viewer in Adminstrative Tools in the Control
Panel.

Also look at the PostgreSQL log files in
   C:\Program Files\postgresql\8.4\data\pg_log
(I think that path is right, by default anyway) and see if there's
anything useful there.

I wouldn't be too surprised to find that PowerDVD has installed
something it shouldn't into C:\WINDOWS\SYSTEM32.

--
Craig Ringer

   
 
   



--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does npgsql have a bunch of bugs with DB enums?

[Mike Christensen]

This is probably not the right forum for this question, but maybe
someone can help me out or redirect me.

I'm running into a lot of problems with npgsql and enum types.  There
seems to be very little support or testing in this area.  The issue
right now is I have a lot of SQL functions that have OUT parameters of
an enum type.  Such as:

CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');


CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
OUT UnitType UnitTypeEnum)
  RETURNS SETOF record AS
...

When I call that function using the npgsql driver, I get all sorts of
errors.  The data table simple has no ProviderType (it's blank), even
though the DataType is System.String.  If I try to read the data
reader, I get exceptions.  The solution I found is to cast the enum to
a text, such as:

CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
OUT UnitType text)
...
select UnitType::text from foo;

However, this becomes a pain if I'm not using OUT parameters and just
returning a set of a row type.  Plus, having to cast ENUMs is kind of
a hack.

Are the people working on npgsql aware of this problem, and can we
expect to get real enum support in future versions?  It seems they
should just magically cast to either a string or an int and I
shouldn't have to worry about that.  Thanks!!

Mike

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Random Weighted Result Ordering

[Lew]

Eliot Gable wrote:

rows. Basically, I thought that if the original data was:

50, 1, 5, 'data1'
55, 1, 4, 'data2'
34, 2, 0, 'data3'
90, 2, 1, 'data4'
95, 2, 1, 'data5

And the input to the functions was:

50, 1, 5
55, 1, 4
34, 2, 0
90, 2, 1
95, 2, 1

And the prioritized and weighted order came back:

50, 1, 5
55, 1, 4
95, 2, 1
90, 2, 1
34, 2, 0

Then, if I INNER JOINED them like:

(
   50, 1, 5
   55, 1, 4
   95, 2, 1
   90, 2, 1
   34, 2, 0
) AS randomized INNER JOIN (
   50, 1, 5, 'data1'
   55, 1, 4, 'data2'
   34, 2, 0, 'data3'
   90, 2, 1, 'data4'
   95, 2, 1, 'data5
) AS data ON (
randomized.id  = data.id 
)

Then the rows would come back as:

50, 1, 5, 'data1'
55, 1, 4, 'data2'
95, 2, 1, 'data5'
90, 2, 1, 'data4'
34, 2, 0, 'data3

Unfortunately, that does not seem to be happening. Before I spend a ton
of time digging into this issue, I thought I would pose the questions here:

Does anyone know for certain why this would not work? Or, does anyone
know why this should work?


It should not "work" in the sense you mean, but it does "work" in the way that 
SQL promises, namely that the order can be anything if you omit an ORDER BY 
clause in the SELECT.



I assumed that the order of the joins would preserve the ordering of the
first set of data. However, I am worried about how the planner might
re-arrange the joins on me, and I am wondering whether the order is
guaranteed to be preserved like this in the first place... Does anyone
know for sure about these assumptions?


SELECT makes no promise about the order of returned rows absent an ORDER BY 
clause.  Your query lacks an ORDER BY clause.  You could therefore get any 
order back, including the possibility of different orders from different runs 
of the same query.


Tables in a relational database have no inherent order.  You took no steps 
whatsoever to guarantee the order of rows returned by the SELECT, so you 
should not be surprised at any order that comes back.


--
Lew

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[顾小波]

 

When running the installer, there is a warning,

 

Problem running post-install step. Installation may not complete correctly

Error reading file

D:/amber/program/postgresql/8.4/data/postgres.conf

When start the service, the following entry is written in windows event log

 

"D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or 
directory

On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:

On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
> Hi ,
> The installer has an error dealing with directory separator char on windows
> platform, it seems the installer causes the server looking for configuration
> file path with /, it should be \.  Hoping Enterprisedb engineers can see
> this message.

/ works just fine on Windows.

What is the actual problem you are seeing, as opposed to the perceived cause?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com  
The Enterprise Postgres Company

 

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[Dave Page]

Ashesh,

Can you take a look at this please? I wonder is there's an issue with
the data directory being on the D drive.

Thanks.

On Mon, Jun 7, 2010 at 11:41 AM, Amber  wrote:
> When running the installer, there is a warning,
>
> Problem running post-install step. Installation may not complete correctly
> Error reading file
> D:/amber/program/postgresql/8.4/data/postgres.conf
> When start the service, the following entry is written in windows event log
>
> "D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or
> directory
>
> On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:
>>
>> On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
>> > Hi ,
>> > The installer has an error dealing with directory separator char on
>> > windows
>> > platform, it seems the installer causes the server looking for
>> > configuration
>> > file path with /, it should be \.  Hoping Enterprisedb engineers can see
>> > this message.
>>
>> / works just fine on Windows.
>>
>> What is the actual problem you are seeing, as opposed to the perceived
>> cause?
>>
>> --
>> Dave Page
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise Postgres Company
>
>



-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[顾小波]

Hi Dave

 

I am not sure whether you have received my message, sorry for duplicate 
messages.

 

Xiaobo gu

 

From: Amber [mailto:guxiaobo1...@gmail.com] 
Sent: Monday, June 07, 2010 6:42 PM
To: Dave Page
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

 

When running the installer, there is a warning,

 

Problem running post-install step. Installation may not complete correctly

Error reading file

D:/amber/program/postgresql/8.4/data/postgres.conf

When start the service, the following entry is written in windows event log

 

"D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or 
directory

On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:

On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
> Hi ,
> The installer has an error dealing with directory separator char on windows
> platform, it seems the installer causes the server looking for configuration
> file path with /, it should be \.  Hoping Enterprisedb engineers can see
> this message.

/ works just fine on Windows.

What is the actual problem you are seeing, as opposed to the perceived cause?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com  
The Enterprise Postgres Company

 

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[Ashesh Vashi]

Hi Amber,

Can you please tell me?
Is the 'D' drive an mapped network drive?

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company


On Mon, Jun 7, 2010 at 5:28 PM, Dave Page  wrote:

> Ashesh,
>
> Can you take a look at this please? I wonder is there's an issue with
> the data directory being on the D drive.
>
> Thanks.
>
> On Mon, Jun 7, 2010 at 11:41 AM, Amber  wrote:
> > When running the installer, there is a warning,
> >
> > Problem running post-install step. Installation may not complete
> correctly
> > Error reading file
> > D:/amber/program/postgresql/8.4/data/postgres.conf
> > When start the service, the following entry is written in windows event
> log
> >
> > "D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or
> > directory
> >
> > On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:
> >>
> >> On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
> >> > Hi ,
> >> > The installer has an error dealing with directory separator char on
> >> > windows
> >> > platform, it seems the installer causes the server looking for
> >> > configuration
> >> > file path with /, it should be \.  Hoping Enterprisedb engineers can
> see
> >> > this message.
> >>
> >> / works just fine on Windows.
> >>
> >> What is the actual problem you are seeing, as opposed to the perceived
> >> cause?
> >>
> >> --
> >> Dave Page
> >> EnterpriseDB UK: http://www.enterprisedb.com
> >> The Enterprise Postgres Company
> >
> >
>
>
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise Postgres Company
>

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[顾小波]

Hi Ashesh,

 

No, it’s a physical partition.

 

Amber

 

From: Ashesh Vashi [mailto:ashesh.va...@enterprisedb.com] 
Sent: Monday, June 07, 2010 8:15 PM
To: Dave Page
Cc: Amber; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

 

Hi Amber,

 

Can you please tell me?

Is the 'D' drive an mapped network drive?

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company  

 

On Mon, Jun 7, 2010 at 5:28 PM, Dave Page  wrote:

Ashesh,

Can you take a look at this please? I wonder is there's an issue with
the data directory being on the D drive.

Thanks.

On Mon, Jun 7, 2010 at 11:41 AM, Amber  wrote:
> When running the installer, there is a warning,
>
> Problem running post-install step. Installation may not complete correctly
> Error reading file
> D:/amber/program/postgresql/8.4/data/postgres.conf
> When start the service, the following entry is written in windows event log
>
> "D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or
> directory

>
> On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:
>>
>> On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
>> > Hi ,
>> > The installer has an error dealing with directory separator char on
>> > windows
>> > platform, it seems the installer causes the server looking for
>> > configuration
>> > file path with /, it should be \.  Hoping Enterprisedb engineers can see
>> > this message.
>>
>> / works just fine on Windows.
>>
>> What is the actual problem you are seeing, as opposed to the perceived
>> cause?
>>
>> --
>> Dave Page
>> EnterpriseDB UK: http://www.enterprisedb.com
>> The Enterprise Postgres Company
>
>



--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

 

Re: [GENERAL] Performance drop after upgrading to 8.4.4?

[Max Williams]

No one has had this problem??? Should I perhaps direct this to the developers 
list?
Cheers,
Max

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Max Williams
Sent: 03 June 2010 14:22
To: pgsql-general@postgresql.org
Subject: [GENERAL] Performance drop after upgrading to 8.4.4?

Hi,
I was doing some benchmarking while changing configuration options to try to 
get more performance out of our postgresql servers and noticed that when 
running pgbench against 8.4.3 vs 8.4.4 on identical hardware and configuration 
there is a large difference in performance. I know tuning is a very deep topic 
and benchmarking is hardly an accurate indication of real world performance but 
I was still surprised by these results and wanted to know what I am doing wrong.

Hardware specs are:
2x Quad core Xeons 2.4Ghz
16GB RAM
2x RAID1 7.2k RPM disks

Relevant Postgresql Configuration:
max_connections = 1000
shared_buffers = 4096MB
temp_buffers = 8MB
max_prepared_transactions = 1000
work_mem = 8MB
maintenance_work_mem = 512MB
wal_buffers = 8MB
checkpoint_segments = 192
checkpoint_timeout = 30min
effective_cache_size = 12288MB

Results for the 8.4.3 (8.4.3-2PGDG.el5) host:
[r...@some-host ~]# pgbench -h dbs3 -U postgres -i -s 100 pgbench1 > /dev/null 
2>&1 && pgbench -h dbs3 -U postgres -c 100 -t 10 pgbench1
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 4612.734318 (including connections establishing)
tps = 4613.308264 (excluding connections establishing)

Results for the 8.4.4 (8.4.4-1PGDG.el5) host:
[root@ some-host ~]# pgbench -h dbs4 -U postgres -i -s 100 pgbench1 > /dev/null 
2>&1 && pgbench -h dbs4 -U postgres -c 100 -t 10 pgbench1
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
query mode: simple
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 2799.134267 (including connections establishing)
tps = 2799.451407 (excluding connections establishing)

Any input? I can reproduce these numbers consistently. By the way, I am a new 
postgresql user so my experience is limited.
Cheers,
Max

Re: [GENERAL] Error in 8.4.4-1 of windows installer from Enterprisedb

[Ashesh Vashi]

Hi Amber,

Can you please share the installation log file with us found under %TEMP%
directory?

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company


On Mon, Jun 7, 2010 at 6:01 PM, 顾小波  wrote:

>  Hi Ashesh,
>
>
>
> No, it’s a physical partition.
>
>
>
> Amber
>
>
>
> *From:* Ashesh Vashi [mailto:ashesh.va...@enterprisedb.com]
> *Sent:* Monday, June 07, 2010 8:15 PM
> *To:* Dave Page
> *Cc:* Amber; pgsql-general@postgresql.org
>
> *Subject:* Re: [GENERAL] Error in 8.4.4-1 of windows installer from
> Enterprisedb
>
>
>
> Hi Amber,
>
>
>
> Can you please tell me?
>
> Is the 'D' drive an mapped network drive?
>
> --
> Thanks & Regards,
>
> Ashesh Vashi
> EnterpriseDB INDIA: Enterprise Postgres Company
>
>
>
> On Mon, Jun 7, 2010 at 5:28 PM, Dave Page  wrote:
>
> Ashesh,
>
> Can you take a look at this please? I wonder is there's an issue with
> the data directory being on the D drive.
>
> Thanks.
>
> On Mon, Jun 7, 2010 at 11:41 AM, Amber  wrote:
> > When running the installer, there is a warning,
> >
> > Problem running post-install step. Installation may not complete
> correctly
> > Error reading file
> > D:/amber/program/postgresql/8.4/data/postgres.conf
> > When start the service, the following entry is written in windows event
> log
> >
> > "D:/Amber/Program/PostgreSQL/8.4/data/postgresql.conf": No such file or
> > directory
>
> >
> > On Mon, Jun 7, 2010 at 6:28 PM, Dave Page  wrote:
> >>
> >> On Mon, Jun 7, 2010 at 11:24 AM, Amber  wrote:
> >> > Hi ,
> >> > The installer has an error dealing with directory separator char on
> >> > windows
> >> > platform, it seems the installer causes the server looking for
> >> > configuration
> >> > file path with /, it should be \.  Hoping Enterprisedb engineers can
> see
> >> > this message.
> >>
> >> / works just fine on Windows.
> >>
> >> What is the actual problem you are seeing, as opposed to the perceived
> >> cause?
> >>
> >> --
> >> Dave Page
> >> EnterpriseDB UK: http://www.enterprisedb.com
> >> The Enterprise Postgres Company
> >
> >
>
>
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise Postgres Company
>
>
>

Re: [GENERAL] Connection's limit in SCO OpenServer 5.0.7 and pg 8.3.11 (no more than 94 connections)

[Joshua Tolley]

On Fri, Jun 04, 2010 at 10:51:27AM -0500, erobles wrote:
> Hello! I have postgres running on SCO OpenServer 5.0.7
>
> and I've noticed that only accepts up to 94 connections, no more ...
> I modified the values in postgresql.conf
> max_connections from  100 to 128
> shared_buffers  from 24 to 48  MB
>
> postgres is started  with:
> su - postgres -c "/usr/local/pgsql83/bin/postmaster -i -D  
> /usr/local/pgsql83/data"

What does it say when it refuses connections above 94? Also, when you connect
and type "show max_connections;", what gets returned? It might be you're
editing the wrong copy of postgresql.conf, or not restarting PostgreSQL after
editing

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


signature.asc
Description: Digital signature

Re: [GENERAL] Download

[Devrim GÜNDÜZ]

On Fri, 2010-05-28 at 09:44 +0100, Piotr Kublicki wrote:
> 
> What's wrong with it? 

Package layouts changed since it was written... I will update it before
9.0.
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Error on Windows server could not open relation base/xxx/xxx Permission denied

[Magnus Hagander]

On Mon, Jun 7, 2010 at 08:44, Craig Ringer  wrote:
> On 07/06/10 10:29, John T. Dow wrote:
>> One of my clients is getting this problem occasionally. Actually, we
>> can cause it to happen quite reliably by pasting certain text into a
>> couple of fields, but the vast majority of text entered into the vast
>> majority of fields causes no problem.
>
>> I've read enough to suggest that AV software might be the culprit.
>> It has been said that it is not sufficient to exclude the database
>> directory nor even to disable to AV protection, it has to be removed.
>
> Depends on the AV software. That advice is general, and is given because
> _some_ antivirus software is badly written and fails to properly exclude Pg.

Actually, the issue isn't that they "fail to properly exclude postgres".

The issue is that they insert filtering functions in the Windows API,
that *breaks the Windows API*. The reason this is often not noticed on
other products before PostgreSQL is that PostgreSQL uses a
multiprocess architecture, and this is specifically the case where
they fail. This is uncommon in the extreme on Windows. In fact,
Microsoft has (or at elast used to have) examples up on their MSDN
site that break in this case.


> Some AV software probably behaves fine.

Probably.


>> The problem is, their database server is also a file server. As a file 
>> server it must have AV protection. The server is running Windows Server 2003 
>> I believe. It has RAID etc.  My client's antivirus software is AVG (paid, 
>> not free).

It's generally a mistake to put a fileserver on the same machien as a
database server, if you want any kind of performance for either one of
them. But if you don't care about performance at all, it's a fairly
sensible move, yes.

>> Question: Is AV software still regarded as the likely culprit?
>
> Likely enough - especially for intermittent issues - that the best thing
> to do is uninstall it, reboot, and re-test to see if the issue remains.
>
> If you can reproduce it without the AV software then it's worth
> investigating further.

Yeah. And if it does go away without the AV you at least know what the
problem was. You can then decide if you want to try a different AV, or
if you want to look at perhaps running pg in a virtual machine on the
box.

> It'd be interesting if someone with a paid contract for AV support would
> go to their AV vendor and get them involved. With the active
> co-operation of an AV vendor or two and a reproducible fault, some
> progress might be possible.

Yes, that would be very useful.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[Thomas Kellerer]

Magnus Hagander, 07.06.2010 15:52:

Some AV software probably behaves fine.


Probably.


In case anyone is interested:

I have two development computers that run Postgres on Windows XP.
One with Avira the other with Sophos.

Neither has or had any problems installing or running Postgres

Regards
Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[Magnus Hagander]

On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer  wrote:
> Magnus Hagander, 07.06.2010 15:52:
>>>
>>> Some AV software probably behaves fine.
>>
>> Probably.
>
> In case anyone is interested:
>
> I have two development computers that run Postgres on Windows XP.
> One with Avira the other with Sophos.
>
> Neither has or had any problems installing or running Postgres

What kind of load do the systems have? Particularly, how many
parallell connections? That seems to push things over the edge more
often than high transaction single-user ones.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[Thomas Kellerer]

Magnus Hagander, 07.06.2010 16:15:

On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer  wrote:

Magnus Hagander, 07.06.2010 15:52:


Some AV software probably behaves fine.


Probably.


In case anyone is interested:

I have two development computers that run Postgres on Windows XP.
One with Avira the other with Sophos.

Neither has or had any problems installing or running Postgres


What kind of load do the systems have? Particularly, how many
parallell connections? That seems to push things over the edge more
often than high transaction single-user ones.



Ah, that might make the difference:
I have no real load on those computers (as I said, developer machine)

So it's more a single-user type of load

Regards
Thomas



 




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Connection's limit in SCO OpenServer 5.0.7 and pg 8.3.11 (no more than 94 connections)

[erobles]

On 06/07/2010 07:44 AM, Joshua Tolley wrote:
What does it say when it refuses connections above 94? Also, when you 
connect:


This is the message:
LOG: could not fork new process  fro connection: Resource temporarily 
unavailable...

and those are the  kernel's  resources  values:

NODE"srvr83"
EVDEVS96
EVQUEUES88
NSPTTYS64
NUMSP256
NSTREAM4352
NHINODE1024
GPGSLO2000
GPGSHI6000
NSTRPAGES6000
NAIOPROC50
NAIOREQ400
NAIOBUF400
NAIOHBUF100
NAIOREQPP400
NAIOLOCKTBL50
MAX_PROC1
MAXUMEM1048576
NCALL256
NCLIST512
NSTREVENT14848
NUMTIM1888
NUMTRW1888
SDSKOUT64
SEMMAP8192
SEMMNI8192
SEMMNS8192
SEMMSL150
SEMMNU100
SHMMAX2147483647
TTHOG4096
SECLUID0
SECSTOPIO1
SECCLEARID1
SHMALL15360
SHMMNI300
LOG:

the SEMMAP, SEMMNI, SEMMNS, SEMMNU and SHMMAX  are the max value. maybe, 
i  lossing some kernel value to set , but i don't know which  kernel 
resource  is .



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Move data from DB2 to Postgres any software/solutions/approach?

[Chris Browne]

dm.a...@gmail.com (DM) writes:
> It is not real time, updates every 5 mins should be fine. 
>
> But the DB2 database is real busy and its real performance based. 

The book "Scalable Internet Architectures" (by Theo Schlossnagle) has
an example of how to build a trigger-based replication system copying
data from an Oracle database to Postgres.

It basically tracks PK values for tuples changed/deleted (which is
what the old RServer and eRServer replication systems for Postgres
did), allowing a process to come in afterwards and pull data over to
the replica.

I presume that DB2 has enough functionality to let you run triggers to
capture which tuples changed, and when.  Given that, it shouldn't be
super-difficult to do what you need.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/slony.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] create index concurrently - duplicate index to reduce time without an index

[Greg Smith]

gareth.willi...@csiro.au wrote:

Re-reading the documentation 
http://www.postgresql.org/docs/8.4/interactive/sql-reindex.html it is clear now 
that reindex or recreating and index should not normally be needed - certainly 
not to keep an index up-to-date.  I would have guessed that VACUUM or VACUUM 
ANALYSE on the table that the index is associated would have been sufficient to 
reclaim space for a 'bloated' index (maybe only VACUUM FULL would help).  In 
any case we can leave reindexing or full vacuum for outages where we are 
interrupting service anyway.
  


It is a periodic preventative maintenance operation you can expect to 
need occasionally, but certainly not often.  Indexes maintain themselves 
just fine most of the time.  They can get out of whack if you delete a 
lot of data out of them and there are some use patterns that tend to a 
aggravate the problems here (like tables where you're always inserting 
new data and deleting old), but it's certainly not something you run all 
the time.


You should read http://wiki.postgresql.org/wiki/VACUUM_FULL to clear up 
when it is needed and what the better alternatives are.




A few times when I was trying to drop an index (before or after creating a 
duplicate index with 'concurrently'), the dropping of the index stalled.  It 
seems that this was because of existing connection:
postgres: rods ICAT 130.102.163.141(58061) idle in transaction
And new clients block.  Is that expected? Should we look for 'bugs' in out 
client that seems to be holding a connection?
  


You do need to be careful that there are no clients connected when you 
try this, or yes this is expected behavior.  One popular technique is to 
put some sort of "block access to the database" switch in the 
application itself, specifically to support small outages while keeping 
the app from going crazy.  You can flip that for a few second around 
when you're doing the index switch.




Ps. Greg, I don't understand the issue with 'concurrently rebuild (of) an index 
that enforces a constraint or unique index'.  I don't think I care much right 
at the moment, but I'm generally interested and others might be too. Would you 
expect the create index to fail or to cause locking or just transient 
performance degradation?
  


The description Alban wrote covers what I was alluding to.  You can't 
just can't drop an index that supports a constraint, and that has some 
(bad) implication for how you can rebuild it.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Performance drop after upgrading to 8.4.4?

[Greg Smith]

Max Williams wrote:


No one has had this problem??? Should I perhaps direct this to the 
developers list?




pgsql-performance is the list you should have sent this to.  There are 
several people who are subscribed to that one but not to -general or 
-hackers who enjoy helping with this particular type of problem.  Make 
sure you include your operating system and disk controller information 
on that one, I wasn't sure where to start without those two bits of info.


Also, see if the problem goes away if the client load is smaller.  Peak 
pgbench performance on  your system is going to be with between 8 and 16 
clients, and the way it falls off after that depends on operating 
systems issues rather than PostgreSQL ones.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Misplaced pg_upgrade_support.so ?

[Glen Barber]

Hi,

Maybe this is better asked on -general instead of -testers.

First off, thank you for bringing this into contrib.

I built beta2 this morning to give pg_upgrade a test run, and everything
worked out great.  The only thing I am not sure of is the expected
location of pg_upgrade_support.so.  It is a default build with the
exception of "--prefix=/usr/local/pgsql-beta2"; pg_upgrade_support.so
ended up in /usr/local/pgsql-beta2/lib.

Here is what I saw when running pg_upgrade initially:

%pg_upgrade -d /var/db/pgsql/data \
 -D /var/db/pgsql/data2 \
 -b /usr/local/pgsql-beta1/bin \
 -B /usr/local/pgsql-beta2/bin
Performing Consistency Checks
-
Checking old data directory (/var/db/pgsql/data)ok
Checking new data directory (/var/db/pgsql/data2)   ok


pg_upgrade_support.so must be created and installed in \
/usr/local/pgsql-beta2/lib/postgresql/pg_upgrade_support.so

Is this module ending up in the wrong location on install, or is
pg_upgrade looking in the wrong place?

Thanks and regards,

--
Glen Barber

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[Vick Khera]

On Sat, Jun 5, 2010 at 11:38 PM, Scott Marlowe  wrote:
> On Sat, Jun 5, 2010 at 5:27 PM, u235sentinel  wrote:
>> I'm curious if anyone has had any experiences (good and bad) using Postgres
>> on Dell PowerEdge servers.
>
> I've had lots of experience with Dell, most of it poor.  Wrong upgrade
> CPUs, use of non-buffered memory meaning I can't max out my machine's
> RAM, mediocre performance from most PERC RAID controllers.  I just got

Ditto.  Of late I'm buying HPs, but I haven't yet put one into
database service.  Our DB servers are all currently Sun with fibre
channel cards to external RAID systems.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does psql -f quit insertion after an error in a statement has been detected?

[Vick Khera]

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y  wrote:
> I'm using a psql -f command to reload the data from a dump file.  I noticed 
> that some tables are not populated with any rows (I mean 0 rows), yet, if I 
> manually insert a row (actually just copy an INSERT statement from that input 
> file) in the interactive terminal, that row was added with no problem.  So my 
> question "does psql -f quits inserting rows for a table when it detects there 
> is an error in a statement?".  The impression that I got is that even though 
> other rows might not have any errors, but psql -f seems just quits after it 
> detects an error in a row.
>

See the psql man page, and search for "ON_ERROR_STOP". This controls
this behavior.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Glen Barber]

On 6/7/10 1:14 PM, Glen Barber wrote:

[snip]


Is this module ending up in the wrong location on install, or is
pg_upgrade looking in the wrong place?



I've received a reply on -testers about this, in case anyone is using 
pg_upgrade with testing 9.0.


http://archives.postgresql.org/pgsql-testers/2010-06/msg0.php

Regards,

--
Glen Barber

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] create index concurrently - duplicate index to reduce time without an index

[Alvaro Herrera]

Excerpts from Greg Smith's message of lun jun 07 12:23:44 -0400 2010:

> It is a periodic preventative maintenance operation you can expect to 
> need occasionally, but certainly not often.  Indexes maintain themselves 
> just fine most of the time.  They can get out of whack if you delete a 
> lot of data out of them and there are some use patterns that tend to a 
> aggravate the problems here (like tables where you're always inserting 
> new data and deleting old), but it's certainly not something you run all 
> the time.

Indexes on which you always insert new data and delete old can keep
themselves in good shape too.  The really problematic cases are those in
which you delete new data and delete most, but not all, old data.  Those
cases would result in almost empty pages that can never be recycled (we
do not have btree page merging).

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does psql -f quit insertion after an error in a statement has been detected?

[Wang, Mary Y]

That's good to know.  I ended up manually copy/paste INSERT statements for each 
table to another file and rerun the psql -f again.  It was painful!  

Mary
-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Vick Khera
Sent: Monday, June 07, 2010 11:21 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Does psql -f quit insertion after an error in a 
statement has been detected?

On Sun, Jun 6, 2010 at 5:29 PM, Wang, Mary Y  wrote:
> I'm using a psql -f command to reload the data from a dump file.  I noticed 
> that some tables are not populated with any rows (I mean 0 rows), yet, if I 
> manually insert a row (actually just copy an INSERT statement from that input 
> file) in the interactive terminal, that row was added with no problem.  So my 
> question "does psql -f quits inserting rows for a table when it detects there 
> is an error in a statement?".  The impression that I got is that even though 
> other rows might not have any errors, but psql -f seems just quits after it 
> detects an error in a row.
>

See the psql man page, and search for "ON_ERROR_STOP". This controls this 
behavior.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Bruce Momjian]

Glen Barber wrote:
> Hi,
> 
> Maybe this is better asked on -general instead of -testers.
> 
> First off, thank you for bringing this into contrib.
> 
> I built beta2 this morning to give pg_upgrade a test run, and everything
> worked out great.  The only thing I am not sure of is the expected
> location of pg_upgrade_support.so.  It is a default build with the
> exception of "--prefix=/usr/local/pgsql-beta2"; pg_upgrade_support.so
> ended up in /usr/local/pgsql-beta2/lib.
> 
> Here is what I saw when running pg_upgrade initially:
> 
> %pg_upgrade -d /var/db/pgsql/data \
>   -D /var/db/pgsql/data2 \
>   -b /usr/local/pgsql-beta1/bin \
>   -B /usr/local/pgsql-beta2/bin
> Performing Consistency Checks
> -
> Checking old data directory (/var/db/pgsql/data)ok
> Checking new data directory (/var/db/pgsql/data2)   ok
> 
> 
> pg_upgrade_support.so must be created and installed in \
> /usr/local/pgsql-beta2/lib/postgresql/pg_upgrade_support.so
> 
> Is this module ending up in the wrong location on install, or is
> pg_upgrade looking in the wrong place?

Odd.  I am confused where that "/postgresql/" is coming from.  Can I see
your output of:

pg_config --pkglibdir

Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does psql -f quit insertion after an error in a statement has been detected?

[Adrian Klaver]

On 06/07/2010 02:10 PM, Wang, Mary Y wrote:

That's good to know.  I ended up manually copy/paste INSERT statements for each 
table to another file and rerun the psql -f again.  It was painful!

Mary


For future reference you could do something like:

pg_dump -a -t table_name -f table_name_data.sql

This will dump the data only from the table 'table_name' in the original 
database to a file you can use to populate the table in the new database.


See here for the gory details:
http://www.postgresql.org/docs/8.4/interactive/app-pgdump.html

--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Glen Barber]

Hi Bruce,

On 6/7/10 5:12 PM, Bruce Momjian wrote:

Glen Barber wrote:

Hi,

Maybe this is better asked on -general instead of -testers.

First off, thank you for bringing this into contrib.

I built beta2 this morning to give pg_upgrade a test run, and everything
worked out great.  The only thing I am not sure of is the expected
location of pg_upgrade_support.so.  It is a default build with the
exception of "--prefix=/usr/local/pgsql-beta2"; pg_upgrade_support.so
ended up in /usr/local/pgsql-beta2/lib.

Here is what I saw when running pg_upgrade initially:

%pg_upgrade -d /var/db/pgsql/data \
   -D /var/db/pgsql/data2 \
   -b /usr/local/pgsql-beta1/bin \
   -B /usr/local/pgsql-beta2/bin
Performing Consistency Checks
-
Checking old data directory (/var/db/pgsql/data)ok
Checking new data directory (/var/db/pgsql/data2)   ok


pg_upgrade_support.so must be created and installed in \
/usr/local/pgsql-beta2/lib/postgresql/pg_upgrade_support.so

Is this module ending up in the wrong location on install, or is
pg_upgrade looking in the wrong place?


Odd.  I am confused where that "/postgresql/" is coming from.  Can I see
your output of:

pg_config --pkglibdir

Thanks.



Sure thing:

  %pg_config --pkglibdir
  /usr/local/pgsql-beta2/lib/postgresql

Let me know if I can provide any additional information.

--
Glen Barber

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Bruce Momjian]

Glen Barber wrote:
> >> exception of "--prefix=/usr/local/pgsql-beta2"; pg_upgrade_support.so
> >> ended up in /usr/local/pgsql-beta2/lib.
> >>
> >> Here is what I saw when running pg_upgrade initially:
> >>
> >> %pg_upgrade -d /var/db/pgsql/data \
> >>-D /var/db/pgsql/data2 \
> >>-b /usr/local/pgsql-beta1/bin \
> >>-B /usr/local/pgsql-beta2/bin
> >> Performing Consistency Checks
> >> -
> >> Checking old data directory (/var/db/pgsql/data)ok
> >> Checking new data directory (/var/db/pgsql/data2)   ok
> >>
> >>
> >> pg_upgrade_support.so must be created and installed in \
> >> /usr/local/pgsql-beta2/lib/postgresql/pg_upgrade_support.so
> >>
> >
> > pg_config --pkglibdir
> >
> > Thanks.
> >
> 
> Sure thing:
> 
>%pg_config --pkglibdir
>/usr/local/pgsql-beta2/lib/postgresql
> 
> Let me know if I can provide any additional information.

That is interesting.  I ran configure --prefix=/usr/local/pgsql-beta2
using CVS HEAD (which should match 9.0 beta2), and my output is
different:

$ ./pg_config --pkglibdir
/usr/var/local/pgsql-beta2/lib

Notice the "/postgresql" is missing.  This is before running pg_upgrade.

I am attaching my entire pg_config output.  Can I see yours?  Thanks.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +
BINDIR = /usr/var/local/pgsql-beta2/bin
DOCDIR = /usr/var/local/pgsql-beta2/share/doc
HTMLDIR = /usr/var/local/pgsql-beta2/share/doc
INCLUDEDIR = /usr/var/local/pgsql-beta2/include
PKGINCLUDEDIR = /usr/var/local/pgsql-beta2/include
INCLUDEDIR-SERVER = /usr/var/local/pgsql-beta2/include/server
LIBDIR = /usr/var/local/pgsql-beta2/lib
PKGLIBDIR = /usr/var/local/pgsql-beta2/lib
LOCALEDIR = /usr/var/local/pgsql-beta2/share/locale
MANDIR = /usr/var/local/pgsql-beta2/share/man
SHAREDIR = /usr/var/local/pgsql-beta2/share
SYSCONFDIR = /usr/var/local/pgsql-beta2/etc
PGXS = /usr/var/local/pgsql-beta2/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-tclconfig=/u/lib' 
'--enable-thread-safety' '--with-includes=/usr/local/include/readline 
/usr/contrib/include' '--with-libraries=/usr/local/lib /usr/contrib/lib' 
'--with-openssl' '--enable-cassert' '--prefix=/usr/local/pgsql-beta2'
CC = ccache gcc
CPPFLAGS = -I/usr/local/include/readline -I/usr/contrib/include
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -fno-strict-aliasing 
-O1 -Wall -Wmissing-prototypes -Wmissing-declarations -Wpointer-arith 
-Wcast-align
CFLAGS_SL = -fpic
LDFLAGS = -L/usr/local/lib -L/usr/contrib/lib 
-Wl,-rpath,'/usr/local/pgsql-beta2/lib' -O1 -Wall -Wmissing-prototypes 
-Wmissing-declarations -Wpointer-arith -Wcast-align
LDFLAGS_SL = 
LIBS = -lpgport -lssl -lcrypto -lz -lreadline -ltermcap -lgetopt -ldl -lutil 
-lm 
VERSION = PostgreSQL 9.0beta2

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Glen Barber]

On 6/7/10 6:59 PM, Bruce Momjian wrote:

Glen Barber wrote:

exception of "--prefix=/usr/local/pgsql-beta2"; pg_upgrade_support.so
ended up in /usr/local/pgsql-beta2/lib.

Here is what I saw when running pg_upgrade initially:

%pg_upgrade -d /var/db/pgsql/data \
-D /var/db/pgsql/data2 \
-b /usr/local/pgsql-beta1/bin \
-B /usr/local/pgsql-beta2/bin
Performing Consistency Checks
-
Checking old data directory (/var/db/pgsql/data)ok
Checking new data directory (/var/db/pgsql/data2)   ok


pg_upgrade_support.so must be created and installed in \
/usr/local/pgsql-beta2/lib/postgresql/pg_upgrade_support.so



pg_config --pkglibdir

Thanks.



Sure thing:

%pg_config --pkglibdir
/usr/local/pgsql-beta2/lib/postgresql

Let me know if I can provide any additional information.


That is interesting.  I ran configure --prefix=/usr/local/pgsql-beta2
using CVS HEAD (which should match 9.0 beta2), and my output is
different:

$ ./pg_config --pkglibdir
/usr/var/local/pgsql-beta2/lib

Notice the "/postgresql" is missing.  This is before running pg_upgrade.

I am attaching my entire pg_config output.  Can I see yours?  Thanks.



Interestingly, I apparently would have had this issue with beta1:

   %pwd
   /usr/local/pgsql-beta1/bin
   %./pg_config --pkglibdir
   /usr/local/pgsql-beta1/lib/postgresql
   %cd ../../pgsql-beta2/bin/
   %./pg_config --pkglibdir
   /usr/local/pgsql-beta2/lib/postgresql

I've attached pg_config output for both.  To rule out something off with 
my environment, I'm currently setting up a test environment on a 
different machine, where I'll check out HEAD as well as beta1 and beta2. 
 Unless, of course, I'm missing something very obvious in the attached 
output.


Regards,

--
Glen Barber
BINDIR = /usr/local/pgsql-beta1/bin
DOCDIR = /usr/local/pgsql-beta1/share/doc/postgresql
HTMLDIR = /usr/local/pgsql-beta1/share/doc/postgresql
INCLUDEDIR = /usr/local/pgsql-beta1/include
PKGINCLUDEDIR = /usr/local/pgsql-beta1/include/postgresql
INCLUDEDIR-SERVER = /usr/local/pgsql-beta1/include/postgresql/server
LIBDIR = /usr/local/pgsql-beta1/lib
PKGLIBDIR = /usr/local/pgsql-beta1/lib/postgresql
LOCALEDIR = /usr/local/pgsql-beta1/share/locale
MANDIR = /usr/local/pgsql-beta1/share/man
SHAREDIR = /usr/local/pgsql-beta1/share/postgresql
SYSCONFDIR = /usr/local/pgsql-beta1/etc/postgresql
PGXS = /usr/local/pgsql-beta1/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local'
CC = gcc
CPPFLAGS = 
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -Wl,--as-needed -Wl,-R'/usr/local/lib'
LDFLAGS_SL = 
LIBS = -lpgport -lz -lreadline -lcrypt -lm 
VERSION = PostgreSQL 9.0beta1
BINDIR = /usr/local/pgsql-beta2/bin
DOCDIR = /usr/local/pgsql-beta2/share/doc/postgresql
HTMLDIR = /usr/local/pgsql-beta2/share/doc/postgresql
INCLUDEDIR = /usr/local/pgsql-beta2/include
PKGINCLUDEDIR = /usr/local/pgsql-beta2/include/postgresql
INCLUDEDIR-SERVER = /usr/local/pgsql-beta2/include/postgresql/server
LIBDIR = /usr/local/pgsql-beta2/lib
PKGLIBDIR = /usr/local/pgsql-beta2/lib/postgresql
LOCALEDIR = /usr/local/pgsql-beta2/share/locale
MANDIR = /usr/local/pgsql-beta2/share/man
SHAREDIR = /usr/local/pgsql-beta2/share/postgresql
SYSCONFDIR = /usr/local/pgsql-beta2/etc/postgresql
PGXS = /usr/local/pgsql-beta2/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local'
CC = gcc
CPPFLAGS = 
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -Wl,--as-needed -Wl,-R'/usr/local/lib'
LDFLAGS_SL = 
LIBS = -lpgport -lz -lreadline -lcrypt -lm 
VERSION = PostgreSQL 9.0beta2

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Random Weighted Result Ordering

[Eliot Gable]

Great suggestion. Thanks. Don't know why I didn't think of that. I do almost
exactly the same thing further down in my stored procedure.

On Mon, Jun 7, 2010 at 4:34 AM, Dimitri Fontaine wrote:

> Eliot Gable >
> writes:
>
> > I have a set of results that I am selecting from a set of tables which I
> want to return in a random weighted order for each priority group returned.
> Each row has a
> > priority column and a weight column. I sort by the priority column with 1
> being highest priority. Then, for each distinct priority, I want to do a
> weighted random
> > ordering of all rows that have that same priority. I select the set of
> rows and pass it to a custom-built function that does the ordering. I have
> tested both the
> > prioritize and the random weighted ordering functions and they do exactly
> what I want them to do for ordering the data that I send them.
> >
> > The problem comes from the fact that I tried to make them generalized.
> They take an array of a small complex type which holds just an arbitrary ID,
> the priority,
> > and the weight. The output is the same information but the rows are in
> > the correct order.
>
> I'd try having the function return just numbers in the right order, then
> use that in the ORDER BY. To have those numbers, you'd still need to
> join with the result of the function, tho.
>
> Hope this helps you already, I don't have time to go deeper in the
> subject!
>
> Regards,
> --
> dim
>



-- 
Eliot Gable

"We do not inherit the Earth from our ancestors: we borrow it from our
children." ~David Brower

"I decided the words were too conservative for me. We're not borrowing from
our children, we're stealing from them--and it's not even considered to be a
crime." ~David Brower

"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not
live to eat.) ~Marcus Tullius Cicero

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Glen Barber]

On 6/7/10 7:48 PM, Glen Barber wrote:
[snip]




Interestingly, I apparently would have had this issue with beta1:

%pwd
/usr/local/pgsql-beta1/bin
%./pg_config --pkglibdir
/usr/local/pgsql-beta1/lib/postgresql
%cd ../../pgsql-beta2/bin/
%./pg_config --pkglibdir
/usr/local/pgsql-beta2/lib/postgresql

I've attached pg_config output for both. To rule out something off with
my environment, I'm currently setting up a test environment on a
different machine, where I'll check out HEAD as well as beta1 and beta2.
Unless, of course, I'm missing something very obvious in the attached
output.



After just realizing CONFIGURE isn't showing what I would expect to see, 
I rebuilt after removing the original source directories.  It looks like 
I didn't 'gmake clean' before reconfiguring for the new target directories.


Sorry for the noise.  Thank you for taking the time to look.

Regards,

--
Glen Barber

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Misplaced pg_upgrade_support.so ?

[Bruce Momjian]

Glen Barber wrote:
> On 6/7/10 7:48 PM, Glen Barber wrote:
> [snip]
> >>
> >
> > Interestingly, I apparently would have had this issue with beta1:
> >
> > %pwd
> > /usr/local/pgsql-beta1/bin
> > %./pg_config --pkglibdir
> > /usr/local/pgsql-beta1/lib/postgresql
> > %cd ../../pgsql-beta2/bin/
> > %./pg_config --pkglibdir
> > /usr/local/pgsql-beta2/lib/postgresql
> >
> > I've attached pg_config output for both. To rule out something off with
> > my environment, I'm currently setting up a test environment on a
> > different machine, where I'll check out HEAD as well as beta1 and beta2.
> > Unless, of course, I'm missing something very obvious in the attached
> > output.
> >
> 
> After just realizing CONFIGURE isn't showing what I would expect to see, 
> I rebuilt after removing the original source directories.  It looks like 
> I didn't 'gmake clean' before reconfiguring for the new target directories.
> 
> Sorry for the noise.  Thank you for taking the time to look.

No problem.  Glad we confirmed the cause.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + None of us is going to be here forever. +

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[John T. Dow]

OP here

We removed AVG from the computer and rebooted.

Same problem.

We are quite certain that AVG is no longer installed. It doesn't show up where 
it used to, and a search of the registry for "AVG" finds a couple leftovers but 
doesn't seem to indicate that it's still installed.

The computer is running Windows 2000 Server, SP4. Build 5.00.2195.  It's a 
Pentium (R) 4 CPU 2.80 GHz, AT/AT Compatible, 1 GB memory.

That's not a particularly powerful computer, but so far we're only testing the 
application. Someone asked about load, someone also mentioned that mixing file 
serving and database serving is not good in general. Since we're only testing 
right now and in fact don't expect much load when in production, those probably 
aren't issues.

Anyway, it now looks like the problem is not caused by AV software.

Could it be 2000 Server? SP4? I've seen reports of other problems that went 
away depending on the version of Windows.

Thanks.

John


On Mon, 07 Jun 2010 16:35:33 +0200, Thomas Kellerer wrote:

>Magnus Hagander, 07.06.2010 16:15:
>> On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer  wrote:
>>> Magnus Hagander, 07.06.2010 15:52:
>
> Some AV software probably behaves fine.

 Probably.
>>>
>>> In case anyone is interested:
>>>
>>> I have two development computers that run Postgres on Windows XP.
>>> One with Avira the other with Sophos.
>>>
>>> Neither has or had any problems installing or running Postgres
>>
>> What kind of load do the systems have? Particularly, how many
>> parallell connections? That seems to push things over the edge more
>> often than high transaction single-user ones.
>>
>
>Ah, that might make the difference:
>I have no real load on those computers (as I said, developer machine)
>
>So it's more a single-user type of load
>
>Regards
>Thomas
>
>
>
>  
>
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [SOLVED] WINDOWS : PostgreSQL 8.4 Server Start Error

[Zery]

Thank you all,

It works now using Craig "Winsock" solution.

Before that i use Ken "client_min_messages" solution, but it doesn't solve it.

I'll check back if this permanent fixed or just temporary.

BR,
Zery

On 6/7/10, Kenichiro Tanaka  wrote:
> Hi.
>
> At an environment(Ver8.4.2),a simmilar error happened and we could work
> around
> setting postgresql.conf
>
> client_min_messages = log
> to
> client_min_messages = notice .
>
>
> I'm not sure that your error is same,
> but can you try this?
>
> //Your message is following
> //2010-06-07 14:11:40 ICT LOG:  incomplete startup packet
> //So I guess that you set client_min_messages = log or higher.
>
>
> (2010/06/07 16:49), Zery wrote:
>> Craig,
>>
>> Nothing is usefull or barely i understand :-)
>>
>> In pg_log i only find this :
>> 2010-06-07 14:11:40 ICT LOG:  could not receive data from client: An
>> operation was attempted on something that is not a socket.
>>
>>
>> 2010-06-07 14:11:40 ICT LOG:  incomplete startup packet
>>
>> In event viewer i found :
>>
>> : Waiting for server startup..
>> and
>> : Timed out waiting for server startup
>>
>> I have removed PowerDVD and still it doesn't solve the problem, this
>> the second time it happen, the first time i couldn't figure out what's
>> the problem, so I reinstalled Windows, this time i found the problem
>> is crashing with PowerDVD, but i'm avoiding to reinstall Windows.
>>
>> I read some other way is to manage Firewall, i did turn off the
>> firewall, still it dosn't solve either.
>>
>> Any other way??
>>
>>
>>
>>> On 6/7/10, Craig Ringer  wrote:
>>>
 On 07/06/10 08:34, Zery wrote:

> All,
>
>
> I'm a newbie in postgresql, a few days ago i install postgresql
> 8.4.3.1 and it works fine, yesterday i install cyberlink powerdvd 9
> and after that my postgresql server cannot start, it give the
> following error info :
>
> " The postgresql-8.4 - PostgreSQL Server 8.4 service is starting
>The postgresql-8.4 - PostgreSQL Server 8.4 service could not be
> started.
>
>The service did not report an error
>."
>
 Look in the Windows event log for details on why it might be failing to
 start. You can find Event Viewer in Adminstrative Tools in the Control
 Panel.

 Also look at the PostgreSQL log files in
C:\Program Files\postgresql\8.4\data\pg_log
 (I think that path is right, by default anyway) and see if there's
 anything useful there.

 I wouldn't be too surprised to find that PowerDVD has installed
 something it shouldn't into C:\WINDOWS\SYSTEM32.

 --
 Craig Ringer


>>>
>>
>
>
> --
> 
> Kenichiro Tanaka
> K.K.Ashisuto
> http://www.ashisuto.co.jp/english/index.html
> 
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[u235sentinel]

On 06/07/2010 12:13 PM, Vick Khera wrote:


Ditto.  Of late I'm buying HPs, but I haven't yet put one into
database service.  Our DB servers are all currently Sun with fibre
channel cards to external RAID systems.

   


What kind of external RAID systems do you connect your Sun servers to?  
I've talked to Oracle/Sun and haven't been able to get a solution even 
similar to the 4540 systems.  I'm hoping to find something that will 
allow a couple disk controllers to a subsystem.  Even one would be an 
improvement.  That way if I have to I can setup a ZFS pool in whatever 
RAID config I want across multiple controllers and disks.  I'm figuring 
a 2TB database will choke if I only have one controller handling more 
than a dozen or so disks.


Dell's solution doesn't sound right to me.  We've looked at HP.  They 
are more expensive with similar hardware to what Dell is offering.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [SOLVED] WINDOWS : PostgreSQL 8.4 Server Start Error

[Craig Ringer]

On 08/06/10 09:40, Zery wrote:
> Thank you all,
> 
> It works now using Craig "Winsock" solution.
> 
> Before that i use Ken "client_min_messages" solution, but it doesn't solve it.
> 
> I'll check back if this permanent fixed or just temporary.

Yikes. I strongly recommend contacting PowerDVD technical support and
reporting what happened, then. Include the .reg files from your winsock
registry trees after searching them to make sure they don't include any
passwords.

To say that PowerDVD has no business messing with the winsock parts of
the registry is a bit of an understatement :S

( Do be sure, though, that *all* you installed is PowerDVD and Pg. If
some registry "cleaner" or "optimizer" was run as well, then you pretty
much just got the expected result, ie a broken system. )

--
Craig Ringer

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[Scott Marlowe]

On Mon, Jun 7, 2010 at 7:43 PM, u235sentinel  wrote:
> On 06/07/2010 12:13 PM, Vick Khera wrote:
>>
>> Ditto.  Of late I'm buying HPs, but I haven't yet put one into
>> database service.  Our DB servers are all currently Sun with fibre
>> channel cards to external RAID systems.
>>
>>
>
> What kind of external RAID systems do you connect your Sun servers to?  I've
> talked to Oracle/Sun and haven't been able to get a solution even similar to
> the 4540 systems.  I'm hoping to find something that will allow a couple
> disk controllers to a subsystem.  Even one would be an improvement.  That
> way if I have to I can setup a ZFS pool in whatever RAID config I want
> across multiple controllers and disks.  I'm figuring a 2TB database will
> choke if I only have one controller handling more than a dozen or so disks.

Where I work we use these:

http://www.pc-pitstop.com/sata_enclosures/scsas16rm.asp

for when we need lots of throughput (file servers).  They allow four
SAS connectors instead of the typical one or two.

and will be using these:

http://www.aberdeeninc.com/abcatg/kitjbod-1003.htm

for our database servers, where IOPS is far more important than seq speed.

My experience has been that the number of RAID controllers is no where
near as important as the speed of said RAID controllers.  I'd rather
have a very fast RAID controller handling 16 disks at once, than 4
mediocre ones handling 4 disks each.  The optimal is to have two RAID
controllers, so you have redundancy.  Most decent RAID controllers can
run RAID-1 across the two and then RAID-0 over those RAID-1 pairs
(either software or hardware, depending on OS and hardware
performance).
>
> Dell's solution doesn't sound right to me.  We've looked at HP.  They are
> more expensive with similar hardware to what Dell is offering.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Deferrable constraint checking with SAVEPOINT?

[Mike Toews]

Hi,

I have a question that is not specified in the docs[1]. I am using
deferrable constraints in a transaction with SET CONSTRAINTS ALL
DEFERRED. Now I know that DEFERRED constraints are not checked until
transaction COMMIT (i.e., the end), however are they checked with
SAVEPOINT (i.e., part-way in)?

Thanks,

-Mike

[1] http://www.postgresql.org/docs/8.4/static/sql-set-constraints.html

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[Vick Khera]

On Mon, Jun 7, 2010 at 9:43 PM, u235sentinel  wrote:
> What kind of external RAID systems do you connect your Sun servers to?  I've
> talked to Oracle/Sun and haven't been able to get a solution even similar to
> the 4540 systems.  I'm hoping to find something that will allow a couple
> disk

Our primary DB server is a Sun X4200 M2 with 20Gb RAM with a "Dual
LSILogic FC7X04X 4Gb/s FC PCI-Express Adapter" fibre channel card
plugged into it, directly connected to a SurfRAID Triton 16 array from
Partners Data Systems.  The SurfRAID does the RAID using its internal
controlled.  It has 16 SATA drives and 2GB of cache.  I bought the
whole system configured and burn-in tested from Partners Data Systems.
 I highly recommend them.

Our backup DB server is almost identical.  The only difference is that
it is a Sun X4200 and uses the PCI-X version of the LSI card.

If you're looking to do ZFS you can set this disk system to be in JBOD
mode and it will easily handle all the data for you; I don't think
you'll need multiple controllers.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Error on Windows server could not open relation base/xxx/xxx Permission denied

[Scott Marlowe]

On Sun, Jun 6, 2010 at 8:29 PM, John T. Dow  wrote:
> I'd had to tell my client to purchase more hardware because the database 
> software I've recommended has a problem. I have a number of other clients 
> using Postgres and nobody else has had any problem. Switching AV software 
> wouldn't be such an issue.

In fairness to pg, the problem here, if it is the antivirus getting in
the way of file reads, is not pgsql's.  It expects to be able to
access it's files in a mode that the antivirus interferes with.  The
anti-virus is broken if it gets in the way of legitmate apps,
especially if turning off the avg doesn't fix it but removing it does
fix pg's problems.

A database expecting its files to be there, unmolested is not some
insane requirement.  It's pretty basic.  Putting a db on a file server
in Windows is a bad move.  I fed and watered NT Back in the 3.51 and
4.0 days, and it was never a good move to put disparate heavy services
like domain controller, file server, dhcp, database or web server on
the same box back then.  It's still not today.

Is it possible for you to run your file server OR pgsql inside a vm
that the anti-virus can't see or affect?  Or is there a spare server,
underutiliized you can clear off to put pg on?

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[Craig Ringer]

On 8/06/2010 9:11 AM, John T. Dow wrote:

OP here

We removed AVG from the computer and rebooted.

Same problem.


OK, good to know. Thanks very much for testing that, and my apologies 
for recommending something that didn't work out. Of course, it would 
have been hard to progress without eliminating that possible factor.



Could it be 2000 Server? SP4? I've seen reports of other problems that went 
away depending on the version of Windows.


Well, certainly I'd expect that Pg on Windows 2000 server gets about 
zero regular testing. Why would you deploy a server OS that's already 10 
years out of date, went EOL five years ago, and lost even the option of 
paid extended support this year?


http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274

It'd be interesting to investigate this issue ... but win2k server isn't 
exactly easy to come by. Anyone on the list got a win2k server (or 
license) around they can do some experimenting on? All I have here is 
NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop 
suspects.


--
Craig Ringer


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[u235sentinel]

On 06/07/2010 08:01 PM, Scott Marlowe wrote:


Where I work we use these:

http://www.pc-pitstop.com/sata_enclosures/scsas16rm.asp

for when we need lots of throughput (file servers).  They allow four
SAS connectors instead of the typical one or two.

and will be using these:

http://www.aberdeeninc.com/abcatg/kitjbod-1003.htm

for our database servers, where IOPS is far more important than seq speed.

My experience has been that the number of RAID controllers is no where
near as important as the speed of said RAID controllers.  I'd rather
have a very fast RAID controller handling 16 disks at once, than 4
mediocre ones handling 4 disks each.  The optimal is to have two RAID
controllers, so you have redundancy.  Most decent RAID controllers can
run RAID-1 across the two and then RAID-0 over those RAID-1 pairs
(either software or hardware, depending on OS and hardware
performance).
   


I appreciate it.  I'll chat with management about these.  Thanks for the tip

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Dell Poweredge server and Postgres

[u235sentinel]

On 06/07/2010 08:08 PM, Vick Khera wrote:


Our primary DB server is a Sun X4200 M2 with 20Gb RAM with a "Dual
LSILogic FC7X04X 4Gb/s FC PCI-Express Adapter" fibre channel card
plugged into it, directly connected to a SurfRAID Triton 16 array from
Partners Data Systems.  The SurfRAID does the RAID using its internal
controlled.  It has 16 SATA drives and 2GB of cache.  I bought the
whole system configured and burn-in tested from Partners Data Systems.
  I highly recommend them.

Our backup DB server is almost identical.  The only difference is that
it is a Sun X4200 and uses the PCI-X version of the LSI card.

If you're looking to do ZFS you can set this disk system to be in JBOD
mode and it will easily handle all the data for you; I don't think
you'll need multiple controllers.

   


Thanks for the help.  Sounds like we have a few things to talk about 
tomorrow in our management review :-)


thanks again!



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does npgsql have a bunch of bugs with DB enums?

[Mike Christensen]

Ok I did more investigation on this and traced the issue down to a
singe npgsql bug.  Enums actually work fine, as long as you're using
an IDataReader to get at the data.  Once you attempt to load the
reader into a DataSet, it blows up.  I'll log this bug..

Mike

On Mon, Jun 7, 2010 at 4:43 AM, Mike Christensen  wrote:
> This is probably not the right forum for this question, but maybe
> someone can help me out or redirect me.
>
> I'm running into a lot of problems with npgsql and enum types.  There
> seems to be very little support or testing in this area.  The issue
> right now is I have a lot of SQL functions that have OUT parameters of
> an enum type.  Such as:
>
> CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');
>
>
> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
> OUT UnitType UnitTypeEnum)
>  RETURNS SETOF record AS
> ...
>
> When I call that function using the npgsql driver, I get all sorts of
> errors.  The data table simple has no ProviderType (it's blank), even
> though the DataType is System.String.  If I try to read the data
> reader, I get exceptions.  The solution I found is to cast the enum to
> a text, such as:
>
> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
> OUT UnitType text)
> ...
> select UnitType::text from foo;
>
> However, this becomes a pain if I'm not using OUT parameters and just
> returning a set of a row type.  Plus, having to cast ENUMs is kind of
> a hack.
>
> Are the people working on npgsql aware of this problem, and can we
> expect to get real enum support in future versions?  It seems they
> should just magically cast to either a string or an int and I
> shouldn't have to worry about that.  Thanks!!
>
> Mike
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] What's the best type of index for an ENUM column?

[Mike Christensen]

I have a column in a table that's an ENUM type.  What's the best type
of index for this?  It seems if I use a btree index, that's more
optimized for doing greater than or less than comparisons which don't
apply to an enum.  I will only be doing equals comparisons on these
columns.  Thanks!

Mike

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What's the best type of index for an ENUM column?

[Tom Lane]

Mike Christensen  writes:
> I have a column in a table that's an ENUM type.  What's the best type
> of index for this?  It seems if I use a btree index, that's more
> optimized for doing greater than or less than comparisons which don't
> apply to an enum.  I will only be doing equals comparisons on these
> columns.  Thanks!

You aren't going to find that anything else beats out btree, I expect.
If we had bitmap indexes, those might be superior; but we don't.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What's the best type of index for an ENUM column?

[Mike Christensen]

Thanks, that's what I figured.

Just to double check, an enum type is gonna be stored on the disk as
an integer, correct?  And all indexes will be based on the integer
value of the enum?  Postgres only wants you to believe it's a string
when you look at the data.

Mike

On Mon, Jun 7, 2010 at 8:31 PM, Tom Lane  wrote:
> Mike Christensen  writes:
>> I have a column in a table that's an ENUM type.  What's the best type
>> of index for this?  It seems if I use a btree index, that's more
>> optimized for doing greater than or less than comparisons which don't
>> apply to an enum.  I will only be doing equals comparisons on these
>> columns.  Thanks!
>
> You aren't going to find that anything else beats out btree, I expect.
> If we had bitmap indexes, those might be superior; but we don't.
>
>                        regards, tom lane
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] What's the best type of index for an ENUM column?

[Tom Lane]

Mike Christensen  writes:
> Just to double check, an enum type is gonna be stored on the disk as
> an integer, correct?  And all indexes will be based on the integer
> value of the enum?

Well, strictly speaking it's an OID, but yeah.  The label is only
of interest for I/O.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general