date:20120613

Re: [HACKERS] xml_is_document and selective pg_re_throw

2012-06-13 Thread Nikhil Sontakke

 No, I don't see any particular risk there.  The places that might throw
 ERRCODE_INVALID_XML_DOCUMENT are sufficiently few (as in, exactly one,
 in this usage) that we can have reasonable confidence we know what the
 system state is when we catch that error.


Hmmm, I was writing some code in which I happened to hold a LWLock when
this function was called. The first catch/rethrow cleaned up the
InterruptHoldoffCount value. A subsequent release of that LWLock tripped up
the (Assert(InterruptHoldoffCount  0);) inside RESUME_INTERRUPTS().

I know holding an lwlock like this might not be a good idea, but this
behavior just got me thinking about other probable issues.

Regards,
Nikhils


  A better way would have been to modify xml_parse to take an additional
  boolean argument to_rethrow and not to rethrow if that is false?

 We could do that, but it would greatly complicate xml_parse IMO, since
 it still needs its own PG_TRY block to handle other error cases, and
 only one of those error cases ought to optionally return failure instead
 of re-throwing.

regards, tom lane

Re: [HACKERS] hint bit i/o reduction

2012-06-13 Thread Amit Kapila

 1) Keep track # times the last transaction id was repeatedly seen in
 tqual.c (resetting as soon as a new xid was touched.  We can do this
 just for xmin, or separately for both xmin and xmax.

Will this be done when we see a new xid duricg scan of tuple, if yes then
Won't this logic impact incase there are very few records per transaction in
database.
As in that case the new optimization won't help much, but it adds the new
instructions.

 We can also try to be smart and disable
 the 'avoid setting the hint bit' logic if the page is already dirty.

Whats the benefit for not setting hint bit for a already dirty page. 

-Original Message-
From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Merlin Moncure
Sent: Wednesday, June 13, 2012 4:28 AM
To: PostgreSQL-development
Subject: [HACKERS] hint bit i/o reduction

hackers,

A while back I made an effort implementing a backend local hint bit
cache with the intention of mitigating i/o impact for scan heavy
workloads that involve moving a lot of records around per transaction.
 The basic concept was to keep some backend private memory that
tracked the resolution of recently seen transactions and worked in a
similar fashion to the hint bits:

if (!commit_hint_bit_set  hint_bit_cache(xid) == committed)
{

The other interesting aspect was that, if the bit was found in the
cache, the bit was set on the tuple but the page was not dirtied.  If
the xid was not found in the cache, the bit was set and the page was
dirtied normally.  From a strictly performance standpoint, limited
testing seemed to indicate it more or less fixed the hint bit i/o
problems.  Still, injecting extra logic (especially a cache lookup)
into the visibility routines is not to be taken lightly, and maybe
there's a better way.  There simply has to be a way to ameliorate hint
bit i/o without spending a lot of cycles and hopefully not too much
impacting other workloads.  Unfortunately, assuming the CRC patch
makes it in, any of the branch of tricks in the line of 'set the bit
but avoid dirtying the page' aren't going to fly.  I think, at the end
of the day, we have to avoid setting the bit, but only in cases where
we are fairly sure we would prefer not to do that.

I'm thinking something fairly simple would get some good mileage:

1) Keep track # times the last transaction id was repeatedly seen in
tqual.c (resetting as soon as a new xid was touched.  We can do this
just for xmin, or separately for both xmin and xmax.
2) right after checking the hint bit (assuming it wasn't set), test to
see if our xid is the 'last xid', and iff #times_seen_same 
#some_number (say, 100 or 1000), use it as if the hint bit was set.
#some_number can be chosen to some fairly conservative defined value,
or perhaps we can be sneaky and try and adjust it based on things like
how many unforced clog faults we're doing -- maybe even keeping some
accounting at the page level.  We can also try to be smart and disable
the 'avoid setting the hint bit' logic if the page is already dirty.

The presumption here is that if you're seeing the same xid over and
over, there simply isn't a lot of value in recording it in page after
page after page.  It's tempting to suggest that there is already an
'is this xid the same as the last one AKA last_transaction_id' at the
clog visibility checking level, but that doesn't help for the
following reasons:

1) while it does avoid the jump out to clog, it occurs at the wrong
place in the sequence of visibility checking (after you've wasted
cycles in TransactionIdIsInProgress() etc) and
2) by being in the clog level can't influence visibility behaviors of
whether or not to set the bit.
3) it's not inline

I see two potential downsides here:
1) maybe the extra logic in visibility is too expensive (but i doubt it)
2) you're missing early opportunities to set the all visible bit which
in turn will influence index only scans.

The above might be a small price to pay for saving all the i/o and
sooner or later if the records are long lived vacuum will swing around
and tag them.

merlin

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


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

Re: [HACKERS] Minimising windows installer password confusion

2012-06-13 Thread Dave Page

On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:

 Users don't remember passwords, though. It's one of those constants, and is
 why practically every web site etc out there offers password recovery.

 The installer IMO needs to store the postgres account password in a registry
 key with permissions set so that only users with local admin rights (ie: who
 can use the installer) can view it. I don't like the idea of storing a
 password, but it's only going to be accessible if you already have rights to
 the registry as local admin, in which case the attacker can just reset it
 themselves (or root your machine). So long as they installer warns that the
 password shouldn't be one you use elsewhere because it can be recovered from
 your computer, I don't see a problem.---

The idea of storing the password in clear text in the registry gives
me nervous twitches. Whilst is should be secure if done as you
suggest, a) a simple mistake could leave it vulnerable and give us an
embarrassing security issue to deal with. It also doesn't help us in
the cases where users have another installation of PostgreSQL from
somewhere that doesn't store the password (which is likely to be the
case for years to come, even if it was our installer that was used
previously).

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Minimising windows installer password confusion

2012-06-13 Thread Dave Page

On Wed, Jun 13, 2012 at 2:18 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:
 On 06/12/2012 08:08 PM, Dave Page wrote:

 Some background: By default the installer will use 'postgres' for both
 the service (OS) account, and the database superuser account. It will
 use the same password for both (though, users have complete control at
 the command line if they want it, which is why the account names are
 shown in the installer). Unlike *nix installations, the service
 account must have a password, which is required during both
 installation and upgrade to configure the PostgreSQL service. We
 therefore ask for the password during both installation and upgrade.
 If the user account exists (which can happen if there has previously
 been an installation of Postgres on the system), the user must specify
 the existing password for the account during installation (and of
 course, during all upgrades). This is where users normally get stuck,
 as they've forgotten the password they set in the past.

 They may not even have set it, because the EnterpriseDB installer can be run
 unattended and may've been used by some 3rd party package.

 I'd be interested in seeing what Microsoft installers that create isolated
 user accounts do. I think .NET creates one for ASP, so that'd be one option
 to look into.

They tend to use the localsystem or networkservice accounts for most
things, which don't have passwords. The reason we don't do that is
that since the early days of 8.0 we've said we want to enable users to
use the service account as if it were a regular account, so that they
can do things like access network resources (useful for server-side
copy for example).

I wasn't overly convinced back then that that was necessary, and I'm
still not now. I suppose we potentially could use the networkservice
account by default, and let advanced users override that on the
command line if they need to. Then remembering the password becomes
their responsibility.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[HACKERS] errdetail_busy_db plural forms

2012-06-13 Thread Peter Eisentraut

I was looking for missing use of gettext plural forms, which led me to
errdetail_busy_db().

While we can't do much about this:

errdetail(There are %d other session(s) and %d prepared transaction(s) 
using the database.,
  notherbackends, npreparedxacts);

I think it's still worth pluralizing the other cases

errdetail(There are %d other session(s) using the database.,
  notherbackends);

and

errdetail(There are %d prepared transaction(s) using the database.,
  npreparedxacts);

Especially the other sessions case is probably the one most seen by users.

So I propose the attached patch.

diff --git i/src/backend/commands/dbcommands.c w/src/backend/commands/dbcommands.c
index b7224bd..c9b80ad 100644
--- i/src/backend/commands/dbcommands.c
+++ w/src/backend/commands/dbcommands.c
@@ -1804,20 +1804,21 @@ static bool get_db_info(const char *name, LOCKMODE lockmode,
 static int
 errdetail_busy_db(int notherbackends, int npreparedxacts)
 {
-	/*
-	 * We don't worry about singular versus plural here, since the English
-	 * rules for that don't translate very well.  But we can at least avoid
-	 * the case of zero items.
-	 */
 	if (notherbackends  0  npreparedxacts  0)
+		/* We don't deal with singular versus plural here, since gettext
+		 * doesn't support multiple plurals in one string. */
 		errdetail(There are %d other session(s) and %d prepared transaction(s) using the database.,
   notherbackends, npreparedxacts);
 	else if (notherbackends  0)
-		errdetail(There are %d other session(s) using the database.,
-  notherbackends);
+		errdetail_plural(There is %d other session using the database.,
+		 There are %d other sessions using the database.,
+		 notherbackends,
+		 notherbackends);
 	else
-		errdetail(There are %d prepared transaction(s) using the database.,
-  npreparedxacts);
+		errdetail_plural(There is %d prepared transaction using the database.,
+		 There are %d prepared transactions using the database.,
+		 npreparedxacts,
+		 npreparedxacts);
 	return 0;	/* just to keep ereport macro happy */
 }
 

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

Re: [HACKERS] Minimising windows installer password confusion

2012-06-13 Thread Dave Page

On Wed, Jun 13, 2012 at 3:07 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:
 On 06/13/2012 01:19 AM, Sachin Srivastava wrote:


 On Tue, Jun 12, 2012 at 7:43 PM, Dave Page dp...@pgadmin.org
 mailto:dp...@pgadmin.org wrote:

    On Tue, Jun 12, 2012 at 2:57 PM, Robert Haas robertmh...@gmail.com
    mailto:robertmh...@gmail.com wrote:


      What we need is to display a different dialogue based on the
    situation.
     
      If the account already exists, we should say Please enter the
      password for the existing postgres account.  If you do not know the
      password, you can reset it using the Windows control panel.


 Why using the windows control panel ?

Because when I wrote the email I was looking for a simple solution
that wouldn't require writing code that has potential to fail
depending on how the users environment is configured (the user account
stuff tends to go wrong in weird ways, for example when used on
domains in unusual (or high security) configurations. We're spending a
lot of effort at the moment getting the 9.2 buildfarm together, and
updating all the StackBuilder add-on packages (think multiple
man-months) - I'm trying not to add to that too much.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Minimising windows installer password confusion

2012-06-13 Thread Florian Pflug

On Jun13, 2012, at 11:10 , Dave Page wrote:
On Wed, Jun 13, 2012 at 2:12 AM, Craig Ringer
cr...@postnewspapers.com.au wrote:

Users don't remember passwords, though. It's one of those constants, and is
why practically every web site etc out there offers password recovery.

The installer IMO needs to store the postgres account password in a registry
key with permissions set so that only users with local admin rights (ie: who
can use the installer) can view it. I don't like the idea of storing a
password, but it's only going to be accessible if you already have rights to
the registry as local admin, in which case the attacker can just reset it
themselves (or root your machine). So long as they installer warns that the
password shouldn't be one you use elsewhere because it can be recovered from
your computer, I don't see a problem.---

The idea of storing the password in clear text in the registry gives
me nervous twitches. Whilst is should be secure if done as you
suggest, a) a simple mistake could leave it vulnerable and give us an
embarrassing security issue to deal with. It also doesn't help us in
the cases where users have another installation of PostgreSQL from
somewhere that doesn't store the password (which is likely to be the
case for years to come, even if it was our installer that was used
previously).

Hm, doesn't the registry already contain the postgres service account's
password? AFAIR, on windows you cannot really impersonate an account without
knowing it's password, which is the reason why a) the password of a user
account is stored in the registry if you enable auto-logon and b) you need
to know the service account's password to create a service.

Some googling brought up a tool called isvcpwd[1] which seems to be able to
change service account passwords without breaking services. Judging from a brief
glance over the source code, it does so by iterating over all services
domain-wide, and adjusting the service definition of those which rely on the
modified account(s). So that seems to support the theory that the passwords
are stored in the individual machine's registries.

Some further googling indicates that, yes, the service account passwords
are stored in the registry, but are only accessible to the LocalSystem
account [2]. Querying them from the postgres installer thus isn't really an
option. But what you could do, I guess, is to offer the user the ability to
change the password, and using the approach from [1] to update the affected
service definitions afterwards.

best regards,
Florian Pflug

[1] https://www.itefix.no/i2/isvcpwd
[2]
http://www.windowsnetworking.com/kbase/windowstips/windowsnt/registrytips/miscellaneous/LSASecrets.html

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

Re: [HACKERS] initdb and fsync

2012-06-13 Thread Peter Eisentraut

On tis, 2012-06-12 at 21:09 -0700, Jeff Davis wrote:
 On Sun, 2012-03-25 at 19:59 -0700, Jeff Davis wrote:
  On Sat, 2012-03-17 at 17:48 +0100, Cédric Villemain wrote:
   I agree with Andres.
   
   
   I believe we should use sync_file_range (_before?) with linux.
   
   And we can use posix_fadvise_dontneed on other kernels.
   
  OK, updated patch attached. sync_file_range() is preferred,
  posix_fadvise() is a fallback.
  
 
 Rebased patch attached. No other changes.

The --help output for the -N option was copy-and-pasted wrongly.

The message issued when using -N is also a bit content-free.  Maybe
something like

Running in nosync mode.  The data directory might become corrupt if the
operating system crashes.\n

Which leads to the question, how does one get out of this state?  Is
running sync(1) enough?  Is starting the postgres server enough?

There are no updates to the initdb man page included in your patch,
which would be a suitable place to discuss this briefly.



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

1 2 >

1 - 100 of 105 matches

Mail list logo