Re: [HACKERS] SSI bug?

2011-04-11 Thread YAMAMOTO Takashi
hi,

 hi,
 
 I think I see what is going on now. We are sometimes failing to set the
 commitSeqNo correctly on the lock. In particular, if a lock assigned to
 OldCommittedSxact is marked with InvalidSerCommitNo, it will never be
 cleared.
 
 The attached patch corrects this:
  TransferPredicateLocksToNewTarget should initialize a new lock
  entry's commitSeqNo to that of the old one being transferred, or take
  the minimum commitSeqNo if it is merging two lock entries.
 
  Also, CreatePredicateLock should initialize commitSeqNo for to
  InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would
  actually affect anything, but we should be consistent.)
 
  I also added a couple of assertions I used to track this down: a
  lock's commitSeqNo should never be zero, and it should be
  InvalidSerCommitSeqNo if and only if the lock is not held by
  OldCommittedSxact.
 
 Takashi, does this patch fix your problem with leaked SIReadLocks?
 
 i'm currently running bf6848bc8c82e82f857d48185554bc3e6dcf1013 with this
 patch applied.  i haven't seen the symptom yet.  i'll keep it running for
 a while.

i haven't seen the symptom since them.  so i guess it was fixed by
your patch.  thanks!

YAMAMOTO Takashi

 
 btw, i've noticed the following message in the server log.  is it normal?
 
 LOG:  could not truncate directory pg_serial: apparent wraparound
 
 YAMAMOTO Takashi
 
 
 Dan
 
 
 -- 
 Dan R. K. Ports  MIT CSAILhttp://drkp.net/
 
 -- 
 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] SSI bug?

2011-04-11 Thread Heikki Linnakangas

On 11.04.2011 11:33, Heikki Linnakangas wrote:

I also noticed that there's a few hash_search(HASH_ENTER) calls in
predicate.c followed by check for a NULL result. But with HASH_ENTER,
hash_search never returns NULL, it throws an out of shared memory
error internally. I changed those calls to use HASH_ENTER_NULL, so you
now get the intended error message with the hint to raise
max_pred_locks_per_transaction.


Oops, those were already fixed. Never mind.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] Transforming IN (...) to ORs, volatility

2011-04-11 Thread Heikki Linnakangas

On 05.04.2011 18:42, Heikki Linnakangas wrote:

On 05.04.2011 13:19, Marti Raudsepp wrote:

On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:

We sometimes transform IN-clauses to a list of ORs:

postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))

But what if you replace a with a volatile function? It doesn't seem
legal
to do that transformation in that case, but we do it:

postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN
(b, c);
QUERY PLAN

Seq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: random() * 2::double precision))::integer = b) OR
(((random()
* 2::double precision))::integer = c))


Is there a similar problem with the BETWEEN clause transformation into
AND expressions?

marti= explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()= 0.25::double precision) AND (random()=
0.75::double precision))


Yes, good point.


Hmm, the SQL specification explicitly says that

X BETWEEN Y AND Z

is equal to

X = Y AND X = Z

It doesn't say anything about side-effects of X. Seems like an oversight 
in the specification. I would not expect X to be evaluated twice, and I 
think we should change BETWEEN to not do that.



Does anyone object to making BETWEEN and IN more strict about the data 
types? At the moment, you can do this:


postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
 ?column?
--
 t
(1 row)

I'm thinking that it should throw an error. Same with IN, if the values 
in the IN-list can't be coerced to a common type. That will probably 
simplify the code a lot, and is what the SQL standard assumes anyway AFAICS.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] switch UNLOGGED to LOGGED

2011-04-11 Thread Leonardo Francalanci
  But re-reading  it, I don't understand: what's the difference in creating
  a new  regular table and crashing before emitting the abort record,
  and  converting an unlogged table to logged and crashing before
  emitting the  abort record? How do the standby servers handle a
  CREATE TABLE  followed by a ROLLBACK if the master crashes
  before writing the abort  record? I thought that too would leave a
  stray file around on a  standby.
 
 I've been thinking about the same thing.  And AFAICS, your  analysis is
 correct, though there may be some angle to it I'm not  seeing.


Anyone else? I would like to know if what I'm trying to do is, in fact,
possible... otherwise starting with thewal_level=minimal case first
will be wasted effort in case the other cases can't be integrated
somehow...



Leonardo

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


[HACKERS] developer.postgresql.org down

2011-04-11 Thread Albert Cervera i Areny
Maybe already known or in scheduled maintenance but developer.postgresql.org 
seems to be down right now.

-- 
Albert Cervera i Areny
http://www.NaN-tic.com
OpenERP Partners
Tel: +34 93 553 18 03
skype: nan-oficina

http://twitter.com/albertnan 
http://www.nan-tic.com/blog


Re: [HACKERS] developer.postgresql.org down

2011-04-11 Thread Marc G. Fournier


Everything should be back up and running now ... sorry for delay ...

On Mon, 11 Apr 2011, Albert Cervera i Areny wrote:



Maybe already known or in scheduled maintenance but developer.postgresql.org
seems to be down right now.


--

Albert Cervera i Areny

http://www.NaN-tic.com

OpenERP Partners

Tel: +34 93 553 18 03

skype: nan-oficina

http://twitter.com/albertnan

http://www.nan-tic.com/blog






Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
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] SSI bug?

2011-04-11 Thread Heikki Linnakangas

On 31.03.2011 22:06, Kevin Grittner wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com  wrote:


That's not enough. The hash tables can grow beyond the maximum
size you specify in ShmemInitHash. It's just a hint to size the
directory within the hash table.

We'll need to teach dynahash not to allocate any more entries
after the preallocation. A new HASH_NO_GROW flag to hash_create()
seems like a suitable interface.


OK.  If we're doing that, is it worth taking a look at the safety
margin added to the size calculations, and try to make the
calculations more accurate?

Would you like me to code a patch for this?


I finally got around to look at this. Attached patch adds a 
HASH_FIXED_SIZE flag, which disables the allocation of new entries after 
the initial allocation. I believe we have consensus to make the 
predicate lock hash tables fixed-size, so that there's no competition of 
the slack shmem space between predicate lock structures and the regular 
lock maanager.


I also noticed that there's a few hash_search(HASH_ENTER) calls in 
predicate.c followed by check for a NULL result. But with HASH_ENTER, 
hash_search never returns NULL, it throws an out of shared memory 
error internally. I changed those calls to use HASH_ENTER_NULL, so you 
now get the intended error message with the hint to raise 
max_pred_locks_per_transaction.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index 401acdb..6ff41fc 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -959,17 +959,15 @@ InitPredicateLocks(void)
 {
 	HASHCTL		info;
 	int			hash_flags;
-	long		init_table_size,
-max_table_size;
+	long		max_table_size;
 	Size		requestSize;
 	bool		found;
 
 	/*
-	 * Compute init/max size to request for predicate lock target hashtable.
+	 * Compute size of predicate lock target hashtable.
 	 * Note these calculations must agree with PredicateLockShmemSize!
 	 */
 	max_table_size = NPREDICATELOCKTARGETENTS();
-	init_table_size = max_table_size / 2;
 
 	/*
 	 * Allocate hash table for PREDICATELOCKTARGET structs.  This stores
@@ -980,17 +978,16 @@ InitPredicateLocks(void)
 	info.entrysize = sizeof(PREDICATELOCKTARGET);
 	info.hash = tag_hash;
 	info.num_partitions = NUM_PREDICATELOCK_PARTITIONS;
-	hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);
+	hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION | HASH_FIXED_SIZE);
 
 	PredicateLockTargetHash = ShmemInitHash(PREDICATELOCKTARGET hash,
-			init_table_size,
+			max_table_size,
 			max_table_size,
 			info,
 			hash_flags);
 
 	/* Assume an average of 2 xacts per target */
 	max_table_size *= 2;
-	init_table_size *= 2;
 
 	/*
 	 * Reserve an entry in the hash table; we use it to make sure there's
@@ -1011,18 +1008,17 @@ InitPredicateLocks(void)
 	info.entrysize = sizeof(PREDICATELOCK);
 	info.hash = predicatelock_hash;
 	info.num_partitions = NUM_PREDICATELOCK_PARTITIONS;
-	hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION);
+	hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_PARTITION | HASH_FIXED_SIZE);
 
 	PredicateLockHash = ShmemInitHash(PREDICATELOCK hash,
-	  init_table_size,
+	  max_table_size,
 	  max_table_size,
 	  info,
 	  hash_flags);
 
 	/*
-	 * Compute init/max size to request for serializable transaction
-	 * hashtable. Note these calculations must agree with
-	 * PredicateLockShmemSize!
+	 * Compute size for serializable transaction hashtable.
+	 * Note these calculations must agree with PredicateLockShmemSize!
 	 */
 	max_table_size = (MaxBackends + max_prepared_xacts);
 
@@ -1093,7 +1089,7 @@ InitPredicateLocks(void)
 	info.keysize = sizeof(SERIALIZABLEXIDTAG);
 	info.entrysize = sizeof(SERIALIZABLEXID);
 	info.hash = tag_hash;
-	hash_flags = (HASH_ELEM | HASH_FUNCTION);
+	hash_flags = (HASH_ELEM | HASH_FUNCTION | HASH_FIXED_SIZE);
 
 	SerializableXidHash = ShmemInitHash(SERIALIZABLEXID hash,
 		max_table_size,
@@ -2045,7 +2041,7 @@ CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
 	target = (PREDICATELOCKTARGET *)
 		hash_search_with_hash_value(PredicateLockTargetHash,
 	targettag, targettaghash,
-	HASH_ENTER, found);
+	HASH_ENTER_NULL, found);
 	if (!target)
 		ereport(ERROR,
 (errcode(ERRCODE_OUT_OF_MEMORY),
@@ -2060,7 +2056,7 @@ CreatePredicateLock(const PREDICATELOCKTARGETTAG *targettag,
 	lock = (PREDICATELOCK *)
 		hash_search_with_hash_value(PredicateLockHash, locktag,
 			PredicateLockHashCodeFromTargetHashCode(locktag, targettaghash),
-	HASH_ENTER, found);
+	HASH_ENTER_NULL, found);
 	if (!lock)
 		ereport(ERROR,
 (errcode(ERRCODE_OUT_OF_MEMORY),
@@ -3251,7 +3247,7 @@ ReleaseOneSerializableXact(SERIALIZABLEXACT *sxact, bool partial,
 			predlock = hash_search_with_hash_value(PredicateLockHash, tag,
 

Re: [HACKERS] Transforming IN (...) to ORs, volatility

2011-04-11 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
 On 05.04.2011 18:42, Heikki Linnakangas wrote:
 On 05.04.2011 13:19, Marti Raudsepp wrote:
 On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
 heikki.linnakan...@enterprisedb.com wrote:
 We sometimes transform IN-clauses to a list of ORs:

 postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
 QUERY PLAN
 Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
 Filter: ((a = b) OR (a = c))

 But what if you replace a with a volatile function? It
 doesn't seem legal to do that transformation in that case, but
 we do it:

 postgres=# explain SELECT * FROM foo WHERE
 (random()*2)::integer IN (b, c);
 QUERY PLAN

 Seq Scan on foo (cost=0.00..68.20 rows=19 width=12)
 Filter: random() * 2::double precision))::integer = b) OR
 (((random()
 * 2::double precision))::integer = c))

 Is there a similar problem with the BETWEEN clause
 transformation into AND expressions?

 marti= explain verbose select random() between 0.25 and 0.75;
 Result (cost=0.00..0.02 rows=1 width=0)
 Output: ((random()= 0.25::double precision) AND (random()=
 0.75::double precision))

 Yes, good point.
 
 Hmm, the SQL specification explicitly says that
 
 X BETWEEN Y AND Z
 
 is equal to
 
 X = Y AND X = Z
 
 It doesn't say anything about side-effects of X. Seems like an
 oversight in the specification. I would not expect X to be
 evaluated twice, and I think we should change BETWEEN to not do
 that.
 
Does the SQL spec explicitly say anything about how many times X
should be evaluated if you were to code it as?:
 
X = Y AND X = Z
 
If it does, evaluating it a different number of times for BETWEEN
would seem to be a deviation from standard.  Evaluating it once seem
less surprising, but if we're going to deviate from the standard in
doing that, it at least deserves a clear note to that effect in the
docs.
 
Evaluating X once for BETWEEN seems better from a POLA perspective,
unless you happen to be massaging a query to another form and
trusting that the equivalence defined in the standard will always
hold.
 
 Does anyone object to making BETWEEN and IN more strict about the
 data types? At the moment, you can do this:
 
 postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
   ?column?
 --
   t
 (1 row)
 
 I'm thinking that it should throw an error. Same with IN, if the
 values in the IN-list can't be coerced to a common type. That will
 probably simplify the code a lot, and is what the SQL standard
 assumes anyway AFAICS.
 
+1 for more strict.
 
-Kevin

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


[HACKERS] pgfoundry down?

2011-04-11 Thread Tatsuo Ishii
Does anybody know what's going on?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] Re: [COMMITTERS] pgsql: Don't make replication magical as a user name, only as a datab

2011-04-11 Thread Andrew Dunstan



On 04/10/2011 10:17 PM, Andrew Dunstan wrote:



On 04/10/2011 09:47 PM, Fujii Masao wrote:
On Mon, Apr 11, 2011 at 3:53 AM, Andrew Dunstanand...@dunslane.net  
wrote:
Don't make replication magical as a user name, only as a database 
name, in pg_hba.conf.

Is it worth backporting this change to 9.0?




I didn't because it's a behaviour change, but arguably it's just us 
being ever so slightly more permissive, and nothing that now works 
would change in any way, so we possibly could.





Well, nobody seems to be very fussed about this idea, so unless someone 
objects I'll do this in 24 hours.


cheers

andrew

--
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] Transforming IN (...) to ORs, volatility

2011-04-11 Thread Heikki Linnakangas

On 11.04.2011 19:06, Kevin Grittner wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com  wrote:

On 05.04.2011 18:42, Heikki Linnakangas wrote:

On 05.04.2011 13:19, Marti Raudsepp wrote:

On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com  wrote:

We sometimes transform IN-clauses to a list of ORs:

postgres=# explain SELECT * FROM foo WHERE a IN (b, c);
QUERY PLAN
Seq Scan on foo (cost=0.00..39.10 rows=19 width=12)
Filter: ((a = b) OR (a = c))

But what if you replace a with a volatile function? It
doesn't seem legal to do that transformation in that case, but
we do it:

postgres=# explain SELECT * FROM foo WHERE
(random()*2)::integer IN (b, c);
QUERY PLAN

Seq Scan on foo (cost=0.00..68.20 rows=19 width=12)
Filter: random() * 2::double precision))::integer = b) OR
(((random()
* 2::double precision))::integer = c))


Is there a similar problem with the BETWEEN clause
transformation into AND expressions?

marti=  explain verbose select random() between 0.25 and 0.75;
Result (cost=0.00..0.02 rows=1 width=0)
Output: ((random()= 0.25::double precision) AND (random()=
0.75::double precision))


Yes, good point.


Hmm, the SQL specification explicitly says that

X BETWEEN Y AND Z

is equal to

X= Y AND X= Z

It doesn't say anything about side-effects of X. Seems like an
oversight in the specification. I would not expect X to be
evaluated twice, and I think we should change BETWEEN to not do
that.


Does the SQL spec explicitly say anything about how many times X
should be evaluated if you were to code it as?:

X= Y AND X= Z


Not explicitly. However, it does say that:


NOTE 258 — Since between predicate is an ordering operation, the 
Conformance Rules of Subclause 9.12, “Ordering

operations”, also apply.


If I'm reading those ordering operation conformance rules correctly, it 
only allows the operand to be a simple column or an expression that's 
specified in the ORDER BY or similar, not an arbitrary expression. Which 
seems quite restrictive, but it would dodge the whole issue..


The spec also has that:

“X BETWEEN SYMMETRIC Y AND Z” is equivalent to “((X BETWEEN ASYMMETRIC Y 
AND Z)

OR (X BETWEEN ASYMMETRIC Z AND Y))”.

So if you take that into account too, X is evaluated four times. The SQL 
standard can be funny sometimes, but I can't believe that they intended 
that.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] pgfoundry down?

2011-04-11 Thread Marc G. Fournier


Apologies ... everything should be back up and running now ...

On Mon, 11 Apr 2011, Tatsuo Ishii wrote:


Does anybody know what's going on?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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




Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

--
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] Transforming IN (...) to ORs, volatility

2011-04-11 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 Does anyone object to making BETWEEN and IN more strict about the data 
 types? At the moment, you can do this:

 postgres=# SELECT '1234' BETWEEN '10001'::text AND 10002::int4;
   ?column?
 --
   t
 (1 row)

 I'm thinking that it should throw an error. Same with IN, if the values 
 in the IN-list can't be coerced to a common type.

You *will* get push-back on that ... maybe from people with badly coded
applications, but I guarantee there will be complaints.

regards, tom lane

-- 
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] switch UNLOGGED to LOGGED

2011-04-11 Thread Noah Misch
On Mon, Apr 11, 2011 at 11:41:18AM +0100, Leonardo Francalanci wrote:
   But re-reading  it, I don't understand: what's the difference in creating
   a new  regular table and crashing before emitting the abort record,
   and  converting an unlogged table to logged and crashing before
   emitting the  abort record? How do the standby servers handle a
   CREATE TABLE  followed by a ROLLBACK if the master crashes
   before writing the abort  record? I thought that too would leave a
   stray file around on a  standby.
  
  I've been thinking about the same thing.  And AFAICS, your  analysis is
  correct, though there may be some angle to it I'm not  seeing.
 
 
 Anyone else? I would like to know if what I'm trying to do is, in fact,
 possible... otherwise starting with thewal_level=minimal case first
 will be wasted effort in case the other cases can't be integrated
 somehow...

If the master crashes while a transaction that used CREATE TABLE is unfinished,
both the master and the standby will indefinitely retain identical, stray (not
referenced by pg_class) files.  The catalogs do reference the relfilenode of
each unlogged relation; currently, that relfilenode never exists on a standby
while that standby is accepting connections.  By the time the startup process
releases the AccessExclusiveLock acquired by the proposed UNLOGGED - normal
conversion process, that relfilenode needs to be either fully copied or unlinked
all over again.  (Alternately, find some other way to make sure queries don't
read the half-copied file.)  In effect, the problem is that the relfilenode is
*not* stray, so its final state does need to be well-defined.

nm

-- 
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] SSI bug?

2011-04-11 Thread Heikki Linnakangas

On 11.04.2011 11:33, Heikki Linnakangas wrote:

On 31.03.2011 22:06, Kevin Grittner wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote:


That's not enough. The hash tables can grow beyond the maximum
size you specify in ShmemInitHash. It's just a hint to size the
directory within the hash table.

We'll need to teach dynahash not to allocate any more entries
after the preallocation. A new HASH_NO_GROW flag to hash_create()
seems like a suitable interface.


OK. If we're doing that, is it worth taking a look at the safety
margin added to the size calculations, and try to make the
calculations more accurate?

Would you like me to code a patch for this?


I finally got around to look at this. Attached patch adds a
HASH_FIXED_SIZE flag, which disables the allocation of new entries after
the initial allocation. I believe we have consensus to make the
predicate lock hash tables fixed-size, so that there's no competition of
the slack shmem space between predicate lock structures and the regular
lock maanager.


Ok, committed that.

I left the safety margins in the size calculations alone for now.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] SSI bug?

2011-04-11 Thread Heikki Linnakangas

On 03.04.2011 09:16, Dan Ports wrote:

I think I see what is going on now. We are sometimes failing to set the
commitSeqNo correctly on the lock. In particular, if a lock assigned to
OldCommittedSxact is marked with InvalidSerCommitNo, it will never be
cleared.

The attached patch corrects this:
  TransferPredicateLocksToNewTarget should initialize a new lock
  entry's commitSeqNo to that of the old one being transferred, or take
  the minimum commitSeqNo if it is merging two lock entries.

  Also, CreatePredicateLock should initialize commitSeqNo for to
  InvalidSerCommitSeqNo instead of to 0. (I don't think using 0 would
  actually affect anything, but we should be consistent.)

  I also added a couple of assertions I used to track this down: a
  lock's commitSeqNo should never be zero, and it should be
  InvalidSerCommitSeqNo if and only if the lock is not held by
  OldCommittedSxact.



Thanks, committed this.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
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] Feature request: pg_basebackup --force

2011-04-11 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
 
 That's exactly what pg_basebackup does. Once you move into more 
 complicated scenarios with multiple standbys and WAL archiving,
 it's inevitably going to be more complicated to set up.
 
 That doesn't mean that we can't make it easier - we can and we
 should - but I don't think the common complaint that replication
 is hard to set up is true anymore.
 
Getting back to the rsync-like behavior, which is what led the
conversation in this direction, I think -- the point of that seemed
to be to allow similar ease of use for those activating a replicated
node as the master, without requiring that the entire data directory
be sent over a slow WAN or Internet path when the delta needed to
modify what was already at the remote end to match the new master
might be orders of magnitude less than data than that.
 
The intelligence to support that would be a fraction of what is in
rsync.  In fact, since we might want to ignore hint bit differences
where possible, rsync might not work nearly as well as a home-grown
solution.
 
-Kevin

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


[HACKERS] Locking when concurrent updated of foreign references

2011-04-11 Thread Jesper Krogh

Hi.

This seem a bit strange to me. In short:

2 tables, one with has a foreign key to the other one;

CREATE TABLE test (id SERIAL primary key, data text);
CREATE TABLE testref(id SERIAL primary key, test_id integer references 
test(id) not null, data text);

INSERT INTO test(data) values('something');
INSERT INTO testref(test_id,data) values(1,'something else');
CREATE OR REPLACE FUNCTION upd(data text) RETURNS text as $$ BEGIN 
PERFORM pg_sleep(3); return data; END; $$ LANGUAGE 'plpgsql' immutable;



Then start 2 transactions (from different psql prompts):

TXN1: BEGIN;
TXN1: update test set data = upd('something');
TXN2: BEGIN;
TXN2: update testref set data = upd('something'); (within 3 seconds of 
the other update, so they are overlapping in execution)
this one blocks on a lock-wait of the other transaction even after this 
other command is done


Wether this is correct or not I'm not sure, but if you remove the 
upd() calls

in both above so the update isn't exactly executed at the same time
then both commands succeed without interfering with each other.

While waiting on TXN2's update, pg_locks shows that there is a ShareLock 
that
cannot be granted, which will first get further when TXN1 commits or 
rolls back.


It seems as the lock isn't released if some other process is actually 
waiting

for it?

It may be related to the plpgsql function, I have a similar one (doing more
clever things than sleep) in the production system, but I cannot figure 
out how

to get the updates overlapping in execution in other ways.

A hugely trimmed down example of something I currently see in a production
system. (8.4.7) but above is on 9.1HEAD
--
Jesper


--
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] SSI bug?

2011-04-11 Thread Kevin Grittner
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
 
 I finally got around to look at this. Attached patch adds a 
 HASH_FIXED_SIZE flag, which disables the allocation of new entries
 after the initial allocation. I believe we have consensus to make
 the predicate lock hash tables fixed-size, so that there's no
 competition of the slack shmem space between predicate lock
 structures and the regular lock maanager.
 
OK, I can see why you preferred this -- the existing exchange of
slack space with the HW lock tables remains unchanged this way, and
only the new tables for predicate locking have the stricter limits. 
This makes it very unlikely to break current apps which might be
unknowingly relying on existing allocation behavior in the HW
locking area.  Smart.
 
I hadn't picked up on your intent that the new flag would only be
used for the new tables, which is why it wasn't quite making sense
to me before.
 
Thanks!
 
-Kevin

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


[HACKERS] Global variables in plpgsql

2011-04-11 Thread Nick Raj
Hi,
Can anyone know how to define global variable in plpgsql?
Thanks

Regards,
Raj


Re: [HACKERS] workaround for expensive KNN?

2011-04-11 Thread PostgreSQL - Hans-Jürgen Schönig
price has a problem :(.
iphone can be a 20 cents bag or a sticker or a 900 euro thing signed by 
whoever ...
so, words and the sort-number / price are not related in anyway. price is in 
this case no way to narrow down the problem (e.g. evaluate first or so).

many thanks,

hans


On Apr 8, 2011, at 5:25 PM, Oleg Bartunov wrote:

 Hans,
 
 what if you create index (price,title) ?
 
 
 On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
 
 hello ...
 
 i got that one ...
 
   idx_product_t_product_titleprice gist (to_tsvector('german'::regconfig, 
 title), int_price)
 
 so, i have a combined index on text + number.
 to me the plan seems fine ... it looks like a prober KNN traversal.
 the difference between my plan and your plan seems to be the fact that i 
 have, say, 1 mio rows which have handy or so in it (1 mio out of 11 mio or 
 so). you are moving out from one specific place.
 
 my maths is like that:
  11 mio in total
  1 mio matching iphone
  cheapest / most expensive 10 out of this mio needed.
 
 operator classes are all nice and in place:
 
 SELECT 10 - 4 as distance;
 distance
 --
   6
 (1 row)
 
 what does buffers true in your case say?
 
 many thanks,
 
  hans
 
 
 On Apr 8, 2011, at 3:22 PM, Oleg Bartunov wrote:
 
 Probably, you miss two-columnt index. From my early post:
 http://www.sai.msu.su/~megera/wiki/knngist
 
 =# CREATE INDEX spots_idx ON spots USING knngist (coordinates, 
 to_tsvector('french',address));
 =# SELECT id, address,  (coordinates - 
 '(2.29470491409302,48.858263472125)'::point) AS dist FROM spots WHERE 
 coordinates  '(2.29470491409302,48.858263472125)'::point AND 
 to_tsvector('french',address) @@ to_tsquery('french','mars')  LIMIT 10;
  id|   address   |  
dist 
 -+-+-
 366096 | 1st Floor Tour Eiffel | Champs de Mars, Paris 75007, France | 
 2.32488941293945e-05
 4356328 | r Champ de Mars 75007 PARIS |  
 0.00421854756964406
 5200167 | Champ De Mars 75007 Paris   |  
 0.00453564562587288
 9301676 | Champ de Mars, 75007 Paris, |  
 0.00453564562587288
 2152213 | 16, ave Rapp, Champ de Mars, Tour Eiffel, Paris, France |  
 0.00624152097590896
 1923818 | Champ de Mars Paris, France |  
 0.00838214733539654
 5165953 | 39 Rue Champ De Mars Paris, France  |  
 0.00874410234569529
 7395870 | 39 Rue Champ De Mars Paris, France  |  
 0.00874410234569529
 4358671 | 32 Rue Champ De Mars Paris, France  |  
 0.00876089659276339
 1923742 | 12 rue du Champ de Mars Paris, France   |  
 0.00876764731845995
 (10 rows)
 
 Time: 7.859 ms
 
 =# EXPLAIN (COSTS OFF) SELECT id, address FROM spots WHERE coordinates  
 '(2.29470491409302,48.858263472125)'::point
 AND to_tsvector('french',address) @@ to_tsquery('french','mars')  LIMIT 10;
 
   QUERY PLAN
 --
 Limit
  -  Index Scan using spots_idx on spots
Index Cond: ((coordinates  
 '(2.29470491409302,48.858263472125)'::point) AND 
 (to_tsvector('french'::regconfig, address) @@ '''mar'''::tsquery))
 (3 rows)
 
 
 On Fri, 8 Apr 2011, PostgreSQL - Hans-J?rgen Sch?nig wrote:
 
 hello all ...
 
 given oleg's posting before i also wanted to fire up some KNN related 
 question.
 let us consider a simple example. i got some million lines and i want all 
 rows matching a tsquery sorted by price.
 i did some tests:
 
 test=# explain (analyze true, buffers true, costs true) SELECT id FROM 
 product.t_product WHERE to_tsvector('german', title) @@ 
 to_tsquery('german', 'iphone') ORDER BY int_price - 0 LIMIT 10;
  
 QUERY PLAN
 
 -
 --
 Limit  (cost=0.00..41.11 rows=10 width=16) (actual 
 time=36391.717..45542.590 rows=10 loops=1)
 Buffers: shared hit=9 read=5004
 -  Index Scan using idx_product_t_product_titleprice on t_product  
 (cost=0.00..13251.91 rows=3224 width=16) (actual time=
 36391.715..45542.573 rows=10 loops=1)
   Index Cond: (to_tsvector('german'::regconfig, title) @@ 
 '''iphon'''::tsquery)
   Order By: (int_price - 0::bigint)
   Buffers: shared hit=9 read=5004
 Total runtime: 45542.676 ms
 (7 rows)
 
 test=# explain (analyze true, buffers true, costs true) SELECT id FROM 
 product.t_product WHERE to_tsvector('german', title) @@ 
 to_tsquery('german', 'handy') ORDER BY int_price - 0 LIMIT 10;

Re: [HACKERS] Global variables in plpgsql

2011-04-11 Thread Christopher Browne
On Mon, Apr 11, 2011 at 7:33 AM, Nick Raj nickrajj...@gmail.com wrote:
 Can anyone know how to define global variable in plpgsql?

I expect you should consult the manual page on the command CREATE
TABLE.  That's what would be the nearest SQL equivalent to a global
variable.

http://www.postgresql.org/docs/9.0/static/sql-createtable.html
-- 
http://linuxfinances.info/info/postgresql.html

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


[HACKERS] how to keep/lock/ hide pg_hba.conf ?

2011-04-11 Thread john.cheng
Dear all:
I am ready to release a client/server software (in windows xp),sure it's
postgresql based application
but I have to hide the password for sensitive data.
I found that,if user modified the pg_hba.conf, modified the METHODfield
from md5 to password
then,user can find out the password by some the TCP/IP peep tool --such as
LayerViewer
(we don't plan to install SSL in server/client)
I think as a newbie as I know this bug,this solution should be released
already
Thanks for any advice/suggestion

Regards

john from Taiwan


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/how-to-keep-lock-hide-pg-hba-conf-tp4296068p4296068.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

-- 
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] switch UNLOGGED to LOGGED

2011-04-11 Thread Robert Haas
On Mon, Apr 11, 2011 at 10:29 AM, Noah Misch n...@leadboat.com wrote:
 On Mon, Apr 11, 2011 at 11:41:18AM +0100, Leonardo Francalanci wrote:
   But re-reading  it, I don't understand: what's the difference in creating
   a new  regular table and crashing before emitting the abort record,
   and  converting an unlogged table to logged and crashing before
   emitting the  abort record? How do the standby servers handle a
   CREATE TABLE  followed by a ROLLBACK if the master crashes
   before writing the abort  record? I thought that too would leave a
   stray file around on a  standby.
 
  I've been thinking about the same thing.  And AFAICS, your  analysis is
  correct, though there may be some angle to it I'm not  seeing.

 Anyone else? I would like to know if what I'm trying to do is, in fact,
 possible... otherwise starting with thewal_level=minimal case first
 will be wasted effort in case the other cases can't be integrated
 somehow...

 If the master crashes while a transaction that used CREATE TABLE is 
 unfinished,
 both the master and the standby will indefinitely retain identical, stray (not
 referenced by pg_class) files.  The catalogs do reference the relfilenode of
 each unlogged relation; currently, that relfilenode never exists on a standby
 while that standby is accepting connections.  By the time the startup process
 releases the AccessExclusiveLock acquired by the proposed UNLOGGED - normal
 conversion process, that relfilenode needs to be either fully copied or 
 unlinked
 all over again.  (Alternately, find some other way to make sure queries don't
 read the half-copied file.)  In effect, the problem is that the relfilenode is
 *not* stray, so its final state does need to be well-defined.

Oh, right.

Maybe we should just put in a rule that a server in Hot Standby mode
won't ever try to read from an unlogged table (right now we count on
the fact that there will be nothing to read).  If we crash before
copying the whole file, it won't matter, because the catalogs won't
have been updated, so we'll refuse to look at it anyway.  And we have
to reinitialize on entering normal running anyway, so we can clean it
up then.

-- 
Robert Haas
EnterpriseDB: 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] how to keep/lock/ hide pg_hba.conf ?

2011-04-11 Thread Jaime Casanova
On Mon, Apr 11, 2011 at 9:35 AM, john.cheng neoart.hi...@msa.hinet.net wrote:
 I found that,if user modified the pg_hba.conf, modified the METHODfield
 from md5 to password

if it's a client/server app the user shouldn't have access to the
server, so how could him to make the change?

Also the directory in which the pg_hba.conf is is only
visible/writable for the database cluster owner and the system
administrator, so that means you're allowing your user to connect to
the server as one of those users? or is windows uncapable of enforce
those restrictions?

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

-- 
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] how to keep/lock/ hide pg_hba.conf ?

2011-04-11 Thread Peter Eisentraut
On mån, 2011-04-11 at 07:35 -0700, john.cheng wrote:
 I found that,if user modified the pg_hba.conf, modified the
 METHODfield from md5 to password then,user can find out the
 password by some the TCP/IP peep tool

Don't do that then.

Are you concerned that your users would do this?  Well, if you install
software on their machine, they can do whatever they want with it.
That's not an easy issue to solve.



-- 
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] Global variables in plpgsql

2011-04-11 Thread Pavel Stehule
Hello

2011/4/11 Nick Raj nickrajj...@gmail.com:
 Hi,
 Can anyone know how to define global variable in plpgsql?
 Thanks

 Regards,
 Raj


plpgsql doesn't support global or session variables.

There are a few techniques - you can emulate it


http://www.postgresql.org/docs/8.3/static/plperl-global.html

Regards

Pavel Stehule

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


[HACKERS] fn_collation in FmgrInfo considered harmful

2011-04-11 Thread Tom Lane
The fact that the collations patch put fn_collation into FmgrInfo,
rather than FunctionCallInfo, has been bothering me for awhile.  The
collation is really a kind of argument, not a property of the function,
so FmgrInfo is logically the wrong place for it.  But I'd not found a
concrete reason not to do it that way.  Now I think I have.  Bug #5970
points out that record_cmp() needs to set up collations for the
comparison functions it calls.  Since record_cmp relies on FmgrInfo
structs that belong to the typcache, this is problematic.  I see three
choices:

1.  Scribble on fn_collation of the FmgrInfo, even though it's in a
cache entry that may be used by other calls.  This is only safe if
you assume that record_cmp (and array_cmp, which is already doing this)
need not be re-entrant, ie the cache entry won't be used for another
purpose before we're done with the comparison.  Considering that the
comparison function can be user-defined code, I don't find that
assumption safe in the slightest.

2.  Copy the FmgrInfo struct to local storage in record_cmp (ick).
Since these FmgrInfo structs advertise that they belong to
CacheMemoryContext, that doesn't seem very safe either.  A function
could allocate fn_extra workspace in CacheMemoryContext, and then do it
over again on the next call, lather rinse repeat.  Maybe we could fix
that by copying the fn_extra pointer *back* to the typcache afterwards,
but double ick.  (And that doesn't seem very safe if the typcache entry
could get used re-entrantly, anyway.)

3.  Don't store fn_collation in FmgrInfo.

A short look around the code suggests that #3 may not be inordinately
painful.  We'd need to add a collation field to ScanKey to make up for
the lack of one in the contained FmgrInfo, but that would make the code
cleaner not dirtier.  I can see a couple of places where the index AMs
assume that the index's collation is available from index_getprocinfo,
but it doesn't look too terribly hard to get them to consult
index-rd_indcollation[] instead.

So, unless there's a really good reason why fn_collation should be in
FmgrInfo and not FunctionCallInfo, I'm going to see about moving it.

regards, tom lane

-- 
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] Locking when concurrent updated of foreign references

2011-04-11 Thread Jesper Krogh

On 2011-04-11 20:18, Jesper Krogh wrote:

Hi.

This seem a bit strange to me. In short:

Not any more I.. I guess what made me a bit confused was that
a update table set key = value would acually block out changes
on tables referencing this tuple even if the referenced column wasn't
effected by the update.

But when the locking is done row-level then it is correct
to do it that way. It would allthough be nice with a weaker
locklevel for that kind of updates (I have no clue if that is
a hard problem).

--
Jesper

--
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] lowering privs in SECURITY DEFINER function

2011-04-11 Thread Alvaro Herrera
Excerpts from Robert Haas's message of dom abr 10 13:37:46 -0300 2011:

 It's maybe worth noting here that what's being asked for is roughly
 what you get from UNIX's distinction between euid and ruid.  Many
 programs that run setuid root perform a few operations that require
 root privileges up front, and then drop privs.  To what degree that
 model applies in an SQL environment I'm not sure, but it might be
 worth looking at some of the parallels, as well as some of the ways
 that the UNIX mechanism has managed to cause all sorts of privilege
 escalation bugs over the years, to make sure we don't repeat those
 mistakes.

Thanks for mentioning that.  It made me recall a couple of articles I
read some time ago,
http://lwn.net/Articles/416494/
and
http://www.cis.upenn.edu/~KeyKOS/ConfusedDeputy.html

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

-- 
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] Windows build issues

2011-04-11 Thread Peter Eisentraut
On tor, 2011-04-07 at 16:20 -0400, Robert Haas wrote:
 It sure would be nice if someone would write a doc patch, or at least
 a wiki page, explaining all the permutations here...  I get the
 impression it's not that hard to set up if you are reasonable
 comfortable working in a Windows environment, but it's pretty
 intimidating if you aren't. 

Here's a patch I came up with that matches my experience, but evidently
there could be many others.

diff --git i/doc/src/sgml/install-windows.sgml w/doc/src/sgml/install-windows.sgml
index f6d38c1..d13a161 100644
--- i/doc/src/sgml/install-windows.sgml
+++ w/doc/src/sgml/install-windows.sgml
@@ -19,11 +19,11 @@
  para
   There are several different ways of building PostgreSQL on
   productnameWindows/productname. The simplest way to build with
-  Microsoft tools is to install a modern version of the
-  productnameMicrosoft Platform SDK/productname and use use the included
+  Microsoft tools is to install
+  productnameMicrosoft Visual C++ 2008 Express Edition/productname and use use the included
   compiler. It is also possible to build with the full
   productnameMicrosoft Visual C++ 2005 or 2008/productname. In some cases
-  that requires the installation of the productnamePlatform SDK/productname
+  that requires the installation of the productnameWindows SDK/productname
   in addition to the compiler.
  /para
 
@@ -68,17 +68,21 @@
  /para
 
  sect1 id=install-windows-full
-  titleBuilding with productnameVisual C++/productname or the
-  productnamePlatform SDK/productname/title
+  titleBuilding with productnameVisual C++/productname/title
 
  para
   PostgreSQL can be built using the Visual C++ compiler suite from Microsoft.
   These compilers can be either from productnameVisual Studio/productname,
-  productnameVisual Studio Express/productname or recent versions of the
-  productnamePlatform SDK/productname. If you do not already have a
-  productnameVisual Studio/productname environment set up, the easiest
-  way us to use the compilers in the productnamePlatform SDK/productname,
-  which is a free download from Microsoft.
+  productnameVisual Studio Express/productname or the
+  productnamePlatform SDK/productname.  (The productnamePlatform
+  SDK/productname approach is obsolescent.  The SDK has been renamed
+  to productnameWindows SDK/productname and is included
+  in productnameVisual Studio Express/productname).  If you do not
+  already have a
+  productnameVisual Studio/productname environment set up, the
+  easiest way us to use the productnameVisual C++
+  Express/productname component from productnameVisual Studio
+  Express/productname, which is a free download from Microsoft.
  /para
 
  para
@@ -86,7 +90,9 @@
   productnameVisual Studio 2005/productname and
   productnameVisual Studio 2008/productname. When using the Platform SDK
   only, or when building for 64-bit Windows, only
-  productnameVisual Studio 2008/productname is supported.
+  productnameVisual Studio 2008/productname is
+  supported.  productnameVisual Studio 2010/productname is not yet
+  supported and will not work.
  /para
 
  para
@@ -94,13 +100,16 @@
   are in the filenamesrc/tools/msvc/filename directory. When building,
   make sure there are no tools from productnameMinGW/productname or
   productnameCygwin/productname present in your system PATH. Also, make
-  sure you have all the required Visual C++ tools available in the PATH. In
-  productnameVisual Studio/productname, start the
-  applicationVisual Studio Command Prompt/application. In the
+  sure you have all the required Visual C++ tools available in the PATH.  The easiest way to accomplish that, when using
+  productnameVisual Studio/productname, is to start the
+  applicationVisual Studio Command Prompt/application that is installed in the Start menu.  When using the
   productnamePlatform SDK/productname, start the
   applicationCMD shell/application listed under the SDK on the Start Menu.
   If you wish to build a 64-bit version, you must use the 64-bit version of
   the command, and vice versa.
+ /para
+
+ para
   All commands should be run from the filenamesrc\tools\msvc/filename
   directory.
  /para
@@ -140,18 +149,20 @@ $ENV{PATH}=$ENV{PATH} . ';c:\some\where\bison\bin';
 
variablelist
 varlistentry
- termproductnameMicrosoft Platform SDK/productname/term
+ termproductnameMicrosoft Visual C++/productname/term
  listitempara
-  It is recommended that you upgrade to the latest available version
-  of the productnameMicrosoft Platform SDK/productname, available
-  for download from ulink url=http://www.microsoft.com/downloads/;/.
+  It is recommended that you use productnameMicrosoft Visual
+  Studio 2008 Express/productname, available for download
+  from ulink url=http://www.microsoft.com/downloads/;/, if
+  you don't already have another version installed.
  /para
  para
-  You must always include the
-  applicationWindows Headers and 

[HACKERS] WAL, xl_heap_insert and tuple oid mystry

2011-04-11 Thread jagan
Hi,
Suppose I create a table as follows:

CREATE TABLE test2 (name TEXT, age INTEGER) WITH oids;

Now, for every tuple in this table is associated with a unique oid, which I can 
retrieve by:

SELECT oid, name, age FROM test2;

which works great. So far so good.

Now, if look at the corresponding WAL entry for any insert into this relation, 
it creates the following WAL entry which I can decode.

XLogRecord --- Followed by -- xl_heap_insert struct --followed by -- 
xl_heap_header -- Followed by -- tuple data

If I use the macro  HeapTupleHeaderGetOid([xl_heap_header struct]) or 
equivalently if I test ([xl_heap_header struct]-t_infomask  HEAP_HASOID), it 
tells me that the tuple oid is not stored with this record.

Where is the OID of tuple stored in a WAL record of a tuple? If not with 
xl_heap_header, where is it stored? Is it stored at all?

Thanks for any responses.  
Jagan


-- 
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] Locking when concurrent updated of foreign references

2011-04-11 Thread Alvaro Herrera
Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:

 But when the locking is done row-level then it is correct
 to do it that way. It would allthough be nice with a weaker
 locklevel for that kind of updates (I have no clue if that is
 a hard problem).

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/

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

-- 
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] POSIX shared memory redux

2011-04-11 Thread Robert Haas
On Sun, Apr 10, 2011 at 5:03 PM, A.M. age...@themactionfaction.com wrote:
 To ensure that no two postmasters can startup in the same data directory, I 
 use fcntl range locking on the data directory lock file, which also works 
 properly on (properly configured) NFS volumes. Whenever a postmaster or 
 postmaster child starts, it acquires a read (non-exclusive) lock on the data 
 directory's lock file. When a new postmaster starts, it queries if anything 
 would block a write (exclusive) lock on the lock file which returns a 
 lock-holding PID in the case when other postgresql processes are running.

This seems a lot leakier than what we do now (imagine, for example,
shared storage) and I'm not sure what the advantage is.  I was
imagining keeping some portion of the data in sysv shm, and moving the
big stuff to a POSIX shm that would operate alongside it.

-- 
Robert Haas
EnterpriseDB: 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] POSIX shared memory redux

2011-04-11 Thread A.M.

On Apr 11, 2011, at 6:06 PM, Robert Haas wrote:

 On Sun, Apr 10, 2011 at 5:03 PM, A.M. age...@themactionfaction.com wrote:
 To ensure that no two postmasters can startup in the same data directory, I 
 use fcntl range locking on the data directory lock file, which also works 
 properly on (properly configured) NFS volumes. Whenever a postmaster or 
 postmaster child starts, it acquires a read (non-exclusive) lock on the data 
 directory's lock file. When a new postmaster starts, it queries if anything 
 would block a write (exclusive) lock on the lock file which returns a 
 lock-holding PID in the case when other postgresql processes are running.
 
 This seems a lot leakier than what we do now (imagine, for example,
 shared storage) and I'm not sure what the advantage is.  I was
 imagining keeping some portion of the data in sysv shm, and moving the
 big stuff to a POSIX shm that would operate alongside it.

What do you mean by leakier? The goal here is to extinguish SysV shared 
memory for portability and convenience benefits. The mini-SysV proposal was 
implemented and shot down by Tom Lane.

Cheers,
M
-- 
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] POSIX shared memory redux

2011-04-11 Thread Robert Haas
On Mon, Apr 11, 2011 at 3:11 PM, A.M. age...@themactionfaction.com wrote:

 On Apr 11, 2011, at 6:06 PM, Robert Haas wrote:

 On Sun, Apr 10, 2011 at 5:03 PM, A.M. age...@themactionfaction.com wrote:
 To ensure that no two postmasters can startup in the same data directory, I 
 use fcntl range locking on the data directory lock file, which also works 
 properly on (properly configured) NFS volumes. Whenever a postmaster or 
 postmaster child starts, it acquires a read (non-exclusive) lock on the 
 data directory's lock file. When a new postmaster starts, it queries if 
 anything would block a write (exclusive) lock on the lock file which 
 returns a lock-holding PID in the case when other postgresql processes are 
 running.

 This seems a lot leakier than what we do now (imagine, for example,
 shared storage) and I'm not sure what the advantage is.  I was
 imagining keeping some portion of the data in sysv shm, and moving the
 big stuff to a POSIX shm that would operate alongside it.

 What do you mean by leakier? The goal here is to extinguish SysV shared 
 memory for portability and convenience benefits. The mini-SysV proposal was 
 implemented and shot down by Tom Lane.

I mean I'm not convinced that fcntl() locking will be as reliable.

I know Tom shot that down before, but I still think it's probably the
best way forward.  The advantage I see is that we would be able to
more easily allocate larger chunks of shared memory with changing
kernel parameters, and perhaps even to dynamically resize shared
memory chunks.  That'd be worth the price of admission even if we
didn't get all those benefits in one commit.

-- 
Robert Haas
EnterpriseDB: 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] lowering privs in SECURITY DEFINER function

2011-04-11 Thread Jim Nasby
On Apr 8, 2011, at 6:17 PM, Alvaro Herrera wrote:
 In other words, if you wrap an unprivileged operation inside of
 privileged operations, it seems like the unprivileged operation then
 becomes privileged. Right?
 
 Well, it's in the hands of the creator of the overall wrapper function
 to ensure that the before/after functions are safe in that sense.

How do you do that in a safe way though? The problem you run into is if you 
have a pair of operations that need to be done as a superuser, and something 
else you want to do in the middle as a non-super user. The goal here is to 
ensure that you MUST perform both operations out of the pair. The problem is: 
how do you enforce that the cleanup will actually happen?

Right now, we're doing this through a single function that performs the first 
SU action, does whatever the user asked, and then performs the second SU 
action. I don't think there's any other way to do that, at least not in 8.3.

To make this robust, you can't just provide secdef functions that wrap your 
operations that require SU: that would mean that anyone could still call them, 
which means they could potentially call the 1st operation and not the 2nd.

I suspect there might be clever ways around this issue, but ISTM that there 
should be some reasonable way to handle this.

BTW, Alvaro did some digging and discovered that the SQL spec allows you to 
drop to a lower privilege state, but then there's no way you can regain your 
higher-level privileges until the code block that requested lower privileges 
exits. That would actually work fine here, so long as you defined a 
sub-transaction (ie: an embedded BEGIN; END; block in plpgsql as a code block. 
With such a facility, you could do:

CREATE FUNCTION () SECURITY DEFINER AS $$
BEGIN;
privileged operation...

BEGIN;
  SET ROLE original_user;
  UNprivileged operation...
END;

privileged operation...
END;
$$;
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



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


[HACKERS] Postgre inner work question

2011-04-11 Thread Lucas Cotta
Hi!

Does postgre execute the queries following a execution plan tree, where the
leafs are table scans, and the nodes are joins?

I'm looking for a database where I can get a cardinality from a partial
result of the execution... for example, print the cardinality of the results
until the next join operator use this result...

Thanks!


[HACKERS] Postgre inner work question

2011-04-11 Thread Lucas Cotta
Hi!

Does postgre execute the queries following a execution plan tree, where the
leafs are table scans, and the nodes are joins?

I'm looking for a database where I can get a cardinality from a partial
result of the execution... for example, print the cardinality of the results
until the next join operator use this result...

Thanks!


Re: [HACKERS] POSIX shared memory redux

2011-04-11 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Mon, Apr 11, 2011 at 3:11 PM, A.M. age...@themactionfaction.com wrote:
 What do you mean by leakier? The goal here is to extinguish SysV shared 
 memory for portability and convenience benefits. The mini-SysV proposal was 
 implemented and shot down by Tom Lane.

 I mean I'm not convinced that fcntl() locking will be as reliable.

I'm not either.  Particularly not on NFS.  (Although on NFS you have
other issues to worry about too, like postmasters on different machines
being able to reach the same data directory.  I wonder if we should do
both SysV and fcntl locking ...)

 I know Tom shot that down before, but I still think it's probably the
 best way forward.

Did I?  I think I pointed out that there's zero gain in portability as
long as we still depend on SysV shmem to work.  However, if you're doing
it for other reasons than portability, it might make sense anyway.  The
question is whether there are adequate other reasons.

 The advantage I see is that we would be able to
 more easily allocate larger chunks of shared memory with changing
 kernel parameters,

Yes, getting out from under the SHMMAX bugaboo would be awfully nice.

 and perhaps even to dynamically resize shared memory chunks.

This I don't really believe will ever work reliably, especially not in
32-bit machines.  Whatever your kernel API is, you still have the
problem of finding address space contiguous to what you were already
using.

regards, tom lane

-- 
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] POSIX shared memory redux

2011-04-11 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Sun, Apr 10, 2011 at 5:03 PM, A.M. age...@themactionfaction.com wrote:
 To ensure that no two postmasters can startup in the same data directory, I 
 use fcntl range locking on the data directory lock file, which also works 
 properly on (properly configured) NFS volumes. Whenever a postmaster or 
 postmaster child starts, it acquires a read (non-exclusive) lock on the data 
 directory's lock file. When a new postmaster starts, it queries if anything 
 would block a write (exclusive) lock on the lock file which returns a 
 lock-holding PID in the case when other postgresql processes are running.

 This seems a lot leakier than what we do now (imagine, for example,
 shared storage) and I'm not sure what the advantage is.

BTW, the above-described solution flat out doesn't work anyway, because
it has a race condition.  Postmaster children have to reacquire the lock
after forking, because fcntl locks aren't inherited during fork().  And
that means you can't tell whether there's a just-started backend that
hasn't yet acquired the lock.  It's really critical for our purposes
that SysV shmem segments are inherited at fork() and so there's no
window where a just-forked backend isn't visible to somebody checking
the state of the shmem segment.

regards, tom lane

-- 
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] POSIX shared memory redux

2011-04-11 Thread A.M.

On Apr 11, 2011, at 7:25 PM, Tom Lane wrote:

 Robert Haas robertmh...@gmail.com writes:
 On Sun, Apr 10, 2011 at 5:03 PM, A.M. age...@themactionfaction.com wrote:
 To ensure that no two postmasters can startup in the same data directory, I 
 use fcntl range locking on the data directory lock file, which also works 
 properly on (properly configured) NFS volumes. Whenever a postmaster or 
 postmaster child starts, it acquires a read (non-exclusive) lock on the 
 data directory's lock file. When a new postmaster starts, it queries if 
 anything would block a write (exclusive) lock on the lock file which 
 returns a lock-holding PID in the case when other postgresql processes are 
 running.
 
 This seems a lot leakier than what we do now (imagine, for example,
 shared storage) and I'm not sure what the advantage is.
 
 BTW, the above-described solution flat out doesn't work anyway, because
 it has a race condition.  Postmaster children have to reacquire the lock
 after forking, because fcntl locks aren't inherited during fork().  And
 that means you can't tell whether there's a just-started backend that
 hasn't yet acquired the lock.  It's really critical for our purposes
 that SysV shmem segments are inherited at fork() and so there's no
 window where a just-forked backend isn't visible to somebody checking
 the state of the shmem segment.

Then you haven't looked at my patch because I address this race condition by 
ensuring that a lock-holding violator is the postmaster or a postmaster child. 
If such as condition is detected, the child exits immediately without touching 
the shared memory. POSIX shmem is inherited via file descriptors.

Cheers,
M
-- 
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] POSIX shared memory redux

2011-04-11 Thread A.M.

On Apr 11, 2011, at 7:13 PM, Tom Lane wrote:

 Robert Haas robertmh...@gmail.com writes:
 On Mon, Apr 11, 2011 at 3:11 PM, A.M. age...@themactionfaction.com wrote:
 What do you mean by leakier? The goal here is to extinguish SysV shared 
 memory for portability and convenience benefits. The mini-SysV proposal was 
 implemented and shot down by Tom Lane.
 
 I mean I'm not convinced that fcntl() locking will be as reliable.
 
 I'm not either.  Particularly not on NFS.  (Although on NFS you have
 other issues to worry about too, like postmasters on different machines
 being able to reach the same data directory.  I wonder if we should do
 both SysV and fcntl locking ...)

Is there an example of a recent system where fcntl is broken (ignoring NFS)? I 
believe my patch addresses all potential race conditions and uses the APIs 
properly to guarantee single-postmaster data directory usage and I tested on 
Darwin and a two-year-old Linux kernel. In the end, fcntl locking relies on the 
same kernel which provides the SysV user count, so I'm not sure what makes it 
less reliable, but I have heard that twice now, so I am open to hearing about 
your experiences.

 I know Tom shot that down before, but I still think it's probably the
 best way forward.
 
 Did I?  I think I pointed out that there's zero gain in portability as
 long as we still depend on SysV shmem to work.  However, if you're doing
 it for other reasons than portability, it might make sense anyway.  The
 question is whether there are adequate other reasons.

I provided an example of postmaster-failover relying on F_SETLKW in the email 
with the patch. Also, as you point out above, fcntl locking at least has a 
chance of working over NFS.

 
 The advantage I see is that we would be able to
 more easily allocate larger chunks of shared memory with changing
 kernel parameters,
 
 Yes, getting out from under the SHMMAX bugaboo would be awfully nice.

Yes, please! That is my primary motivation for this patch.

 
 and perhaps even to dynamically resize shared memory chunks.
 
 This I don't really believe will ever work reliably, especially not in
 32-bit machines.  Whatever your kernel API is, you still have the
 problem of finding address space contiguous to what you were already
 using.

Even if expanding shmem involves copying large regions of memory, it could be 
at least useful to adjust buffer sizes live without a restart.

Cheers,
M


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


[HACKERS] Calling Matlab function from Postgres

2011-04-11 Thread Susan M Farley
I'm trying to call MATLAB functions from PostgreSQL. I was trying to use Joshua 
Kaplan's java MATLAB control. I install my jar file which in turn calls MATLAB 
to run my algorithm, but get the error message ERROR:  
java.lang.NoClassDefFoundError: matlabcontrol/RemoteMatlabProxyFactory when I 
call the java function. I saw where DerTech LLC developed a MATLAB interface 
for PostgreSQL, but their web site seems to be gone. Does anyone either have an 
idea of how to solve my error or have a copy of the code that DerTech developed 
for the interface?

Thank you,
Susan

-- 
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] Locking when concurrent updated of foreign references

2011-04-11 Thread Jesper Krogh

On 2011-04-11 23:30, Alvaro Herrera wrote:

Excerpts from Jesper Krogh's message of lun abr 11 17:07:33 -0300 2011:


But when the locking is done row-level then it is correct
to do it that way. It would allthough be nice with a weaker
locklevel for that kind of updates (I have no clue if that is
a hard problem).

http://www.commandprompt.com/blogs/alvaro_herrera/2010/11/fixing_foreign_key_deadlocks/


That looks exactly what I have been seeing.

Naive suggestion (at least to part of the problem):
Would it be possible to identify updates that never
can violate any constraints and not do any verification
of foreign keys on the update and only pick a lock
that block concurrent updates of the same tuple?

UPDATE table set something which is neither referenced or a reference;
would all be of that type.

Would allthough require the database to examine
the UPDATE statement and in comparison with the
table definition figure out which of the column are
safe to update.

There might actually be a potential speedup since the update
would require to go visit the foreign table at all.

Jesper
--
Jesper


--
Jesper

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