Re: [HACKERS] Optimize referential integrity checks (todo item)

2012-02-13 Thread Chetan Suttraway
On Sun, Feb 12, 2012 at 7:36 AM, Vik Reykja vikrey...@gmail.com wrote:

 I decided to take a crack at the todo item created from the following post:
 http://archives.postgresql.org/pgsql-performance/2005-10/msg00458.php

 The attached patch makes the desired changes in both code and function
 naming.

 It seemed quite easy to do but wasn't marked as easy on the todo, so I'm
 wondering if I've missed something.  All regression tests pass.


The patch was not getting applied. Was seeing below message:
postgresql$ git apply  /Downloads/unchanged.patch
error: src/backend/utils/adt/ri_triggers.c: already exists in working
directory

Have come up with attached patch which hopefully should not have missed any
of your changes.
Please verify the changes.

Regards,
Chetan

PS: would like the patch name to be something meaningful.


-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 03a974a..09bacb7 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -205,11 +205,11 @@ static void ri_BuildQueryKeyFull(RI_QueryKey *key,
 static void ri_BuildQueryKeyPkCheck(RI_QueryKey *key,
 		const RI_ConstraintInfo *riinfo,
 		int32 constr_queryno);
-static bool ri_KeysEqual(Relation rel, HeapTuple oldtup, HeapTuple newtup,
+static bool ri_KeysUnchanged(Relation rel, HeapTuple oldtup, HeapTuple newtup,
 			 const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_AllKeysUnequal(Relation rel, HeapTuple oldtup, HeapTuple newtup,
+static bool ri_AllKeysChanged(Relation rel, HeapTuple oldtup, HeapTuple newtup,
   const RI_ConstraintInfo *riinfo, bool rel_is_pk);
-static bool ri_OneKeyEqual(Relation rel, int column,
+static bool ri_OneKeyUnchanged(Relation rel, int column,
 			   HeapTuple oldtup, HeapTuple newtup,
 			   const RI_ConstraintInfo *riinfo, bool rel_is_pk);
 static bool ri_AttributesEqual(Oid eq_opr, Oid typeid,
@@ -932,9 +932,9 @@ RI_FKey_noaction_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to check anything if old and new keys are equal
+			 * No need to check anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowShareLock);
 return PointerGetDatum(NULL);
@@ -1281,9 +1281,9 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowExclusiveLock);
 return PointerGetDatum(NULL);
@@ -1646,9 +1646,9 @@ RI_FKey_restrict_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to check anything if old and new keys are equal
+			 * No need to check anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowShareLock);
 return PointerGetDatum(NULL);
@@ -1993,9 +1993,9 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if (ri_KeysEqual(pk_rel, old_row, new_row, riinfo, true))
+			if (ri_KeysUnchanged(pk_rel, old_row, new_row, riinfo, true))
 			{
 heap_close(fk_rel, RowExclusiveLock);
 return PointerGetDatum(NULL);
@@ -2012,13 +2012,10 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 			 * our cached plan, unless the update happens to change all
 			 * columns in the key.	Fortunately, for the most common case of a
 			 * single-column foreign key, this will be true.
-			 *
-			 * In case you're wondering, the inequality check works because we
-			 * know that the old key value has no NULLs (see above).
 			 */
 
 			use_cached_query = (riinfo.confmatchtype == FKCONSTR_MATCH_FULL) ||
-ri_AllKeysUnequal(pk_rel, old_row, new_row,
+ri_AllKeysChanged(pk_rel, old_row, new_row,
   riinfo, true);
 
 			/*
@@ -2064,7 +2061,7 @@ RI_FKey_setnull_upd(PG_FUNCTION_ARGS)
 	 * to changed columns in pk_rel's key
 	 */
 	if (riinfo.confmatchtype == FKCONSTR_MATCH_FULL ||
-		!ri_OneKeyEqual(pk_rel, i, old_row, new_row,
+		!ri_OneKeyUnchanged(pk_rel, i, old_row, new_row,
 		riinfo, true))
 	{
 		appendStringInfo(querybuf,
@@ -2389,9 +2386,9 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
 			}
 
 			/*
-			 * No need to do anything if old and new keys are equal
+			 * No need to do anything if old and new keys are unchanged
 			 */
-			if 

Re: [HACKERS] patch for implementing SPI_gettypemod()

2012-02-07 Thread Chetan Suttraway
On Tue, Feb 7, 2012 at 5:44 PM, Kevin Grittner
kevin.gritt...@wicourts.govwrote:

 Kevin Grittner  wrote:

  moved this to Replication and Recovery

 Oh, that was a different patch -- I didn't see yours.

 (It's early, and the caffeine isn't working yet.)

 Anyway, you should have plenty of options now.

 -Kevin


Thanks Kevin :)


-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb


[HACKERS] patch for preventing the specification of conflicting transaction read/write options

2012-02-07 Thread Chetan Suttraway
Hi,

This is regarding the TODO item:
Prevent the specification of conflicting transaction read/write options

listed at:
http://wiki.postgresql.org/wiki/Todo

The issue is :

SET TRANSACTION read only read write read only;

The fix involved iteration over transaction_mode_list and checking for
duplicate entries.
The patch is based on suggestions mentioned in message:
http://archives.postgresql.org/pgsql-hackers/2009-01/msg00692.php

As per this, the patch does not throw any error for the first test case
mentioned above.
It throws error only in case of conflicting modes.

For ex:
postgres=# SET TRANSACTION read only read only;
SET

postgres=# SET TRANSACTION read only read write;
ERROR:  conflicting options
LINE 1: SET TRANSACTION read only read write;
^

Below are basic unit test logs:

postgres=# SET TRANSACTION ISOLATION LEVEL serializable ISOLATION LEVEL
serializable;
SET
postgres=# SET TRANSACTION ISOLATION LEVEL serializable ISOLATION LEVEL
repeatable read;
ERROR:  conflicting options
LINE 1: SET TRANSACTION ISOLATION LEVEL serializable ISOLATION LEVEL...

postgres=# SET TRANSACTION DEFERRABLE DEFERRABLE;
SET
postgres=# SET TRANSACTION DEFERRABLE NOT DEFERRABLE;
ERROR:  conflicting options
LINE 1: SET TRANSACTION DEFERRABLE NOT DEFERRABLE;
^
postgres=# START TRANSACTION read only, read only;
START TRANSACTION
postgres=# rollback;
ROLLBACK
postgres=# START TRANSACTION read only, read write;
ERROR:  conflicting options
LINE 1: START TRANSACTION read only, read write;
postgres=# rollback;
ROLLBACK  ^
postgres=# BEGIN TRANSACTION ISOLATION LEVEL serializable, ISOLATION LEVEL
serializable;
BEGIN
postgres=# rollback;
ROLLBACK
postgres=# BEGIN TRANSACTION ISOLATION LEVEL serializable, ISOLATION LEVEL
repeatable read;
ERROR:  conflicting options
LINE 1: BEGIN TRANSACTION ISOLATION LEVEL serializable, ISOLATION LE...
  ^

Please pass on any inputs on the patch.

Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 62fde67..28c987f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -146,6 +146,9 @@ static void processCASbits(int cas_bits, int location, const char *constrType,
 			   bool *deferrable, bool *initdeferred, bool *not_valid,
 			   core_yyscan_t yyscanner);
 
+static void
+check_trans_mode(List *list, DefElem *elem, core_yyscan_t yyscanner);
+
 %}
 
 %pure-parser
@@ -7510,9 +7513,16 @@ transaction_mode_list:
 			transaction_mode_item
 	{ $$ = list_make1($1); }
 			| transaction_mode_list ',' transaction_mode_item
-	{ $$ = lappend($1, $3); }
+	{
+		check_trans_mode((List *)$1, (DefElem *)$3, yyscanner);
+		$$ = lappend($1, $3);
+	}
+
 			| transaction_mode_list transaction_mode_item
-	{ $$ = lappend($1, $2); }
+	{
+		check_trans_mode((List *)$1, (DefElem *)$2, yyscanner);
+		$$ = lappend($1, $2);
+	}
 		;
 
 transaction_mode_list_or_empty:
@@ -13215,6 +13225,57 @@ parser_init(base_yy_extra_type *yyext)
 }
 
 /*
+ * checks for conflicting transaction modes by looking up current
+ * element in the given list.
+ */
+static void
+check_trans_mode(List *list, DefElem *elem, core_yyscan_t yyscanner)
+{
+	ListCell *lc;
+	A_Const *elem_arg;
+
+	elem_arg =(A_Const *) elem-arg;
+
+	/* cannot specify conflicting value for transaction mode. */
+	foreach (lc, list)
+	{
+		DefElem *next;
+		A_Const *arg;
+
+		next = (DefElem*)lfirst (lc);
+		arg = (A_Const *)next-arg;
+
+		/* check for duplicate value in remaining list */
+		if (strcmp(elem-defname, next-defname) == 0)
+		{
+			/*
+			 * isolation level values are stored
+			 * as string whereas other modes are
+			 * stored as integer values.
+			 */
+			if (strcmp(elem-defname,transaction_isolation) == 0)
+			{
+/* check for conflicting values */
+if (strcmp(elem_arg-val.val.str,arg-val.val.str)!= 0)
+			ereport(ERROR,
+(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting options),
+	 parser_errposition(arg-location)));
+			}
+			else
+			{
+/* check for conflicting values */
+if (elem_arg-val.val.ival != arg-val.val.ival)
+			ereport(ERROR,
+(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg(conflicting options),
+	 parser_errposition(arg-location)));
+			}
+		}
+	}
+}
+
+/*
  * Must undefine this stuff before including scan.c, since it has different
  * definitions for these macros.
  */

-- 
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] patch for preventing the specification of conflicting transaction read/write options

2012-02-07 Thread Chetan Suttraway
On Tue, Feb 7, 2012 at 8:44 PM, Kevin Grittner
kevin.gritt...@wicourts.govwrote:

 Chetan Suttraway chetan.suttra...@enterprisedb.com wrote:

  This is regarding the TODO item:
  Prevent the specification of conflicting transaction read/write
  options
 
  listed at:
  http://wiki.postgresql.org/wiki/Todo

 Thanks for chipping away at items on the list.

  Please pass on any inputs on the patch.

 Right now we want people focusing on fixing bugs and reviewing
 patches submitted by the start of the current CommitFest.  Please
 add this to the open CF so we don't lose track of it.

 -Kevin


Sure.

Thanks!

-Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb


Re: [HACKERS] patch for implementing SPI_gettypemod()

2012-02-07 Thread Chetan Suttraway
On Wed, Feb 8, 2012 at 8:15 AM, Robert Haas robertmh...@gmail.com wrote:

 On Tue, Feb 7, 2012 at 4:25 PM, Peter Eisentraut pete...@gmx.net wrote:
  On ons, 2012-02-01 at 15:53 +0530, Chetan Suttraway wrote:
  This is regarding the TODO item :
  Add SPI_gettypmod() to return a field's typemod from a TupleDesc
 
  My first thought was, this should be spelled SPI_gettypmod().  Not sure
  what others think.

 +1.


The reason for using SPI_gettypemod() name was that I did see
SPI_gettypeid().

Anyways, will update patch with recommended name.

Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb


Re: [HACKERS] patch for implementing SPI_gettypemod()

2012-02-07 Thread Chetan Suttraway
On Wed, Feb 8, 2012 at 12:19 PM, Chetan Suttraway 
chetan.suttra...@enterprisedb.com wrote:



 On Wed, Feb 8, 2012 at 8:15 AM, Robert Haas robertmh...@gmail.com wrote:

 On Tue, Feb 7, 2012 at 4:25 PM, Peter Eisentraut pete...@gmx.net wrote:
  On ons, 2012-02-01 at 15:53 +0530, Chetan Suttraway wrote:
  This is regarding the TODO item :
  Add SPI_gettypmod() to return a field's typemod from a TupleDesc
 
  My first thought was, this should be spelled SPI_gettypmod().  Not sure
  what others think.

 +1.


 The reason for using SPI_gettypemod() name was that I did see
 SPI_gettypeid().

 Anyways, will update patch with recommended name.


 Regards,
 Chetan



Please refer the attached patch  which now uses SPI_gettypmod() name.

Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 81f284c..1f4632e 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -955,6 +955,24 @@ SPI_gettypeid(TupleDesc tupdesc, int fnumber)
 		return (SystemAttributeDefinition(fnumber, true))-atttypid;
 }
 
+int4
+SPI_gettypmod(TupleDesc tupdesc, int fnumber)
+{
+	SPI_result = 0;
+
+	if (fnumber  tupdesc-natts || fnumber == 0 ||
+		fnumber = FirstLowInvalidHeapAttributeNumber)
+	{
+		SPI_result = SPI_ERROR_NOATTRIBUTE;
+		return -1;
+	}
+
+	if (fnumber  0)
+		return tupdesc-attrs[fnumber - 1]-atttypmod;
+	else
+		return (SystemAttributeDefinition(fnumber, true))-atttypmod;
+}
+
 char *
 SPI_getrelname(Relation rel)
 {
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index cfbaa14..dedb1c7 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -113,6 +113,7 @@ extern char *SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int fnumber);
 extern Datum SPI_getbinval(HeapTuple tuple, TupleDesc tupdesc, int fnumber, bool *isnull);
 extern char *SPI_gettype(TupleDesc tupdesc, int fnumber);
 extern Oid	SPI_gettypeid(TupleDesc tupdesc, int fnumber);
+extern int4 SPI_gettypmod(TupleDesc tupdesc, int fnumber);
 extern char *SPI_getrelname(Relation rel);
 extern char *SPI_getnspname(Relation rel);
 extern void *SPI_palloc(Size size);

-- 
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] patch for implementing SPI_gettypemod()

2012-02-06 Thread Chetan Suttraway
On Thu, Feb 2, 2012 at 8:11 PM, Robert Haas robertmh...@gmail.com wrote:

 On Wed, Feb 1, 2012 at 5:23 AM, Chetan Suttraway
 chetan.suttra...@enterprisedb.com wrote:
  Hi All,
 
  This is regarding the TODO item :
  Add SPI_gettypmod() to return a field's typemod from a TupleDesc
 
  The related message is:
  http://archives.postgresql.org/pgsql-hackers/2005-11/msg00250.php
 
  This basically talks about having an SPI_gettypemod() which returns the
  typmod of a field of tupdesc
 
  Please refer the attached patch based on the suggested implementation.

 Please add this to the next CommitFest:

 https://commitfest.postgresql.org/action/commitfest_view/open

 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company


At the given link, I am able to choose only System administration under
commitfest topic.
I think there has to be server features or Miscellaneous.


Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb


[HACKERS] patch for implementing SPI_gettypemod()

2012-02-01 Thread Chetan Suttraway
Hi All,

This is regarding the TODO item :
Add SPI_gettypmod() to return a field's typemod from a TupleDesc

The related message is:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg00250.php

This basically talks about having an SPI_gettypemod() which returns the
typmod of a field of tupdesc

Please refer the attached patch based on the suggested implementation.


Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB Blog : http://blogs.enterprisedb.com
Follow us on Twitter : http://www.twitter.com/enterprisedb
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 81f284c..659122e 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -955,6 +955,24 @@ SPI_gettypeid(TupleDesc tupdesc, int fnumber)
 		return (SystemAttributeDefinition(fnumber, true))-atttypid;
 }
 
+int4
+SPI_gettypemod(TupleDesc tupdesc, int fnumber)
+{
+	SPI_result = 0;
+
+	if (fnumber  tupdesc-natts || fnumber == 0 ||
+		fnumber = FirstLowInvalidHeapAttributeNumber)
+	{
+		SPI_result = SPI_ERROR_NOATTRIBUTE;
+		return -1;
+	}
+
+	if (fnumber  0)
+		return tupdesc-attrs[fnumber - 1]-atttypmod;
+	else
+		return (SystemAttributeDefinition(fnumber, true))-atttypmod;
+}
+
 char *
 SPI_getrelname(Relation rel)
 {
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index cfbaa14..a358710 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -113,6 +113,7 @@ extern char *SPI_getvalue(HeapTuple tuple, TupleDesc tupdesc, int fnumber);
 extern Datum SPI_getbinval(HeapTuple tuple, TupleDesc tupdesc, int fnumber, bool *isnull);
 extern char *SPI_gettype(TupleDesc tupdesc, int fnumber);
 extern Oid	SPI_gettypeid(TupleDesc tupdesc, int fnumber);
+extern int4 SPI_gettypemod(TupleDesc tupdesc, int fnumber);
 extern char *SPI_getrelname(Relation rel);
 extern char *SPI_getnspname(Relation rel);
 extern void *SPI_palloc(Size size);

-- 
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] Confusing EXPLAIN output in case of inherited tables

2012-01-11 Thread Chetan Suttraway
On Wed, Jan 11, 2012 at 5:13 PM, Ashutosh Bapat 
ashutosh.ba...@enterprisedb.com wrote:

 Hi,
 After running regression, I ran EXPLAIN on one of the queries in
 regression (test create_misc) and got following output
 regression=# explain verbose select * into table ramp from road where name
 ~ '.*Ramp';
  QUERY
 PLAN

 
  Result  (cost=0.00..154.00 rows=841 width=67)
Output: public.road.name, public.road.thepath
-  Append  (cost=0.00..154.00 rows=841 width=67)
  -  Seq Scan on public.road  (cost=0.00..135.05 rows=418 width=67)
Output: public.road.name, public.road.thepath
Filter: (public.road.name ~ '.*Ramp'::text)
  -  Seq Scan on public.ihighway road  (cost=0.00..14.99 rows=367
 width=67)
 ^
Output: public.road.name, public.road.thepath
^^,   ^^
Filter: (public.road.name ~ '.*Ramp'::text)
  ^^^
  -  Seq Scan on public.shighway road  (cost=0.00..3.96 rows=56
 width=67)
Output: public.road.name, public.road.thepath
Filter: (public.road.name ~ '.*Ramp'::text)
 (12 rows)

 regression=# \d+ road
 Table public.road
  Column  | Type | Modifiers | Storage  | Stats target | Description
 -+--+---+--+--+-
  name| text |   | extended |  |
  thepath | path |   | extended |  |
 Indexes:
 rix btree (name)
 Child tables: ihighway,
   shighway
 Has OIDs: no

 Table road has children ihighway and shighway as seen in the \d+
 output above. The EXPLAIN output of Seq Scan node on children has
 public.road as prefix for variables. public.road could imply the parent
 table road and thus can cause confusion, as to what's been referreed, the
 columns of parent table or child table. In the EXPLAIN output children
 tables have road as alias (as against public.road). The alias comes
 from RangeTblEntry-eref-aliasname. It might be better to have road as
 prefix in the variable names over public.road.

 The reason why this happens is the code in get_variable()
 3865 /* Exceptions occur only if the RTE is alias-less */
 3866 if (rte-alias == NULL)
 3867 {
 3868 if (rte-rtekind == RTE_RELATION)
 3869 {
 3870 /*
 3871  * It's possible that use of the bare refname would find
 another
 3872  * more-closely-nested RTE, or be ambiguous, in which
 case we need
 3873  * to specify the schemaname to avoid these errors.
 3874  */
 3875 if (find_rte_by_refname(rte-eref-aliasname, context) !=
 rte)
 3876 schemaname =
 get_namespace_name(get_rel_namespace(rte-relid));
 3877 }

 If there is no alias, we find out the schema name and later add it to the
 prefix. In the inherited table case, we are actually creating a kind of
 alias for the children table and thus we should not find out the schema
 name and add it to the prefix. This case has been taken care of in
 get_from_clause_item(),
 6505 else if (rte-rtekind == RTE_RELATION 
 6506 strcmp(rte-eref-aliasname,
 get_relation_name(rte-relid)) != 0)
 6507 {
 6508 /*
 6509  * Apparently the rel has been renamed since the rule was
 made.
 6510  * Emit a fake alias clause so that variable references
 will still
 6511  * work.  This is not a 100% solution but should work in
 most
 6512  * reasonable situations.
 6513  */
 6514 appendStringInfo(buf,  %s,
 6515  quote_identifier(rte-eref-aliasname));
 6516 gavealias = true;
 6517 }

 I see similar code in ExplainTargetRel()
 1778 if (objectname == NULL ||
 1779 strcmp(rte-eref-aliasname, objectname) != 0)
 1780 appendStringInfo(es-str,  %s,
 1781  quote_identifier(rte-eref-aliasname));

 Based on this, here is patch to not add schemaname in the prefix for a
 variable.

 I have run make check. All except inherit.sql passed. The expected output
 change is included in the patch.

 --
 Best Wishes,
 Ashutosh Bapat
 EntepriseDB Corporation
 The Enterprise Postgres Company



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


A table can inherit from one or more parent table. So in that case,
qualifying schema/table name
helps in finding out where the column is coming from.

Regards,
Chetan

-- 
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

 Website: www.enterprisedb.com
EnterpriseDB 

Re: [HACKERS] initdb failing for chinese locale on windows machine

2010-04-13 Thread Chetan Suttraway
On Tue, Apr 13, 2010 at 6:08 PM, Magnus Hagander mag...@hagander.netwrote:

 On Tue, Apr 13, 2010 at 14:25, Chetan Suttraway
 chetan.suttra...@enterprisedb.com wrote:
  Hi,
 
  I am trying to install postgres 8.4.3.1 on windows  machine which has
  chinese locale.
  The install wizard throws warning message and continues.
  However the logs show that initdb failed and so the service was not
 started.
 
  Have tested this on windows 95 but I think this problem can be reproduced
 on
  latest windows release.

 PostgreSQL is not supported on Windows 95 as a server, and has never
 been. Only under cygwin, in which case you should be using a
 completely different installer.

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


This is reproducible on windows xp.


-- 
Chetan Sutrave
http://www.enterprisedb.com


Re: [HACKERS] Auto-extending table partitions?

2010-01-07 Thread Chetan Suttraway
Adding on to this use case:
what do we do when we reach end of year?
Probably auto-archive as per weekly, monthly , quarterly or yearly tables?


On Thu, Jan 7, 2010 at 1:14 AM, Josh Berkus j...@agliodbs.com wrote:

 On 1/6/10 9:13 AM, Robert Haas wrote:
  On Wed, Jan 6, 2010 at 12:06 PM, David Fetter da...@fetter.org wrote:
  On Tue, Jan 05, 2010 at 08:50:25PM -0700, u235sentinel wrote:
  Robert Haas wrote:
  Getting full?
 
  ...Robert
 
  Ok.  Bad analogy.  We have the tables setup to write data according
  to the month it was loaded.  We have a December table, a January
  table and so on.  Basically following the examples given on the 8.4
  web site.

 FWIW, our roadmap is to add a 2nd type or partitioning which would be on
 the sub-table level and much more automated for that reason.

 --Josh Berkus

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




-- 
Chetan Sutrave
http://www.enterprisedb.com


Re: [HACKERS] Thoughts on statistics for continuously advancing columns

2010-01-05 Thread Chetan Suttraway
Hi,

My suggestion is to keep two sets of histograms. One which is generated by
running ANALYZE and
the other which is dynamically generated histograms using the entries from
logging (that is done
in insert/update/delete operations).
I am not sure how difficult is it to read  such record details from logs.

Basically from the details mentioned here what i understand is that the
table data (timestamp) is added
in incremental way, ie existing data is not modified to great extent and the
new data is
merely appended to old data.
In this case, the only work for analyse/statistics generation is to merge
the histograms of newly added records to old histograms.

if we can treat this case as similar to that of merging of histograms in
case of joins involving 2 tables and generating the histograms for the
cartesian (result) node, then all we need to do is somehow generate
temporary histogram for the new set of records and merge them with the old
histogram.
The information of interesting columns from the new records can be read from
the logging section.
We must be already having the part of merging of histograms and I hope that
it wont be very costly
to make these calls so as to effect planner.
(Further my opinion is to calculate this cost of histogram generation and
use it in costing in some way)

Further we can put some threshold limit to make this merge happen
automatically. Say if the temporary histograms reach some set threshold,
only then these will be merged with the older histograms.

Please pass on your inputs.

Regards,
Chetan


On Wed, Dec 30, 2009 at 8:38 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Josh Berkus j...@agliodbs.com writes:
  My thoughts on dealing with this intelligently without a major change to
  statstics gathering went along these lines:

  1. add columns to pg_statistic to hold estimates of upper and lower
  bounds growth between analyzes.

 This seems like a fundamentally broken approach, first because time
 between analyzes is not even approximately a constant, and second
 because it assumes that we have a distance metric for all datatypes.
 (Note that convert_to_scalar does not assume that it can measure
 arbitrary distances, but only fractions *within* a histogram bucket;
 and even that is pretty shaky.)

 I don't have a better idea at the moment :-(

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