Re: [GENERAL] Does enterprisedb.com down?

[Magnus Hagander]

On Tue, Jun 15, 2010 at 07:21, M. Bashir Al-Noimi  wrote:
> On 15/06/2010 06:00 ص, John Gage wrote:
>
> I ran the IP on http://whatismyipaddress.com/blacklist-check and it is not
> blacklisted.
>
> Actually I suspect that pg takes same policy of sf.net where sf.net forbids
> open source projects to specific countries as mentioned in the following
> links (for that I migrated  my projects from sf.net to Launchpad because
> sf.net works against FOSS):
> http://arabcrunch.com/2010/01/following-clintons-internet-freedom-speech-us-based-sourceforge-blocked-syria-sudan-iran-korea-cuba-is-open-source-still-really-open.html
> http://sourceforge.net/blog/clarifying-sourceforgenets-denial-of-site-access-for-certain-persons-in-accordance-with-us-law/
> http://sourceforge.net/blog/some-good-news-sourceforge-removes-blanket-blocking/
>
> So I wish to get a clarification about this issue, does pg forbids my
> country? is it still open source?

PostgreSQL does *not* forbid your country. As you showed further down
this thread, you can reach the main website and mirror systems, so
that's not where the problem is. But you wanted a statement on it.

This is, however, the second report in just a couple of days of people
not being able to reach the windows downloads that are hosted by
EnterpriseDB (I assume you are looking for the Windows downloads - if
you're actually looking for EnterpriseDB's commercial product, you
need to talk to them and not us). This indicates a real problem.

Can someone from EnterpriseDB (hi, Dave!) verify with their provider
that they do *not* intentionally or unintentionally prevent people
from downloading the software based on their location?

Unfortunately, there are no backup download locations available for
the windows installers, but you can always download the source off the
postgresql.org mirrors and build your own binaries.


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

Re: [GENERAL] Does enterprisedb.com down?

[M. Bashir Al-Noimi]

All the mirrors work except the enterprisedb.com itself.

On Tue, Jun 15, 2010 at 7:50 AM, Jayadevan M
wrote:

> May be you will be able to get one that is not blocked from the ftp sites
> list? I don't know if the rules applicable to main server are
> automatically applied to the mirror sites too.
> http://wwwmaster.postgresql.org/download/mirrors-ftp
> Regards,
> Jayadevan
>
>
>
>
>
> DISCLAIMER:
>
> "The information in this e-mail and any attachment is intended only for
> the person to whom it is addressed and may contain confidential and/or
> privileged material. If you have received this e-mail in error, kindly
> contact the sender and destroy all copies of the original communication.
> IBS makes no warranty, express or implied, nor guarantees the accuracy,
> adequacy or completeness of the information contained in this email or any
> attachment and is not liable for any errors, defects, omissions, viruses
> or for resultant loss or damage, if any, direct or indirect."
>
>
>
>
>
>


-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

Re: [GENERAL] thoughts about constraint trigger

[Craig Ringer]

On 15/06/10 02:33, Adrian von Bidder wrote:
> Heyho!
> 
> I was trying to implement a deferred NOT NULL constraint using a deferred 
> constraint trigger (on update and insert of this row) because some values 
> would be filled in later during the transaction, after the initial part of 
> the record has been filled.

AFAIK, at this point only FOREIGN KEY constraints may be deferred.

http://www.postgresql.org/docs/current/static/sql-set-constraints.html
http://www.postgresql.org/docs/current/static/sql-createtable.html

"DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint
that is not deferrable will be checked immediately after every command.
Checking of constraints that are deferrable can be postponed until the
end of the transaction (using the SET CONSTRAINTS command). NOT
DEFERRABLE is the default. Only foreign key constraints currently accept
this clause. All other constraint types are not deferrable."

-- 
Craig Ringer

Tech-related writing: http://soapyfrogs.blogspot.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] Enforcing password standards

[Joshua Tolley]

On Fri, Jun 11, 2010 at 10:40:29AM -0700, DM wrote:
>Thanks everyone,
>I will wait for Postgres 9.0 to implement this feature then. Thanks

The contrib module supports enforcement of only some of the things you've
listed you want. For other items on your list (notably renewal), you're better
off integrating with some external authentication provider, as has been
suggested elsewhere in this thread.

--
Josh

>Thanks
>Deepak
>On Fri, Jun 11, 2010 at 10:30 AM, Joshua Tolley 
>wrote:
> 
>  On Thu, Jun 10, 2010 at 06:01:24PM -0700, DM wrote:
>  >How to force postgres users to follow password standards and
>  renewal
>  >policies?
>  >Thanks
>  >Deepak
> 
>  9.0 will ship with a contrib module called "passwordcheck" which will
>  enforce
>  some of these things, FWIW.
>  --
>  Joshua Tolley / eggyknap
>  End Point Corporation
>  http://www.endpoint.com
>  -BEGIN PGP SIGNATURE-
>  Version: GnuPG v1.4.9 (GNU/Linux)
> 
>  iEYEARECAAYFAkwScpkACgkQRiRfCGf1UMMOzgCfW1P8SpFR53OSjm/og3hQFjba
>  0dIAoJK9mkm07XCAyfnPeiygBgrKuFG2
>  =XESJ
>  -END PGP SIGNATURE-


signature.asc
Description: Digital signature

Re: [GENERAL] Does enterprisedb.com down?

[Scott Marlowe]

On Mon, Jun 14, 2010 at 11:21 PM, M. Bashir Al-Noimi  wrote:
> So I wish to get a clarification about this issue, does pg forbids my
> country? is it still open source?

Can you get to postgresql.org?  Cause that is pg.  enterprisedb is NOT
pg, it is a commercial company that provides a lot of services for the
pg community.  Not the same thing.

-- 
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 enterprisedb.com down?

[Jayadevan M]

May be you will be able to get one that is not blocked from the ftp sites 
list? I don't know if the rules applicable to main server are 
automatically applied to the mirror sites too.
http://wwwmaster.postgresql.org/download/mirrors-ftp
Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."






-- 
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 enterprisedb.com down?

[M. Bashir Al-Noimi]

On 15/06/2010 06:00 ?, John Gage wrote:
I ran the IP on http://whatismyipaddress.com/blacklist-check and it is 
not blacklisted.
Actually I _*suspect*_ that pg takes same policy of sf.net where sf.net 
forbids open source projects to specific countries as mentioned in the 
following links (for that I migrated  my projects from sf.net to 
Launchpad  because sf.net works against FOSS):

http://arabcrunch.com/2010/01/following-clintons-internet-freedom-speech-us-based-sourceforge-blocked-syria-sudan-iran-korea-cuba-is-open-source-still-really-open.html
http://sourceforge.net/blog/clarifying-sourceforgenets-denial-of-site-access-for-certain-persons-in-accordance-with-us-law/
http://sourceforge.net/blog/some-good-news-sourceforge-removes-blanket-blocking/

So I wish to get a clarification about this issue, does pg forbids my 
country? is it still open source?


--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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 enterprisedb.com down?

[John R Pierce]

John Gage wrote:
I ran the IP on http://whatismyipaddress.com/blacklist-check and it is 
not blacklisted.



On Jun 15, 2010, at 6:53 AM, M. Bashir Al-Noimi wrote:

Now I wondering does postgresql forbids my country or not?, is it 
open source or something else?





maybe an ISP between point A and point B is blocking Syria, they are 
still on the US Government's technical embargo list, aren't they?




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

[GENERAL] CFP for Surge Scalability Conference 2010

[Jason Dixon]

We're excited to announce Surge, the Scalability and Performance
Conference, to be held in Baltimore on Sept 30 and Oct 1, 2010.  The
event focuses on case studies that demonstrate successes (and failures)
in Web applications and Internet architectures.

Robert Treat will be presenting one of his PostgreSQL talks at Surge,
and our Keynote speakers include John Allspaw and Theo Schlossnagle.  We
are currently accepting submissions for the Call For Papers through July
9th.  You can find more information, including our current list of
speakers, online:

http://omniti.com/surge/2010

If you've been to Velocity, or wanted to but couldn't afford it, then
Surge is just what you've been waiting for.  For more information,
including CFP, sponsorship of the event, or participating as an
exhibitor, please contact us at su...@omniti.com.

Thanks,

-- 
Jason Dixon
OmniTI Computer Consulting, Inc.
jdi...@omniti.com
443.325.1357 x.241

-- 
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 enterprisedb.com down?

[John Gage]

I ran the IP on http://whatismyipaddress.com/blacklist-check and it is  
not blacklisted.



On Jun 15, 2010, at 6:53 AM, M. Bashir Al-Noimi wrote:

Now I wondering does postgresql forbids my country or not?, is it  
open source or something else?



--
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 enterprisedb.com down?

[M. Bashir Al-Noimi]

What's going on!
http://downforeveryoneorjustme.com/
gave me the following stupid message:

Forbidden
Your client does not have permission to get URL / from
this server. (Client IP address: 213.178.224.178)

You are accessing this page from a forbidden country.
and enterprisedb.com
still unavailable! 

Now I wondering does postgresql forbids my country or not?, is it open
source or something else?



On 15/06/2010 04:47 ص, Adam Alkins wrote:
It works fine for me. I would suggest using http://downforeveryoneorjustme.com/ or
something similar to check in the future.
  
  
-- 
Adam Alkins || http://www.rasadam.com
  
  
  
  On 14 June 2010 22:45, M. Bashir Al-Noimi 
wrote:
  

Hi folks,


I tried to visit
enterprisedb.com yesterday and today but I
couldn't the browser gave me
this error message:




  

  Network Error (tcp_error)
  
  
   


  A communication error occurred:
"Operation timed out" 


  The Web Server may be down, too
busy, or experiencing other problems
preventing it from responding to requests. You may wish to try again at
a later time. 

  


I tried to visit it
from another ISP but it gave me same error!


So I'm wondering
does
enterprisedb.com down?





  
  
  


-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net



<>
-- 
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] Tracking SQLs that update data

[Tom Lane]

Scott Marlowe  writes:
> On Mon, Jun 14, 2010 at 5:55 PM, Phoenix Kiula  
> wrote:
>> My question: how can I set up a "RULE" so that when a specific column
>> is updated, a separate table also logs which update SQL was issued?

> It's far easier to adjust the logging.

Or, if you're worried about actions from functions, use a trigger to do
the logging.  There are approximately no cases where a rule is really
better than a trigger :-(

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] Does enterprisedb.com down?

[Adam Alkins]

It works fine for me. I would suggest using
http://downforeveryoneorjustme.com/ or something similar to check in the
future.


-- 
Adam Alkins || http://www.rasadam.com


On 14 June 2010 22:45, M. Bashir Al-Noimi  wrote:

>  Hi folks,
>
>
>  I tried to visit enterprisedb.com yesterday and today but I couldn't the
> browser gave me this error message:
>
>
>  --
>   Network Error (tcp_error)
>
>A communication error occurred: "Operation timed out"   The Web Server
> may be down, too busy, or experiencing other problems preventing it from
> responding to requests. You may wish to try again at a later time.
> --
>
> I tried to visit it from another ISP but it gave me same error!
>
>
>  So I'm wondering does enterprisedb.com down?
>
>
>
>  --
> Best Regards
> Muhammad Bashir Al-Noimi
> My Blog: http://mbnoimi.net
>
>
>
> --
> 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 enterprisedb.com down?

[M. Bashir Al-Noimi]

Hi folks,


I tried to visit enterprisedb.com yesterday and today but I couldn't the 
browser gave me this error message:




Network Error (tcp_error)

A communication error occurred: "Operation timed out"
The Web Server may be down, too busy, or experiencing other problems 
preventing it from responding to requests. You may wish to try again at 
a later time.




I tried to visit it from another ISP but it gave me same error!


So I'm wondering does enterprisedb.com down?



--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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] Tracking SQLs that update data

[Andy Colson]

On 06/14/2010 06:55 PM, Phoenix Kiula wrote:

Hi

I'm having some issues with a code base where several different
programs are updating one "status" column in the DB with their code.
Mostly this is working, but in some cases the status column in a DB is
getting updated when it shouldn't have been, and we're trying to
locate which program did it.

While checking through all the code, I'm also wondering if it is
possible to somehow maintain a log of which SQL did the updating?

My question: how can I set up a "RULE" so that when a specific column
is updated, a separate table also logs which update SQL was issued?

Thanks for any ideas.



If you are on 8.5, this will be helpful:

http://www.depesz.com/index.php/2009/12/29/waiting-for-8-5-application-name-reporting/


--
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] Unable to install pg in Win (MVR error)

[M. Bashir Al-Noimi]

On 15/06/2010 02:26 ?, Dave Page wrote:



On Mon, Jun 14, 2010 at 11:33 PM, M. Bashir Al-Noimi 
mailto:ad...@mbnoimi.net>> wrote:


Wow, I catch installer bug.

This problem related to executing .vbs files because of that whole
installing process failed.

In the attachment will find that Bitrock installer unable to run
.vbs script because in my PC I specified .vbs file association
with notepad. For that I could install pg successfully on another PC.

I think this problem should not be exists in the installer because
many script editors associate .vbs files by default, is it right?
If yes where I can report a bug for this issue?


I'm not sure it's the file association that is causing the problem, 
because we explicitly pass the filename to the script interpreter 
rather than relying on ShellExecute. If that were the issue, you would 
see the script pop up in notepad.


However, the log does perhaps give us a clue. Ashesh - would you mind 
investigating further please?
As soon as I restored file association of .vbs to the default the 
problem disappeared so from my side I think this is clear clue.


What you think Ashesh?


--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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] Tracking SQLs that update data

[Scott Marlowe]

On Mon, Jun 14, 2010 at 5:55 PM, Phoenix Kiula  wrote:
> Hi
>
> I'm having some issues with a code base where several different
> programs are updating one "status" column in the DB with their code.
> Mostly this is working, but in some cases the status column in a DB is
> getting updated when it shouldn't have been, and we're trying to
> locate which program did it.
>
> While checking through all the code, I'm also wondering if it is
> possible to somehow maintain a log of which SQL did the updating?
>
> My question: how can I set up a "RULE" so that when a specific column
> is updated, a separate table also logs which update SQL was issued?

It's far easier to adjust the logging.

psql mydb
alter mydb set log_statement='mod';

-- 
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] DDL partitioning with insert trigger issue

[Scott Marlowe]

On Mon, Jun 14, 2010 at 4:46 PM, mark  wrote:
> Hello,
>
> I am running PG 8.3. and following the guide found at
> http://www.postgresql.org/docs/current/static/ddl-partitioning.html
>
>
> I have followed the steps outlined here nearly exactly with regards to using
> an insert trigger to call a function to insert data into the child
> partition. I am wondering why I am getting the record inserted in both the
> child and the parent partition when executing an insert into the parent.
>
> Is there a step missing from the DOC? Something else I need to do?

Got a short, self-contained example of how you're doing it?  My guess
is you made an after instead of before trigger.

-- 
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] Unable to install pg in Win (MVR error)

[Dave Page]

On Mon, Jun 14, 2010 at 11:33 PM, M. Bashir Al-Noimi wrote:

>  Wow, I catch installer bug.
>
> This problem related to executing .vbs files because of that whole
> installing process failed.
>
> In the attachment will find that Bitrock installer unable to run .vbs
> script because in my PC I specified .vbs file association with notepad. For
> that I could install pg successfully on another PC.
>
> I think this problem should not be exists in the installer because many
> script editors associate .vbs files by default, is it right? If yes where I
> can report a bug for this issue?
>

I'm not sure it's the file association that is causing the problem, because
we explicitly pass the filename to the script interpreter rather than
relying on ShellExecute. If that were the issue, you would see the script
pop up in notepad.

However, the log does perhaps give us a clue. Ashesh - would you mind
investigating further please?

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company
Log started 06/15/10 at 01:01:06
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-8.4 Data 
Directory. Setting variable iDataDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Base 
Directory. Setting variable iBaseDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Service ID. 
Setting variable iServiceName to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Service 
Account. Setting variable iServiceAccount to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Super User. 
Setting variable iSuperuser to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Branding. 
Setting variable iBranding to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Version. 
Setting variable brandingVer to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Shortcuts. 
Setting variable iShortcut to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 
DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
[01:01:55] Existing base directory: 
[01:01:55] Existing data directory: 
[01:01:55] Using branding: PostgreSQL 8.4
[01:01:55] Using Super User: postgres and Service Account: postgres
[01:01:56] Using Service Name: postgresql-8.4
Executing cscript //NoLogo "C:\Documents and Settings\Bashir\Local 
Settings\Temp\postgresql_installer\installruntimes.vbs" "C:\Documents and 
Settings\Bashir\Local Settings\Temp\postgresql_installer\vcredist_x86.exe"
Script exit code: 1

Script output:
 Input Error: There is no script engine for file extension ".vbs".

Script stderr:
 Program ended with an error exit code

An error occured executing the Microsoft VC++ runtime installer.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Tracking SQLs that update data

[Phoenix Kiula]

Hi

I'm having some issues with a code base where several different
programs are updating one "status" column in the DB with their code.
Mostly this is working, but in some cases the status column in a DB is
getting updated when it shouldn't have been, and we're trying to
locate which program did it.

While checking through all the code, I'm also wondering if it is
possible to somehow maintain a log of which SQL did the updating?

My question: how can I set up a "RULE" so that when a specific column
is updated, a separate table also logs which update SQL was issued?

Thanks for any ideas.

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

[GENERAL] DDL partitioning with insert trigger issue

[mark]

Hello, 

 

I am running PG 8.3. and following the guide found at
http://www.postgresql.org/docs/current/static/ddl-partitioning.html 

 

 

I have followed the steps outlined here nearly exactly with regards to using
an insert trigger to call a function to insert data into the child
partition. I am wondering why I am getting the record inserted in both the
child and the parent partition when executing an insert into the parent. 

 

 

Is there a step missing from the DOC? Something else I need to do?

 

Thank you

 

 

..: Mark

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

[John T. Dow]

Today we were able to look at the first computer's files.

About 20 - 30 of them were marked system, hidden, read only.

We cleared the attribute bits with the ATTRIB command and the database now 
appears to be normal. We were able to do a backup (that failed before) and were 
able to paste in 50K blocks of text with no problems.

We verified that the data directory is owned by the proper postgres user 
("limited account") and that only that user has access to the directory or its 
files. (This is unlike the second computer, where we had permitted other users 
to have access.)

So it appears to be a pristine postgres installation, except that somehow some 
of the files were changed.

We have no clue how that happened. At this time the AV software is not 
installed. We will watch carefully to see if files get altered again in this 
way. Although the second computer has a newer OS and is more powerful, the 
client prefers to leave the database on the original computer because it has 
better physical security.

Now we just wait to see what happens next, if anything. Thanks for your help.

John


On Mon, 14 Jun 2010 10:59:18 +0200, Magnus Hagander wrote:

>On Mon, Jun 14, 2010 at 10:57, Scott Marlowe  wrote:
>> On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander  wrote:
>>> On Mon, Jun 14, 2010 at 05:17, John T. Dow  wrote:
 Apparently the problem boils down to this question: how did some of the 
 files get set to be system and read only?
>>>
>>> Yes. That would be very interesting to know. PostgreSQL never
>>> (intentionally) sets these flags, so they must've come from something
>>> else.
>>
>> Being a non-privaledged account, does the postgres user even have the
>> power to do that?
>
>Yes, IIRC any user that has write permissions on a file can set the
>attributes, including readonly and system.
>
>
>-- 
> 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





-- 
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] Unable to install pg in Win (MVR error)

[M. Bashir Al-Noimi]

Wow, I catch installer bug.

This problem related to executing .vbs files because of that whole
installing process failed.

In the attachment will find that Bitrock installer unable to run .vbs
script because in my PC I specified .vbs file association with notepad.
For that I could install pg successfully on another PC.

I think this problem should not be exists in the installer because many
script editors associate .vbs files by default, is it right? If yes
where I can report a bug for this issue?


On 14/06/2010 09:51 م, Sachin Srivastava wrote:

  
When you run the installer without specifying "--install_runtimes 0",
What is the error code returned by the MS VC++ Runtime installer. You
can check that in the log file
(%TEMP%\bitrock_installer_.log). 
  
On 6/15/10 2:17 AM, M. Bashir Al-Noimi wrote:
  

On 14/06/2010 08:43 م, Dave Page wrote:

  
  On Mon, Jun 14, 2010 at 7:22 PM, M.
Bashir
Al-Noimi  wrote:
  

Hi All,


As shown in the
following screenshot I couldn't install pg in Windows because it
couldn't install Microsoft VC++ Runtime!


How I can fix
this
issue?

  
  
  
  If you already have the right version (VC++ 2005 SP1) of the
runtimes installed, you could run the installer with the option:
  
  
  --install_runtimes 0
  
  
  to skip the runtime installation. 
  

I tried to run pg installer with "--install_runtimes 0" but I got
warning message tell me that database cluster failed does this problem
related to wrong MVR?

if yes how I can fix this issue? do I need to remove installed MVR (I
didn't find it in Add or Remove Programs list)?



PS.
After finishing the installer I noticed that pg services didn't start
at all although I successfully installed pg on another PC!
  


-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net



Log started 06/15/10 at 01:01:06
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-8.4 Data 
Directory. Setting variable iDataDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Base 
Directory. Setting variable iBaseDirectory to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Service ID. 
Setting variable iServiceName to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Service 
Account. Setting variable iServiceAccount to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Super User. 
Setting variable iSuperuser to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Branding. 
Setting variable iBranding to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Version. 
Setting variable brandingVer to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 Shortcuts. 
Setting variable iShortcut to empty value
Could not find registry key 
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4 
DisableStackBuilder. Setting variable iDisableStackBuilder to empty value
[01:01:55] Existing base directory: 
[01:01:55] Existing data directory: 
[01:01:55] Using branding: PostgreSQL 8.4
[01:01:55] Using Super User: postgres and Service Account: postgres
[01:01:56] Using Service Name: postgresql-8.4
Executing cscript //NoLogo "C:\Documents and Settings\Bashir\Local 
Settings\Temp\postgresql_installer\installruntimes.vbs" "C:\Documents and 
Settings\Bashir\Local Settings\Temp\postgresql_installer\vcredist_x86.exe"
Script exit code: 1

Script output:
 Input Error: There is no script engine for file extension ".vbs".

Script stderr:
 Program ended with an error exit code

An error occured executing the Microsoft VC++ runtime installer.
<>
-- 
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] Unable to install pg in Win (MVR error)

[M. Bashir Al-Noimi]

On 14/06/2010 11:16 م, Dave Page wrote:



On Mon, Jun 14, 2010 at 8:47 PM, M. Bashir Al-Noimi > wrote:


On 14/06/2010 08:43 م, Dave Page wrote:



On Mon, Jun 14, 2010 at 7:22 PM, M. Bashir Al-Noimi
mailto:ad...@mbnoimi.net>> wrote:

Hi All,


As shown in the following screenshot I couldn't install pg in
Windows because it couldn't install Microsoft VC++ Runtime!


How I can fix this issue?


If you already have the right version (VC++ 2005 SP1) of the
runtimes installed, you could run the installer with the option:

--install_runtimes 0

to skip the runtime installation.

I tried to run pg installer with "--install_runtimes 0" but I got
warning message tell me that database cluster failed does this
problem related to wrong MVR?

if yes how I can fix this issue? do I need to remove installed MVR
(I didn't find it in Add or Remove Programs list)?

Install the correct runtimes version (you don't need to uninstall any 
existing ones). This should do: 
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647 


OK, I'll try it now.



BTW, what language does your copy of Windows run in?

English/Arabic Enabled.

Do you think this problem related to local language config?

--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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] GSS Authentication

[greigwise]

One other thing possibly worth noting I tried to connect to the Postgres DB 
using pgAdmin III and it gives a very similar error to the test perl program 
that I wrote: 



Error connecting to the server: FATAL: accepting GSS security context failed 
DETAIL: Miscellaneous failure: Unknown code ggss 3 



So, it seems as if it must be something on the server side as 2 different 
clients are failing in the same way I think. 



Thanks again. 



Greig 




- Original Message - 
From: greigw...@comcast.net 
To: "Stephen Frost"  
Cc: pgsql-general@postgresql.org 
Sent: Monday, June 14, 2010 3:22:36 PM GMT -05:00 US/Canada Eastern 
Subject: Re: [GENERAL] GSS Authentication 




Thanks for the help. 



In response to your questions, I did make sure the service name was right. 

klist -k on the keytab file gives: 



KVNO Principal 
 -- 
   3 POSTGRES/hostname.domain@domain.com 





I replaced our real domain with an example obviously, but that's what it looks 
like. 

I'm thinking it looks correct.  



By testing with psql locally first, do you mean running psql right on the 
postgres server itself?  To test the GSS authentication?  I tried to set the 
local connections in the pg_hba.conf to use gss authentication locally, but 
then when I tried to restart postgres, the logs said that GSS authentication 
wasn't allowed for local connections (see log message below): 



2010-06-14 14:42:24 EDTLOG:  F: gssapi authentication is not supported on 
local sockets 



I did change the default service name to POSTGRES instead of postgres. 

Reverse DNS is working and I think the default realm is right.  I'm a little 
unclear on exactly what that should be, but I'm thinking that based on the 
example above it should be something like "domain.com".  



I did give the server side logs in my original message, but I'll include more.  
So, in this log entry I'll paste below (it's a little lengthy), we have a 
startup, then a failed connection from the windows client, then a shutdown. 



What should I try next?  Thanks for the help. 



Greig Wise 



 



2010-06-14 15:12:21 EDTLOG:  0: database system was shut down at 2010-06-14 
15:12:08 EDT 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5243 
2010-06-14 15:12:21 EDTDEBUG:  0: checkpoint record is at 1/BD20 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5340 
2010-06-14 15:12:21 EDTDEBUG:  0: redo record is at 1/BD20; shutdown 
TRUE 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5366 
2010-06-14 15:12:21 EDTDEBUG:  0: next transaction ID: 0/696; next OID: 
16400 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5370 
2010-06-14 15:12:21 EDTDEBUG:  0: next MultiXactId: 1; next 
MultiXactOffset: 0 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5373 
2010-06-14 15:12:21 EDTDEBUG:  0: transaction ID wrap limit is 2147484295, 
limited by database "template1" 
2010-06-14 15:12:21 EDTLOCATION:  SetTransactionIdLimit, varsup.c:285 
2010-06-14 15:12:21 EDTDEBUG:  0: shmem_exit(0): 3 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:21 EDTDEBUG:  0: proc_exit(0): 2 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:21 EDTDEBUG:  0: exit(0) 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit, ipc.c:135 
2010-06-14 15:12:21 EDTDEBUG:  0: shmem_exit(-1): 0 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:21 EDTDEBUG:  0: proc_exit(-1): 0 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:21 EDTDEBUG:  0: reaping dead processes 
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2238 
2010-06-14 15:12:21 EDTLOG:  0: autovacuum launcher started 
2010-06-14 15:12:21 EDTLOCATION:  AutoVacLauncherMain, autovacuum.c:529 
2010-06-14 15:12:21 EDTLOG:  0: database system is ready to accept 
connections 
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2326 
2010-06-14 15:12:26 EDTDEBUG:  0: forked new backend, pid=4750 socket=8 
2010-06-14 15:12:26 EDTLOCATION:  BackendStartup, postmaster.c:3085 
2010-06-14 15:12:26 EDTDEBUG:  0: Processing received GSS token of length 
2007 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:965 
2010-06-14 15:12:26 EDTDEBUG:  0: gss_accept_sec_context major: 851968, 
minor: -2045022973, outlen: 0, outflags: 7f 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:984 
2010-06-14 15:12:26 EDTFATAL:  XX000: accepting GSS security context failed 
2010-06-14 15:12:26 EDTDETAIL:  Miscellaneous failure: Unknown code ggss 3 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_error, auth.c:866 
2010-06-14 15:12:26 EDTDEBUG:  0: shmem_exit(1): 0 callbacks to make 
2010-06-14 15:12:26 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:26 EDTDEBUG:  0: pr

Re: [GENERAL] Re: Moving a live production database to different server and postgres release

[Alban Hertroys]

On 14 Jun 2010, at 22:22, Scott Marlowe wrote:
> Is there are good reason to go to Windows instead of a new BSD system?
> Windows is a known mediocre performer for postgres.


I was wondering that too. I assume the good reasons wear ties.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c16a94f286219093520888!



-- 
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] Unable to install pg in Win (MVR error)

[Dave Page]

On Mon, Jun 14, 2010 at 8:47 PM, M. Bashir Al-Noimi wrote:

>  On 14/06/2010 08:43 م, Dave Page wrote:
>
>
>
> On Mon, Jun 14, 2010 at 7:22 PM, M. Bashir Al-Noimi wrote:
>
>>  Hi All,
>>
>>
>>  As shown in the following screenshot I couldn't install pg in Windows
>> because it couldn't install Microsoft VC++ Runtime!
>>
>>
>>  How I can fix this issue?
>>
>
>  If you already have the right version (VC++ 2005 SP1) of the runtimes
> installed, you could run the installer with the option:
>
>  --install_runtimes 0
>
>  to skip the runtime installation.
>
> I tried to run pg installer with "--install_runtimes 0" but I got warning
> message tell me that database cluster failed does this problem related to
> wrong MVR?
>
> if yes how I can fix this issue? do I need to remove installed MVR (I
> didn't find it in Add or Remove Programs list)?
>
> Install the correct runtimes version (you don't need to uninstall any
existing ones). This should do:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=200b2fd9-ae1a-4a14-984d-389c36f85647

BTW, what language does your copy of Windows run in?


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

Re: [GENERAL] table partition or index

[Vick Khera]

On Sun, Jun 13, 2010 at 9:14 AM, AI Rumman  wrote:
> For how many records I should go for a table partition instead of using just
> index?
> Any idea please.

I concur with Stephen.  We tend to split our tables when they exceed
100 million rows *if* they are experiencing performance issues, and
target them to be no more than 10 million rows each after expected
growth over the next few years.

I have one table that is very hot that is about 70 million rows I'd
like to partition, but it has many FK's pointing to it, which
complicates it considerably.

-- 
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: Moving a live production database to different server and postgres release

[Scott Marlowe]

On Mon, Jun 14, 2010 at 7:09 AM, Ulas Albayrak  wrote:
> The database is < 10GB and currently on a postgres version 8.2.15 on a
> BSD system and moving to postgres version 8.4.4 on a windows 2008
> server. The adding of data is continuous but in small quantities,
> totaling at about 20MB a day.

Is there are good reason to go to Windows instead of a new BSD system?
 Windows is a known mediocre performer for postgres.

BTW the slony versions need to match down to the minor rev number.

-- 
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: Moving a live production database to different server and postgres release

[Scott Marlowe]

On Mon, Jun 14, 2010 at 7:33 AM, Ulas Albayrak  wrote:
> OK,
>
> I see. Does this mean I need to install Slony-I 1.x on both systems or
> is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
> reason I'm asking is because the new server db will be part of a
> permanent postgres replication system in the future and installing a
> newer verision of Slony-I would spare me some future work.

Slony I 2.x is still not quite ready for production anyway (but it's
getting close) so you're better off with Slony I 1.2.x anyway.  If the
older version of pgsql needs an older slony, then you'd have to go
back to Slony I 1.0.x or 1.1.x or whatever to migrate, then you could
upgrade to Slony I 1.2.latest.

We do all our migrations / upgrades with slony I 1.2.x and it works
like a champ, with total downtime measured in seconds or minutes for
the switchover.

-- 
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] Cognitive dissonance

[Chris Browne]

t...@sss.pgh.pa.us (Tom Lane) writes:
> Peter Eisentraut  writes:
>> On lör, 2010-06-12 at 11:18 +0200, John Gage wrote:
>>> A one file html version would be a godsend.
>
>> I've committed a build target for that now.  Use 'make postgres.html' in
>> doc/src/sgml/.
>
> Huh, is that actually worth anything?  How many browsers will open it
> without crashing, or will navigate the page with decent performance
> if they do manage to open it?
>
> (Not that I object to providing this Make target.  But I thought the
> discussion was about plain-text output.)

I expect that having one HTML file would make it reasonably easy to
use lynx/links/w3m [some text-based HTML browser] to transform HTML
into plain text.

I should think that it would also enable using analagous tools to
transform HTML into eBook formats like ePub and mobi, which are the
frequently-preferable-formats on the emerging category of "electronic
book" appliances.

I have browsed the CHM form of the docs using a CHM reader on my
phone, and found that less than wonderful, which had a lot to do with
the reader not being particularly great.  A format that plays well
with a decent reader may turn out pretty happily.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.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] Unable to install pg in Win (MVR error)

[Sachin Srivastava]

When you run the installer without specifying "--install_runtimes 0",
What is the error code returned by the MS VC++ Runtime installer. You
can check that in the log file
(%TEMP%\bitrock_installer_.log). 

On 6/15/10 2:17 AM, M. Bashir Al-Noimi wrote:

  
On 14/06/2010 08:43 م, Dave Page wrote:
  

On Mon, Jun 14, 2010 at 7:22 PM, M. Bashir
Al-Noimi  wrote:

  
  Hi All,
  
  
  As shown in the
following screenshot I couldn't install pg in Windows because it
couldn't install Microsoft VC++ Runtime!
  
  
  How I can fix
this
issue?
  



If you already have the right version (VC++ 2005 SP1) of the
runtimes installed, you could run the installer with the option:


--install_runtimes 0


to skip the runtime installation. 

  
I tried to run pg installer with "--install_runtimes 0" but I got
warning message tell me that database cluster failed does this problem
related to wrong MVR?
  
if yes how I can fix this issue? do I need to remove installed MVR (I
didn't find it in Add or Remove Programs list)?
  
  
  
PS.
After finishing the installer I noticed that pg services didn't start
at all although I successfully installed pg on another PC!
  
  -- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net
  
  


  



-- 
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise Postgres company.

Re: [GENERAL] Unable to install pg in Win (MVR error)

[M. Bashir Al-Noimi]

On 14/06/2010 08:43 م, Dave Page wrote:

  
  On Mon, Jun 14, 2010 at 7:22 PM, M. Bashir
Al-Noimi  wrote:
  

Hi All,


As shown in the
following screenshot I couldn't install pg in Windows because it
couldn't install Microsoft VC++ Runtime!


How I can fix this
issue?

  
  
  
  If you already have the right version (VC++ 2005 SP1) of the
runtimes installed, you could run the installer with the option:
  
  
  --install_runtimes 0
  
  
  to skip the runtime installation. 
  

I tried to run pg installer with "--install_runtimes 0" but I got
warning message tell me that database cluster failed does this problem
related to wrong MVR?

if yes how I can fix this issue? do I need to remove installed MVR (I
didn't find it in Add or Remove Programs list)?



PS.
After finishing the installer I noticed that pg services didn't start
at all although I successfully installed pg on another PC!

-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net



<>
-- 
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] Silent installer in Windows

[M. Bashir Al-Noimi]

Thanks Sachin

On 14/06/2010 08:55 م, Sachin Srivastava wrote:
In the One-Click Installer for PostgreSQL use the CLI option, --mode 
unattended for more options see --help.


On 6/15/10 12:45 AM, M. Bashir Al-Noimi wrote:


Hi folks,


I want to install ps silentely in Windows how I can do it? what's 
needed arguments?



Sorry I'm still a newbie


Ps

I read pginstaller doc about silent installing 
 but I 
noticed that pginstaller no longer available as mentioned in its home 
page.





--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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] GSS Authentication

[greigwise]

Thanks for the help. 



In response to your questions, I did make sure the service name was right. 

klist -k on the keytab file gives: 



KVNO Principal 
 -- 
   3 POSTGRES/hostname.domain@domain.com 





I replaced our real domain with an example obviously, but that's what it looks 
like. 

I'm thinking it looks correct.  



By testing with psql locally first, do you mean running psql right on the 
postgres server itself?  To test the GSS authentication?  I tried to set the 
local connections in the pg_hba.conf to use gss authentication locally, but 
then when I tried to restart postgres, the logs said that GSS authentication 
wasn't allowed for local connections (see log message below): 



2010-06-14 14:42:24 EDTLOG:  F: gssapi authentication is not supported on 
local sockets 



I did change the default service name to POSTGRES instead of postgres. 

Reverse DNS is working and I think the default realm is right.  I'm a little 
unclear on exactly what that should be, but I'm thinking that based on the 
example above it should be something like "domain.com".  



I did give the server side logs in my original message, but I'll include more.  
So, in this log entry I'll paste below (it's a little lengthy), we have a 
startup, then a failed connection from the windows client, then a shutdown. 



What should I try next?  Thanks for the help. 



Greig Wise 



 



2010-06-14 15:12:21 EDTLOG:  0: database system was shut down at 2010-06-14 
15:12:08 EDT 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5243 
2010-06-14 15:12:21 EDTDEBUG:  0: checkpoint record is at 1/BD20 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5340 
2010-06-14 15:12:21 EDTDEBUG:  0: redo record is at 1/BD20; shutdown 
TRUE 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5366 
2010-06-14 15:12:21 EDTDEBUG:  0: next transaction ID: 0/696; next OID: 
16400 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5370 
2010-06-14 15:12:21 EDTDEBUG:  0: next MultiXactId: 1; next 
MultiXactOffset: 0 
2010-06-14 15:12:21 EDTLOCATION:  StartupXLOG, xlog.c:5373 
2010-06-14 15:12:21 EDTDEBUG:  0: transaction ID wrap limit is 2147484295, 
limited by database "template1" 
2010-06-14 15:12:21 EDTLOCATION:  SetTransactionIdLimit, varsup.c:285 
2010-06-14 15:12:21 EDTDEBUG:  0: shmem_exit(0): 3 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:21 EDTDEBUG:  0: proc_exit(0): 2 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:21 EDTDEBUG:  0: exit(0) 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit, ipc.c:135 
2010-06-14 15:12:21 EDTDEBUG:  0: shmem_exit(-1): 0 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:21 EDTDEBUG:  0: proc_exit(-1): 0 callbacks to make 
2010-06-14 15:12:21 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:21 EDTDEBUG:  0: reaping dead processes 
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2238 
2010-06-14 15:12:21 EDTLOG:  0: autovacuum launcher started 
2010-06-14 15:12:21 EDTLOCATION:  AutoVacLauncherMain, autovacuum.c:529 
2010-06-14 15:12:21 EDTLOG:  0: database system is ready to accept 
connections 
2010-06-14 15:12:21 EDTLOCATION:  reaper, postmaster.c:2326 
2010-06-14 15:12:26 EDTDEBUG:  0: forked new backend, pid=4750 socket=8 
2010-06-14 15:12:26 EDTLOCATION:  BackendStartup, postmaster.c:3085 
2010-06-14 15:12:26 EDTDEBUG:  0: Processing received GSS token of length 
2007 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:965 
2010-06-14 15:12:26 EDTDEBUG:  0: gss_accept_sec_context major: 851968, 
minor: -2045022973, outlen: 0, outflags: 7f 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_recvauth, auth.c:984 
2010-06-14 15:12:26 EDTFATAL:  XX000: accepting GSS security context failed 
2010-06-14 15:12:26 EDTDETAIL:  Miscellaneous failure: Unknown code ggss 3 
2010-06-14 15:12:26 EDTLOCATION:  pg_GSS_error, auth.c:866 
2010-06-14 15:12:26 EDTDEBUG:  0: shmem_exit(1): 0 callbacks to make 
2010-06-14 15:12:26 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:26 EDTDEBUG:  0: proc_exit(1): 1 callbacks to make 
2010-06-14 15:12:26 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:26 EDTDEBUG:  0: exit(1) 
2010-06-14 15:12:26 EDTLOCATION:  proc_exit, ipc.c:135 
2010-06-14 15:12:26 EDTDEBUG:  0: shmem_exit(-1): 0 callbacks to make 
2010-06-14 15:12:26 EDTLOCATION:  shmem_exit, ipc.c:211 
2010-06-14 15:12:26 EDTDEBUG:  0: proc_exit(-1): 0 callbacks to make 
2010-06-14 15:12:26 EDTLOCATION:  proc_exit_prepare, ipc.c:183 
2010-06-14 15:12:26 EDTDEBUG:  0: reaping dead processes 
2010-06-14 15:12:26 EDTLOCATION:  reaper, postmaster.c:2238 
2010-06-14 15:12:26 EDTDEBUG:  0: server process (PID 4750) exited with 
exit code 1 
2010-06-14 

Re: [GENERAL] Silent installer in Windows

[Sachin Srivastava]

In the One-Click Installer for PostgreSQL use the CLI option, --mode 
unattended for more options see --help.


On 6/15/10 12:45 AM, M. Bashir Al-Noimi wrote:


Hi folks,


I want to install ps silentely in Windows how I can do it? what's 
needed arguments?



Sorry I'm still a newbie


Ps

I read pginstaller doc about silent installing 
 but I noticed 
that pginstaller no longer available as mentioned in its home page.


--
Best Regards
Muhammad Bashir Al-Noimi
My Blog:http://mbnoimi.net
   




   



--
Regards,
Sachin Srivastava
EnterpriseDB , the Enterprise Postgres 
 company.

Re: [GENERAL] Unable to install pg in Win (MVR error)

[Dave Page]

On Mon, Jun 14, 2010 at 7:22 PM, M. Bashir Al-Noimi wrote:

>  Hi All,
>
>
>  As shown in the following screenshot I couldn't install pg in Windows
> because it couldn't install Microsoft VC++ Runtime!
>
>
>  How I can fix this issue?
>

If you already have the right version (VC++ 2005 SP1) of the runtimes
installed, you could run the installer with the option:

--install_runtimes 0

to skip the runtime installation. If you don't have them, or have the wrong
version, the installer will almost certainly fail later on.


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

[GENERAL] IMMUTABLE columns in tables?

[Adrian von Bidder]

Heyho!

(Ok, seems to be feature wish day ...)

I was wondering if others would find an IMMUTABLE (or whatever) column 
constraint useful as well.  Semantics would (obviously?) be to disallow 
changing the value of this column after insert.

I realize that this is possible via triggers, and with the recent 
possibility of having triggers fire only on changes to certain columns it's 
even (presumably) not much runtime overhead, but creating triggers is very 
verbose and doesn't make the db schema very readable.

cheers
-- vbi

-- 
Could this mail be a fake? (Answer: No! - http://fortytwo.ch/gpg/intro)


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

[GENERAL] thoughts about constraint trigger

[Adrian von Bidder]

Heyho!

I was trying to implement a deferred NOT NULL constraint using a deferred 
constraint trigger (on update and insert of this row) because some values 
would be filled in later during the transaction, after the initial part of 
the record has been filled.

I asked myself if a type of deferred trigger that would

 * trigger only once per affected row
 * with the NEW value set to what is about to be committed (OLD should 
probably be OLD from when the trigger was first fired the first time)

might not be useful (compared with the current model -- trigger it once for 
each operation, with OLD/NEW being the same as for non-deferred trigger)

At least, I was expecting this behaviour (undoubtedly because I only skimmed 
the docs and did not really read them thoroughly ;-) and was surprised when 
I got my error for a non-null value (IF .. IS NULL THEN RAISE ...), when I 
did set the value in an UPDATE during the same transaction...  [0]

I'm neither an SQL guru nor familiar with PostgreSQL internals.  I was only 
starting from the viewpoint of deferred triggers as an implementation for 
deferred NOT NULL (or other CHECK) constraints.  There may as well be other 
usecases where the current behaviour is appropriate.

(ironically it turned out that I didn't think about my DB schema carefully 
enough and this particular column did not need the NOT NULL constraint, so I 
scrapped the trigger.)

cheers
-- vbi

[0] The implementation I ended with was PERFORM ... WHERE id = NEW.id AND 
mycol IS NULL and then RAISing if FOUND; the id will not change.  But the 
fact that this may end up being executed several times at commit seems less 
than ideal.
-- 
featured link: http://www.pool.ntp.org


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

[GENERAL] Unable to install pg in Win (MVR error)

[M. Bashir Al-Noimi]

Hi All,


As shown in the
following screenshot I couldn't install pg in Windows because it
couldn't install Microsoft VC++ Runtime!


How I can fix this
issue?






PS
I didn't find any
problem during using pg in kubuntu it worked smoothly within 10 seconds.

-- 
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net



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

[GENERAL] Silent installer in Windows

[M. Bashir Al-Noimi]

Hi folks,


I want to install ps silentely in Windows how I can do it? what's needed 
arguments?



Sorry I'm still a newbie


Ps

I read pginstaller doc about silent installing 
 but I noticed 
that pginstaller no longer available as mentioned in its home page.


--
Best Regards
Muhammad Bashir Al-Noimi
My Blog: http://mbnoimi.net

<>
-- 
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] Trigger get dissabled

[Abbas]

On Mon, Jun 14, 2010 at 3:52 PM, Gaurav K Srivastav wrote:

> Hi,
>
> I am using  postgre SQL 8.3 on centos,
>
> My case is Suppose  I a databse ABC (In this database I have few enabled
> trigger) and I am making  abc.tar as a dump file using pg_dump utility.
> Now I am restoring abc.tar using pg_restore on another machine .
>
> Up to now every thing is fine but the trigger get dissabled.
>
> Can you please let me know that how can I again enable these triggers in my
> database schema?
>
> Or Is there any way to not make trigger dissable while pg_dump or
> pg_restore?
>
> Or is there any query to enable all the dissabled triggers in a database
> schema?
>
> Please let me know I will be highly obliged.
>
>
>
> Are you sure that you didn't use  --disable-triggers option while taking
the pg_dump?


---
Abbas.

> --
> Thanks & Regards
> Gaurav K Srivastav
>

Re: [GENERAL] Unable to (re) start PostgreSQL 8.4.4/WinXP

[John R Pierce]

kunalashar wrote:

The pg_log files contain, rather inexplicably:

2010-06-14 09:47:32 IST LOG:  invalid IP mask "trust": Unknown host
2010-06-14 09:47:32 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:47:32 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:45:06 IST LOG:  invalid IP mask "md5": Unknown host
2010-06-14 09:45:06 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:45:06 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:45:06 IST LOG:  invalid IP mask "md5": Unknown host
2010-06-14 09:45:06 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:45:06 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:37:36 IST LOG:  invalid connection type "all
"
2010-06-14 09:37:36 IST CONTEXT:  line 70 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:37:36 IST LOG:  invalid CIDR mask in address "192.168.0.0/255"
2010-06-14 09:37:36 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:37:36 IST FATAL:  could not load pg_hba.conf

  


sounds liek there's more than just the entry you gave us before...


My pg_hba.conf file has the following line:
host   all   all   192.168.0.1/24   trust
  



which, btw, is probably wrong.with a mask of /24 (which is 
equivalent to 255.255.255.0, eg 24 '1's), the IP part would have to be 
192.168.0.0 (as in this context, thats the network number).your last 
error, /255 is under no conditions a valid CIDR mask length.



--
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] Unable to (re) start PostgreSQL 8.4.4/WinXP

[kunalashar]

The pg_log files contain, rather inexplicably:

2010-06-14 09:47:32 IST LOG:  invalid IP mask "trust": Unknown host
2010-06-14 09:47:32 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:47:32 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:45:06 IST LOG:  invalid IP mask "md5": Unknown host
2010-06-14 09:45:06 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:45:06 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:45:06 IST LOG:  invalid IP mask "md5": Unknown host
2010-06-14 09:45:06 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:45:06 IST FATAL:  could not load pg_hba.conf


2010-06-14 09:37:36 IST LOG:  invalid connection type "all
"
2010-06-14 09:37:36 IST CONTEXT:  line 70 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:37:36 IST LOG:  invalid CIDR mask in address "192.168.0.0/255"
2010-06-14 09:37:36 IST CONTEXT:  line 74 of configuration file
"E:/Data/PostgreSQL/8.4/data/pg_hba.conf"
2010-06-14 09:37:36 IST FATAL:  could not load pg_hba.conf

-- 
View this message in context: 
http://old.nabble.com/Unable-to-%28re%29-start-PostgreSQL-8.4.4-WinXP-tp28875697p28875911.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Trigger get dissabled

[Gaurav K Srivastav]

Hi,

I am using  postgre SQL 8.3 on centos,

My case is Suppose  I a databse ABC (In this database I have few enabled
trigger) and I am making  abc.tar as a dump file using pg_dump utility.
Now I am restoring abc.tar using pg_restore on another machine .

Up to now every thing is fine but the trigger get dissabled.

Can you please let me know that how can I again enable these triggers in my
database schema?

Or Is there any way to not make trigger dissable while pg_dump or
pg_restore?

Or is there any query to enable all the dissabled triggers in a database
schema?

Please let me know I will be highly obliged.



-- 
Thanks & Regards
Gaurav K Srivastav

Re: [GENERAL] Is there a way to backup Postgres via SQL commands?

[Merlin Moncure]

On Mon, Jun 14, 2010 at 7:28 AM, Frank Church  wrote:
> Are there SQL commands that can do a backup over a client connection,
> rather than from the command line like pgsql etc?
>
> By that I mean some kind of SELECT commands that can retrieve the
> database's content as SQL commands that can be replayed to a server to
> restore it, rather than something that saves directly to file, or
> passes it through a pipe?

no -- it's actually possible for some trivial things (just grabbing a
few tables) -- but anything more complicated will quickly become
unreasonably difficult.

TBH, I think backups through SQL are quite nice.  Judging by the
standard of other databases, I generally always prefer the sql version
to the command line variant with both tools are available.

It would also be nice to be able to generate a restorable script from
any userland object.  pg_dump/pg_restore gives you the ability to
specifically grab indexes, functions, table, and triggers, but that's
it.

merlin

-- 
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 are the minimal files required to backup a postgresql database

[Craig Ringer]

On 14/06/2010 9:14 PM, Alban Hertroys wrote:

On 14 Jun 2010, at 14:20, Frank Church wrote:


I need to package the ability to backup a PostgreSQL database in my application?


What are you trying to accomplish by that?


I obviously need to add pg_dump.exe to the package, and the dlls
required are listed below,


You can check what dll's an executable depends on by using some MS tools. 
There's some documentation about that in the postgres wiki IIRC, look for 
debugging tools on windows.


depends.exe from dependencywalker.com .

Basically, ship everything in the `bin' directory where pg_dump lives if 
it's listed by depends.exe as used by pg_dump. Also make sure to provide 
and silent-install the MSVC++ 2005 redist, which you can get from Microsoft.


Do *NOT* ship any system DLLs.


Something to do with SSL apparently; you should only need that if you plan to 
use pg_dump over SSL.


That depends entirely on how DLL linkage is done. If it's an immediate 
dependency rather than a delayed dependency, which it will be unless 
configured otherwise, pg_dump will fail to load without that DLL even if 
it's not going to use ssl.


I'm not on a Windows box with PG at the moment so I can't test.

--
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] Is there a way to backup Postgres via SQL commands?

[Craig Ringer]

On 14/06/2010 9:05 PM, Raymond O'Donnell wrote:

On 14/06/2010 12:28, Frank Church wrote:

Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?


No, not that I'm aware of.


By that I mean some kind of SELECT commands that can retrieve the
database's content as SQL commands that can be replayed to a server to
restore it, rather than something that saves directly to file, or
passes it through a pipe?


What exactly are you trying to do? If you're building database backup
functionality into an application, you're better off just spawning a
sub-process with pg_dump.


If you really, really need it, you might be able to experiment with a 
gruesome hack like:


- Use a plperlu function running in the server to spawn the desired 
"pg_dump" command, with stdout connected to a pipe. Make sure this 
function is only accessible to the DB superuser.


- Stream the pg_dump output from plperlu to your client. You could read 
line-by-line and return SETOF TEXT, or read (say) 1MB chunks and return 
SETOF BYTEA, for example. Appropriate client buffering and/or an 
explicit cursor may be required.


I haven't tested this, and it might not work due to requirements/issues 
I'm unaware of. If it doesn't work, you should be able to at least 
invoke pg_dump via a stored function to dump to a server-side file, then 
slurp that file via the admin functions.


I agree with others that if at all possible, pg_dump should be spawned 
client side. That said, spawning pg_dump client side is a pain if the 
app doesn't use libpq (say it's a Java/JDBC app), as you have to 
maintain two different configurations for database access. It's worse 
yet if the DB requires installation of a trusted SSL root cert, and 
totally impractical if the DB requires client certificates.


I've never had any desire to perform client-side backups, so this hasn't 
come up as an issue for me. What problem are you trying to solve with 
this, and in what situation?


--
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] Partial indexes instead of partitions

[Sam Mason]

On Mon, Jun 14, 2010 at 08:27:49AM -0400, David Wilson wrote:
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F  wrote:
> > > For "inserts" I do not see the reason why it would be better to
> > > use index partitioning because AFAIK b-tree would behave exactly
> > > the same in both cases.
> >
> > no, when the index gets very big inserting random values gets
> > very slow.
> 
> Do you have any empirical evidence for this being a real problem, or are you
> simply guessing?

Just guessing here as well, but when you're inserting uniformly
distributed "random" values, then it should slow down quite a lot.  You
may happen to be lucky in your distributions and keep the upper nodes
of the tree in cache but with more uniform distributions the less this
is going to happen.  The larger an index and the more uniform the
distribution the more time is going to be spent pulling blocks off the
disk.

AFAIU the OP is trying to give the cache a chance of doing some useful
work by partitioning by time so it's going to be forced to go to disk
less.

Slightly more usefully for the OP, have you considered a couple of
"levels" to your hierarchy.  Maybe bi-hourly (~15 million records?)
within the current day and move them over into a "day" table at night
(or whenever is better).  It would be a good time to cluster the data,
if that would help as well.

-- 
  Sam  http://samason.me.uk/

-- 
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: Moving a live production database to different server and postgres release

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


...
> After your post I had a look at Slony-I, which, according to it web
> page, requires postgres 8.3 or later, so I guess that won't work. That
> leaves Bucardo: will it work on a Windows system?

The daemon itself cannot run on a Windows system, but it's perfectly fine 
for any of the databases it is replicating to be Windows. So in this case 
you'd just install Bucardo on the BSD machines, tell it about the Windows 
box, setup the tables, kick it off in onetimecopy mode, then do the final 
transfer once the initial copying is done.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201006141048
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkwWQUAACgkQvJuQZxSWSsjMiQCcDo9QJDdeZKziEBFBeR0yyb9M
ATkAoJO1dJkkeADnDcPLTtUkcGUF8tRF
=EF+J
-END PGP SIGNATURE-



-- 
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: Moving a live production database to different server and postgres release

[Raymond O'Donnell]

On 14/06/2010 14:33, Ulas Albayrak wrote:
> OK,
> 
> I see. Does this mean I need to install Slony-I 1.x on both systems or
> is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
> reason I'm asking is because the new server db will be part of a
> permanent postgres replication system in the future and installing a
> newer verision of Slony-I would spare me some future work.

Yes, I'm afraid the versions of Slony have to be identical across the
replicated systems.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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: Moving a live production database to different server and postgres release

[Ulas Albayrak]

OK,

I see. Does this mean I need to install Slony-I 1.x on both systems or
is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
reason I'm asking is because the new server db will be part of a
permanent postgres replication system in the future and installing a
newer verision of Slony-I would spare me some future work.

/Regards

On Mon, Jun 14, 2010 at 3:15 PM, Raymond O'Donnell  wrote:
> On 14/06/2010 14:09, Ulas Albayrak wrote:
>
>> After your post I had a look at Slony-I, which, according to it web
>> page, requires postgres 8.3 or later, so I guess that won't work. That
>> leaves Bucardo: will it work on a Windows system?
>
> AIUI, the Slony-I 2.x branch requires PG 8.3+, but the 1.x branch ill
> work perfectly fine on 8.2 and lower.
>
> This is because 1.x messes with the system catalogues and strange ways,
> whereas changes in PG 8.3 made this unnecessary.
>
> HTH,
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>



-- 
Ulas Albayrak
ulas.albay...@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] Hosting without pgcrypto functions. There are other solutions?

[John Gage]

Have you talked to A2 about this?  They are very good about installing  
things.


John


On Jun 14, 2010, at 3:20 PM, Merlin Moncure wrote:

On Sun, Jun 13, 2010 at 4:37 PM, Andre Lopes  
 wrote:

Hi,

I have an account in A2Hosting.com, and I'm developing some  
functions that

deal with encryption.

A2Hosting.com don't have available the function "digest()"




--
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] Advice on contingency plan for DAS array with separate local WAL drives

[Greg Smith]

Jeff Amiel wrote:

Recently migrated to a shiny new 8.4.4 postgres instancedata stored on 
attached storage array.  Transaction logs stored on 2 local mirrored drives 
(local to the database server itself) for best performance.
  


Have you benchmarked that this really helps?  Splitting the WAL out onto 
a drive pair can help a lot in some cases, but given a large attached 
array the improvement tends to be minimal relative to just lumping those 
writes in with the rest of the array.  The easy way out of your problem 
is to just not put the WAL on the local drives, and instead use them for 
things like temp file storage.


--
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] Hosting without pgcrypto functions. There are other solutions?

[Merlin Moncure]

On Sun, Jun 13, 2010 at 4:37 PM, Andre Lopes  wrote:
> Hi,
>
> I have an account in A2Hosting.com, and I'm developing some functions that
> deal with encryption.
>
> A2Hosting.com don't have available the function "digest()"
>
> [code]
> ERROR:  function digest(unknown, unknown) does not exist
> LINE 1: select digest('', 'sha1')

I bet the crypto library is built/installed -- you just need to
install the functions.   Try this:
CREATE OR REPLACE FUNCTION digest(text, text)
AS '$libdir/pgcrypto', 'pg_digest'


If that fails then start thinking about plan 'B'.

merlin

-- 
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] shared_buffer advice

[Greg Smith]

AI Rumman wrote:

I have a server with Dual-Core 4 cpu and 32 GB RAM.
This is the database server. Only Postgresql 8.1 is running on it with 
multiple databases.

How should I plan for shared_buffers and effective cache size?


Set shared_buffers very low--at most 128MB--because you're running 
PostgreSQL 8.1.  If you upgrade to 8.3 or later, which you should, you 
might explore higher values.  On 8.1 and 8.2, trying to use any large 
size for shared_buffers makes the whole database freeze under any sort 
of heavy write load when checkpoints happen.


The first question you should be asking is not "how can I tune the 
parameters on my 8.1 server?", it should be "how can I upgrade this 8.1 
server to a newer version?".  That's not always possible, because some 
8.1 applications won't run on 8.3 or later without changes to them.  But 
moving onto 8.3 is by far a better way to get better performance from 
your system than trying to tune 8.1.


--
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] Re: Moving a live production database to different server and postgres release

[Raymond O'Donnell]

On 14/06/2010 14:09, Ulas Albayrak wrote:

> After your post I had a look at Slony-I, which, according to it web
> page, requires postgres 8.3 or later, so I guess that won't work. That
> leaves Bucardo: will it work on a Windows system?

AIUI, the Slony-I 2.x branch requires PG 8.3+, but the 1.x branch ill
work perfectly fine on 8.2 and lower.

This is because 1.x messes with the system catalogues and strange ways,
whereas changes in PG 8.3 made this unnecessary.

HTH,

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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 are the minimal files required to backup a postgresql database

[Alban Hertroys]

On 14 Jun 2010, at 14:20, Frank Church wrote:

> I need to package the ability to backup a PostgreSQL database in my 
> application?

What are you trying to accomplish by that?

> I obviously need to add pg_dump.exe to the package, and the dlls
> required are listed below,

You can check what dll's an executable depends on by using some MS tools. 
There's some documentation about that in the postgres wiki IIRC, look for 
debugging tools on windows.

I don't think you'd need all of these, but I can only guess. Here are my 
thoughts though:

> 15/08/2007  15:4112,288 comerr32.dll

No idea what this is.

> 15/08/2007  15:41   139,264 gssapi32.dll
> 15/08/2007  15:4119,968 k5sprt32.dll
> 15/08/2007  15:41   602,112 krb5_32.dll

I think you only need these if you're using kerberos or GSSAPI for 
authentication.

> 27/02/2007  15:39 1,040,384 libeay32.dll

No idea about this one.

> 31/01/2003  19:41   916,849 libiconv-2.dll
> 16/03/2004  16:37   898,048 libiconv2.dll

These are most likely two versions of the same dll. Taking the newest should be 
safe.

> 27/04/2004  10:4532,256 libintl-2.dll
> 06/05/2005  17:52   103,424 libintl3.dll

Same here.

> 31/10/2008  07:15   167,936 libpq.dll
> 05/11/2005  14:51   169,092 libpq81.dll

And same here; the libpq81.dll is probably left over from an old PG81 
installation.

> 16/10/2007  13:40   348,160 msvcr71.dll
> 02/12/2006  00:03   626,688 msvcr80.dll

It needs two different runtimes? Maybe they're disjunct then?

> 09/03/2010  16:09   520,190 sqlite3.dll

No idea why you'd want SQLite in there, I can't imagine it being used by 
pg_dump.

> 27/02/2007  15:40   196,608 ssleay32.dll

Something to do with SSL apparently; you should only need that if you plan to 
use pg_dump over SSL.

> 
> Are there any other executables besides pg_dump I need to add to this list?
> 
> If some of them are likely to come in useful, rather than necessary
> for backup I don't mind adding them


That highly depends on what you're trying to accomplish. I'd think pg_dumpall, 
pg_restore and psql would be useful too.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c162b52286211582820103!



-- 
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] Advice on contingency plan for DAS array with separate local WAL drives

[Jeff Amiel]

On 6/14/10 8:05 AM, "Jeff Amiel"  wrote:


> What is recommended in terms of prep/switchover in this instance?  Should we
> be rsyncing or using built-in wal-log shipping of these transaction logs to
> our stand-by server?  Simply pop out these drives and hand-move them to the
> standby-server? (assuming they are not the issue in the first place)


I should note that this 'similar' server is not intended to actually be a
real warm standby server in the postgres sense...it serves other
purposes...it simply possesses the right configuration (memory, cpu) to act
as a temporary database server.


-- 
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: Moving a live production database to different server and postgres release

[Ulas Albayrak]

The database is < 10GB and currently on a postgres version 8.2.15 on a
BSD system and moving to postgres version 8.4.4 on a windows 2008
server. The adding of data is continuous but in small quantities,
totaling at about 20MB a day.

After your post I had a look at Slony-I, which, according to it web
page, requires postgres 8.3 or later, so I guess that won't work. That
leaves Bucardo: will it work on a Windows system?

/Regards


> > My best idea so far is to do a pg_dump and somehow archive all the DML
> > in the original db from that point in time for later insertion in the
> > new db, but I dont know how that would be done practically. And I
> > dont even know if thats the best way to go, as I said, its only an
> > idea.
>
> What you need is a replication system. Take a look at Slony or Bucardo.
> Basically, you copy over everything except for the data to the new
> database, switch the replication system on, let it catch up, then
> stop apps from hitting the server, wait for the new one to catch up,
> and point your apps to the new one.
>
> Important factors that you left out are exactly how big your database is,
> what version you are on, what version you are moving to, and how busy your
> system is. Also keep in mind that both Bucardo and Slony are trigger based
> on primary keys or unique indexes, so tables without such constraints
> cannot be replicated: you'll need to either add a unique constraint to
> the tables, or copy them separately (e.g. pg_dump -t tablename or
> Bucardo's fullcopy mode).
>
> If you weren't also moving your OS and server, pg_migrator (aka pg_upgrade)
> might work for you as well: it does an inplace, one-time upgrade but only
> supports a limited number of versions at the moment.
>
> - --
> Greg Sabino Mullane g...@turnstep.com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201006110927
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-- 
Ulas Albayrak
ulas.albay...@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

[GENERAL] Advice on contingency plan for DAS array with separate local WAL drives

[Jeff Amiel]

Recently migrated to a shiny new 8.4.4 postgres instancedata stored on 
attached storage array.  Transaction logs stored on 2 local mirrored drives 
(local to the database server itself) for best performance.

While we are replicating (using slony) to our DR site, our first-choice plan 
(in the event of an issue simply with the database server itself) was to 
disconnect the SAS cables from it (connected to the DAS) and connect them to a 
'similar' box we have on standby.  With the WAL logs physically on the drives 
of the original database server, this obviously becomes an issue.  

What is recommended in terms of prep/switchover in this instance?  Should we be 
rsyncing or using built-in wal-log shipping of these transaction logs to our 
stand-by server?  Simply pop out these drives and hand-move them to the 
standby-server? (assuming they are not the issue in the first place)   

Any thoughts would be appreciated.





  


-- 
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] Is there a way to backup Postgres via SQL commands?

[Raymond O'Donnell]

On 14/06/2010 12:28, Frank Church wrote:
> Are there SQL commands that can do a backup over a client connection,
> rather than from the command line like pgsql etc?

No, not that I'm aware of.

> By that I mean some kind of SELECT commands that can retrieve the
> database's content as SQL commands that can be replayed to a server to
> restore it, rather than something that saves directly to file, or
> passes it through a pipe?

What exactly are you trying to do? If you're building database backup
functionality into an application, you're better off just spawning a
sub-process with pg_dump.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
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] Is there a way to backup Postgres via SQL commands?

[Alban Hertroys]

On 14 Jun 2010, at 13:28, Frank Church wrote:

> Are there SQL commands that can do a backup over a client connection,
> rather than from the command line like pgsql etc?

No.

> By that I mean some kind of SELECT commands that can retrieve the
> database's content as SQL commands that can be replayed to a server to
> restore it, rather than something that saves directly to file, or
> passes it through a pipe?


That sounds quite a bit like replicating the DB to a warm standby, is that what 
you're after? There are several solutions for that.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c16281d286215086519202!



-- 
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] Partial indexes instead of partitions

[David Wilson]

On Mon, Jun 14, 2010 at 8:38 AM, Peter Hunsberger <
peter.hunsber...@gmail.com> wrote:

>
>
> Can you define acceptable?  IIRC the OP is looking for 20,000+ inserts /
> sec.
>
>
> He's actually only looking for 2k inserts/sec. With a battery backed
controller I can sustain that, yes. That's also on commodity hardware (the
whole system was under $2k a year and a half ago).


-- 
- David T. Wilson
david.t.wil...@gmail.com

Re: [GENERAL] Partial indexes instead of partitions

[Peter Hunsberger]

On Mon, Jun 14, 2010 at 7:27 AM, David Wilson  wrote:
>
>
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F  wrote:
>>
>> > For "inserts" I do not see the reason
>> > why
>> > it would be better to use index partitioning because AFAIK
>> > b-tree
>> > would behave exactly the same in both cases.
>>
>> no, when the index gets very big inserting random values gets
>> very slow.
>
> Do you have any empirical evidence for this being a real problem, or are you
> simply guessing? I have tables with 500m+ rows, on commodity hardware (4
> SATA disks in raid 10), and inserts to the indexes on those tables remain
> quite acceptable from a performance standpoint.
>

Can you define acceptable?  IIRC the OP is looking for 20,000+ inserts / sec.


-- 
Peter Hunsberger

-- 
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] Partial indexes instead of partitions

[David Wilson]

On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F  wrote:

> > For "inserts" I do not see the reason
> > why
> > it would be better to use index partitioning because AFAIK
> > b-tree
> > would behave exactly the same in both cases.
>
> no, when the index gets very big inserting random values gets
> very slow.
>

Do you have any empirical evidence for this being a real problem, or are you
simply guessing? I have tables with 500m+ rows, on commodity hardware (4
SATA disks in raid 10), and inserts to the indexes on those tables remain
quite acceptable from a performance standpoint.

-- 
- David T. Wilson
david.t.wil...@gmail.com

[GENERAL] What are the minimal files required to backup a postgresql database

[Frank Church]

I need to package the ability to backup a PostgreSQL database in my application?

I obviously need to add pg_dump.exe to the package, and the dlls
required are listed below,


15/08/2007  15:4112,288 comerr32.dll
15/08/2007  15:41   139,264 gssapi32.dll
15/08/2007  15:4119,968 k5sprt32.dll
15/08/2007  15:41   602,112 krb5_32.dll
27/02/2007  15:39 1,040,384 libeay32.dll
31/01/2003  19:41   916,849 libiconv-2.dll
16/03/2004  16:37   898,048 libiconv2.dll
27/04/2004  10:4532,256 libintl-2.dll
06/05/2005  17:52   103,424 libintl3.dll
31/10/2008  07:15   167,936 libpq.dll
05/11/2005  14:51   169,092 libpq81.dll
16/10/2007  13:40   348,160 msvcr71.dll
02/12/2006  00:03   626,688 msvcr80.dll
09/03/2010  16:09   520,190 sqlite3.dll
27/02/2007  15:40   196,608 ssleay32.dll

Are there any other executables besides pg_dump I need to add to this list?

If some of them are likely to come in useful, rather than necessary
for backup I don't mind adding them

Regards

/voipfc

-- 
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] Partial indexes instead of partitions

[Sergey Konoplev]

On 14 June 2010 13:24, Leonardo F  wrote:
>> For "inserts" I do not see the reason
>> why
>> it would be better to use index partitioning because AFAIK
>> b-tree
>> would behave exactly the same in both cases.
>
> no, when the index gets very big inserting random values gets
> very slow.

Hm, interesting. Could you please provide some references/links?


-- 
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802

-- 
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] Is there a way to backup Postgres via SQL commands?

[A. Kretschmer]

In response to Frank Church :
> Are there SQL commands that can do a backup over a client connection,
> rather than from the command line like pgsql etc?
> 
> By that I mean some kind of SELECT commands that can retrieve the
> database's content as SQL commands that can be replayed to a server to
> restore it, rather than something that saves directly to file, or
> passes it through a pipe?

What's wrong with pg_dump?

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

[GENERAL] Is there a way to backup Postgres via SQL commands?

[Frank Church]

Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?

By that I mean some kind of SELECT commands that can retrieve the
database's content as SQL commands that can be replayed to a server to
restore it, rather than something that saves directly to file, or
passes it through a pipe?


/voipfc

-- 
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] How to show the current schema or search path in the psql PROMP

[Alban Hertroys]

On 14 Jun 2010, at 12:14, Schwaighofer Clemens wrote:

> On Mon, Jun 14, 2010 at 19:10, Alban Hertroys
>  wrote:
>> On 14 Jun 2010, at 2:02, Clemens Schwaighofer wrote:
>> 
>>> Right now I added two simple wrappers in my .psqlrc
>>> 
>>> \set shsh 'SHOW search_path;'
>>> \set setsh 'SET search_path TO'
>>> 
>>> So I can at least set and check the schema more quickly.
>> 
>> 
>> That only saves you a few key-presses though. Is your psql built with 
>> libreadline? Without it you don't get TAB-completion, that would be a 
>> nuisance!
> 
> even with readline it is quicker to type :shsh to see the current
> schema thant to type full thing.


Do you realise you only save 3 key-presses there, compared to 
"sh[TAB]sea[TAB];"? That's a rhetorical question btw, it's entirely up to you 
to define any short-cuts you want.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c160855286213993713473!



-- 
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] How to show the current schema or search path in the psql PROMP

[Schwaighofer Clemens]

On Mon, Jun 14, 2010 at 19:10, Alban Hertroys
 wrote:
> On 14 Jun 2010, at 2:02, Clemens Schwaighofer wrote:
>
>> Right now I added two simple wrappers in my .psqlrc
>>
>> \set shsh 'SHOW search_path;'
>> \set setsh 'SET search_path TO'
>>
>> So I can at least set and check the schema more quickly.
>
>
> That only saves you a few key-presses though. Is your psql built with 
> libreadline? Without it you don't get TAB-completion, that would be a 
> nuisance!

even with readline it is quicker to type :shsh to see the current
schema thant to type full thing.


-- 
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp


This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is 
privileged, confidential and/or otherwise protected from disclosure. 
If you received this e-mail in error, any review, use, dissemination,
distribution or copying of this e-mail is strictly prohibited.   
Please notify us immediately of the error via e-mail to 
disclai...@tbwaworld.com and please delete the e-mail from your system, 
retaining no copies in any media.
We appreciate your cooperation.


-- 
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] How to show the current schema or search path in the psql PROMP

[Alban Hertroys]

On 14 Jun 2010, at 2:02, Clemens Schwaighofer wrote:

> Right now I added two simple wrappers in my .psqlrc
> 
> \set shsh 'SHOW search_path;'
> \set setsh 'SET search_path TO'
> 
> So I can at least set and check the schema more quickly.


That only saves you a few key-presses though. Is your psql built with 
libreadline? Without it you don't get TAB-completion, that would be a nuisance!

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4c160039286219954085656!



-- 
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] checkpoint spikes

[Janning]

Hi Martijn, hi Greg,

thanks you very much for your help. We finally got rid of these annoying 
spikes.

First we tried to set 
  checkpoint_segments = 3# before 16
  checkpoint_timeout = 5min  # before: 60min
which didn't really help.

we had the same spikes but more often. Then we tried to lower 
dirty_writeback_centisecs from 500 to 100

This helped a little bit, but we investigated this problem further.

we monitored "Dirty" memory from /proc/meminfo and we saw very long query 
durations (>5s, sometimes 10s and more) correlating with the kernel writing 
out Dirty buffer. When we saw a massive reduction in "Dirty" memory, there were 
spikes in the query duration. As long as the kernel didn't write out the dirty 
memory, everything did run fine.

So finally we tried 
echo 0 > /proc/sys/vm/dirty_background_ratio

and now everything runs very smooth. We see a few longer query durations over 
2 seconds but no more spikes of 5 or 10 seconds.

Our average response time from our our tomcat servers suddenly dropped from 
300ms to 100ms. Great!!

We know that our limitation is cheap disks, but with /dirty_background_ratio = 
0 you really have big advantages and much better performance. 

So for further reference for other people reading this thread, I really 
recommend trying this out. 

best regards
Janning

On Friday 11 June 2010 21:48:54 Martijn van Oosterhout wrote:
> On Thu, Jun 10, 2010 at 04:00:54PM -0400, Greg Smith wrote:
> >> 5. Does anybody know if I can set dirty_background_ratio to 0.5? As we
> >> have 12 GB RAM and rather slow disks 0,5% would result in a maximum of
> >> 61MB dirty pages.
> >
> > Nope.  Linux has absolutely terrible controls for this critical
> > performance parameter.   The sort of multi-second spikes you're seeing
> > are extremely common and very difficult to get rid of.
>
> Another relevent parameter is /proc/sys/vm/dirty_writeback_centisecs.
> By default linux only wakes up once every 5 seconds to check if there
> is stuff to write out. I have found that reducing this tends to smooth
> out bursty spikes. However, see:
>
> http://www.westnet.com/~gsmith/content/linux-pdflush.htm
>
> which indicates that kernel may try to defeat you here...
>
> Have a nice day,


-- 
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] Partial indexes instead of partitions

[Leonardo F]

> For "inserts" I do not see the reason 
> why
> it would be better to use index partitioning because AFAIK 
> b-tree
> would behave exactly the same in both cases.

no, when the index gets very big inserting random values gets
very slow.

But still, my approach doesn't work because I thought Postgresql
was able to "merge" different partial indexes (using 
BitmapOr/BitmapAnd) when the WHERE condition matches multiple
partial indexes... but that's (I guess) not that easy to do

(basically partial indexes condition should be checked like the
constraint of the inheritance mechanism, and Or/And Bitmapped
as happens in the "regular" partitioning method).

that is, having a table with:


CREATE TABLE test
(
a timestamp without time zone,
b integer
)


CREATE INDEX test1idx
  ON test
  (b) 
  WHERE a >= '2008-03-10 14:00:00' AND a < '2008-03-10 16:00:00';

CREATE INDEX test2idx
  ON test
  (b)
  WHERE a >= '2008-03-10 16:00:00' AND a < '2008-03-10 18:00:00';


the select:

select * from test where a > '2008-03-10 15:00:00' 
   and a < '2008-03-10 17:00:00'  and b = 100

should use a BitmapOr between an index scan on test1idx and test2idx...

But I think that's a complicated thing for the planner... even though
it doesn't sound that different from what the planner does for partition
pruning...




-- 
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 14, 2010 at 10:57, Scott Marlowe  wrote:
> On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander  wrote:
>> On Mon, Jun 14, 2010 at 05:17, John T. Dow  wrote:
>>> Apparently the problem boils down to this question: how did some of the 
>>> files get set to be system and read only?
>>
>> Yes. That would be very interesting to know. PostgreSQL never
>> (intentionally) sets these flags, so they must've come from something
>> else.
>
> Being a non-privaledged account, does the postgres user even have the
> power to do that?

Yes, IIRC any user that has write permissions on a file can set the
attributes, including readonly and system.


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

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

[Scott Marlowe]

On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander  wrote:
> On Mon, Jun 14, 2010 at 05:17, John T. Dow  wrote:
>> Apparently the problem boils down to this question: how did some of the 
>> files get set to be system and read only?
>
> Yes. That would be very interesting to know. PostgreSQL never
> (intentionally) sets these flags, so they must've come from something
> else.

Being a non-privaledged account, does the postgres user even have the
power to do that?

-- 
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] shared_buffer advice

[Scott Marlowe]

On Mon, Jun 14, 2010 at 1:36 AM, AI Rumman  wrote:
> I have a server with Dual-Core 4 cpu and 32 GB RAM.
> This is the database server. Only Postgresql 8.1 is running on it with
> multiple databases.
>
> How should I plan for shared_buffers and effective cache size?
> Any idea please.

Note that effective cache size is way simpler.  Add up the size of the
kernel cache and shared_buffers, set it to that if db is the only
thing on the machine.  If it shares it then maybe cut it down a bit.
It's not a real sensitive setting to a few gigabytes here or there,
and allocates nothing, just helps the planner decide teh % chance
something is in cache or not.

-- 
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 14, 2010 at 05:17, John T. Dow  wrote:
> I was talking to a friend (Joe Newcomer) who said that Unix doesn't have 
> mandatory file locks and he guessed that the empty, system, read only files I 
> saw at my client's site were unix-like lock files.

They are not. They are regular relation files.


> To test that, on my home development computer I typed this command in the 
> base\16384 diretory:
>
> attrib +r 2611
>
> That is, I made 2611 read only.
>
> Sure enough, pgadmin can't display the columns for any of the tables. I get 
> "permission denied" for 2611.
>
> And sure enough, the Java application runs fine and indeed is able to export 
> the table definition, complete with columns.

Most likely because pgadmin tries to fetch all information about the
table, including toast relations, whereas the java application only
fetches the information it actually needs.


> So this is exactly the behavior observed at my client's site.
>
> Apparently the problem boils down to this question: how did some of the files 
> get set to be system and read only?

Yes. That would be very interesting to know. PostgreSQL never
(intentionally) sets these flags, so they must've come from something
else.

If you remove those flags, do they eventually come back on? Is so, you
probably want to install some level of monitoring tool (process
monitor from sysinternals is recommended) to figure out when that gets
set.


> Anybody ever seen this?

Well, at the risk of sounding like a broken clock, yes - with
antivirus or antispyware that sets the flag on things they find
suspicious.


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

Re: [GENERAL] shared_buffer advice

[Scott Marlowe]

On Mon, Jun 14, 2010 at 1:36 AM, AI Rumman  wrote:
> I have a server with Dual-Core 4 cpu and 32 GB RAM.
> This is the database server. Only Postgresql 8.1 is running on it with
> multiple databases.
>
> How should I plan for shared_buffers and effective cache size?
> Any idea please.

Well, that really depends on what you're doing with this machine.  If
you're maintaining a small, high turn over db (say < 1Gig) then
setting shared_buffers real high won't help at all, and may hurt
performance.  Your OS matters.  Most observed behaviour on windows
says that having a large shared_buffers doesn't help and may in fact
hurt performance, no matter how big your dataset.

The general rule of thumb I use is to model how much data you've got
being operated on at a time, in memory, and make sure it's bigger than
that, if you can.  I.e. if we'd have say 1G of data being operated on
at once, then I'd want 2G to 4G of shared_buffers so I'd be sure it
always fit into ram and that interleaved accesses won't bump each
other out of the shared_buffers.  Note that this is on a database much
bigger than 1G itself, it's just that all the various live connections
at any one time into it might be operating on about 1G at once.

Note that on another server that handles sessions, the shared_buffers
are something like 512Megs.  No fsync, as they're completely
replaceable at any time with a config change / auto failover.  They
need to make updates fast, every time.  And they get a lot of updates
on the same records all the time, but they're all small records, in
the 1K to 2K range.  Having a large shared_buffers here just makes
those machines slower.  And with fsync off the major limit on commit
speed was the size of shared_buffers to keep bg writing.

So, what are you doing with your servers?

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

[Massa, Harald Armin]

John,


> It's the user created by the one-click installer. I believe it owns the
> postgres data directory and is used to start the server. Other than that,
> the intention is for this user to have no other file privileges. The default
> is "postgres" but it could be anything.
>
> doing the default install, the installer uses cacls to correctly set the
access privileges to the database files for the created user.

AS the database directory was zipped and copied from computer to computer;
something different happened  I know it is possible to transfer NTFS
files keeping their ACL, but I have no information HOW the files were
transferred.

HOW containing information as in: - under which user account - using which
method - preserving or not preserving ACLs

>
> >Are there any group-policies or similar, or "security-applications"
> present,
> >which can change the rights of this user postgres? (Or, can change the
> >access-properties of files on the system?)
>
> I don't know. It is not my computer, it is my client's computer. We will
> investigate if anything like that is going on. He was only available until
> 4PM today and we just discovered what was happening shortly before that
> point. The people that do their security should be available Monday and we
> can ask them this type of question.
>

> please check out the "cacls" command line utitlity of windows. With this
you should be able to  print out all privileges of the PostgreSQL data
directory to a text file, which can be transferred to you. You can then
compare the privileges of the files on the non-working computer with the
working computer.

You can especially check the privileges for the toast-files (the ones named
in the error message)

>Any idea of what to look for?

when working with the first PostgreSQL versions on windows, I was surprised
by a group policy randomly taking away the "run as service" privilege for
the local user. Just want to point out that system-level changes which can
affect PostgreSQL WITHOUT anything in PostgreSQL.

That somebody was me, experimenting over the years. But I have not been
> messing around with this particular application. However, I'm not sure what
> the client did, as they copied the data files between the two computers at a
> time when I wasn't available. (They zipped, then unzipped after logging in
> as the proper user.)
>
> Okay, that experimenting is good thing to do :) on development systems.


> As a developer for multiple clients, I need easy access to my development
> copies of my clients' postgres data files. Therefore I have experimented
> with allowing my own userid to have access to the "data" directory and the
> subdirectories and files. I believe postgres doesn't care if you allow extra
> users, as long as "postgres" still has the proper access.
>
> Postgres does not even know about extra access privileges. Only the
installer does something with access rights during database installation;
after that everything changing the access permissions is from outside.

(One possible scenario: the postgres service being started with its
authorization set to "local system" - that would explain your files with
owner "system". And "local system" (or similar) is the default for SQL
Server and Oracle ... the danger that one good-willing local administrator
changed the logon-credentials?)

Harald


> John
>
> >
> >Harald
> >
> >
> >--
> >GHUM Harald Massa
> >persuadere et programmare
> >Harald Armin Massa
> >Spielberger StraAYe 49
> >70435 Stuttgart
> >0173/9409607
> >no fx, no carrier pigeon
> >-
> >Using PostgreSQL is mostly about sleeping well at night.
> >
>
>
>


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
Using PostgreSQL is mostly about sleeping well at night.

[GENERAL] shared_buffer advice

[AI Rumman]

I have a server with Dual-Core 4 cpu and 32 GB RAM.
This is the database server. Only Postgresql 8.1 is running on it with
multiple databases.

How should I plan for shared_buffers and effective cache size?
Any idea please.