Re: [GENERAL] Server to run Postgresql

2012-11-03 Thread Xiong He 
You can build PG based on the source code of the latest release. Or you can install PG9.2.1 via the installer.  All you need do is build it or download it and install.See:installer: http://www.enterprisedb.com/products-services-training/pgdownload#windowssourcecode: http://www.postgresql.org/ftp/source/v9.2.1/As for the stability,  it depends on the real environment.   I believe PG can run well on windows environment. --Thanks&Regards,Xiong He -- Original --From:  "Bob Pawley";Date:  Sun, Nov 4, 2012 07:08 AMTo:  "Postgresql"; Subject:  [GENERAL] Server to run Postgresql



Hi
 
I have an unused computer which I am considering turning into a server to 
run my Postgresql database.
 
Is this even possible to do?
 
If so, can someone suggest an open source server that is relatively easy to 
set up? Windows based would be ideal.
 
Bob
 
 

Re: [GENERAL] Why PGDLLIMPORT is needed

2012-10-31 Thread Xiong He 
You can check the macro definition there:

#ifdef BUILDING_DLL
#define PGDLLIMPORT __declspec (dllexport)
#else/* not BUILDING_DLL */
#define PGDLLIMPORT __declspec (dllimport)
#endif

#ifdef _MSC_VER
#define PGDLLEXPORT __declspec (dllexport)
#else
#define PGDLLEXPORT
#endif

It's only useful on Windows platform for the dll building(import, export) the 
API entries.


--
Thanks&Regards,
Xiong He





 




-- Original --
From:  "";
Date:  Mon, Oct 29, 2012 02:05 PM
To:  "pgsql-general"; 

Subject:  [GENERAL] Why PGDLLIMPORT is needed



On /src/include/storage/proc.h:

I saw the following line:


extern PGDLLIMPORT PGPROC *MyProc;


I want to know why PGDLLIMPORT is used here?


 Does it mean: exten PGPROC *MyProc;  right?

Re: [GENERAL] Why SyncOneBuffer does not called frequently?

2012-10-31 Thread Xiong He 
This perhaps proves that the bgwriter  doesn't need to call the SyncOneBuffer again on the same buffer.--Thanks&Regards,Xiong He-- Original --From:  "Xiong He";Date:  Thu, Nov 1, 2012 07:40 AMTo:  "高健"; "pgsql-general"; Subject:  Re: [GENERAL] Why SyncOneBuffer does not called frequently?HI, I just debug the code, find that the following stack will call the SyncOneBuffer.>    postgres.exe!SyncOneBuffer(int buf_id=2, char skip_recently_used=0)  Line 1640    C     postgres.exe!BufferSync(int flags=64)  Line 1284 + 0xb bytes    C     postgres.exe!CheckPointBuffers(int flags=64)  Line 1801 + 0x9 bytes    C     postgres.exe!CheckPointGuts(XLogRecPtr checkPointRedo={...}, int flags=64)  Line 8129 + 0x9 bytes    C     postgres.exe!CreateCheckPoint(int flags=64)  Line 7977 + 0x11 bytes    C     postgres.exe!CheckpointerMain()  Line 505 + 0x9 bytes    C     postgres.exe!AuxiliaryProcessMain(int argc=2, char * * argv=0x002f6fb8)  Line 429    C     postgres.exe!SubPostmasterMain(int argc=4, char * * argv=0x002f6fb0)  Line 4136 + 0x13 bytes    C     postgres.exe!main(int argc=4, char * * argv=0x002f6fb0)  Line 176 + 0xd bytes    C     postgres.exe!__tmainCRTStartup()  Line 582 + 0x19 bytes    C     postgres.exe!mainCRTStartup()  Line 399    CThis is the check point background process.--Thanks&Regards,Xiong He -- Original --From:  "高健";Date:  Wed, Oct 31, 2012 04:53 PMTo:  "pgsql-general"; Subject:  [GENERAL] Why SyncOneBuffer does not called frequently?Hi all:I am trying to understand when the bgwriter is written.I thought that the  bgwriter.c's calling turn is:BackgroundWriterMain ->BgBufferSync-> SyncOneBuffer
And In my postgresql.conf , the bgwriter_delay=200ms.I did the following:postgres=# select * from testtab; id |  val  +---
  1 | 12345(1 row)postgres=# update testtab set val='54321' where id=1;UPDATE 1postgres=# select * from testtab; id |  val  +---
  1 | 54321(1 row)postgres=# Now I can say the buffer is dirty ,right?I wait for a few minutes, I can found bgwriter's BackgroundWriterMain called BgBufferSync many times.
But I can't find BgBufferSync really call SyncOneBuffer to put the dirty data todisk.Untill I close the postgres process, I can find the SyncOneBuffer is called for many times.
My question is: Why even there are dirty buffer(s), the SyncOneBuffer is still not called?  Is it violating the background writer's purpose? Or the flushing to disk will be done untill  the amount of block/buffer is satisfied? If so , what is it?
Thanks in advance for any help

Re: [GENERAL] Why SyncOneBuffer does not called frequently?

2012-10-31 Thread Xiong He 
HI, 

I just debug the code, find that the following stack will call the 
SyncOneBuffer.

>postgres.exe!SyncOneBuffer(int buf_id=2, char skip_recently_used=0)  Line 
> 1640C
 postgres.exe!BufferSync(int flags=64)  Line 1284 + 0xb bytesC
 postgres.exe!CheckPointBuffers(int flags=64)  Line 1801 + 0x9 bytesC
 postgres.exe!CheckPointGuts(XLogRecPtr checkPointRedo={...}, int flags=64) 
 Line 8129 + 0x9 bytesC
 postgres.exe!CreateCheckPoint(int flags=64)  Line 7977 + 0x11 bytesC
 postgres.exe!CheckpointerMain()  Line 505 + 0x9 bytesC
 postgres.exe!AuxiliaryProcessMain(int argc=2, char * * argv=0x002f6fb8)  
Line 429C
 postgres.exe!SubPostmasterMain(int argc=4, char * * argv=0x002f6fb0)  Line 
4136 + 0x13 bytesC
 postgres.exe!main(int argc=4, char * * argv=0x002f6fb0)  Line 176 + 0xd 
bytesC
 postgres.exe!__tmainCRTStartup()  Line 582 + 0x19 bytesC
 postgres.exe!mainCRTStartup()  Line 399C

This is the check point background process.



--
Thanks&Regards,
Xiong He





 




-- Original --
From:  "";
Date:  Wed, Oct 31, 2012 04:53 PM
To:  "pgsql-general"; 

Subject:  [GENERAL] Why SyncOneBuffer does not called frequently?



Hi all:


I am trying to understand when the bgwriter is written.


I thought that the  bgwriter.c's calling turn is:


BackgroundWriterMain ->BgBufferSync-> SyncOneBuffer
 

And In my postgresql.conf , the bgwriter_delay=200ms.


I did the following:


postgres=# select * from testtab;
 id |  val  
+---
   1 | 12345
(1 row)


postgres=# update testtab set val='54321' where id=1;
UPDATE 1
postgres=# select * from testtab;
 id |  val  
+---
   1 | 54321
(1 row)


postgres=# 


Now I can say the buffer is dirty ,right?


I wait for a few minutes, I can found bgwriter's BackgroundWriterMain called 
BgBufferSync many times.
 

But I can't find BgBufferSync really call SyncOneBuffer to put the dirty data 
todisk.


Untill I close the postgres process, I can find the SyncOneBuffer is called for 
many times.
 

My question is: 
Why even there are dirty buffer(s), the SyncOneBuffer is still not called?  
Is it violating the background writer's purpose? 


Or the flushing to disk will be done untill  the amount of block/buffer is 
satisfied? If so , what is it?
 

Thanks in advance for any help

Re: [GENERAL] Too much clients connected to the PostgreSQL Database

2012-10-29 Thread Xiong He 
If you are using connection pool, did you use pgbouncer to reduce the maximum 
physical connection?
You said, "but we have some low usage application getting direct db 
connection(out of the pool)."   It will mean that some connections are not 
controlled by the connection pool.  This can be variable in the real 
environment.

--
Thanks&Regards,
Xiong He





 




-- Original --
From:  "rodrigo";
Date:  Tue, Oct 30, 2012 09:06 AM
To:  "Kevin Grittner"; 
Cc:  "pgsql-general"; 
Subject:  Re: [GENERAL] Too much clients connected to the PostgreSQL Database



150 maxActive setting, but we have some low usage application getting direct db
connection(out of the pool).

The interesting is that the server suddently get a lot of connections and reach 
the
limit and then release them.

I'm willing to change to Tomcat 7 connection pool. We are using c3pO and apache 
ones,
maybe that's a problem.

Thank you,

Well,

> Rodrigo Pereira da Silva wrote:
>
>> We have about 10 web applications deployed in a Tomcat 7.0
>> accessing a Postgresql 9.1 database. We do use connection pooling.
>> We have the max_connections parameter set to 200 connections,
>> however, we are reaching the max connections sporadically(about 4
>> time/day).
>
> What is the pooler's maxActive setting? (It should be less than the
> PostgreSQL max_connections setting.)
>
> BTW, 200 seems alwfully high unless a *really* high end machine.   You
> may have fewer timeouts if you avoid swamping the server with a
> "thundering herd" of requests.
>
> http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
>
> -Kevin
>
>
> --
> 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] Why BgWriterDelay is fixed?

2012-10-29 Thread Xiong He 
You can check the code in guc.c,  search "bgwriter_delay",  &BgWriterDelayIn the global user configuration, it can change the value of BgWriterDelay.Since the BgWriterDelay declared in bgwriter.h as extern.  It can be changed in the global namespace.--Thanks&Regards,Xiong He -- Original --From:  "高健";Date:  Mon, Oct 29, 2012 03:17 PMTo:  "pgsql-general"; Subject:  [GENERAL] Why BgWriterDelay is fixed?In src/backend/postmaster/bgwriter.c , I can find the following source code(PostgreSQL9.2):/* * GUC parameters */int			BgWriterDelay = 200;
...		rc = WaitLatch(&MyProc->procLatch,	   WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH,
	   BgWriterDelay /* ms */ );...		if (rc == WL_TIMEOUT && can_hibernate && prev_hibernate)
		{			/* Ask for notification at next buffer allocation */			StrategyNotifyBgWriter(&MyProc->procLatch);
			/* Sleep ... */			rc = WaitLatch(&MyProc->procLatch,		   WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH,
		   BgWriterDelay * HIBERNATE_FACTOR);			/* Reset the notification request in case we timed out */
			StrategyNotifyBgWriter(NULL);		}But I also found  the following in postgresql.conf:
#bgwriter_delay = 200ms                 # 10-1ms between roundsIt is now comment .But according to the fixed code of  BgWriterDelay = 200, even when I update bgwriter_delay in postgresql.conf to a different value(eg 300ms), 
how can it ovewrite the fixed  200ms in bgwriter.c ?I also want to know,  if it is not a bug, then what is the reason?

Re: [GENERAL] PostgresQL intallation error

2012-10-27 Thread Xiong He 
Can you copy C:\Users\Ral\AppData\Local\Temp\prerun_checks.vbs to another 
location and verify if it can run without any issue?

Make sure Windows Script Host is installed. and ".vbs" is associated with it.

--
Thanks&Regards,
Xiong He





 




-- Original --
From:  "Raul Feliu";
Date:  Sat, Oct 27, 2012 06:30 PM
To:  "pgsql-general"; 

Subject:  Re: [GENERAL] PostgresQL intallation error



  I have windows vista. I tried to run the installer in admin mode and I 
disabled UAC. Still having the same problem.

Any other help will be wellcome :)

Thanks again, and thanks Xiong He.



From: iih...@qq.com
To: raulfp...@hotmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgresQL intallation error
Date: Sat, 27 Oct 2012 11:04:31 +0800

Seems you are using Win7 or above environment.
You perhaps need to run the installer in administrator mode. An alternative way 
is to disable UAC.

------
Thanks&Regards,
Xiong He





-- Original --
From:  "Raul Feliu";
Date:  Sat, Oct 27, 2012 04:04 AM
To:  "pgsql-general"; 

Subject:  [GENERAL] PostgresQL intallation error



  
Hi,

I am facing a problem when I try to install PostgresQL (version 9.2.1.). I get 
the following message:

"Unable to write inside TEMP environment variable path"

I need some help with this as I've read lots of posts about how to solve the 
problem and I have not succeed.

Here I forward to you the bitrock_installer text.

---
Log started 10/26/2012 at 19:06:53
Preferred installation mode : qt
Trying to init installer in mode qt
Mode qt successfully initialized
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Data 
Directory. Setting variable iDataDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Base 
Directory. Setting variable iBaseDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service ID. 
Setting variable iServiceName to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service 
Account. Setting variable iServiceAccount to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Super User. 
Setting variable iSuperuser to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Branding. 
Setting variable iBranding to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Version. 
Setting variable brandingVer to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Shortcuts. 
Setting variable iShortcut to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 
DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
[19:07:01] Existing base directory: 
[19:07:01] Existing data directory: 
[19:07:01] Using branding: PostgreSQL 9.2
[19:07:01] Using Super User: postgres and Service Account: NT 
AUTHORITY\NetworkService
[19:07:01] Using Service Name: postgresql-9.2
Executing cscript //NoLogo "C:\Users\Raül\AppData\Local\Temp\prerun_checks.vbs"
Script exit code: 1

Script output:
 Error de CScript: No se encuentra el motor de secuencias de comandos 
"VBScript" para la secuencia 
"C:\Users\Ral\AppData\Local\Temp\prerun_checks.vbs".

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo 
"C:\Users\Raül\AppData\Local\Temp\prerun_checks.vbs" : Program ended with an 
error exit code


I'm not a computer expert, so I'll need detailed steps to solve the problem.

Thanks in advance

Re: [GENERAL] PostgresQL intallation error

2012-10-26 Thread Xiong He 
Seems you are using Win7 or above environment.
You perhaps need to run the installer in administrator mode. An alternative way 
is to disable UAC.

--
Thanks&Regards,
Xiong He





-- Original --
From:  "Raul Feliu";
Date:  Sat, Oct 27, 2012 04:04 AM
To:  "pgsql-general"; 

Subject:  [GENERAL] PostgresQL intallation error



  
Hi,

I am facing a problem when I try to install PostgresQL (version 9.2.1.). I get 
the following message:

"Unable to write inside TEMP environment variable path"

I need some help with this as I've read lots of posts about how to solve the 
problem and I have not succeed.

Here I forward to you the bitrock_installer text.

---
Log started 10/26/2012 at 19:06:53
Preferred installation mode : qt
Trying to init installer in mode qt
Mode qt successfully initialized
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Data 
Directory. Setting variable iDataDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Base 
Directory. Setting variable iBaseDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service ID. 
Setting variable iServiceName to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Service 
Account. Setting variable iServiceAccount to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Super User. 
Setting variable iSuperuser to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Branding. 
Setting variable iBranding to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Version. 
Setting variable brandingVer to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 Shortcuts. 
Setting variable iShortcut to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-9.2 
DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
[19:07:01] Existing base directory: 
[19:07:01] Existing data directory: 
[19:07:01] Using branding: PostgreSQL 9.2
[19:07:01] Using Super User: postgres and Service Account: NT 
AUTHORITY\NetworkService
[19:07:01] Using Service Name: postgresql-9.2
Executing cscript //NoLogo "C:\Users\Raül\AppData\Local\Temp\prerun_checks.vbs"
Script exit code: 1

Script output:
 Error de CScript: No se encuentra el motor de secuencias de comandos 
"VBScript" para la secuencia 
"C:\Users\Ral\AppData\Local\Temp\prerun_checks.vbs".

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo 
"C:\Users\Raül\AppData\Local\Temp\prerun_checks.vbs" : Program ended with an 
error exit code


I'm not a computer expert, so I'll need detailed steps to solve the problem.

Thanks in advance

Re: [GENERAL] migrate from PostgreSQL to Oracle

2012-10-25 Thread Xiong He 
You can try this one:

http://code.google.com/p/hisql/
then JMyETL1.0.6

--
Thanks&Regards,
Xiong He 





 




-- Original --
From:  "Martin Gainty";
Date:  Thu, Oct 25, 2012 10:18 PM
To:  "jose.soares"; 
"pgsql-general@postgresql.org"; 

Subject:  Re: [GENERAL] migrate from PostgreSQL to Oracle



  squirrel

http://www.squirrelsql.org/index.php?page=plugins

Saludos desde EEUU
Martin 
__ 
Porfavor ..no altere o interrumptir esta communicacion


> Date: Thu, 25 Oct 2012 15:50:08 +0200
> From: jose.soa...@sferacarta.com
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] migrate from PostgreSQL to Oracle
> 
> Hi all,
> 
> I'm working with same db schema in PostgreSQL and Oracle,
> We mainly work in PostgreSQL but sometimes we need to copy schema and 
> data from pg to oracle
> because some our customers want to use oracle instead of pg.
> 
> Thus I'm looking for some linux script to migrate from pg to oracle.
> At the moment, I dump data from pg using pg_dump in the format:
> INSERT INTO table (columns) (values)
> then I load it into the Oracle db using cx_Oracle
> this procedure is so slow, and sometimes I have to edit and modify
> data manually, because some INSERT format aren't compatible.
> Is there any interesting linux script to do this more easily? something 
> like the ora2pg script.
> 
> thanks
> 
> j
> 
> 
> 
> -- 
> 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] oracle_fdw with oracle os authentication

2012-10-24 Thread Xiong He 
Not sure. But you can always avoid the OS authentication with empty user and 
password to access Oracle.
You can use other authentication mode to access oracle instead. 



--
Thanks&Regards,
Xiong He





 




-- Original --
From:  "Papiernik Anna-AAP053";
Date:  Tue, Oct 23, 2012 06:07 PM
To:  "pgsql-general@postgresql.org"; 

Subject:  [GENERAL] oracle_fdw with oracle os authentication



  
I have Oracle with OS authentication.
 
And I have installed PostgreSQL and I want to migrate data from Oracle to 
PostgreSQL database.
 
 
 
I have performed steps:
 
CREATE EXTENSION oracle_fdw;
 
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '');
 
GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
 
CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user '', password '');
 
 
 
Then I have created foreign table and I want to select from it.
 
Unfortunatelly, oracle_fdw is unable to connect using OS authentication and it 
doesn??t work.
 
 
 
Is it possible to use oracle_fdw to have OS authentication?
 
 
 
Regards,
 Anna

Re: [GENERAL] Is this a Postgres Bug?

2012-08-05 Thread Xiong He 
Seems this is not a bug.
Although 32767  doesn't exceed the 2bit int range.
But (32767 + 10) exceed the signed 2bit int range.
If you want to add index for the sum of the 2 columns, why don't you add a new 
column with a larger range (int32) ?

-- Original --
From:  "Mike Christensen";
Date:  Sun, Aug 5, 2012 01:14 PM
To:  "pgsql-general"; 

Subject:  [GENERAL] Is this a Postgres Bug?



First off, I've posted this question on StackOverflow in case anyone
wants to answer it:

http://stackoverflow.com/questions/11814132/postgresql-smallint-overflowing-when-creating-index-on-multiple-columns-is-th

The repro can be found here: http://sqlfiddle.com/#!1/734d7/1

I'm happy to log this as a bug, unless someone can explain to me why
this behavior is by design.  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