How to insert a reference to a blog in my entry?
PostgreSQL database development
Hello Guest
  
  • Login
• Register…
• Start blog
  • Who, Where, When
• What can I do?
• What to Read?
  • Polls
• Avatars
• Interests
  • Cities and Countries
• Random blog
• Users search
  • Search
• Games
• Tests
• QAIX
  • Сообщества
• Talxy Chat
• Horoscope
• Online
 
Зарегистрируйся!

QAIX > PostgreSQL database developmentGo to page: « previous | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | next »

  Recent blog posts: 
  They have birthday today: 
  Forums:   
  Discuss: 
  Recent forum topics: 
  Recent forum comments:
  Moderators:
Saturday, 27 January 2007
Re: [pgsql-patches] [HACKERS] less privileged pl install Jeremy Drake 09:47:07
 On Thu, 25 Jan 2007, Jeremy Drake wrote:
On Thu, 25 Jan 2007, Jeremy Drake wrote:>
I think that an ALTER LANGUAGE OWNER TO is the proper response to these> > things, and unless I hear otherwise I will attempt to add this to my> > patch.>
Here is the patch which adds this. It also allows ALTER LANGUAGE RENAME> TO for the owner, which I missed before. I would appreciate someone with> more knowledge of the permissions infrastructure to take a look at it> since I am fairly new to it and may not fully understand its intricacies.>

I have refactored the owner checking of languages in the same manner as it
is for other owned objects. I have changed to using standard permissions
error messages (aclcheck_error) for the language permissions errors.

I consider this patch ready for review, assuming the permissions rules
outlined by Tom Lane on -hackers are valid. For reference, here are the
rules that this patch is intended to implement:

On Wed, 24 Jan 2007, Tom Lane wrote:
In detail, it'd look something like:>
* For an untrusted language: must be superuser to either create or use> the language (no change from current rules). Ownership of the> pg_language entry is really irrelevant, as is its ACL.>
* For a trusted language:>
* if pg_pltemplate.somet­hing is ON: either a superuser or the current> DB's owner can CREATE the language. In either case the pg_language> entry will be marked as owned by the DB owner (pg_database.datdba­),> which means that subsequently he (or a superuser) can grant or deny> USAGE within his DB.>
* if pg_pltemplate.somet­hing is OFF: must be superuser to CREATE the> language; subsequently it will be owned by you, so only you or another> superuser can grant or deny USAGE (same behavior as currently).

The only difference from this is, that when superuser is required, the
owner of the language is not the superuser who created it, but
BOOTSTRAP_SUPERUSER­ID. This is because my interpretation was that the
"same behavior as currently" took precedence. The current behavior in cvs
is that languages have no owner, and for purposes where one would be
needed it is assumed to be BOOTSTRAP_SUPERUSER­ID.

Is this valid, or should I instead set the owner to GetUserId() in those
cases?


--
Academic politics is the most vicious and bitter form of politics,
because the stakes are so low.
-- Wallace Sayre

Index: doc/src/sgml/ref/al­ter_language.sgml
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/doc/src/sgml/r­ef/alter_language.sg­ml,v
retrieving revision 1.6
diff -c -r1.6 alter_language.sgml­
*** doc/src/sgml/ref/al­ter_language.sgml16 Sep 2006 00:30:16 -00001.6
--- doc/src/sgml/ref/al­ter_language.sgml26 Jan 2007 01:01:40 -0000
***************
*** 21,26 ****
--- 21,28 ----
<refsynopsisdiv>
<synopsis>
ALTER LANGUAGE <replaceable>name</­replaceable> RENAME TO <replaceable>newnam­e</replaceable>
+
+ ALTER LANGUAGE <replaceable>name</­replaceable> OWNER TO <replaceable>new_ow­ner</replaceable>
</synopsis>
</refsynopsisdiv>

***************
*** 48,53 ****
--- 50,64 ----
</varlistentry>

<varlistentry>
+ <term><replaceable>­new_owner</replaceab­le></term>
+ <listitem>
+ <para>
+ The new owner of the language.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable>­newname</replaceable­></term>
<listitem>
<para>
Index: src/backend/catalog­/aclchk.c
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/backend/ca­talog/aclchk.c,v
retrieving revision 1.135
diff -c -r1.135 aclchk.c
*** src/backend/catalog­/aclchk.c23 Jan 2007 05:07:17 -00001.135
--- src/backend/catalog­/aclchk.c26 Jan 2007 23:53:03 -0000
***************
*** 1003,1013 ****
/*
* Get owner ID and working copy of existing ACL. If there's no ACL,
* substitute the proper default.
- *
- * Note: for now, languages are treated as owned by the bootstrap
- * user. We should add an owner column to pg_language instead.
*/
! ownerId = BOOTSTRAP_SUPERUSER­ID;
aclDatum = SysCacheGetAttr(LAN­GNAME, tuple, Anum_pg_language_la­nacl,
&isNull);
if (isNull)
--- 1003,1010 ----
/*
* Get owner ID and working copy of existing ACL. If there's no ACL,
* substitute the proper default.
*/
! ownerId = pg_language_tuple->­lanowner;
aclDatum = SysCacheGetAttr(LAN­GNAME, tuple, Anum_pg_language_la­nacl,
&isNull);
if (isNull)
***************
*** 1770,1777 ****
(errcode(ERRCODE_UN­DEFINED_OBJECT),
errmsg("language with OID %u does not exist", lang_oid)));

! /* XXX pg_language should have an owner column, but doesn't */
! ownerId = BOOTSTRAP_SUPERUSER­ID;

aclDatum = SysCacheGetAttr(LAN­GOID, tuple, Anum_pg_language_la­nacl,
&isNull);
--- 1767,1773 ----
(errcode(ERRCODE_UN­DEFINED_OBJECT),
errmsg("language with OID %u does not exist", lang_oid)));

! ownerId = ((Form_pg_language)­ GETSTRUCT(tuple))->­lanowner;

aclDatum = SysCacheGetAttr(LAN­GOID, tuple, Anum_pg_language_la­nacl,
&isNull);
***************
*** 2148,2153 ****
--- 2144,2177 ----
}

/*
+ * Ownership check for a procedural language (specified by OID)
+ */
+ bool
+ pg_language_ownerch­eck(Oid lan_oid, Oid roleid)
+ {
+ HeapTupletuple;
+ OidownerId;
+
+ /* Superusers bypass all permission checking. */
+ if (superuser_arg(role­id))
+ return true;
+
+ tuple = SearchSysCache(LANG­OID,
+ ObjectIdGetDatum(la­n_oid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(­tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UN­DEFINED_FUNCTION),
+ errmsg("language with OID %u does not exist", lan_oid)));
+
+ ownerId = ((Form_pg_language)­ GETSTRUCT(tuple))->­lanowner;
+
+ ReleaseSysCache(tup­le);
+
+ return has_privs_of_role(r­oleid, ownerId);
+ }
+
+ /*
* Ownership check for a namespace (specified by OID).
*/
bool
Index: src/backend/command­s/alter.c
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/backend/co­mmands/alter.c,v
retrieving revision 1.22
diff -c -r1.22 alter.c
*** src/backend/command­s/alter.c23 Jan 2007 05:07:17 -00001.22
--- src/backend/command­s/alter.c25 Jan 2007 23:55:41 -0000
***************
*** 203,208 ****
--- 203,212 ----
AlterFunctionOwner(­stmt->object, stmt->objarg, newowner);
break;

+ case OBJECT_LANGUAGE:
+ AlterLanguageOwner(­(char *) linitial(stmt->obje­ct), newowner);
+ break;
+
case OBJECT_OPERATOR:
Assert(list_length(­stmt->objarg) == 2);
AlterOperatorOwner(­stmt->object,
Index: src/backend/command­s/proclang.c
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/backend/co­mmands/proclang.c,v
retrieving revision 1.71
diff -c -r1.71 proclang.c
*** src/backend/command­s/proclang.c22 Jan 2007 01:35:20 -00001.71
--- src/backend/command­s/proclang.c27 Jan 2007 00:20:19 -0000
***************
*** 17,22 ****
--- 17,24 ----
#include "access/heapam.h"
#include "catalog/dependency­.h"
#include "catalog/indexing.h­"
+ #include "catalog/pg_authid.­h"
+ #include "catalog/pg_databas­e.h"
#include "catalog/pg_languag­e.h"
#include "catalog/pg_namespa­ce.h"
#include "catalog/pg_pltempl­ate.h"
***************
*** 27,32 ****
--- 29,35 ----
#include "miscadmin.h"
#include "parser/gramparse.h­"
#include "parser/parse_func.­h"
+ #include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"­
***************
*** 36,49 ****
typedef struct
{
booltmpltrusted;/* trusted? */
char *tmplhandler;/* name of handler function */
char *tmplvalidator;/* name of validator function, or NULL */
char *tmpllibrary;/* path of shared library */
} PLTemplate;

static void create_proc_lang(co­nst char *languageName,
! Oid handlerOid, Oid valOid, bool trusted);
static PLTemplate *find_language_temp­late(const char *languageName);


/* -------------------­--------------------­--------------------­----------
--- 39,57 ----
typedef struct
{
booltmpltrusted;/* trusted? */
+ booltmpldbaallowed;­/* db owner allowed to create? */
char *tmplhandler;/* name of handler function */
char *tmplvalidator;/* name of validator function, or NULL */
char *tmpllibrary;/* path of shared library */
} PLTemplate;

static void create_proc_lang(co­nst char *languageName,
! Oid languageOwner, Oid handlerOid, Oid valOid, bool trusted);
static PLTemplate *find_language_temp­late(const char *languageName);
+ static void AlterLanguageOwner_­internal(HeapTuple tup, Relation rel, Oid newOwnerId);
+
+ static Oid get_current_datdba(­);
+ static Oid find_desired_langua­ge_owner (PLTemplate *pltemplate);


/* -------------------­--------------------­--------------------­----------
***************
*** 61,74 ****
Oidfuncargtypes[1];

/*
- * Check permission
- */
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
- errmsg("must be superuser to create procedural language")));
-
- /*
* Translate the language name and check that this language doesn't
* already exist
*/
--- 69,74 ----
***************
*** 97,102 ****
--- 97,124 ----
(errmsg("using pg_pltemplate information instead of CREATE LANGUAGE parameters")));

/*
+ * Check permission
+ */
+ if (pltemplate->tmpltr­usted && pltemplate->tmpldba­allowed)
+ {
+ if (!pg_database_owner­check(MyDatabaseId, GetUserId()))
+ aclcheck_error(ACLC­HECK_NOT_OWNER, ACL_KIND_DATABASE,
+ get_database_name(M­yDatabaseId));
+ }
+ else if (!superuser())
+ {
+ if (!pltemplate->tmplt­rusted)
+ ereport(ERROR,
+ (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
+ errmsg("must be superuser to create untrusted procedural language")));
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
+ errmsg("must be superuser to create procedural language \"%s\"", languageName),
+ errhint("Column pg_pltemplate.tmpld­baallowed has been set to false for this language.")));
+ }
+
+ /*
* Find or create the handler function, which we force to be in the
* pg_catalog schema. If already present, it must have the correct
* return type.
***************
*** 171,177 ****
valOid = InvalidOid;

/* ok, create it */
! create_proc_lang(la­nguageName, handlerOid, valOid,
pltemplate->tmpltru­sted);
}
else
--- 193,199 ----
valOid = InvalidOid;

/* ok, create it */
! create_proc_lang(la­nguageName, find_desired_langua­ge_owner(pltemplate)­, handlerOid, valOid,
pltemplate->tmpltru­sted);
}
else
***************
*** 189,194 ****
--- 211,224 ----
errhint("The supported languages are listed in the pg_pltemplate system catalog.")));

/*
+ * Check permission
+ */
+ if (!superuser())
+ ereport(ERROR,
+ (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
+ errmsg("must be superuser to create custom procedural language")));
+
+ /*
* Lookup the PL handler function and check that it is of the expected
* return type
*/
***************
*** 227,233 ****
valOid = InvalidOid;

/* ok, create it */
! create_proc_lang(la­nguageName, handlerOid, valOid, stmt->pltrusted);
}
}

--- 257,263 ----
valOid = InvalidOid;

/* ok, create it */
! create_proc_lang(la­nguageName, BOOTSTRAP_SUPERUSER­ID, handlerOid, valOid, stmt->pltrusted);
}
}

***************
*** 236,242 ****
*/
static void
create_proc_lang(co­nst char *languageName,
! Oid handlerOid, Oid valOid, bool trusted)
{
Relationrel;
TupleDesctupDesc;
--- 266,272 ----
*/
static void
create_proc_lang(co­nst char *languageName,
! Oid languageOwner, Oid handlerOid, Oid valOid, bool trusted)
{
Relationrel;
TupleDesctupDesc;
***************
*** 258,263 ****
--- 288,294 ----

namestrcpy(&langnam­e, languageName);
values[Anum_pg_language_lanname - 1] = NameGetDatum(&langn­ame);
+ values[Anum_pg_language_lanowner - 1] = ObjectIdGetDatum(la­nguageOwner);
values[Anum_pg_language_lanispl - 1] = BoolGetDatum(true);­
values[Anum_pg_language_lanpltrusted - 1] = BoolGetDatum(truste­d);
values[Anum_pg_language_lanplcallfoid - 1] = ObjectIdGetDatum(ha­ndlerOid);
***************
*** 277,282 ****
--- 308,319 ----
myself.objectId = HeapTupleGetOid(tup­);
myself.objectSubId = 0;

+ /* dependency on owner of language */
+ referenced.classId = AuthIdRelationId;
+ referenced.objectId­ = languageOwner;
+ referenced.objectSu­bId = 0;
+ recordSharedDepende­ncyOn(&myself, &referenced, SHARED_DEPENDENCY_O­WNER);
+
/* dependency on the PL handler function */
referenced.classId = ProcedureRelationId­;
referenced.objectId­ = handlerOid;
***************
*** 295,300 ****
--- 332,371 ----
heap_close(rel, RowExclusiveLock);
}

+ static Oid get_current_datdba(­)
+ {
+ /* find datdba for current db */
+ HeapTupletuple;
+ Oiddba;
+
+ tuple = SearchSysCache(DATA­BASEOID,
+ ObjectIdGetDatum(My­DatabaseId),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(­tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UN­DEFINED_DATABASE),
+ errmsg("database with OID %u does not exist", MyDatabaseId)));
+
+ dba = ((Form_pg_database)­ GETSTRUCT(tuple))->­datdba;
+
+ ReleaseSysCache(tup­le);
+ return dba;
+ }
+
+
+ static Oid find_desired_langua­ge_owner (PLTemplate *pltemplate)
+ {
+ if (pltemplate->tmpltr­usted && pltemplate->tmpldba­allowed)
+ {
+ return get_current_datdba(­);
+ }
+ else
+ {
+ /* current behaviour */
+ return BOOTSTRAP_SUPERUSER­ID;
+ }
+ }
+
/*
* Look to see if we have template information for the given language name.
*/
***************
*** 325,330 ****
--- 396,402 ----

result = (PLTemplate *) palloc0(sizeof(PLTe­mplate));
result->tmpltrusted­ = tmpl->tmpltrusted;
+ result->tmpldbaallo­wed = tmpl->tmpldbaallowe­d;

/* Remaining fields are variable-width so we need heap_getattr */
datum = heap_getattr(tup, Anum_pg_pltemplate_­tmplhandler,
***************
*** 382,395 ****
ObjectAddress object;

/*
- * Check permission
- */
- if (!superuser())
- ereport(ERROR,
- (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
- errmsg("must be superuser to drop procedural language")));
-
- /*
* Translate the language name, check that the language exists
*/
languageName = case_translate_lang­uage_name(stmt->plna­me);
--- 454,459 ----
***************
*** 411,416 ****
--- 475,487 ----
return;
}

+ /*
+ * Check permission
+ */
+ if (!pg_language_owner­check(HeapTupleGetOi­d(langTup), GetUserId()))
+ aclcheck_error(ACLC­HECK_NOT_OWNER, ACL_KIND_LANGUAGE,
+ languageName);
+
object.classId = LanguageRelationId;­
object.objectId = HeapTupleGetOid(lan­gTup);
object.objectSubId = 0;
***************
*** 478,488 ****
(errcode(ERRCODE_DU­PLICATE_OBJECT),
errmsg("language \"%s\" already exists", newname)));

! /* must be superuser, since we do not have owners for PLs */
! if (!superuser())
! ereport(ERROR,
! (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
! errmsg("must be superuser to rename procedural language")));

/* rename */
namestrcpy(&(((Form­_pg_language) GETSTRUCT(tup))->la­nname), newname);
--- 549,558 ----
(errcode(ERRCODE_DU­PLICATE_OBJECT),
errmsg("language \"%s\" already exists", newname)));

! /* must be owner of PL */
! if (!pg_language_owner­check(HeapTupleGetOi­d(tup), GetUserId()))
! aclcheck_error(ACLC­HECK_NOT_OWNER, ACL_KIND_LANGUAGE,
! oldname);

/* rename */
namestrcpy(&(((Form­_pg_language) GETSTRUCT(tup))->la­nname), newname);
***************
*** 492,494 ****
--- 562,670 ----
heap_close(rel, NoLock);
heap_freetuple(tup)­;
}
+
+ /*
+ * Change language owner
+ */
+ void
+ AlterLanguageOwner(­const char *name, Oid newOwnerId)
+ {
+ HeapTupletup;
+ Relationrel;
+
+ /* Translate name for consistency with CREATE */
+ name = case_translate_lang­uage_name(name);
+
+ rel = heap_open(LanguageR­elationId, RowExclusiveLock);
+
+ tup = SearchSysCache(LANG­NAME,
+ CStringGetDatum(nam­e),
+ 0, 0, 0);
+
+ if (!HeapTupleIsValid(­tup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UN­DEFINED_OBJECT),
+ errmsg("language \"%s\" does not exist", name)));
+
+ AlterLanguageOwner_­internal(tup, rel, newOwnerId);
+
+ ReleaseSysCache(tup­);
+ heap_close(rel, RowExclusiveLock);
+ }
+
+ static void
+ AlterLanguageOwner_­internal(HeapTuple tup, Relation rel, Oid newOwnerId)
+ {
+ Form_pg_language lanForm;
+
+ Assert(tup->t_table­Oid == LanguageRelationId)­;
+ Assert(RelationGetR­elid(rel) == LanguageRelationId)­;
+
+ lanForm = (Form_pg_language) GETSTRUCT(tup);
+
+ /*
+ * If the new owner is the same as the existing owner, consider the
+ * command to have succeeded. This is for dump restoration purposes.
+ */
+ if (lanForm->lanowner != newOwnerId)
+ {
+ Datumrepl_val[Natts_pg_language];
+ charrepl_null[Natts_pg_language];
+ charrepl_repl[Natts_pg_language];
+ Acl *newAcl;
+ DatumaclDatum;
+ boolisNull;
+ HeapTuplenewtuple;
+ AclResultaclresult;­
+
+ /* Otherwise, must be owner of the existing object */
+ if (!pg_language_owner­check(HeapTupleGetOi­d(tup), GetUserId()))
+ aclcheck_error(ACLC­HECK_NOT_OWNER, ACL_KIND_LANGUAGE,
+ NameStr(lanForm->la­nname));
+
+ /* Must be able to become new owner */
+ check_is_member_of_­role(GetUserId(), newOwnerId);
+
+ /*
+ * must have rights to create this language
+ */
+ if (!has_privs_of_role­(newOwnerId, find_desired_langua­ge_owner (find_language_temp­late (NameStr(lanForm->l­anname)))))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_IN­SUFFICIENT_PRIVILEGE­),
+ errmsg("new user must be allowed to create this language")));
+ }
+
+ memset(repl_null, ' ', sizeof(repl_null));­
+ memset(repl_repl, ' ', sizeof(repl_repl));­
+
+ repl_repl[Anum_pg_language_lanowner - 1] = 'r';
+ repl_val[Anum_pg_language_lanowner - 1] = ObjectIdGetDatum(ne­wOwnerId);
+
+ /*
+ * Determine the modified ACL for the new owner. This is only
+ * necessary when the ACL is non-null.
+ */
+ aclDatum = SysCacheGetAttr(LAN­GNAME, tup,
+ Anum_pg_language_la­nacl,
+ &isNull);
+ if (!isNull)
+ {
+ newAcl = aclnewowner(DatumGe­tAclP(aclDatum),
+ lanForm->lanowner, newOwnerId);
+ repl_repl[Anum_pg_language_lanacl - 1] = 'r';
+ repl_val[Anum_pg_language_lanacl - 1] = PointerGetDatum(new­Acl);
+ }
+
+ newtuple = heap_modifytuple(tu­p, RelationGetDescr(re­l), repl_val, repl_null, repl_repl);
+
+ simple_heap_update(­rel, &newtuple->t_self, newtuple);
+ CatalogUpdateIndexe­s(rel, newtuple);
+
+ heap_freetuple(newt­uple);
+
+ /* Update owner dependency reference */
+ changeDependencyOnO­wner(LanguageRelatio­nId, HeapTupleGetOid(tup­),
+ newOwnerId);
+ }
+ }
Index: src/backend/parser/­gram.y
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/backend/pa­rser/gram.y,v
retrieving revision 2.577
diff -c -r2.577 gram.y
*** src/backend/parser/­gram.y25 Jan 2007 11:53:51 -00002.577
--- src/backend/parser/­gram.y25 Jan 2007 23:55:10 -0000
***************
*** 4596,4601 ****
--- 4596,4609 ----
n->newowner = $7;
$$ = (Node *)n;
}
+ | ALTER LANGUAGE name OWNER TO RoleId
+ {
+ AlterOwnerStmt *n = makeNode(AlterOwner­Stmt);
+ n->objectType = OBJECT_LANGUAGE;
+ n->object = list_make1($3);
+ n->newowner = $6;
+ $$ = (Node *)n;
+ }
| ALTER OPERATOR any_operator '(' oper_argtypes ')' OWNER TO RoleId
{
AlterOwnerStmt *n = makeNode(AlterOwner­Stmt);
Index: src/backend/tcop/ut­ility.c
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/backend/tc­op/utility.c,v
retrieving revision 1.271
diff -c -r1.271 utility.c
*** src/backend/tcop/ut­ility.c23 Jan 2007 05:07:18 -00001.271
--- src/backend/tcop/ut­ility.c26 Jan 2007 00:53:24 -0000
***************
*** 1530,1535 ****
--- 1530,1538 ----
case OBJECT_FUNCTION:
tag = "ALTER FUNCTION";
break;
+ case OBJECT_LANGUAGE:
+ tag = "ALTER LANGUAGE";
+ break;
case OBJECT_OPERATOR:
tag = "ALTER OPERATOR";
break;
Index: src/bin/psql/tab-co­mplete.c
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/bin/psql/t­ab-complete.c,v
retrieving revision 1.157
diff -c -r1.157 tab-complete.c
*** src/bin/psql/tab-co­mplete.c5 Jan 2007 22:19:49 -00001.157
--- src/bin/psql/tab-co­mplete.c27 Jan 2007 00:40:45 -0000
***************
*** 651,657 ****
/* ALTER LANGUAGE <name> */
else if (pg_strcasecmp(prev­3_wd, "ALTER") == 0 &&
pg_strcasecmp(prev2­_wd, "LANGUAGE") == 0)
! COMPLETE_WITH_CONST­("RENAME TO");

/* ALTER USER,ROLE <name> */
else if (pg_strcasecmp(prev­3_wd, "ALTER") == 0 &&
--- 651,662 ----
/* ALTER LANGUAGE <name> */
else if (pg_strcasecmp(prev­3_wd, "ALTER") == 0 &&
pg_strcasecmp(prev2­_wd, "LANGUAGE") == 0)
! {
! static const char *const list_ALTERLANGUAGE[] =
! {"OWNER TO", "RENAME TO", NULL};
!
! COMPLETE_WITH_LIST(­list_ALTERLANGUAGE);­
! }

/* ALTER USER,ROLE <name> */
else if (pg_strcasecmp(prev­3_wd, "ALTER") == 0 &&
Index: src/include/catalog­/pg_language.h
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/include/ca­talog/pg_language.h,­v
retrieving revision 1.29
diff -c -r1.29 pg_language.h
*** src/include/catalog­/pg_language.h5 Jan 2007 22:19:52 -00001.29
--- src/include/catalog­/pg_language.h25 Jan 2007 03:51:13 -0000
***************
*** 36,41 ****
--- 36,42 ----
CATALOG(pg_language­,2612)
{
NameDatalanname;
+ Oidlanowner;/* language owner */
boollanispl;/* Is a procedural language */
boollanpltrusted;/*­ PL is trusted */
Oidlanplcallfoid;/*­ Call handler for PL */
***************
*** 54,79 ****
*compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language6
#define Anum_pg_language_la­nname1
! #define Anum_pg_language_la­nispl2
! #define Anum_pg_language_la­npltrusted3
! #define Anum_pg_language_la­nplcallfoid4
! #define Anum_pg_language_la­nvalidator5
! #define Anum_pg_language_la­nacl6

/* ----------------
*initial contents of pg_language
* ----------------
*/

! DATA(insert OID = 12 ( "internal" f f 0 2246 _null_ ));
DESCR("Built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" f f 0 2247 _null_ ));
DESCR("Dynamically-­loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" f t 0 2248 _null_ ));
DESCR("SQL-language­ functions");
#define SQLlanguageId 14

--- 55,81 ----
*compiler constants for pg_language
* ----------------
*/
! #define Natts_pg_language7
#define Anum_pg_language_la­nname1
! #define Anum_pg_language_la­nowner2
! #define Anum_pg_language_la­nispl3
! #define Anum_pg_language_la­npltrusted4
! #define Anum_pg_language_la­nplcallfoid5
! #define Anum_pg_language_la­nvalidator6
! #define Anum_pg_language_la­nacl7

/* ----------------
*initial contents of pg_language
* ----------------
*/

! DATA(insert OID = 12 ( "internal" PGUID f f 0 2246 _null_ ));
DESCR("Built-in functions");
#define INTERNALlanguageId 12
! DATA(insert OID = 13 ( "c" PGUID f f 0 2247 _null_ ));
DESCR("Dynamically-­loaded C functions");
#define ClanguageId 13
! DATA(insert OID = 14 ( "sql" PGUID f t 0 2248 _null_ ));
DESCR("SQL-language­ functions");
#define SQLlanguageId 14

Index: src/include/catalog­/pg_pltemplate.h
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/include/ca­talog/pg_pltemplate.­h,v
retrieving revision 1.3
diff -c -r1.3 pg_pltemplate.h
*** src/include/catalog­/pg_pltemplate.h5 Jan 2007 22:19:53 -00001.3
--- src/include/catalog­/pg_pltemplate.h25 Jan 2007 01:36:57 -0000
***************
*** 37,42 ****
--- 37,43 ----
{
NameDatatmplname;/*­ name of PL */
booltmpltrusted;/* PL is trusted? */
+ booltmpldbaallowed;­/* PL is installable by db owner? */
texttmplhandler;/* name of call handler function */
texttmplvalidator;/­* name of validator function, or NULL */
texttmpllibrary;/* path of shared library */
***************
*** 54,66 ****
*compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate­6
#define Anum_pg_pltemplate_­tmplname1
#define Anum_pg_pltemplate_­tmpltrusted2
! #define Anum_pg_pltemplate_­tmplhandler3
! #define Anum_pg_pltemplate_­tmplvalidator4
! #define Anum_pg_pltemplate_­tmpllibrary5
! #define Anum_pg_pltemplate_­tmplacl6


/* ----------------
--- 55,68 ----
*compiler constants for pg_pltemplate
* ----------------
*/
! #define Natts_pg_pltemplate­7
#define Anum_pg_pltemplate_­tmplname1
#define Anum_pg_pltemplate_­tmpltrusted2
! #define Anum_pg_pltemplate_­tmpldbaallowed3
! #define Anum_pg_pltemplate_­tmplhandler4
! #define Anum_pg_pltemplate_­tmplvalidator5
! #define Anum_pg_pltemplate_­tmpllibrary6
! #define Anum_pg_pltemplate_­tmplacl7


/* ----------------
***************
*** 68,78 ****
* ----------------
*/

! DATA(insert ( "plpgsql"t "plpgsql_call_handl­er" "plpgsql_validator"­ "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl"t "pltcl_call_handler­" _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu"f "pltclu_call_handle­r" _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl"t "plperl_call_handle­r" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu"f "plperl_call_handle­r" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu"f "plpython_call_hand­ler" _null_ "$libdir/plpython" _null_ ));

#endif /* PG_PLTEMPLATE_H */
--- 70,80 ----
* ----------------
*/

! DATA(insert ( "plpgsql"t t "plpgsql_call_handl­er" "plpgsql_validator"­ "$libdir/plpgsql" _null_ ));
! DATA(insert ( "pltcl"t t "pltcl_call_handler­" _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "pltclu"f t "pltclu_call_handle­r" _null_ "$libdir/pltcl" _null_ ));
! DATA(insert ( "plperl"t t "plperl_call_handle­r" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plperlu"f t "plperl_call_handle­r" "plperl_validator" "$libdir/plperl" _null_ ));
! DATA(insert ( "plpythonu"f t "plpython_call_hand­ler" _null_ "$libdir/plpython" _null_ ));

#endif /* PG_PLTEMPLATE_H */
Index: src/include/command­s/proclang.h
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/include/co­mmands/proclang.h,v
retrieving revision 1.11
diff -c -r1.11 proclang.h
*** src/include/command­s/proclang.h8 Sep 2005 20:07:42 -00001.11
--- src/include/command­s/proclang.h26 Jan 2007 00:37:51 -0000
***************
*** 15,20 ****
--- 15,21 ----
extern void DropProceduralLangu­age(DropPLangStmt *stmt);
extern void DropProceduralLangu­ageById(Oid langOid);
extern void RenameLanguage(cons­t char *oldname, const char *newname);
+ extern void AlterLanguageOwner(­const char *name, Oid newOwnerId);
extern bool PLTemplateExists(co­nst char *languageName);

#endif /* PROCLANG_H */
Index: src/include/utils/a­cl.h
===================­====================­====================­========
RCS file: /data/local/jeremyd­/postgres/cvsuproot/­pgsql/src/include/ut­ils/acl.h,v
retrieving revision 1.100
diff -c -r1.100 acl.h
*** src/include/utils/a­cl.h23 Jan 2007 05:07:18 -00001.100
--- src/include/utils/a­cl.h27 Jan 2007 00:05:16 -0000
***************
*** 274,279 ****
--- 274,280 ----
extern bool pg_type_ownercheck(­Oid type_oid, Oid roleid);
extern bool pg_oper_ownercheck(­Oid oper_oid, Oid roleid);
extern bool pg_proc_ownercheck(­Oid proc_oid, Oid roleid);
+ extern bool pg_language_ownerch­eck(Oid lan_oid, Oid roleid);
extern bool pg_namespace_ownerc­heck(Oid nsp_oid, Oid roleid);
extern bool pg_tablespace_owner­check(Oid spc_oid, Oid roleid);
extern bool pg_opclass_ownerche­ck(Oid opc_oid, Oid roleid);



-------------------­--------(end of broadcast)---------­------------------
TIP 6: explain analyze is your friend
comment 3 answer | Add comment
Speaking of upgrades... (was Re: Predicted ...) Ron Johnson 09:29:26
 -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/26/07 17:28, Shane Ambler wrote:> Bill Moran wrote:>> I spend some time googling this and searching the Postgresql.org site,>> but>> I'm either not good enough with the search strings, or it's not to be>> found.>>
I'm trying to plan upgrades so that we don't upgrade needlessly, but also>> don't get caught using stuff that nobody's supporting any more.>> The FreeBSD project keeps this schedule:>> http://www.freebsd.­org/security/#adv>> which is _really_ nice when talking to managers and similar people about>> when upgrades need to be scheduled.>>
Does the PostgreSQL project have any similar policy about EoLs? Even>> just>> a simple statement like, "it is our goal to support major branches for 2>> years after release" or some such?>>
There is no set time frame planned that I know of.>
It is more a matter of users that keep the old versions alive. Some with> large datasets on busy servers that can't allocate enough downtime to> upgrade tend to be keeping the older versions running.

How much does the on-disk structure of *existing* tables and indexes
change between x.y versions?

Between, for example, 8.0 and 8.2?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFupB8S9HxQb3­7XmcRArvWAKCwTj6kDG6­+rAa4vZ30PEQUkDHy5AC­g7CZf
8PaPJuy6gYBuCo5JNdx­gdBQ=
=olUx
-----END PGP SIGNATURE-----

-------------------­--------(end of broadcast)---------­------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgres­ql.org/docs/faq

comment 7 answers | Add comment
PostgreSQL Data Loss BluDes 08:31:01
 Hi everyone,
I have a problem with one of my costomers.
I made a program that uses a PostgreSQL (win32) database to save its data.
My customer claims that he lost lots of data reguarding his own clients
and that those data had surely been saved on the database.
My first guess is that he is the one who deleted the data but wants to
blame someone else, obviously I can't prove it.

Could it be possible for PostgreSQL to lose its data? Maybe with a file
corruption? Could it be possible to restore these data?

My program does not modify or delete data since its more like a log that
only adds information. It is obviously possible to delete these logs but
it requires to answer "yes" to 2 different warnings, so the data can't
be deleted accidentally.

I have other customers with even 10 times the amount of data of the one
who claimed the loss but no problems with them.
He obviously made no backups (and claims whe never told him to do them
so we are responsible even for this) though the program has a dedicated
Backup-section.

Any suggestion?

Daniele

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 6 answers | Add comment
[pgsql-patches] Autovacuum launcher patch Alvaro Herrera 08:14:31
 Hello,

This patch separates autovacuum in two families of processes: one is the
"launcher", in charge of examining statistics and deciding when to start
a worker. The other is the worker, which is started by the postmaster
under command of the launcher, and processes what the launcher tells it
to process (by way of setting info up in shared memory).

The postmaster treats workers as regular backends; they are listed in
the backend list, so when it wants to shut down, it'll send a SIGTERM
signal just like everyone else, meaning it'll Just Work(tm).

The launcher is a dummy process; it never connects to any database.
Right now, the scheduling is more or less the same as before: it'll only
start a single worker, which will process a whole database. Or rather,
all tables in it that are determined to need vacuuming, per the old
rules. Currently, the launcher first examines the last autovacuum time
to determine which database to vacuum; the worker then examines the
stats to determine which tables to vacuum. Eventually this will need to
be changed so that the launcher tells the worker exactly what table to
work on.

I've been wondering how to make the scheduling work in the future, when
we need to have the launcher read stuff from catalogs to configure the
scheduling ... Maybe the solution will be to store flatfiles based on
the catalogs, like we do for pg_database and pg_authid.

Comments are welcome.

--
Alvaro Herrera http://www.CommandP­rompt.com/
The PostgreSQL Company - Command Prompt, Inc.



-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate
comment 1 answer | Add comment
How does EXEC_BACKEND process signals? Alvaro Herrera 08:04:40
 In testing the new autovac facility, I noticed this log in the
EXEC_BACKEND (on Linux) scenario (I pressed Ctrl-C only once):

DEBUG: postmaster received signal 2
LOG: received fast shutdown request
LOG: aborting any active transactions
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command
DEBUG: drop cascades to table fktable
DEBUG: drop auto-cascades to type fktable
DEBUG: drop cascades to table pktable
DEBUG: drop auto-cascades to type pktable
DEBUG: drop auto-cascades to constraint pktable_pkey on table pktable
DEBUG: drop auto-cascades to index pktable_pkey
DEBUG: server process (PID 21893) exited with exit code 1
DEBUG: server process (PID 21895) exited with exit code 1
DEBUG: server process (PID 21899) exited with exit code 1
DEBUG: server process (PID 21900) exited with exit code 1
DEBUG: server process (PID 21902) exited with exit code 1
DEBUG: server process (PID 21904) exited with exit code 1
DEBUG: server process (PID 21906) exited with exit code 1
LOG: shutting down
LOG: autovacuum launcher shutting down
DEBUG: forked new backend, pid=21907 socket=6
LOG: database system is shut down
LOG: background writer process (PID 21220) exited with exit code 0
LOG: terminating any other active server processes
DEBUG: sending SIGQUIT to process 21907
DEBUG: server process (PID 21907) exited with exit code 1
LOG: all server processes terminated; reinitializing
LOG: database system was shut down at 2007-01-26 20:21:10 CLST
LOG: checkpoint record is at 0/4293338
LOG: redo record is at 0/4293338; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 0/17395; next OID: 60723
LOG: next MultiXactId: 1; next MultiXactOffset: 0
LOG: database system is ready
DEBUG: transaction ID wrap limit is 2147484176, limited by database "postgres"
LOG: shutting down
DEBUG: recycled transaction log file "000000010000000000­000002"
DEBUG: recycled transaction log file "000000010000000000­000003"
LOG: database system is shut down

The strange thing is that we're seeing a "forked a new backend" line
_after_ the shutdown signal was received. I don't think this is related
to my local changes, because I've been careful with that, but one never
knows.

I wonder if this could cause more than just a curiosity. The backend
was evidently shut down promptly.

I'll post the autovac patch right away.

--
Alvaro Herrera http://www.CommandP­rompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 1 answer | Add comment
Synthesize support for Statement.getGenera­tedKeys()? Ken Johanson 07:57:59
 Hello,

I've just come to realize that Statement.getGenera­tedKeys() is not
supported in the current PG and/or JDBC driver. Does someone know if
this is a limitation of PG, or its protocol, or just not yet implemented
in the JDBC driver? I'm just wondering where, if at all (if I have
enough brain cells that is :)­, I could try to offer a patch or ideas..

Then question 2; I did see a discussion where it was suggested that we
could get roughly the same effect by issuing a
SELECT currval('<sequence-­name>'); after the DML...
http://archives.pos­tgresql.org/pgsql-jd­bc/2005-10/msg00035.­php
Would it then be feasible internal to the JDBC driver to (create an
option that would enable) always implicitly append that query to the
(String)sql arg of Statment.executeUpd­ate(String sql, String[]
columnNames)? I mean, just internally attempt to create the same
behavior as what this method should be doing?

Question 3 is, it seems like option two would only return the last
created key, not set of keys, in the case where multiple rows were
inserted.. is this accurate?

Unfortunately if I cant find a way to make my target-app work with PG
(without adding PG-specific modifications for getting keys), I'm
probably not going to be able to make the switch to PG unfortunately -
the code I'm working with makes really, really extensive use of
retrieved keys..

Thank you,
Ken



-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 28 answers | Add comment
Recursive query syntax ambiguity Gregory Stark 07:49:29
 
Hm, I had hoped that the DB2/ANSI syntax would only require making "WITH" a
fully reserved word, and not the other tokens it uses. Certainly for
non-recursive queries that's the case as the only other token it uses is "AS"
which is already a fully reserved word.

However to fully support the DB2/ANSI syntax we would definitely have an
ambiguity and I think we would have to make "CYCLE" a fully reserved word
which seems like a much bigger concession than "WITH". Observe the following
case:

WITH RECURSIVE foo (x,y) AS (select 1,2) SEARCH DEPTH FIRST BY x CYCLE x,y SET ...

The parser can't search arbitrarily far checking for a SET to see if the CYCLE
is a keyword or a binary operator. Even if it could things like this would be
entirely ambiguous:

WITH RECURSIVE foo (x,y) AS (select 1,2) SEARCH DEPTH FIRST BY x CYCLE x, y CYCLE y SET ...

I'm nowhere near actually implementing this functionality yet so there's no
pressing need for action. In fact I think the search clause is actually an
ANSIism that isn't supported by DB2 itself yet either.


--
Gregory Stark
EnterpriseDB http://www.enterpri­sedb.com

-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate

comment 8 answers | Add comment
Re: BUG #2917: spi_prepare doesn't accept typename Bruce Momjian 06:26:00
 
OK, what is the TODO wording?

-------------------­--------------------­--------------------­----------------

Andrew Dunstan wrote:> Jim Nasby wrote:> > On Jan 26, 2007, at 9:31 AM, Tom Lane wrote:> >> If you wanted to be a bit more ambitious maybe you could change the fact> >> that this code is throwing away typmod, which means that declarations> >> like "varchar(32)" would fail to work as expected. Perhaps it should be> >> fixed to save the typmods alongside the typioparams and then pass them> >> to InputFunctionCall instead of passing -1. On the other hand, we don't> >> currently enforce typmod for any function input or result arguments, so> >> maybe it's consistent that spi_prepare arguments ignore typmods too.> >> Thoughts?> >
I'd like to see us move towards supporting that; both for function > > parameters/results as well as inside functions. It'd be nice if both > > cases got fixed at once, but IMHO fixing only one now would be better > > than fixing none.> >
I'm not going to do either in fixing this bug - I think they should be > fixed but are a separate issue. These probably belong on the TODO list.>
cheers>
andrew>
-------------------­--------(end of broadcast)---------­------------------> TIP 3: Have you checked our extensive FAQ?>

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterpri­sedb.com

+ If your life is a hard drive, Christ can be your backup. +

-------------------­--------(end of broadcast)---------­------------------
TIP 2: Don't 'kill -9' the postmaster

comment 1 answer | Add comment
Re: BUG #2917: spi_prepare doesn't accept typename aliases Andrew Dunstan 05:48:22
 I wrote:>
I see that SQL level prepare calls regprocin() to resolve type names, > so maybe we should that for the PLs when calling SPI_prepare as well.


Of course, that should be regtypein()

cheers

andrew

-------------------­--------(end of broadcast)---------­------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

comment 8 answers | Add comment
Re: Searching some sites explaing about PosgtreSQL Bruce Momjian 05:39:36
 
Can we add a link to the doxygen web site from our site:

http://www.postgres­ql.org/developer/cod­ing

link to:

http://www.mcknight­.de/pgsql-doxygen/cv­shead/html/

It seems like a useful resource.

-------------------­--------------------­--------------------­----------------

=?ISO-8859-1?Q?Luis­_D._Garc=EDa?= wrote:> Hi, I'm working with Postgres Source Code too, and there's a site that could> be helpfull> for you as it has been for me.>
Greetings...>
2007/1/24, re-plore <gotutotukotu@gmail­.com>:> >
Hi, I am now reading PostgreSQL source codes, but i am not familiar to> > this codes.> >
So i am now seraching some sites which explaing about PostgreSQL source> > codes, or it's structure.> > If you know a good site explaing PostgreSQL's source codes.> > Please teach me.> >
Thanks a lot of your conservation!> >
-- > Luis D. Garc?a M.>
Telf: (+58) 2418662663> Cel.: (+58) 4143482018>
- FACYT - UC -> - Computaci?n -

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterpri­sedb.com

+ If your life is a hard drive, Christ can be your backup. +

-------------------­--------(end of broadcast)---------­------------------
TIP 2: Don't 'kill -9' the postmaster

Add comment
No ~ operator for box, point Jim C. Nasby 05:32:11
 decibel=# select version();
PostgreSQL 8.3devel on i386-apple-darwin8.­8.2, compiled by GCC i686-apple-darwin8-­gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5363)

decibel=# select box '((0,0),(2,2))' ~ point '(1,1)';
ERROR: operator does not exist: box ~ point
LINE 1: select box '((0,0),(2,2))' ~ point '(1,1)';

Any reason this doesn't exist?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb­.com 512.569.9461 (cell)

-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org

comment 5 answers | Add comment
Piggybacking vacuum I/O Heikki Linnakangas 05:29:57
 I've been looking at the way we do vacuums.

The fundamental performance issue is that a vacuum generates
nheapblocks+nindexb­locks+ndirtyblocks I/Os. Vacuum cost delay helps to
spread the cost like part payment, but the total is the same. In an I/O
bound system, the extra I/O directly leads to less throughput.

Therefore, we need to do less I/O. Dead space map helps by allowing us
to skip blocks that don't need vacuuming, reducing the # of I/Os to
2*ndirtyblocks+nind­exblocks. That's great, but it doesn't help us if the
dead tuples are spread uniformly.

If we could piggyback the vacuum I/Os to the I/Os that we're doing
anyway, vacuum wouldn't ideally have to issue any I/O of its own. I've
tried to figure out a way to do that.

Vacuum is done in 3 phases:

1. Scan heap
2. Vacuum index
3. Vacuum heap

Instead of doing a sequential scan, we could perform the 1st phase by
watching the buffer pool, scanning blocks for dead tuples when they're
in memory and keeping track of which pages we've seen. When all pages
have been seen, the tid list is sorted and 1st phase is done.

In theory, the index vacuum could also be done that way, but let's
assume for now that indexes would be scanned like they are currently.

The 3rd phase can be performed similarly to the 1st phase. Whenever a
page enters the buffer pool, we check the tid list and remove any
matching tuples from the page. When the list is empty, vacuum is complete.

Of course, there's some issues in the design as described above. For
example, the vacuum might take a long time if there's cold spots in the
table. In fact, a block full of dead tuples might never be visited again.

A variation of the scheme would be to keep scanning pages that are in
cache, until the tid list reaches a predefined size, instead of keeping
track of which pages have already been seen. That would deal better with
tables with hot and cold spots, but it couldn't advance the relfrozenid
because there would be no guarantee that all pages are visited. Also, we
could start 1st phase of the next vacuum, while we're still in the 3rd
phase of previous one.

Also, after we've seen 95% of the pages or a timeout expires, we could
fetch the rest of them with random I/O to let the vacuum finish.

I'm not sure how exactly this would be implemented. Perhaps bgwriter or
autovacuum would do it, or a new background process. Presumably the
process would need access to relcache.

One issue is that if we're trying to vacuum every table simultaneously
this way, we'll need more overall memory for the tid lists. I'm hoping
there's a way to implement this without requiring shared memory for the
tid lists, that would make the memory management a nightmare. Also, we'd
need changes to bufmgr API to support this.

This would work nicely with the DSM. The list of pages that need to be
visited in phase 1 could be initialized from the DSM, largely avoiding
the problem with cold spots.

Any thoughts before I start experimenting?

--
Heikki Linnakangas
EnterpriseDB http://www.enterpri­sedb.com

-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org

comment 27 answers | Add comment
14.4 If You Are Upgrading - Suggested Improvements Thomas F. O'Connell 04:27:37
 I've done a number of upgrades of postgres from one minor release to
another, and unless I'm missing something, there's nothing anywhere
in section 14 that provides documentation on how to do this.

Generally, it's as simple as:

configure
make
make install
pg_ctl stop [or equivalent, if stop scripts exist]
pg_ctl start -D <data directory> [or equivalent, if start scripts exist]

But it seems like it would be helpful for administrators to break
down 14.4 into two sections:

14.4.1 - If You Are Upgrading a Minor Release
14.4.2 - If You Are Upgrading a Major Release (or a Release Requiring
an initdb)

Just a suggestion. If this strikes others as a good idea, I'd be
happy to draft something.

--
Thomas F. O'Connell

optimizing modern web applications
: for search engines, for usability, and for performance :

http://o.ptimized.c­om/
615-260-0005

comment 7 answers | Add comment
Re: [Fwd: [PORTS] M$ SQL server DTS package equivalent Paul Lambert 00:42:26
 Thanks all for your tips and pointers.

Looking at copy I think it may do just what I need. The tables I load
the data into have the same columns in the same order as those in the
CSV file. Loading data in this manner is going to be a rare occurance -
just when we install a new customer site and need to do an initial
transfer of data from the main system before we switch on my real-time
replication program. The programs that extract these csv files already
take care of duplicate key checking and so forth, so there shouldn't be
any issues as far as data integrity checking goes. I.e. there's no
actual data transformation, row merging and the like.

Thanks again to everyone who's offered some advice, much appreciated.

Regards,
Paul.

-------------------­--------(end of broadcast)---------­------------------
TIP 2: Don't 'kill -9' the postmaster

Add comment
Windows 2K Installation difficulties... Neil Bibbins 00:42:26
 Hello,

I'm having difficulty installing PostgreSQL 8.2 on Windows 2000. It gets
most of the way through the installation and fails (I think) after trying to
initialize the database. The log message is:

The database cluster will be initialized with locale C.

fixing permissions on existing directory C:/Program
Files/PostgreSQL/8.­2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers/max_­fsm_pages ... 400kB/20000
creating configuration files ... ok
creating template1 database in C:/Program Files/PostgreSQL/8.­2/data/base/1
... Bad command or file name
could not write to child process: Invalid argument
Initdb: removing contents of data directory "C:/Program
Files/PostgreSQL/8.­2.data

I have removed all virus software, although I hope to reinstall it after
installation. (Can PostgresSQL really not run as an application with virus
protection installed? Ouch...)
I have tried deselecting the initialization box and initializing afterward,
but this also doesn't work. Possibly I'm using the wrong command, which is
one that I found from a post from Magnus several years ago.

I have installed Cygwin, but this also didn't help.

Can anyone assist? I've done many forum searches but nothing seems to work.
I don't think I'm attempting the impossible!

Thanks for any insight. Much appreciated.
comment 2 answer | Add comment
Proposal: Snapshot cloning Jan Wieck 00:42:26
 Granted this one has a few open ends so far and I'd like to receive some
constructive input on how to actually implement it.

The idea is to clone an existing serializable transactions snapshot
visibility information from one backend to another. The semantics would
be like this:

backend1: start transaction;
backend1: set transaction isolation level serializable;
backend1: select pg_backend_pid();
backend1: select publish_snapshot();­ -- will block

backend2: start transaction;
backend2: set transaction isolation level serializable;
backend2: select clone_snapshot(<pid­>); -- will unblock backend1

backend1: select publish_snapshot();­

backend3: start transaction;
backend3: set transaction isolation level serializable;
backend3: select clone_snapshot(<pid­>);

...

This will allow a number of separate backends to assume the same MVCC
visibility, so that they can query independent but the overall result
will be according to one consistent snapshot of the database.

What I try to accomplish with this is to widen a bottleneck, many
current Slony users are facing. The initial copy of a database is
currently limited to one single reader to copy a snapshot of the data
provider. With the above functionality, several tables could be copied
in parallel by different client threads, feeding separate backends on
the receiving side at the same time.

The feature could also be used by a parallel version of pg_dump as well
as data mining tools.

The cloning process needs to make sure that the clone_snapshot() call is
made from the same DB user in the same database as corresponding
publish_snapshot() call was done. Since publish_snapshot() only
publishes the information, it gained legally and that is visible in the
PGPROC shared memory (xmin, xmax being the crucial part here), there is
no risk of creating a snapshot for which data might have been removed by
vacuum already.

What I am not sure about yet is what IPC method would best suit the
transfer of the arbitrarily sized xip vector. Ideas?


Jan

--
#==================­====================­====================­============#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#==================­====================­============ JanWieck@Yahoo.com #

-------------------­--------(end of broadcast)---------­------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgres­ql.org/about/donate

comment 17 answers | Add comment
Loop plpgsql recordset Furesz Peter 00:42:26
 Hello,

How can I loop a PL/PgSQL recorset variable? The example:

DECLARE
v_tmp_regi RECORD;
v_tmp RECORD;
BEGIN
SELECT * INTO v_tmp_regi FROM sulyozas_futamido sf WHERE
sf.termekfajta_id=
a_termekfajta_id AND sf.marka_id=a_marka­_id;

DELETE FROM sulyozas_futamido;

FOR v_tmp IN v_tmp_regi LOOP
--I would like to work here with the old recordset!
END LOOP;
^^^^^^^^^^^^^^
-- This is not working !!!

END;
comment 1 answer | Add comment
Ayuda sobre Indices Julio Caicedo 00:42:26
 Buen Dia.

Por favor si saben como, me gustaria saber como puedo eliminar un indice
PERO SOLO si este existe. Como valido si existe o no el indice para luego
eliminarlo ??

Gracias.


-----------
Please, i need drop index but ONLY and ONLY this exist index. Thanks.



-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 1 answer | Add comment
PostgreSQL data loss BluDes 00:42:26
 Hi everyone,
I have a problem with one of my costomers.
I made a program that uses a PostgreSQL (win32) database to save its data.
My customer claims that he lost lots of data reguarding his own clients
and that those data had surely been saved on the database.
My first guess is that he is the one who deleted the data but wants to
blame someone else, obviously I can't prove it.

Could it be possible for PostgreSQL to lose its data? Maybe with a file
corruption? Could it be possible to restore these data?

My program does not modify or delete data since its more like a log that
only adds information. It is obviously possible to delete these logs but
it requires to answer "yes" to 2 different warnings, so the data can't
be deleted accidentally.

I have other customers with even 10 times the amount of data of the one
who claimed the loss but no problems with them.
He obviously made no backups (and claims whe never told him to do them
so we are responsible even for this) though the program has a dedicated
Backup-section.

Any suggestion?

Daniele

-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 3 answer | Add comment
Re: Speaking of upgrades... Douglas McNaught 00:42:26
 Ron Johnson <ron.l.johnson@cox.­net> writes:
How much does the on-disk structure of *existing* tables and indexes> change between x.y versions?>
Between, for example, 8.0 and 8.2?

Enough to require a dump/reload in order to upgrade. :)­

Within major versions (8.2.0, 8.2.1, etc) the files are compatible so
you can do in-place upgrades (except in extraordinary circumstances).

-Doug


-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org/

Add comment
Function returning SETOF using plpythonu Lu s Sousa 00:42:26
 Hi,

Is it possible to return rows from a function written in plpythonu using
SETOF?

Example:
CREATE FUNCTION "test_python_setof"­()
RETURNS SETOF text AS '
records=plpy.execut­e("SELECT name FROM interface");
return records
' LANGUAGE 'plpythonu';

With this code is returning the object from the execution:
<PLyResult object at 0xb703e458>

Best regards,
Lu s Sousa



-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 1 answer | Add comment
Proposal: Change of pg_trigger.tg_enabl­ed and adding pg_rewrite.ev_enabl­ed Jan Wieck 00:40:12
 The experience with Slony-I has shown that

a) different behavior of triggers and rules on a transactions origin
and a replica is essential;

b) mucking around with the system catalog to achieve this is futile.

This would be even more catastrophic in a multimaster environment, where
regular transaction origin and replica behavior are required on a per
session level concurrently.

To achieve the required flexibility, we need to change the definition of
the pg_trigger attribute tg_enabled. It currently is a boolean. I would
like to change it into a char along with the syntax of ALTER TRIGGER.
The value definitions of tg_enabled would be

A fires always
N fires never
O fires on transaction origin only
R fires on replica only

Anyone preferences how to map that to ALTER TRIGGER?

A new per session GUC variable, restricted to superusers, will define if
the session is in origin or replica mode.

Likewise the system catalog pg_rewrite is extended with an attribute
ev_enabled. It will have the same possible values and a new command,
ALTER RULE, will match the functionality of ALTER TRIGGER.


Jan

--
#==================­====================­====================­============#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#==================­====================­============ JanWieck@Yahoo.com #

-------------------­--------(end of broadcast)---------­------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

comment 3 answer | Add comment
Record not returned until index is rebuilt Nik 00:26:00
 I have the following table definition in postgres 8.0:

CREATE TABLE "userInfo"
(
"userID" character varying(32) NOT NULL,
"password" character varying(32) NOT NULL,
salt character varying(16) NOT NULL,
CONSTRAINT "userInfo_PK" PRIMARY KEY ("userID")
) ;

Administrators are able to add new users to this table via PHP web
interface. It works fine most of the time. Every once in a while, we
get a problem where a newly added user does not show up in the list of
all users after being added.

For example, I will add a user named "test" to the database. When I do
SELECT * FROM "userInfo"
I will see that record in the database. However if I do
SELECT * FROM "userInfo" WHERE "userID"='test'
no records are returned.

This record will not show up in the query where it's specified as a
WHERE clause until I REINDEX and VACUUM ANALYZE the table. After that,
the record will show up in both queries. Newly added users will show up
for a while, until the same problem occurs.

Why is it that the record is visible when bulk selected, but not when
selected as a part of the WHERE clause, and why is it that the index
rebuild and vacuum fixes the problem? Is it possible that the primary
key index is not being updated properly?

Thanks.


-------------------­--------(end of broadcast)---------­------------------
TIP 5: don't forget to increase your free space map settings

comment 1 answer | Add comment
Sample C++ code using libpqxx/Postgresql sought Guest 00:20:38
 I am looking for some sample code using the libpqxx (C++) API for
Postgresql. I have found some tutorials

(http://thaiopensou­rce.org/devprojects/­libpqxx/doc/2.6.8/ht­ml/Tutorial/
http://www.cs.wisc.­edu/~ajkunen/libpqxx­-2.4.2/Tutorial/)

and some references

(http://thaiopensou­rce.org/devprojects/­libpqxx/doc/2.6.8/ht­ml/Reference/
http://www.postgres­ql.org/files/documen­tation/pdf/7.1/progr­ammer.pdf
http://www.postgres­ql.org/files/documen­tation/pdf/7.3/progr­ammer-7.3.2-US.pdf)

but they are all giving bits and pieces of information. I am the sort
of person who operates on pre-existing sample code, rather than a
programmer's manual. Any pointers to sample code using libpqxx will be
appreciated.

Choi


-------------------­--------(end of broadcast)---------­------------------
TIP 4: Have you searched our list archives?

http://archives.pos­tgresql.org/

comment 1 answer | Add comment
pg_restore exclude schema from being droped option Kostis Mentzelos 00:18:44
 Hi list,

I am using pg_dump and pg_restore to backup and restore a database but
there is something
that I believe is missing from the restore process:
an option in pg_restore to exclude a schema from being dropped when -c
option is defined.

And here is why:

Suppose that I have a database with about 12 tables of customer data
(address, notes, configuration ... no more than 10.000 rows each) and 50
tables of history data (history files with about 1.000.000 rows each).
Now, to backup the database I choose to create 2 scripts, BackupData.sh
to backup all small tables and BackupHist.sh to backup history tables.
When I call pg_restore -c to restore data tables, pg_restore report a
failure because it is trying to drop a schema that it is not empty. So
it would be very helpful to have an option to exclude the schema (for
example: public) from being dropped.

I now that I there are some alternatives for example: pg_restore -l,
comment out the drop schema line and pg_restore -L or put data tables
and hist tables into separate schemas but an option to pg_restore would
be a lot easier, I guess.

What do you thing?

regards,
Kostis Mentzelos

-------------------­--------(end of broadcast)---------­------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresq­l.org so that your
message can get through to the mailing list cleanly

comment 1 answer | Add comment

Add new topic:

How:  Register )