[HACKERS] pgbench hard coded constants

[Jeff Janes]

pgbench has #defines for number of branches, tellers, and accounts.  There
are used to populate the tables with -i, but when running actual benchmark
it has values separately hard-coded in the query metacommands.  This patch
makes the metacommands obtain their values from the relevant #defines.

It has been tested to the extent that after changing the #define naccounts
downward, without the patch, after running both -i once and the benchmark
itself once leads to inconsistent results (select sum(abalance) from
pgbench_accounts  does not equal sum(delta) from pgbench_history), while
with the patch they are equal.

Cheers,

Jeff
Index: pgbench.c
===
RCS file: /home/jjanes/pgrepo/pgsql/contrib/pgbench/pgbench.c,v
retrieving revision 1.90
diff -c -r1.90 pgbench.c
*** pgbench.c	3 Aug 2009 18:30:55 -	1.90
--- pgbench.c	31 Aug 2009 21:04:46 -
***
*** 132,138 
   * end of configurable parameters
   */
  
! #define nbranches	1
  #define ntellers	10
  #define naccounts	10
  
--- 132,138 
   * end of configurable parameters
   */
  
! #define nbranches	1  /* Makes little sense to change this.  Change -s instead */
  #define ntellers	10
  #define naccounts	10
  
***
*** 232,240 
  
  /* default scenario */
  static char *tpc_b = {
! 	"\\set nbranches :scale\n"
! 	"\\set ntellers 10 * :scale\n"
! 	"\\set naccounts 10 * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"\\setrandom bid 1 :nbranches\n"
  	"\\setrandom tid 1 :ntellers\n"
--- 232,240 
  
  /* default scenario */
  static char *tpc_b = {
! 	"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
! 	"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
! 	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"\\setrandom bid 1 :nbranches\n"
  	"\\setrandom tid 1 :ntellers\n"
***
*** 250,258 
  
  /* -N case */
  static char *simple_update = {
! 	"\\set nbranches :scale\n"
! 	"\\set ntellers 10 * :scale\n"
! 	"\\set naccounts 10 * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"\\setrandom bid 1 :nbranches\n"
  	"\\setrandom tid 1 :ntellers\n"
--- 250,258 
  
  /* -N case */
  static char *simple_update = {
! 	"\\set nbranches " CppAsString2(nbranches) " * :scale\n"
! 	"\\set ntellers " CppAsString2(ntellers) " * :scale\n"
! 	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"\\setrandom bid 1 :nbranches\n"
  	"\\setrandom tid 1 :ntellers\n"
***
*** 266,272 
  
  /* -S case */
  static char *select_only = {
! 	"\\set naccounts 10 * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
  };
--- 266,272 
  
  /* -S case */
  static char *select_only = {
! 	"\\set naccounts " CppAsString2(naccounts) " * :scale\n"
  	"\\setrandom aid 1 :naccounts\n"
  	"SELECT abalance FROM pgbench_accounts WHERE aid = :aid;\n"
  };

-- 
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] Linux LSB init script

[Greg Smith]

On Mon, 31 Aug 2009, Kevin Grittner wrote:


My counter-argument to that would be that the SuSE distribution's
version of PostgreSQL is so out-of-date that we don't install it.


Given that it's also RPM based, is it possible to get SuSE in sync so that 
it shares the same init script as RHEL?  Devrim is in the middle of a 
major overhaul of the RHEL/Fedora init script lately, adding better 
support for multiple postmasters and the like, and I'd think that SuSE 
users would like to take advantage of that work too.


It seems to me that the only popular Linux versions that people use for 
PostgreSQL work that don't have active init script maintainers are SuSE 
and Gentoo.  If your LSB-based approach could be made to work on both 
those, that would be a nice step forward.  I don't know what the state of 
the init script that Gentoo ships is though, I'm guessing it may diverge 
from the standard approach due to its slots implementation.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Largeobject access controls

[KaiGai Kohei]

Alvaro Herrera wrote:
> Tom Lane wrote:
>> KaiGai Kohei  writes:
>>> BTW, currently, the default ACL of largeobject allows anything for owner
>>> and nothing for world. Do you have any comment for the default behavior?
>> Mph.  I think the backlash will be too great.  You have to leave the
>> default behavior the same as it is now, ie, world access.
> 
> BTW as a default it is pretty bad.  Should we have a GUC var to set the
> default LO permissions?

It seems to me a reasonable idea in direction.
However, it might be better to add a GUC variable to turn on/off LO
permission feature, not only default permissions.
It allows us to control whether the privilege mechanism should perform
in backward compatible, or not.
-- 
OSS Platform Development Division, NEC
KaiGai Kohei 

-- 
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] remove flatfiles.c

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> Hmm, I had been assuming we wouldn't need that anymore.

> The comment in user.c and dbcommands.c says [...]
> so I think those ones are still necessary.

Yeah, after a look through the code I think you can trust the associated
comments: if it says it needs sync commit, put in ForceSyncCommit, else
we don't need 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] remove flatfiles.c

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:

> > Regarding sync commits that previously happen and now won't, I think the
> > only case worth worrying about is the one in vacuum.c.  Do we need a
> > ForceSyncCommit() in there?  I'm not sure if vacuum itself already
> > forces sync commit.
> 
> Hmm, I had been assuming we wouldn't need that anymore.

The comment in user.c and dbcommands.c says


/*
 * Force synchronous commit, thus minimizing the window between
 * creation of the database files and commital of the 
transaction. If
 * we crash before committing, we'll have a DB that's taking up 
disk
 * space but is not in pg_database, which is not good.
 */
ForceSyncCommit();

so I think those ones are still necessary.  There's another call in
RenameDatabase() which I don't think needs a sync commit (because it
won't change the dir name), and one in vacuum.c:

/*
!* If we were able to advance datfrozenxid, mark the flat-file copy of
!* pg_database for update at commit, and see if we can truncate pg_clog.
!* Also force update if the shared XID-wrap-limit info is stale.
 */
if (dirty || !TransactionIdLimitIsValid())
-   {
-   database_file_update_needed();
vac_truncate_clog(newFrozenXid);
-   }
  }

AFAICT this doesn't need a sync commit.  (Right now, VACUUM FULL forces
one, but lazy vacuum doesn't).

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] remove flatfiles.c

[Tom Lane]

Alvaro Herrera  writes:
> This patch removes flatfiles.c for good.

Aw, you beat me to it.

> Regarding sync commits that previously happen and now won't, I think the
> only case worth worrying about is the one in vacuum.c.  Do we need a
> ForceSyncCommit() in there?  I'm not sure if vacuum itself already
> forces sync commit.

Hmm, I had been assuming we wouldn't need that anymore.

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

[HACKERS] remove flatfiles.c

[Alvaro Herrera]

This patch removes flatfiles.c for good.

It doesn't change the keeping of locks in dbcommands.c and user.c,
because at least some of them are still required.

Regarding sync commits that previously happen and now won't, I think the
only case worth worrying about is the one in vacuum.c.  Do we need a
ForceSyncCommit() in there?  I'm not sure if vacuum itself already
forces sync commit.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/access/transam/twophase_rmgr.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/twophase_rmgr.c,v
retrieving revision 1.8
diff -c -p -r1.8 twophase_rmgr.c
*** src/backend/access/transam/twophase_rmgr.c	1 Jan 2009 17:23:36 -	1.8
--- src/backend/access/transam/twophase_rmgr.c	31 Aug 2009 21:52:17 -
***
*** 18,24 
  #include "commands/async.h"
  #include "pgstat.h"
  #include "storage/lock.h"
- #include "utils/flatfiles.h"
  #include "utils/inval.h"
  
  
--- 18,23 
*** const TwoPhaseCallback twophase_recover_
*** 27,33 
  	NULL,		/* END ID */
  	lock_twophase_recover,		/* Lock */
  	NULL,		/* Inval */
- 	NULL,		/* flat file update */
  	NULL,		/* notify/listen */
  	NULL		/* pgstat */
  };
--- 26,31 
*** const TwoPhaseCallback twophase_postcomm
*** 37,43 
  	NULL,		/* END ID */
  	lock_twophase_postcommit,	/* Lock */
  	inval_twophase_postcommit,	/* Inval */
- 	flatfile_twophase_postcommit,		/* flat file update */
  	notify_twophase_postcommit, /* notify/listen */
  	pgstat_twophase_postcommit	/* pgstat */
  };
--- 35,40 
*** const TwoPhaseCallback twophase_postabor
*** 47,53 
  	NULL,		/* END ID */
  	lock_twophase_postabort,	/* Lock */
  	NULL,		/* Inval */
- 	NULL,		/* flat file update */
  	NULL,		/* notify/listen */
  	pgstat_twophase_postabort	/* pgstat */
  };
--- 44,49 
Index: src/backend/access/transam/xact.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.274
diff -c -p -r1.274 xact.c
*** src/backend/access/transam/xact.c	11 Jun 2009 14:48:54 -	1.274
--- src/backend/access/transam/xact.c	31 Aug 2009 21:50:41 -
***
*** 43,49 
  #include "storage/sinvaladt.h"
  #include "storage/smgr.h"
  #include "utils/combocid.h"
- #include "utils/flatfiles.h"
  #include "utils/guc.h"
  #include "utils/inval.h"
  #include "utils/memutils.h"
--- 43,48 
*** CommitTransaction(void)
*** 1608,1619 
  	/* NOTIFY commit must come before lower-level cleanup */
  	AtCommit_Notify();
  
- 	/*
- 	 * Update flat files if we changed pg_database, pg_authid or
- 	 * pg_auth_members.  This should be the last step before commit.
- 	 */
- 	AtEOXact_UpdateFlatFiles(true);
- 
  	/* Prevent cancel/die interrupt while cleaning up */
  	HOLD_INTERRUPTS();
  
--- 1607,1612 
*** PrepareTransaction(void)
*** 1797,1803 
  	/* close large objects before lower-level cleanup */
  	AtEOXact_LargeObject(true);
  
! 	/* NOTIFY and flatfiles will be handled below */
  
  	/*
  	 * Don't allow PREPARE TRANSACTION if we've accessed a temporary table in
--- 1790,1796 
  	/* close large objects before lower-level cleanup */
  	AtEOXact_LargeObject(true);
  
! 	/* NOTIFY will be handled below */
  
  	/*
  	 * Don't allow PREPARE TRANSACTION if we've accessed a temporary table in
*** PrepareTransaction(void)
*** 1860,1866 
  	StartPrepare(gxact);
  
  	AtPrepare_Notify();
- 	AtPrepare_UpdateFlatFiles();
  	AtPrepare_Inval();
  	AtPrepare_Locks();
  	AtPrepare_PgStat();
--- 1853,1858 
*** PrepareTransaction(void)
*** 1909,1915 
  	/* Clean up the snapshot manager */
  	AtEarlyCommit_Snapshot();
  
! 	/* notify and flatfiles don't need a postprepare call */
  
  	PostPrepare_PgStat();
  
--- 1901,1907 
  	/* Clean up the snapshot manager */
  	AtEarlyCommit_Snapshot();
  
! 	/* notify doesn't need a postprepare call */
  
  	PostPrepare_PgStat();
  
*** AbortTransaction(void)
*** 2036,2042 
  	AtAbort_Portals();
  	AtEOXact_LargeObject(false);	/* 'false' means it's abort */
  	AtAbort_Notify();
- 	AtEOXact_UpdateFlatFiles(false);
  
  	/*
  	 * Advertise the fact that we aborted in pg_clog (assuming that we got as
--- 2028,2033 
*** CommitSubTransaction(void)
*** 3764,3771 
  	AtEOSubXact_LargeObject(true, s->subTransactionId,
  			s->parent->subTransactionId);
  	AtSubCommit_Notify();
- 	AtEOSubXact_UpdateFlatFiles(true, s->subTransactionId,
- s->parent->subTransactionId);
  
  	CallSubXactCallbacks(SUBXACT_EVENT_COMMIT_SUB, s->subTransactionId,
  		 s->parent->subTransactionId);
--- 3755,3760 
*** AbortSubTransaction(void)
*** 38

Re: [HACKERS] 8.5 release timetable, again

[Bruce Momjian]

Josh Berkus wrote:
> Bruce,
> 
> > I am not sure what other checklist items there would be (or I am
> > refusing to divulge).
> 
> Hopefully the last is a joke. ;-)

Yes.

> So, the only post-CF tasks are issues with specific patches?  This seems
> resolvable, especially if we take a hard line with patch readiness.
> There isn't anything else in that period?

Nope.  Release notes and open items is all I remember, and the release
notes were done at the end of the commit-fest, so that isn't really even
an issue.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

[HACKERS] 8.5 release note editing

[Bruce Momjian]

I just talked to Josh Berkus via phone.

We have decided to put the rough commit messages on a wiki for 8.5
final so they can be easily edited by the community, before markup is
added and they are moved to the SGML docs.  This should increase
community involvement in editing the items, and if it goes well, we can
try to do this in a more fine-grained fashion for every alpha release
for 8.6.

For those who need details on how long each item takes, see:

http://momjian.us/main/blogs/pgblog.html#March_25_2009

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Josh Berkus]

Bruce,

> I am not sure what other checklist items there would be (or I am
> refusing to divulge).

Hopefully the last is a joke. ;-)

So, the only post-CF tasks are issues with specific patches?  This seems
resolvable, especially if we take a hard line with patch readiness.
There isn't anything else in that period?

This doesn't sound that difficult then.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.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] autovacuum launcher using InitPostgres

[Dimitri Fontaine]

Hi,

Tom Lane  writes:
>> The user may not care about the difference, but there's a point in
>> having the limit be the simpler concept of "this is the maximum amount
>> of processes running vacuum at any time".  The launcher is very
>> uninteresting to users.

Adding to this, the launcher will not consume maintenance_work_mem
whereas each worker is able to allocate that much, IIUC.

> I committed things that way, but I'm still not convinced that we
> shouldn't expose the launcher in pg_stat_activity.  The thing that
> is bothering me is that it is now able to take locks and potentially
> could block some other process or even participate in a deadlock.
> Do we really want to have entries in pg_locks that don't match any
> entry in pg_stat_activity?

Having the launcher locks show as such gets my vote too, but then I'm
following on your opinion that a launcher ain't a worker and that users
need to know about it. 

Let's keep the autovacuum_max_workers GUC naming, not counting the
"there can be only one" launcher so that we're able to size
maintenance_work_mem.

Regards,
-- 
dim

-- 
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] Largeobject access controls

[Alvaro Herrera]

Tom Lane wrote:
> KaiGai Kohei  writes:
> > BTW, currently, the default ACL of largeobject allows anything for owner
> > and nothing for world. Do you have any comment for the default behavior?
> 
> Mph.  I think the backlash will be too great.  You have to leave the
> default behavior the same as it is now, ie, world access.

BTW as a default it is pretty bad.  Should we have a GUC var to set the
default LO permissions?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] plpythonu datatype conversion improvements

[Peter Eisentraut]

On sön, 2009-08-16 at 02:44 +0300, Peter Eisentraut wrote:
> The remaining problem is that the patch loses domain checking on the
> return types, because some paths no longer go through the data type's
> input function.  I have marked these places as FIXME, and the regression
> tests also contain a failing test case for this.
> 
> What's needed here, I think, is an API that takes a datum plus type
> information and checks whether the datum is valid within the domain.  I
> haven't found one that is exported, but maybe someone could give a tip.

Got that fixed now.  Updated patch is attached.  I will sleep over it,
but I think it's good to go.
diff --git a/src/backend/utils/adt/domains.c b/src/backend/utils/adt/domains.c
index ffd5c7a..bda200b 100644
--- a/src/backend/utils/adt/domains.c
+++ b/src/backend/utils/adt/domains.c
@@ -302,3 +302,40 @@ domain_recv(PG_FUNCTION_ARGS)
 	else
 		PG_RETURN_DATUM(value);
 }
+
+/*
+ * domain_check - check that a datum satisfies the constraints of a
+ * domain.  extra and mcxt can be passed if they are available from,
+ * say, a FmgrInfo structure, or they can be NULL, in which case the
+ * setup is repeated for each call.
+ */
+void
+domain_check(Datum value, bool isnull, Oid domainType, void **extra, MemoryContext mcxt)
+{
+	DomainIOData *my_extra = NULL;
+
+	if (mcxt == NULL)
+		mcxt = CurrentMemoryContext;
+
+	/*
+	 * We arrange to look up the needed info just once per series of calls,
+	 * assuming the domain type doesn't change underneath us.
+	 */
+	if (extra)
+		my_extra = (DomainIOData *) *extra;
+	if (my_extra == NULL)
+	{
+		my_extra = (DomainIOData *) MemoryContextAlloc(mcxt,
+	   sizeof(DomainIOData));
+		domain_state_setup(my_extra, domainType, true, mcxt);
+		if (extra)
+			*extra = (void *) my_extra;
+	}
+	else if (my_extra->domain_type != domainType)
+		domain_state_setup(my_extra, domainType, true, mcxt);
+
+	/*
+	 * Do the necessary checks to ensure it's a valid domain value.
+	 */
+	domain_check_input(value, isnull, my_extra);
+}
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 9b0a2b7..df37b16 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -137,6 +137,7 @@ extern Datum char_text(PG_FUNCTION_ARGS);
 /* domains.c */
 extern Datum domain_in(PG_FUNCTION_ARGS);
 extern Datum domain_recv(PG_FUNCTION_ARGS);
+extern void domain_check(Datum value, bool isnull, Oid domainType, void **extra, MemoryContext mcxt);
 
 /* encode.c */
 extern Datum binary_encode(PG_FUNCTION_ARGS);
diff --git a/src/pl/plpython/expected/plpython_types.out b/src/pl/plpython/expected/plpython_types.out
index 19b3c9e..2a08834 100644
--- a/src/pl/plpython/expected/plpython_types.out
+++ b/src/pl/plpython/expected/plpython_types.out
@@ -32,6 +32,74 @@ CONTEXT:  PL/Python function "test_type_conversion_bool"
  
 (1 row)
 
+-- test various other ways to expression Booleans in Python
+CREATE FUNCTION test_type_conversion_bool_other(n int) RETURNS bool AS $$
+# numbers
+if n == 0:
+   ret = 0
+elif n == 1:
+   ret = 5
+# strings
+elif n == 2:
+   ret = ''
+elif n == 3:
+   ret = 'fa' # true in Python, false in PostgreSQL
+# containers
+elif n == 4:
+   ret = []
+elif n == 5:
+   ret = [0]
+plpy.info(ret, not not ret)
+return ret
+$$ LANGUAGE plpythonu;
+SELECT * FROM test_type_conversion_bool_other(0);
+INFO:  (0, False)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ f
+(1 row)
+
+SELECT * FROM test_type_conversion_bool_other(1);
+INFO:  (5, True)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ t
+(1 row)
+
+SELECT * FROM test_type_conversion_bool_other(2);
+INFO:  ('', False)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ f
+(1 row)
+
+SELECT * FROM test_type_conversion_bool_other(3);
+INFO:  ('fa', True)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ t
+(1 row)
+
+SELECT * FROM test_type_conversion_bool_other(4);
+INFO:  ([], False)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ f
+(1 row)
+
+SELECT * FROM test_type_conversion_bool_other(5);
+INFO:  ([0], True)
+CONTEXT:  PL/Python function "test_type_conversion_bool_other"
+ test_type_conversion_bool_other 
+-
+ t
+(1 row)
+
 CREATE FUNCTION test_type_conversion_char(x char) RETURNS char AS $$
 plpy.info(x, type(x))
 return x
@@ -278,13 +346,21 @@ plpy.info(x, type(x))
 return x
 $$ LANGUAGE plpythonu;
 SELECT * FROM test_type_conversion_bytea('hello world');
-INFO:  ('\\x68656c6c6f20776f726c64', )
+INFO:  ('hello world', )
 CONTEXT:  PL/Python function "test_type_conversion_bytea"
  test_type_conversi

Re: [HACKERS] 8.5 release notes idea

[Bruce Momjian]

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: RIPEMD160
> 
> Just noticed in the release notes for 8.4, there are two items
> accredited to just "Greg" (but there are three of us Gregs who
> contributed to 8.4 and are in the notes). While this is very likely
> Greg Stark, due to the context, I think at this point in the project
> we simply should spell out everyone's complete name every time, rather
> than the hit or miss style we are developing.
> 
> (Enjoy while you can, Bruce, we'll get another Bruce to contribute
> someday! Zoltan might be harder... :)

That is an interesting suggestion.  I have tried to use short names
where possible to avoid having the usernames become too prominent.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Kevin Grittner wrote:
> Bruce Momjian  wrote: 
>  
> > The issues are different for every commitfest-beta period, so I have
> > no idea what to list there, but we do alway have an open issues wiki
> > that is maintained, at least for the most recent releases.
>  
> After a quick search of the wiki, it appears that the list for 8.4
> was:
>  
> http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items
>  
> and that there is not yet a list for 8.5.  Is that correct?
>  
> If I understand what you're saying, this list would contain issues
> where a patch was committed and later found to have problems which
> need to be fixed.  Did I understand that correctly?  Anything else go
> on there, or possibly belong on there?  Can we take the absence of a
> list for 8.5 to indicate that no such problems have been found with
> any patches committed since 8.4 was tagged?

Yes, though I have a few items that I should transfer from my mailbox to
that list.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Kevin Grittner wrote:
> Bruce Momjian  wrote: 
>  
> > it gets no easier to make the decisions later rather than now.  The
> > delay forces us to make a final decision.  We often had months to
> > make the decision earlier, but didn't.
>  
> So you're advocating that we find a way to force more timely
> decisions?

That would be good.  :-)

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release notes idea

[Greg Stark]

On Mon, Aug 31, 2009 at 8:47 PM, Greg Smith wrote:
> That's correct--you and I are spelled out completely everwhere we show up
> there, so all the "Greg" references without a last name are to Greg Stark.

Well they're intended to be. I'm not sure I actually contributed much
to those though.


-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] 8.5 release timetable, again

[Peter Eisentraut]

On sön, 2009-08-30 at 21:09 -0400, Robert Haas wrote:
> I really can't understand why it isn't possible for us to find a way
> to make an annual release happen, and with more than 8-12 weeks of
> development time (ie non-CommitFest non-beta) available during that
> year.  I understand that there is a need for some time to be set aside
> for reviewing, testing, debugging, packaging, and so forth, but the
> current schedule contemplates that this time is upwards of 75%, and I
> think that's excessive.

Well, the best way to improve that is to organize people and push things
forward when the time comes.


-- 
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] \d+ for long view definitions?

[Tom Lane]

Peter Eisentraut  writes:
> On sön, 2009-08-30 at 18:43 -0400, Tom Lane wrote:
>> Seems like a more general answer would be
>> for \d output to go through the pager ...

> That should also be fixed, but I'm not sure if it really does it for me.

Why not?  Just quit out of the pager when you've seen enough.

If the view definition precedes other data that is deemed more
important, then we'd need to adjust the ordering, but I'm not
entirely seeing the point of having to suppress the definition.

I especially don't like the thought of making it depend on the length
of the definition.  I would prefer \d not showing it at all.

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] Linux LSB init script

[Kevin Grittner]

Peter Eisentraut  wrote:
 
> While the major distributions support LSB, the major distributions
> also have PostgreSQL packages available and so will likely not need
> the init script shipped in the source.
 
My counter-argument to that would be that the SuSE distribution's
version of PostgreSQL is so out-of-date that we don't install it.  It
also doesn't enable debug info or integer date times.  So we switched
to build from source before we actually got as far as loading any
data.  I'm inclined to recommend the same to others.
 
> it might be best to keep both, if they are maintained.
 
Sounds good to me; although, now that there is a full LSB version, I
should probably withdraw my meager suggested patch to the existing
linux script, eh?  (If they're using an LSB conforming implementation,
they'll want the linux-lsb script, and if they're not, the suggested
patch has no point, I think.)  Unless someone thinks otherwise, I'll
drop that patch to the linux script from the CF page.  Any thoughts on
what that script needs, if anything?
 
-Kevin

-- 
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] \d+ for long view definitions?

[Peter Eisentraut]

On sön, 2009-08-30 at 18:43 -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > Using \d on, say, information schema views is completely hilarious
> > because the column name/data type information is usually scrolled off
> > the screen by the immense view definition.
> 
> > Could we change this perhaps so that the full view definition is only
> > shown with \d+ when the view definition is longer than N characters or N
> > lines or some other suitable cutoff.  Ideas?
> 
> The same complaint could be made for any table with more than
> twenty-some columns.

I guess my premise is that if I use \d, I'm primarily interested in the
column names and types.  The view definition is secondary.  If the view
definition is a single line or uses a single table, it's interesting
because it might describe something about the schema design, but if it's
20 lines it's an implementation detail.

I think this is quite similar to showing the function definition only
with \df+.  If I'm looking at the function, I'm usually only looking for
name and parameter information, not the full source code.

>   Seems like a more general answer would be
> for \d output to go through the pager ...

That should also be fixed, but I'm not sure if it really does it for me.


-- 
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] Add YAML option to explain

[daveg]

On Mon, Aug 31, 2009 at 02:15:08PM -, Greg Sabino Mullane wrote:
> > Greg, can we see a few examples of the YAML output
> > compared to both json and text?
...
> greg=# explain (format json, analyze on) select * from pg_class where relname 
> ~ 'x' order by 1,2,3;
> QUERY PLAN
> - ---

An interesting property of json, it is almost exactly the same as python
data structure syntax. If I paste the following into python:

plan = [
 {
   "Plan": {
 "Node Type": "Sort",
 "Startup Cost": 12.82,
 "Total Cost": 13.10,
 "Plan Rows": 111,
 "Plan Width": 185,
 "Actual Startup Time": 1.152,
 "Actual Total Time": 1.373,
 "Actual Rows": 105,
 "Actual Loops": 1,
 "Sort Key": ["relname", "relnamespace", "reltype"],
 "Sort Method": "quicksort",
 "Sort Space Used": 44,
 "Sort Space Type": "Memory",
 "Plans": [
   {
 "Node Type": "Seq Scan",
 "Parent Relationship": "Outer",
 "Relation Name": "pg_class",
 "Alias": "pg_class",
 "Startup Cost": 0.00,
 "Total Cost": 9.05,
 "Plan Rows": 111,
 "Plan Width": 185,
 "Actual Startup Time": 0.067,
 "Actual Total Time": 0.817,
 "Actual Rows": 105,
 "Actual Loops": 1,
 "Filter": "(relname ~ 'x'::text)"
   }
 ]
   },
   "Triggers": [
   ],
   "Total Runtime": 1.649
 }
   ]

I get a python data structure. Which can be manipulated directly, or pretty
printed:

>>> import pprint
>>> pprint.pprint(plan)
[{'Plan': {'Actual Loops': 1,
   'Actual Rows': 105,
   'Actual Startup Time': 1.1519,
   'Actual Total Time': 1.373,
   'Node Type': 'Sort',
   'Plan Rows': 111,
   'Plan Width': 185,
   'Plans': [{'Actual Loops': 1,
  'Actual Rows': 105,
  'Actual Startup Time': 0.067004,
  'Actual Total Time': 0.81695,
  'Alias': 'pg_class',
  'Filter': "(relname ~ 'x'::text)",
  'Node Type': 'Seq Scan',
  'Parent Relationship': 'Outer',
  'Plan Rows': 111,
  'Plan Width': 185,
  'Relation Name': 'pg_class',
  'Startup Cost': 0.0,
  'Total Cost': 9.0507}],
   'Sort Key': ['relname', 'relnamespace', 'reltype'],
   'Sort Method': 'quicksort',
   'Sort Space Type': 'Memory',
   'Sort Space Used': 44,
   'Startup Cost': 12.82,
   'Total Cost': 13.1},
  'Total Runtime': 1.649,
  'Triggers': []}]

I'm not sure if all json can be read this way, but the python and json
notations are very similar.

-dg

--
David Gould   da...@sonic.net  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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] Linux LSB init script

[Peter Eisentraut]

On mån, 2009-08-31 at 12:07 -0500, Kevin Grittner wrote:
> Is the LSB standard sufficiently widely adopted to omit the older
> format?  I was concerned that there might be Linux versions we wanted
> to support which wouldn't have a /lib/lsb/init-functions file to
> source.  If that's not an issue, I could submit this as a patch to the
> existing file.  (It'd be a - for almost every non-blank line in the
> old, and a + for almost every non-blank line in the new, of course.)

While the major distributions support LSB, the major distributions also
have PostgreSQL packages available and so will likely not need the init
script shipped in the source.  It will most likely be useful for various
do-it-yourself setups on fringe distributions.  So I don't know; it
might be best to keep both, if they are maintained.


-- 
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] set_client_encoding is broken

[Tom Lane]

Zdenek Kotala  writes:
> Tom Lane píše v po 31. 08. 2009 v 11:00 -0400:
>> I'm leaning to the third choice, but I wonder if anyone has any
>> comments or better ideas.

> It seems to me that 3 is OK.

> Another possibility is that InitPostgres can only fill up rel cache and
> GUC processing can stay on the same place. But in general, this problem
> can affect any other GUC variable which has assign hook and needs to
> lookup. 

Yeah, if it was *only* client_encoding then I wouldn't mind a hack
solution too much, but search_path is similarly affected and it's not
hard to foresee other GUCs in future that might require catalog access.
So I'd prefer a reasonably clean solution.

> I don't know how it works before, but I'm afraid that user can get error
> message in server encoding before it is correctly set.

It's always been the case that messages could come out before we can set
client_encoding.  I believe we have things set up so that you'll get the
untranslated, plain-ASCII-English message in that situation.  Feel free
to test ;-)

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] Linux LSB init script

[Kevin Grittner]

Greg Smith  wrote: 
 
> I'm similarly not sure just what the benefits of a LSB compatible
> script are here given that several major distributions like
> RHEL/Debian have their own thing they're doing and are unlikely to
> change.
 
I don't know about other platforms, but on SuSE Linux, you're not
likely to get things installed properly to start and stop in the right
timing with other services unless you have a good INIT INFO block and
use the appropriate OS tools to "install" it.  You might get it right
for the time being by adding a bunch of symlinks by hand, but it'd be
liable to break next time something was installed "by the book."
  
> Given that there was recent chatter on removing the Linux init
> scripts altogether,
 
I thought that suggestion got rather a cool reception...
 
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01393.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01394.php
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01398.php
 
> I think that the first thing to do here is survey where the 
> current script and a LSB-based one might fit into current/future
> Linux init script plans on the most popular platforms.  Your code is
> interesting but I'm not sure what problem it's intended to solve
> yet.
 
The current linux script, and the techniques recommended so far, don't
play well in an environment where you want the LSB INIT INFO
specifications of the services to ensure that each services waits
until the right time to start.
 
It's still somewhat flawed, in that PostgreSQL doesn't give you a way
to wait until it's ready to accept connections:
 
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01735.php
 
but this script could be expanded to deal with that better.  I see it
as a pretty solid base to build on.  I think it might be premature to
try to address that issue because of the interest in creating a
pg_ping functionality, which is what would make this nice and clean:
 
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01741.php
 
I didn't proceed to try to write up a solid patch which I felt
suitable for public distribution without someone seconding the motion,
as it were:
 
http://archives.postgresql.org/pgsql-hackers/2009-08/msg01780.php
 
Let me know if you have any concerns I didn't address.
 
-Kevin

-- 
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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> Well, I'm not sure the average user knows or cares about the difference
>> between the launcher and the workers.  The thing that was in the back of
>> my mind was that we would now have the option to have the launcher show
>> up in pg_stat_activity.  If we were to do that then the case for
>> counting it in the user-visible number-of-processes parameter would get
>> a lot stronger (enough to justify renaming the parameter, if you insist
>> that the launcher isn't a worker).  I don't however have any strong
>> opinion on whether we *should* include it in pg_stat_activity ---
>> comments?

> The user may not care about the difference, but there's a point in
> having the limit be the simpler concept of "this is the maximum amount
> of processes running vacuum at any time".  The launcher is very
> uninteresting to users.

I committed things that way, but I'm still not convinced that we
shouldn't expose the launcher in pg_stat_activity.  The thing that
is bothering me is that it is now able to take locks and potentially
could block some other process or even participate in a deadlock.
Do we really want to have entries in pg_locks that don't match any
entry in pg_stat_activity?

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] 8.5 release notes idea

[Greg Smith]

On Mon, 31 Aug 2009, Greg Sabino Mullane wrote:


Just noticed in the release notes for 8.4, there are two items
accredited to just "Greg" (but there are three of us Gregs who
contributed to 8.4 and are in the notes). While this is very likely
Greg Stark, due to the context...


That's correct--you and I are spelled out completely everwhere we show up 
there, so all the "Greg" references without a last name are to Greg Stark.


I would not be surprised to find so many Greg Smith's one day that we all 
end up identified by SHA-1 hash.



Enjoy while you can, Bruce, we'll get another Bruce to contribute
someday!


I think any Bruce or Tom who tries to submit a patch will have to be given 
a funny nickname instead.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> The only thing I'm aware is missing from this patch is fixing up
> avlauncher's signal handling, and adding a bit more commentary; also I
> haven't tested it under EXEC_BACKEND yet.

I did the signal handling work and fixed a couple of small oversights,
and applied it.  I'm not sure what other commentary you had in mind,
but feel free to add.

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

[HACKERS] 8.5 release notes idea

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

Just noticed in the release notes for 8.4, there are two items
accredited to just "Greg" (but there are three of us Gregs who
contributed to 8.4 and are in the notes). While this is very likely
Greg Stark, due to the context, I think at this point in the project
we simply should spell out everyone's complete name every time, rather
than the hit or miss style we are developing.

(Enjoy while you can, Bruce, we'll get another Bruce to contribute
someday! Zoltan might be harder... :)

- --
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 200908311229
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAkqcJRkACgkQvJuQZxSWSsiOvgCg2g5ugh2v2XPxaKYXmKZTSHr2
8tcAoInJBqXfdn57eIEEtp5hc4nF+wlL
=Uydn
-END PGP SIGNATURE-



-- 
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] Linux LSB init script

[Greg Smith]

On Mon, 31 Aug 2009, Kevin Grittner wrote:


Is the LSB standard sufficiently widely adopted to omit the older
format?


I'm not sure, and I'm similarly not sure just what the benefits of a LSB 
compatible script are here given that several major distributions like 
RHEL/Debian have their own thing they're doing and are unlikely to change. 
Given that there was recent chatter on removing the Linux init scripts 
altogether, I think that the first thing to do here is survey where the 
current script and a LSB-based one might fit into current/future Linux 
init script plans on the most popular platforms.  Your code is interesting 
but I'm not sure what problem it's intended to solve yet.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Linux LSB init script

[Kevin Grittner]

Andrew Dunstan  wrote:
 
> cvsutils
 
That allowed me to use 'cvsdo add' and 'cvs diff -cN' to generate the
attached.  This contains a couple minor fixes to what I posted in "new
file" form.  I was holding off on the CommitFest entry until I sorted
out the format; I'll link here as version 1 of the patch.
 
-Kevin

Index: contrib/start-scripts/linux-lsb
===
RCS file: contrib/start-scripts/linux-lsb
diff -N contrib/start-scripts/linux-lsb
*** /dev/null   1 Jan 1970 00:00:00 -
--- contrib/start-scripts/linux-lsb 31 Aug 2009 19:02:11 -
***
*** 0 
--- 1,298 
+ #! /bin/sh
+ 
+ ### BEGIN INIT INFO
+ # Provides: postgresql
+ # Required-Start: $local_fs $network $syslog
+ # Should-Start: $remote_fs $named $time
+ # Required-Stop: $local_fs $network $syslog
+ # Should-Stop: $remote_fs $named
+ # Default-Start: 2 3 4 5
+ # Default-Stop: 0 1 6
+ # Short-Description: PostgreSQL RDBMS
+ # Description: PostgreSQL RDBMS service.
+ #  The world's most advanced open source database.
+ #  See http://www.postgresql.org/ for more information.
+ ### END INIT INFO
+ 
+ # This is an example of a Linux LSB conforming init script.
+ # See http://refspecs.freestandards.org/ for more information on LSB.
+ 
+ # Original author:  Kevin Grittner
+ 
+ # $PostgreSQL$
+ 
+ #
+ # The only edits needed should be in the INIT INFO block above
+ # and between the lines of dashes below.  If any other
+ # changes are needed, or you find a way to enhance the script,
+ # consider posting to the PostgreSQL hackers list.
+ #
+ 
+ # Installation prefix
+ prefix=/usr/local/pgsql
+ 
+ # Data directory
+ PGDATA="/var/local/pgsql/data"
+ 
+ # Who to run the postmaster as, usually "postgres".  (NOT "root")
+ PGUSER=postgres
+ 
+ # Where to keep a log file
+ PGLOG="$PGDATA/serverlog"
+ 
+ #
+ 
+ # The path that is to be used for the script
+ PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
+ 
+ # The LSB functions must be present.
+ lsbf=/lib/lsb/init-functions
+ test -r "$lsbf" || {
+ echo "$0: not able to read $lsbf: script cannot run" 1>&2
+ exit 5
+   }
+ 
+ # Source the functions.
+ . "$lsbf"
+ # All output from the script should be through the LSB msg functions after 
this.
+ 
+ # Define usage string, used in more than one place.
+ usage="Usage: $0 {start|stop|restart|try-restart|reload|force-reload|status}"
+ 
+ # Check that we have one parameter: action
+ if [ $# -ne 1 ] ; then
+   if [ $# -lt 1 -o "$1" = "" ] ] ; then
+ log_failure_msg "$0: action not specified"
+   else
+ log_failure_msg "$0: too many parameters"
+   fi
+   log_warning_msg "$usage"
+   exit 2
+ fi
+ action="$1"
+ 
+ # What to use to manipulate the postmaster.
+ PGCTL="$prefix/bin/pg_ctl"
+ 
+ # Only start if we can find pg_ctl.
+ test -x "$PGCTL" || {
+ if [ "$action" = "stop" ] ; then
+   log_warning_msg "$0: executable $PGCTL not found: $action request 
ignored"
+   exit 0
+ else
+   log_failure_msg "$0: executable $PGCTL not found: $action request 
failed"
+   exit 5
+ fi
+   }
+ 
+ pidfile="$PGDATA/postmaster.pid"
+ servicename=$( basename "$0" )
+ daemon="$prefix/bin/postgres"
+ 
+ pg_initd_start () {
+   echo -n "Starting $servicename: "
+   su -c ". '$lsbf' ; start_daemon -p '$pidfile' '$PGCTL' -w -D '$PGDATA' -l 
'$PGLOG' start" - $PGUSER
+   rc=$?
+ }
+ 
+ pg_initd_stop () {
+   pidlist=$( pidofproc -p "$pidfile" "$daemon" )
+   if [ "$pidlist" = "" ] ; then
+ # If this happens, the process went away after the initial check.
+ echo "$servicename: not running"
+ rc=0
+ return
+   fi
+   echo -n "Shutting down $servicename: "
+   su -c ". \"$lsbf\" ; killproc -p '$pidfile' '$daemon' SIGINT" - $PGUSER
+   echo -n 'waiting for server to stop...'
+   rc=1
+   # Try "fast" shutdown for a while.
+   for seconds in $( seq 50 ) ; do
+ echo -n '.'
+ if ! ps -o pid= -p "$pidlist" >/dev/null ; then
+   rc=0
+   break
+ fi
+ sleep 1
+   done
+   # Fast didn't do it; try immediate shutdown.
+   if [ $rc -ne 0 ] ; then
+ su -c ". \"$lsbf\" ; killproc -p '$pidfile' '$daemon' SIGQUIT" - $PGUSER
+ for seconds in $( seq 10 ) ; do
+   echo -n '!'
+   if ! ps -o pid= -p "$pidlist" >/dev/null ; then
+ rc=0
+ break
+   fi
+   sleep 1
+ done
+   fi
+   ! ps -o pid= -p "$pidlist" >/dev/null
+   rc=$?
+   if [ "$rc" -eq 0 ] ; then
+ echo ' done'
+ rm -f "$pidfile"
+   else
+ echo ' failed'
+   fi
+ }
+ 
+ pg_initd_reload () {
+   su -c "$PGCTL reload -D '$PGDATA'" - $PGUSER
+   rc=$?
+ }
+ 
+ pg_initd_status () {
+   if [ ! -f "$pidfile" ] ; then
+ rc=3
+   else
+ su -c ". \"$lsbf\" ; pidofproc -p '$pidfile' '$daemon

Re: [HACKERS] Hot Standby, conflict cache

[Simon Riggs]

On Mon, 2009-08-31 at 15:50 +0300, Heikki Linnakangas wrote:

> The conflict cache code is broken

I've already removed that code from the version I'm working on as
mentioned on another thread, for the same reasons you just mentioned. I
think that the conflict options require more discussion and are an area
that many people will wish input into; we need a longer and wider
discussion about user-controls for that. I suggest we get the basic
patch ready to commit and then add back the gloss later, if any.

There is some hope for a per-relation conflict cache, just not yet.

-- 
 Simon Riggs   www.2ndQuadrant.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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > Tom Lane wrote:
> >> I wonder if it would be cleaner to include the launcher in
> >> the autovacuum_max_workers parameter, and increase the min/default
> >> values of that by one.
> 
> > Huh, yeah, sorry about that -- fixed here.  I think the name of the
> > param, which includes "worker", precludes from raising the values.
> 
> Well, I'm not sure the average user knows or cares about the difference
> between the launcher and the workers.  The thing that was in the back of
> my mind was that we would now have the option to have the launcher show
> up in pg_stat_activity.  If we were to do that then the case for
> counting it in the user-visible number-of-processes parameter would get
> a lot stronger (enough to justify renaming the parameter, if you insist
> that the launcher isn't a worker).  I don't however have any strong
> opinion on whether we *should* include it in pg_stat_activity ---
> comments?

The user may not care about the difference, but there's a point in
having the limit be the simpler concept of "this is the maximum amount
of processes running vacuum at any time".  The launcher is very
uninteresting to users.

> In the meantime, this looks reasonably sane in a fast read-through,
> but I saw a few comments that could use improvement, and I have not
> tried to actually review it (like look for missed places to change).
> Do you mind if I work it over for an hour or two?

Please go ahead.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Tom Lane wrote:

> Actually, there is a better way to do this: if we move up the
> RelationCacheInitializePhase2 call, then we can have the AV launcher
> case just fall out *before* the transaction start.  It can do
> GetTransactionSnapshot inside its own transaction that reads
> pg_database.  This is a better solution because it'll have a more
> up-to-date version of RecentGlobalXmin while scanning pg_database.
> (Indeed, I think this might be *necessary* over the very long haul.)

Hmm, good idea.

> I think I've got the signal handling cleaned up, but need to test.
> Is there any really good reason why autovacuum has its own avl_quickdie
> instead of using quickdie() for SIGQUIT?

No, probably I just copied it because the others were static.  Not sure
about quickdie() itself.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] 8.5 release timetable, again

[Kevin Grittner]

Bruce Momjian  wrote: 
 
> it gets no easier to make the decisions later rather than now.  The
> delay forces us to make a final decision.  We often had months to
> make the decision earlier, but didn't.
 
So you're advocating that we find a way to force more timely
decisions?
 
-Kevin

-- 
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] 8.5 release timetable, again

[Kevin Grittner]

Bruce Momjian  wrote: 
 
> The issues are different for every commitfest-beta period, so I have
> no idea what to list there, but we do alway have an open issues wiki
> that is maintained, at least for the most recent releases.
 
After a quick search of the wiki, it appears that the list for 8.4
was:
 
http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items
 
and that there is not yet a list for 8.5.  Is that correct?
 
If I understand what you're saying, this list would contain issues
where a patch was committed and later found to have problems which
need to be fixed.  Did I understand that correctly?  Anything else go
on there, or possibly belong on there?  Can we take the absence of a
list for 8.5 to indicate that no such problems have been found with
any patches committed since 8.4 was tagged?
 
-Kevin

-- 
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] 8.5 release timetable, again

[Robert Haas]

On Mon, Aug 31, 2009 at 2:20 PM, Bruce Momjian wrote:
> Knowing about the problem usually isn't hard , e.g. \df, but getting
> agreement on them is.  One nifty idea would be to do a commit-fest for
> open items so we can get to beta.

I like that idea very much.

> The last commit-fest usually is long
> because we can't postpone patches easily and often we are not 100% sure
> how to apply them either, so that make it extra-long.
>
> I am not sure what other checklist items there would be (or I am
> refusing to divulge).

LOL.  Well, there are things like release notes... and maybe others?

...Robert

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Josh Berkus wrote:
> Bruce,
> 
> > Huh, who has asked for a list from me?  This entire post is mostly
> > over-the-top and not worth responding to.
> 
> To quote myself:
> 
> > Post-CF:
> > Make a list (now, not in January) of the tasks which need to be done
> > between CFend and Beta.  We'll find that some of them could be done by
> > someone other than Tom and Bruce, and that others could be done before
> > CFend.
> >
> > Beta:
> > Create a mailing list (why don't we have a list for testers?  is
> > testing less important than the JDBC driver?) and a simple web app or
> > templated wiki page for testers.  Allow people to check in with which
> > version they tested (Alpha1,2,3, Beta 1,2,3) and what tests they ran,
> > and issues encountered (if any).  We should do this now so we can get
> > started with Alpha testing.
> > When this testing gets into swing, we can also look at recruiting
> > volunteers to run various popular OSS apps' test suites against the
> > developing version of PostgreSQL.
> > Once beta starts, have a list of pending issues in some
> > editable/commentable location (wiki is ok for this, or we can pervert
> > the commitfest app) *as soon as those issues arise* so that as many
> > hackers as possible can work on those issues. We did do a "pending
> > issues" list for 8.4, but not until we were already over a month into
> > beta, and then the list wasn't very accurate.
> 
> Therefore:
> 
> I will create a "cleanup issues" wikipage.  Please contribute to it by
> listing the *general* kinds of things you need to do between CF and
> beta.  Then we can look at which things don't need your special
> experience and could be done by other contributors.

That was a request for me to answer?  I had no idea.

The issues are different for every commitfest-beta period, so I have no
idea what to list there, but we do alway have an open issues wiki that
is maintained, at least for the most recent releases.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Joshua D. Drake wrote:
> On Mon, 2009-08-31 at 13:59 -0400, Bruce Momjian wrote:
> > Robert Haas wrote:
> > > On Mon, Aug 31, 2009 at 1:57 PM, Bruce Momjian wrote:
> > > > Josh Berkus wrote:
> > > >> Per the above, it would not. ?It would make things worse. ?This has 
> > > >> been
> > > >> true at every other OSS project I've seen documented (disastrously so
> > > >> with MySQL); there is no reason to believe that Postgres would be any
> > > >> different.
> > > >>
> > > >> I also do not see why you are so resistant to the idea of documenting a
> > > >> tracking the post-CF steps so that we can get more people on them.
> > > >
> > > > Huh, who has asked for a list from me? ?This entire post is mostly
> > > > over-the-top and not worth responding to.
> > > 
> > > OK, I so request.  :-)
> > 
> > What do you want to know?  Would someone post exactly what question I
> > have not answered in the past?
> 
> This is a fair point. I bet 10 bucks that a lot of the questions that
> would be asked would be answered with, "check the archives".
> 
> Didn't we do a release wiki page at one point?

Yes, we have a wiki for open items for the current major release.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Robert Haas wrote:
> That having been said, I think there is a legitimate concern about
> organizing and documenting the steps that are required to get a
> release out the door.  A number of people have said (on this thread
> and previous ones) that we didn't know what we were supposed to be
> doing during the period after the end of the last CommitFest and prior
> to release.  It appeared that all of the activity (to the extent that
> there was any activity) was by committers, particularly you and Tom.
> 
> Well, OK.  We want the release to happen faster next time.  We're
> willing to help.  In order to help, we first need a list of the tasks
> that need to be completed after the last CommitFest and before
> release.  Then we can try to figure out whether any of those tasks can
> be done (or assisted with) by someone other than you or Tom.
> 
> Can you provide one?

Well, at the end of the release I have a mailbox full of open items,
that I think need to be addressed before we go into beta.  Tom has a
similar list.  I usually put my mbox file up on a web site, and
sometimes it is transfered to a wiki by others.

Knowing about the problem usually isn't hard , e.g. \df, but getting
agreement on them is.  One nifty idea would be to do a commit-fest for
open items so we can get to beta.  The last commit-fest usually is long
because we can't postpone patches easily and often we are not 100% sure
how to apply them either, so that make it extra-long.

I am not sure what other checklist items there would be (or I am
refusing to divulge).

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] set_client_encoding is broken

[Zdenek Kotala]

Tom Lane píše v po 31. 08. 2009 v 11:00 -0400:
> 3. Push the startup-packet GUC processing (approx. lines 3340..3395 of
> postgres.c, as of CVS HEAD) into InitPostgres, so it can be run during
> the startup transaction.  This is not too unclean, though it would
> mean exporting process_postgres_switches() from postgres.c; I guess
> the main thing I don't like about it is that InitPostgres has enough
> weird responsibilities already.
> 
> I'm leaning to the third choice, but I wonder if anyone has any
> comments
> or better ideas.

It seems to me that 3 is OK.

Another possibility is that InitPostgres can only fill up rel cache and
GUC processing can stay on the same place. But in general, this problem
can affect any other GUC variable which has assign hook and needs to
lookup. 

I don't know how it works before, but I'm afraid that user can get error
message in server encoding before it is correctly set.


Zdenek 


-- 
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] 8.5 release timetable, again

[Josh Berkus]

Bruce,

> Huh, who has asked for a list from me?  This entire post is mostly
> over-the-top and not worth responding to.

To quote myself:

> Post-CF:
>   Make a list (now, not in January) of the tasks which need to be done
> between CFend and Beta.  We'll find that some of them could be done by
> someone other than Tom and Bruce, and that others could be done before
> CFend.
>
> Beta:
>   Create a mailing list (why don't we have a list for testers?  is
> testing less important than the JDBC driver?) and a simple web app or
> templated wiki page for testers.  Allow people to check in with which
> version they tested (Alpha1,2,3, Beta 1,2,3) and what tests they ran,
> and issues encountered (if any).  We should do this now so we can get
> started with Alpha testing.
>   When this testing gets into swing, we can also look at recruiting
> volunteers to run various popular OSS apps' test suites against the
> developing version of PostgreSQL.
>   Once beta starts, have a list of pending issues in some
> editable/commentable location (wiki is ok for this, or we can pervert
> the commitfest app) *as soon as those issues arise* so that as many
> hackers as possible can work on those issues. We did do a "pending
> issues" list for 8.4, but not until we were already over a month into
> beta, and then the list wasn't very accurate.

Therefore:

I will create a "cleanup issues" wikipage.  Please contribute to it by
listing the *general* kinds of things you need to do between CF and
beta.  Then we can look at which things don't need your special
experience and could be done by other contributors.


-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.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] 8.5 release timetable, again

[Joshua D. Drake]

On Mon, 2009-08-31 at 13:59 -0400, Bruce Momjian wrote:
> Robert Haas wrote:
> > On Mon, Aug 31, 2009 at 1:57 PM, Bruce Momjian wrote:
> > > Josh Berkus wrote:
> > >> Per the above, it would not. ?It would make things worse. ?This has been
> > >> true at every other OSS project I've seen documented (disastrously so
> > >> with MySQL); there is no reason to believe that Postgres would be any
> > >> different.
> > >>
> > >> I also do not see why you are so resistant to the idea of documenting a
> > >> tracking the post-CF steps so that we can get more people on them.
> > >
> > > Huh, who has asked for a list from me? ?This entire post is mostly
> > > over-the-top and not worth responding to.
> > 
> > OK, I so request.  :-)
> 
> What do you want to know?  Would someone post exactly what question I
> have not answered in the past?

This is a fair point. I bet 10 bucks that a lot of the questions that
would be asked would be answered with, "check the archives".

Didn't we do a release wiki page at one point?


Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



-- 
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] 8.5 release timetable, again

[Robert Haas]

On Mon, Aug 31, 2009 at 1:59 PM, Bruce Momjian wrote:
> Robert Haas wrote:
>> On Mon, Aug 31, 2009 at 1:57 PM, Bruce Momjian wrote:
>> > Josh Berkus wrote:
>> >> Per the above, it would not. ?It would make things worse. ?This has been
>> >> true at every other OSS project I've seen documented (disastrously so
>> >> with MySQL); there is no reason to believe that Postgres would be any
>> >> different.
>> >>
>> >> I also do not see why you are so resistant to the idea of documenting a
>> >> tracking the post-CF steps so that we can get more people on them.
>> >
>> > Huh, who has asked for a list from me? ?This entire post is mostly
>> > over-the-top and not worth responding to.
>>
>> OK, I so request.  :-)
>
> What do you want to know?  Would someone post exactly what question I
> have not answered in the past?

I don't know whether there is a specific question that you have
refused to answer in the past, or not.  My suspicion is that there
isn't, but perhaps someone else is aware of something I'm not.

That having been said, I think there is a legitimate concern about
organizing and documenting the steps that are required to get a
release out the door.  A number of people have said (on this thread
and previous ones) that we didn't know what we were supposed to be
doing during the period after the end of the last CommitFest and prior
to release.  It appeared that all of the activity (to the extent that
there was any activity) was by committers, particularly you and Tom.

Well, OK.  We want the release to happen faster next time.  We're
willing to help.  In order to help, we first need a list of the tasks
that need to be completed after the last CommitFest and before
release.  Then we can try to figure out whether any of those tasks can
be done (or assisted with) by someone other than you or Tom.

Can you provide one?

...Robert

-- 
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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> While I was looking at this I wondered whether
>> RelationCacheInitializePhase2 really needs to be inside the startup
>> transaction at all.  I think it could probably be moved up before
>> that.  However, if the AV launcher has to do GetTransactionSnapshot
>> then it's not clear that improves matters anyway. 

> Well, the difference is that the initial transaction would be a few
> microsec shorter ... not sure if that matters.

Actually, there is a better way to do this: if we move up the
RelationCacheInitializePhase2 call, then we can have the AV launcher
case just fall out *before* the transaction start.  It can do
GetTransactionSnapshot inside its own transaction that reads
pg_database.  This is a better solution because it'll have a more
up-to-date version of RecentGlobalXmin while scanning pg_database.
(Indeed, I think this might be *necessary* over the very long haul.)

I think I've got the signal handling cleaned up, but need to test.
Is there any really good reason why autovacuum has its own avl_quickdie
instead of using quickdie() for SIGQUIT?

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] 8.5 release timetable, again

[Bruce Momjian]

Robert Haas wrote:
> On Mon, Aug 31, 2009 at 1:57 PM, Bruce Momjian wrote:
> > Josh Berkus wrote:
> >> Per the above, it would not. ?It would make things worse. ?This has been
> >> true at every other OSS project I've seen documented (disastrously so
> >> with MySQL); there is no reason to believe that Postgres would be any
> >> different.
> >>
> >> I also do not see why you are so resistant to the idea of documenting a
> >> tracking the post-CF steps so that we can get more people on them.
> >
> > Huh, who has asked for a list from me? ?This entire post is mostly
> > over-the-top and not worth responding to.
> 
> OK, I so request.  :-)

What do you want to know?  Would someone post exactly what question I
have not answered in the past?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Kevin Grittner wrote:
> Bruce Momjian  wrote: 
>  
> > Yep, the bottom line here is that patches get into CVS, but issues
> > come up related to the patch, and we keep looking for good fixes,
> > but once the final commit-fest is over, we _have_ to fix these
> > issues.
>  
> If, hypothetically, it might hold up the release for two weeks while
> such issues are sorted out, might it be better to revert and say the
> patch missed the release because it wasn't workable enough at the end
> of the last CF to allow a beta release to be generated?  If the net
> result was that a feature or two were delayed until the next release,
> but all developers had two more weeks of development time in the next
> release cycle, it seems like reverting would be a net gain.

The problem is that many of these decisions are complex so it gets no
easier to make the decisions later rather than now.  The delay forces us
to make a final decision.  We often had months to make the decision
earlier, but didn't.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Robert Haas]

On Mon, Aug 31, 2009 at 1:57 PM, Bruce Momjian wrote:
> Josh Berkus wrote:
>> Per the above, it would not.  It would make things worse.  This has been
>> true at every other OSS project I've seen documented (disastrously so
>> with MySQL); there is no reason to believe that Postgres would be any
>> different.
>>
>> I also do not see why you are so resistant to the idea of documenting a
>> tracking the post-CF steps so that we can get more people on them.
>
> Huh, who has asked for a list from me?  This entire post is mostly
> over-the-top and not worth responding to.

OK, I so request.  :-)

...Robert

-- 
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] 8.5 release timetable, again

[Bruce Momjian]

Josh Berkus wrote:
> Per the above, it would not.  It would make things worse.  This has been
> true at every other OSS project I've seen documented (disastrously so
> with MySQL); there is no reason to believe that Postgres would be any
> different.
> 
> I also do not see why you are so resistant to the idea of documenting a
> tracking the post-CF steps so that we can get more people on them.

Huh, who has asked for a list from me?  This entire post is mostly
over-the-top and not worth responding to.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

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

-- 
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] 8.5 release timetable, again

[Kevin Grittner]

Bruce Momjian  wrote: 
 
> Yep, the bottom line here is that patches get into CVS, but issues
> come up related to the patch, and we keep looking for good fixes,
> but once the final commit-fest is over, we _have_ to fix these
> issues.
 
If, hypothetically, it might hold up the release for two weeks while
such issues are sorted out, might it be better to revert and say the
patch missed the release because it wasn't workable enough at the end
of the last CF to allow a beta release to be generated?  If the net
result was that a feature or two were delayed until the next release,
but all developers had two more weeks of development time in the next
release cycle, it seems like reverting would be a net gain.
 
-Kevin

-- 
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] 8.5 release timetable, again

[Joshua D. Drake]

On Mon, 2009-08-31 at 10:30 -0700, Josh Berkus wrote:
> >>> Another solution would be to make major releases less frequent.
> >> That's not a solution and you know it.
> > 
> > I do?
> 
> Ok, here's the reasons it's not a solution:

> Per the above, it would not.  It would make things worse.  This has been
> true at every other OSS project I've seen documented (disastrously so
> with MySQL); there is no reason to believe that Postgres would be any
> different.
> 
> I also do not see why you are so resistant to the idea of documenting a
> tracking the post-CF steps so that we can get more people on them.
> 

I love how we all have the same arguments, every year, year after year.
So let me just throw in my proverbial two cents.

As I see it we can *NOT* increase our development time line. Open Source
just doesn't work that way. People want it, and want it now. Period. It
will alienate feature contributors and make us fodder for bad press
(blogs whatever) when we are lacking in some area where another isn't.

We can decrease our development cycle. We could do an Ubuntu (and
similarly Fedora) style cycle where people that want the hot new
features now, can. They would do this by using our 6 month releases,
while stable enterprises would use our LTS release. This is "kind of"
happening now with our new Alpha release status.

We can release annually and go all balls toward each release.

The second option seems to be the middle ground that we will settle on
regardless of what arguments are presented. The third option is what I
would like to see happen. Which means we would actually have a 9 month
development cycle/cutoff and a three month alpha/beta/release.

Joshua D. Drake
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



-- 
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] 8.5 release timetable, again

[Josh Berkus]

>>> Another solution would be to make major releases less frequent.
>> That's not a solution and you know it.
> 
> I do?

Ok, here's the reasons it's not a solution:

1) having a longer development cycle would frustrate many users who want
new features sooner, not later.  The current 1 year is a good compromise
between reliability and "release often".  A longer period would not be.

2) Lengthening the development period would make things less efficient.
 The amount of effort we need to test, document, integrate, package,
etc., gets *greater* per patch when we have hundreds of patches.  So if
we *planned* an 18-month release, I expect that it would end up being a
24-month release.

3) If we deliberately lengthen the release cycle without doing anything
about why the post-CF portion takes so long, it will continue to get
longer, indefinitely.  Eventually, we're at 3.5 year releases and our
users abandoning Postgres for another database who can actually get a
release out.

4) It does nothing to address the *contributor* complaint that the
non-development part of our dev cycle is too long and keeps getting
longer.  A longer release cycle would make that worse.

If we could concievably do a release every 4 months, I believe that it
would be easy to keep the non-development portion of our cycle down to
30% or less.  We can't, so we need to look at ways to speed up the work
we're already doing.

> I have no idea how you know so much about me, but don't realize I was
> saying that we should extend the release cycle so we don't release as
> often, "make major releases less frequent" (every 12-14 months).  This
> has nothing to do with how we process the releases, parallel or not.

OK, to restate: making the cycle longer will not help the
development-to-integration&testing ratio.  It will make it worse.

> As I have said in the past, we are nearing feature-completeness (in a
> way), so having perhaps an 18-month release cycle is an idea.  That
> would give more time for development compared to beta, etc.

Per the above, it would not.  It would make things worse.  This has been
true at every other OSS project I've seen documented (disastrously so
with MySQL); there is no reason to believe that Postgres would be any
different.

I also do not see why you are so resistant to the idea of documenting a
tracking the post-CF steps so that we can get more people on them.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.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] Linux LSB init script

[Kevin Grittner]

Peter Eisentraut  wrote: 
 
> If it's a new file, it's pointless to send a patch anyway.
 
If people are OK with just sending the new file, that's cool with me.
>From the other posts, it appears that I need to have my own copy of
the repository to do it as a patch, or download a tool.  (Thanks to
those who offered the suggestions.)
 
> You could also consider putting it in place of the linux file.
 
Is the LSB standard sufficiently widely adopted to omit the older
format?  I was concerned that there might be Linux versions we wanted
to support which wouldn't have a /lib/lsb/init-functions file to
source.  If that's not an issue, I could submit this as a patch to the
existing file.  (It'd be a - for almost every non-blank line in the
old, and a + for almost every non-blank line in the new, of course.)
 
-Kevin

-- 
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] Add YAML option to explain

[Kevin Grittner]

"Greg Sabino Mullane"  wrote:
 
>  -
>Plan:
>  Node Type: Index Scan
>  Scan Direction: Forward
>  Index Name: pg_class_relname_nsp_index
>  Relation Name: pg_class
>  Alias: pg_class
>  Startup Cost: 0.00
>  Total Cost: 8.27
>  Plan Rows: 1
>  Plan Width: 0
>  Actual Startup Time: 0.019
>  Actual Total Time: 0.019
>  Actual Rows: 0
>  Actual Loops: 1
>  Index Cond: "(relname = 'foo\"bar\"'::name)"
>Triggers:
>Total Runtime: 0.058
 
+1 for including this format.  On a ten point scale for human
readability, I'd give this about a nine.  It's something I'd be
comfortable generating in order to annotate and include in an email to
programmers or managers who wouldn't have a clue how to read the
current text version of a plan.
 
-Kevin

-- 
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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> I wonder if it would be cleaner to include the launcher in
>> the autovacuum_max_workers parameter, and increase the min/default
>> values of that by one.

> Huh, yeah, sorry about that -- fixed here.  I think the name of the
> param, which includes "worker", precludes from raising the values.

Well, I'm not sure the average user knows or cares about the difference
between the launcher and the workers.  The thing that was in the back of
my mind was that we would now have the option to have the launcher show
up in pg_stat_activity.  If we were to do that then the case for
counting it in the user-visible number-of-processes parameter would get
a lot stronger (enough to justify renaming the parameter, if you insist
that the launcher isn't a worker).  I don't however have any strong
opinion on whether we *should* include it in pg_stat_activity ---
comments?

In the meantime, this looks reasonably sane in a fast read-through,
but I saw a few comments that could use improvement, and I have not
tried to actually review it (like look for missed places to change).
Do you mind if I work it over for an hour or two?

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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > How about this?
> 
> I think the accounting for the AV launcher in shmem allocation is a bit
> confused yet --- for instance, isn't MaxBackends already including the
> launcher?  I wonder if it would be cleaner to include the launcher in
> the autovacuum_max_workers parameter, and increase the min/default
> values of that by one.

Huh, yeah, sorry about that -- fixed here.  I think the name of the
param, which includes "worker", precludes from raising the values.

Changes between v2 and v3:

diff -u src/backend/storage/lmgr/proc.c src/backend/storage/lmgr/proc.c
--- src/backend/storage/lmgr/proc.c 31 Aug 2009 13:36:56 -
+++ src/backend/storage/lmgr/proc.c 31 Aug 2009 16:14:08 -
@@ -103,7 +103,7 @@
/* AuxiliaryProcs */
size = add_size(size, mul_size(NUM_AUXILIARY_PROCS, sizeof(PGPROC)));
/* MyProcs, including autovacuum workers and launcher */
-   size = add_size(size, mul_size(MaxBackends + 1, sizeof(PGPROC)));
+   size = add_size(size, mul_size(MaxBackends, sizeof(PGPROC)));
/* ProcStructLock */
size = add_size(size, sizeof(slock_t));
 
@@ -192,6 +192,7 @@
ProcGlobal->freeProcs = &procs[i];
}
 
+   /* note: the "+1" here accounts for the autovac launcher */
procs = (PGPROC *) ShmemAlloc((autovacuum_max_workers + 1) * 
sizeof(PGPROC));
if (!procs)
ereport(FATAL,
diff -u src/backend/utils/misc/guc.c src/backend/utils/misc/guc.c
--- src/backend/utils/misc/guc.c31 Aug 2009 03:07:47 -
+++ src/backend/utils/misc/guc.c31 Aug 2009 16:12:56 -
@@ -7570,7 +7570,7 @@
 static bool
 assign_maxconnections(int newval, bool doit, GucSource source)
 {
-   if (newval + autovacuum_max_workers > INT_MAX / 4)
+   if (newval + autovacuum_max_workers + 1 > INT_MAX / 4)
return false;
 
if (doit)


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.103
diff -c -p -r1.103 autovacuum.c
*** src/backend/postmaster/autovacuum.c	27 Aug 2009 17:18:44 -	1.103
--- src/backend/postmaster/autovacuum.c	31 Aug 2009 15:49:14 -
*** AutoVacLauncherMain(int argc, char *argv
*** 424,432 
  #endif
  
  	/*
! 	 * Set up signal handlers.	Since this is an auxiliary process, it has
! 	 * particular signal requirements -- no deadlock checker or sinval
! 	 * catchup, for example.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
--- 424,432 
  #endif
  
  	/*
! 	 * Set up signal handlers.	We operate on databases much like a regular
! 	 * backend, so we use the same signal handling.  See equivalent code in
! 	 * tcop/postgres.c.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
*** AutoVacLauncherMain(int argc, char *argv
*** 451,459 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitAuxiliaryProcess();
  #endif
  
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
--- 451,461 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitProcess();
  #endif
  
+ 	InitPostgres(NULL, InvalidOid, NULL, NULL);
+ 
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
*** AutoVacLauncherMain(int argc, char *argv
*** 470,476 
  	/*
  	 * If an exception is encountered, processing resumes here.
  	 *
! 	 * This code is heavily based on bgwriter.c, q.v.
  	 */
  	if (sigsetjmp(local_sigjmp_buf, 1) != 0)
  	{
--- 472,478 
  	/*
  	 * If an exception is encountered, processing resumes here.
  	 *
! 	 * This code is a stripped down version of PostgresMain error recovery.
  	 */
  	if (sigsetjmp(local_sigjmp_buf, 1) != 0)
  	{
*** AutoVacLauncherMain(int argc, char *argv
*** 483,496 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		/*
! 		 * These operations are really just a minimal subset of
! 		 * AbortTransaction().	We don't have very many resources to worry
! 		 * about, but we do have LWLocks.
! 		 */
! 		LWLockReleaseAll();
! 		AtEOXact_Files();
! 		AtEOXact_HashTables(false);
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
--- 485,492 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		/* Abort the current transaction in order to recover */
! 		AbortCurrentTransaction();
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
*** autovac_balance_cost(void)
*** 1784,1829

Re: [HACKERS] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> Heikki Linnakangas wrote:
>> I quite like the idea of splitting initialization into two phases, one
>> that let's you access shared catalogs, and one to bind to a database. I
>> didn't look into the details, though.

> The problem is that it only gives you access to pg_database, because the
> other shared catalogs require more relcache initialization than we do.
> So I'm not sure it'd be useful for anything else.

The part I was unhappy about was falling out with the startup
transaction still open (which the patch as written didn't do, but
would have needed to given the snapshot issue).  I don't object to
trying to restructure InitPostgres along the above lines if it can be
done cleanly.  But on the third hand, Alvaro's right that there isn't
any other obvious use for it.

We've also got the client_encoding (GUC initialization timing) issue
to fix in this area.  I suggest that any major restructuring-for-beauty
wait till after the dust settles.  I think what we have here (in the
revised patch) is ugly but not too ugly to live with.

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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> How about this?

I think the accounting for the AV launcher in shmem allocation is a bit
confused yet --- for instance, isn't MaxBackends already including the
launcher?  I wonder if it would be cleaner to include the launcher in
the autovacuum_max_workers parameter, and increase the min/default
values of that by one.

>> While I was looking at this I wondered whether
>> RelationCacheInitializePhase2 really needs to be inside the startup
>> transaction at all.  I think it could probably be moved up before
>> that.  However, if the AV launcher has to do GetTransactionSnapshot
>> then it's not clear that improves matters anyway. 

> Well, the difference is that the initial transaction would be a few
> microsec shorter ... not sure if that matters.

I can't see how it would.  At the point where that runs we'd not be
holding any locks of interest, so there's no concurrency benefit
to be gained.  With this setup it wouldn't make InitPostgres any
cleaner anyway, so I'd leave it alone.

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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Heikki Linnakangas wrote:
> Tom Lane wrote:
> > Alvaro Herrera  writes:
> >> Tom Lane wrote:
> >>> This just seems truly messy :-(.  Let me see if I can find something
> >>> cleaner.
> 
> I quite like the idea of splitting initialization into two phases, one
> that let's you access shared catalogs, and one to bind to a database. I
> didn't look into the details, though.

The problem is that it only gives you access to pg_database, because the
other shared catalogs require more relcache initialization than we do.
So I'm not sure it'd be useful for anything else.

> >> I was considering having InitPostgres be an umbrella function, so that
> >> extant callers stay as today, but the various underlying pieces are
> >> skipped depending on who's calling.  For example I didn't like the bit
> >> about starting a transaction or not depending on whether it was the
> >> launcher.
> > 
> > Yeah.  If you have InitPostgres know that much about the AV launcher's
> > requirements, it's not clear why it shouldn't just know everything.
> > Having it return with the initial transaction still open just seems
> > completely horrid.
> 
> Yeah, that sounds messy. Can AV launcher simply open a 2nd initial
> transaction?

The main body of avlauncher opens a new transaction whenever it needs
one.  The problem is that the transaction in InitPostgres is closed in
the second half -- the code I had was skipping StartTransactionCommand
in the launcher case, but as Tom says that was wrong because it was
failing to set RecentGlobalXmin.

If we're looking at simplifing InitPostgres, one thing we could do is
separate the part for the bootstrap process, which is like 10% of the
work for a regular backend ...

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
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] autovacuum launcher using InitPostgres

[Heikki Linnakangas]

Tom Lane wrote:
> Alvaro Herrera  writes:
>> Tom Lane wrote:
>>> This just seems truly messy :-(.  Let me see if I can find something
>>> cleaner.

I quite like the idea of splitting initialization into two phases, one
that let's you access shared catalogs, and one to bind to a database. I
didn't look into the details, though.

>> I was considering having InitPostgres be an umbrella function, so that
>> extant callers stay as today, but the various underlying pieces are
>> skipped depending on who's calling.  For example I didn't like the bit
>> about starting a transaction or not depending on whether it was the
>> launcher.
> 
> Yeah.  If you have InitPostgres know that much about the AV launcher's
> requirements, it's not clear why it shouldn't just know everything.
> Having it return with the initial transaction still open just seems
> completely horrid.

Yeah, that sounds messy. Can AV launcher simply open a 2nd initial
transaction?


-- 
  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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > Tom Lane wrote:
> >> This just seems truly messy :-(.  Let me see if I can find something
> >> cleaner.
> 
> > I was considering having InitPostgres be an umbrella function, so that
> > extant callers stay as today, but the various underlying pieces are
> > skipped depending on who's calling.  For example I didn't like the bit
> > about starting a transaction or not depending on whether it was the
> > launcher.
> 
> Yeah.  If you have InitPostgres know that much about the AV launcher's
> requirements, it's not clear why it shouldn't just know everything.
> Having it return with the initial transaction still open just seems
> completely horrid.

How about this?

> While I was looking at this I wondered whether
> RelationCacheInitializePhase2 really needs to be inside the startup
> transaction at all.  I think it could probably be moved up before
> that.  However, if the AV launcher has to do GetTransactionSnapshot
> then it's not clear that improves matters anyway. 

Well, the difference is that the initial transaction would be a few
microsec shorter ... not sure if that matters.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.103
diff -c -p -r1.103 autovacuum.c
*** src/backend/postmaster/autovacuum.c	27 Aug 2009 17:18:44 -	1.103
--- src/backend/postmaster/autovacuum.c	31 Aug 2009 15:49:14 -
*** AutoVacLauncherMain(int argc, char *argv
*** 424,432 
  #endif
  
  	/*
! 	 * Set up signal handlers.	Since this is an auxiliary process, it has
! 	 * particular signal requirements -- no deadlock checker or sinval
! 	 * catchup, for example.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
--- 424,432 
  #endif
  
  	/*
! 	 * Set up signal handlers.	We operate on databases much like a regular
! 	 * backend, so we use the same signal handling.  See equivalent code in
! 	 * tcop/postgres.c.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
*** AutoVacLauncherMain(int argc, char *argv
*** 451,459 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitAuxiliaryProcess();
  #endif
  
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
--- 451,461 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitProcess();
  #endif
  
+ 	InitPostgres(NULL, InvalidOid, NULL, NULL);
+ 
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
*** AutoVacLauncherMain(int argc, char *argv
*** 470,476 
  	/*
  	 * If an exception is encountered, processing resumes here.
  	 *
! 	 * This code is heavily based on bgwriter.c, q.v.
  	 */
  	if (sigsetjmp(local_sigjmp_buf, 1) != 0)
  	{
--- 472,478 
  	/*
  	 * If an exception is encountered, processing resumes here.
  	 *
! 	 * This code is a stripped down version of PostgresMain error recovery.
  	 */
  	if (sigsetjmp(local_sigjmp_buf, 1) != 0)
  	{
*** AutoVacLauncherMain(int argc, char *argv
*** 483,496 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		/*
! 		 * These operations are really just a minimal subset of
! 		 * AbortTransaction().	We don't have very many resources to worry
! 		 * about, but we do have LWLocks.
! 		 */
! 		LWLockReleaseAll();
! 		AtEOXact_Files();
! 		AtEOXact_HashTables(false);
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
--- 485,492 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		/* Abort the current transaction in order to recover */
! 		AbortCurrentTransaction();
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
*** autovac_balance_cost(void)
*** 1784,1829 
  
  /*
   * get_database_list
   *
!  *		Return a list of all databases.  Note we cannot use pg_database,
!  *		because we aren't connected; we use the flat database file.
   */
  static List *
  get_database_list(void)
  {
- 	char	   *filename;
  	List	   *dblist = NIL;
! 	char		thisname[NAMEDATALEN];
! 	FILE	   *db_file;
! 	Oid			db_id;
! 	Oid			db_tablespace;
! 	TransactionId db_frozenxid;
! 
! 	filename = database_getflatfilename();
! 	db_file = AllocateFile(filename, "r");
! 	if (db_file == NULL)
! 		ereport(FATAL,
! (errcode_for_file_access(),
!  errmsg("could not open file \"%s\": %m", filename)));
  
! 	while (read_pg_database_line(db_file, thisname, &db_id,
!  &db_tablespace, &db_frozenxid))
  	{
! 		avw_dbase  *avdb;
  
  		avdb = (avw_dbase *) palloc(sizeof(avw_dba

Re: [HACKERS] XLogFlush

[Jeff Janes]

On Fri, Aug 21, 2009 at 1:18 AM, Jeff Janes  wrote:

> Maybe this is one of those things that is obvious when someone points
> it out to you, but right now I am not seeing it.  If you look at the
> last eight lines of this snippet from XLogFlush, you see that if we
> obtain WriteRqstPtr under the WALInsertLock, then we both write and
> flush up to the highest write request.  But if we obtain it under the
> info_lck, then we write up to the highest write request but flush only
> up to our own records flush request.  Why the disparate treatment?
> The effect of this seems to be that when WALInsertLock is busy, group
> commits are suppressed.
>

I realized I was misinterpreting this.  XLogWrite doesn't just flush up to
WriteRqst.Flush, because fsync doesn't work that way.  If it flushes at all
(which I think it always will when invoked from XLogFlush, as otherwise
XLogFlush would not call it), it will flush up to WriteRqst.Write anyway,
even if WriteRqst.Flush is behind.  So as long as record <= WriteRqst.Flush
<= WriteRqst.Write, then it doesn't matter exactly what WriteRqst.Flush is.
The problem with group commit on a busy WALInsertLock is that if the
xlogctl->LogwrtRqst.Write does get advanced by someone else, it is almost
surely going to be while we are waiting on the WALWriteLock, and so too late
for us to have discovered it when we previously checked under the protection
of info_lck.  We should probably have an else branch on the
LWLockConditionalAcquire so that if it fails, we get the info_lck and check
again for advancement of xlogctl->LogwrtRqst.Write.

But since Simon is doing big changes as part of sync rep, I'll hold off on
doing much experimentation on this until then.



>LWLockRelease(WALInsertLock);
>WriteRqst.Write = WriteRqstPtr;
>WriteRqst.Flush = WriteRqstPtr;
>}
>else
>{
>WriteRqst.Write = WriteRqstPtr;
>WriteRqst.Flush = record;
>}
>

Cheers,

Jeff

Re: [HACKERS] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> Tom Lane wrote:
>> This just seems truly messy :-(.  Let me see if I can find something
>> cleaner.

> I was considering having InitPostgres be an umbrella function, so that
> extant callers stay as today, but the various underlying pieces are
> skipped depending on who's calling.  For example I didn't like the bit
> about starting a transaction or not depending on whether it was the
> launcher.

Yeah.  If you have InitPostgres know that much about the AV launcher's
requirements, it's not clear why it shouldn't just know everything.
Having it return with the initial transaction still open just seems
completely horrid.

>> BTW, is it *really* the case that the AV launcher won't need
>> RecentGlobalXmin?  The way the HOT stuff works, I think anything that
>> examines heap pages at all had better have that set.

> Ugh.  I forgot about that.

We could possibly put

if (IsAutovacuumLauncher())
{
CommitTransactionCommand();
return;
}

right after the RelationCacheInitializePhase2 call.  No uglier than
what's here, for sure.

While I was looking at this I wondered whether
RelationCacheInitializePhase2 really needs to be inside the startup
transaction at all.  I think it could probably be moved up before
that.  However, if the AV launcher has to do GetTransactionSnapshot
then it's not clear that improves matters anyway. 

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] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera  writes:
> > To this end, InitPostgres has been split in two.  The launcher only
> > calls the first half, the rest of the callers have been patched to
> > invoke the second half.
> 
> This just seems truly messy :-(.  Let me see if I can find something
> cleaner.

I was considering having InitPostgres be an umbrella function, so that
extant callers stay as today, but the various underlying pieces are
skipped depending on who's calling.  For example I didn't like the bit
about starting a transaction or not depending on whether it was the
launcher.

> BTW, is it *really* the case that the AV launcher won't need
> RecentGlobalXmin?  The way the HOT stuff works, I think anything that
> examines heap pages at all had better have that set.

Ugh.  I forgot about that.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] combined indexes with Gist - planner issues?

[Hans-Juergen Schoenig -- PostgreSQL]

hello ...

we did some experiments with doing such a table.
the problem is if you want to allow arbitrary combinations of words 
which can be modeled perfectly with FTI.
you would instantly end up with a self join with 5 relations or so - 
which is again bad.


there are too many common words to consider doing with partly with gist 
and partly with a btree.


is there any option to adapt gist in a way that a combined index would 
make sense here?


   many thanks,

  hans




Heikki Linnakangas wrote:

Hans-Juergen Schoenig -- PostgreSQL wrote:
  

my knowledge of how gist works internally is not too extensive. any
"kickstart" idea would be appreciated.



If there's not too many of those common words, you can create a simple
partial b-tree index for each, and handle the less common words with the
gist index you have (you can drop the display_price column from the index).

Another idea:

Create a table containing one row for each word in each product:

CREATE TABLE t_product_word (id bigint, word text, display_price
numeric(10,4));

with triggers to keep it up-to-date. You can then create a regular two
column b-tree index on that:

CREATE INDEX idx_word_price ON t_product_word (word, display_price);

And query with:

SELECT p.art_number, p.title
   FROM t_product p INNER JOIN t_product_word pw ON p.id = pw.id
   WHERE pw.word = 'harddisk'
ORDER BY pw.display_price DESC LIMIT 10;

The t_product_word table will be huge, but with a few gigabytes of data
it should still be manageable.

  



--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


--
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] combined indexes with Gist - planner issues?

[Heikki Linnakangas]

Hans-Juergen Schoenig -- PostgreSQL wrote:
> my knowledge of how gist works internally is not too extensive. any
> "kickstart" idea would be appreciated.

If there's not too many of those common words, you can create a simple
partial b-tree index for each, and handle the less common words with the
gist index you have (you can drop the display_price column from the index).

Another idea:

Create a table containing one row for each word in each product:

CREATE TABLE t_product_word (id bigint, word text, display_price
numeric(10,4));

with triggers to keep it up-to-date. You can then create a regular two
column b-tree index on that:

CREATE INDEX idx_word_price ON t_product_word (word, display_price);

And query with:

SELECT p.art_number, p.title
   FROM t_product p INNER JOIN t_product_word pw ON p.id = pw.id
   WHERE pw.word = 'harddisk'
ORDER BY pw.display_price DESC LIMIT 10;

The t_product_word table will be huge, but with a few gigabytes of data
it should still be manageable.

-- 
  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] autovacuum launcher using InitPostgres

[Tom Lane]

Alvaro Herrera  writes:
> To this end, InitPostgres has been split in two.  The launcher only
> calls the first half, the rest of the callers have been patched to
> invoke the second half.

This just seems truly messy :-(.  Let me see if I can find something
cleaner.

BTW, is it *really* the case that the AV launcher won't need
RecentGlobalXmin?  The way the HOT stuff works, I think anything that
examines heap pages at all had better have that set.

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] Feature request : add REMAP_SCHEMA-like option to pg_restore

[Robert Haas]

On Mon, Aug 31, 2009 at 10:42 AM, Andrew Dunstan wrote:
>>> Hope you find the idea interesting. I'm willing to test anything or add
>>> more specification to the feature. I must admit too this is a patch I'd
>>> like to write too (it would be my very first) but I don't know if my C
>>> skills are good enough to do so.
>>>
>>
>> Well, you have a much better chance of having it happen if you write
>> the patch...  people are usually willing to tell you what you did
>> wrong and give a few hints out to fix it.
>
> Or pay someone to do it. I gather this requirement is from a commercial
> user. There are plenty of hired guns available.

Yep, that works too.

...Robert

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

[HACKERS] autovacuum launcher using InitPostgres

[Alvaro Herrera]

Hi,

This seems to be the last holdup for flatfiles.c.  This patch removes
usage of that code in autovacuum launcher, instead making it go through
the most basic relcache initialization so that it is able to read
pg_database.

To this end, InitPostgres has been split in two.  The launcher only
calls the first half, the rest of the callers have been patched to
invoke the second half.

The only thing I'm aware is missing from this patch is fixing up
avlauncher's signal handling, and adding a bit more commentary; also I
haven't tested it under EXEC_BACKEND yet.

Comments?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/bootstrap/bootstrap.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/bootstrap/bootstrap.c,v
retrieving revision 1.252
diff -c -p -r1.252 bootstrap.c
*** src/backend/bootstrap/bootstrap.c	2 Aug 2009 22:14:51 -	1.252
--- src/backend/bootstrap/bootstrap.c	31 Aug 2009 13:57:37 -
*** CheckerModeMain(void)
*** 469,475 
  	 * Do backend-like initialization for bootstrap mode
  	 */
  	InitProcess();
! 	InitPostgres(NULL, InvalidOid, NULL, NULL);
  	proc_exit(0);
  }
  
--- 469,476 
  	 * Do backend-like initialization for bootstrap mode
  	 */
  	InitProcess();
! 	InitPostgres();
! 	InitPostgresPhase2(NULL, InvalidOid, NULL, NULL);
  	proc_exit(0);
  }
  
*** BootstrapModeMain(void)
*** 493,499 
  	 * Do backend-like initialization for bootstrap mode
  	 */
  	InitProcess();
! 	InitPostgres(NULL, InvalidOid, NULL, NULL);
  
  	/* Initialize stuff for bootstrap-file processing */
  	for (i = 0; i < MAXATTR; i++)
--- 494,501 
  	 * Do backend-like initialization for bootstrap mode
  	 */
  	InitProcess();
! 	InitPostgres();
! 	InitPostgresPhase2(NULL, InvalidOid, NULL, NULL);
  
  	/* Initialize stuff for bootstrap-file processing */
  	for (i = 0; i < MAXATTR; i++)
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.103
diff -c -p -r1.103 autovacuum.c
*** src/backend/postmaster/autovacuum.c	27 Aug 2009 17:18:44 -	1.103
--- src/backend/postmaster/autovacuum.c	31 Aug 2009 14:45:39 -
*** StartAutoVacLauncher(void)
*** 386,391 
--- 386,392 
  	return 0;
  }
  
+ bool		in_transaction = false;
  /*
   * Main loop for the autovacuum launcher process.
   */
*** AutoVacLauncherMain(int argc, char *argv
*** 424,432 
  #endif
  
  	/*
! 	 * Set up signal handlers.	Since this is an auxiliary process, it has
! 	 * particular signal requirements -- no deadlock checker or sinval
! 	 * catchup, for example.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
--- 425,433 
  #endif
  
  	/*
! 	 * Set up signal handlers.	We operate on databases much like a regular
! 	 * backend, so we use the same signal handling.  See equivalent code in
! 	 * tcop/postgres.c.
  	 */
  	pqsignal(SIGHUP, avl_sighup_handler);
  
*** AutoVacLauncherMain(int argc, char *argv
*** 451,459 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitAuxiliaryProcess();
  #endif
  
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
--- 452,462 
  	 * had to do some stuff with LWLocks).
  	 */
  #ifndef EXEC_BACKEND
! 	InitProcess();
  #endif
  
+ 	InitPostgres();
+ 
  	/*
  	 * Create a memory context that we will do all our work in.  We do this so
  	 * that we can reset the context during error recovery and thereby avoid
*** AutoVacLauncherMain(int argc, char *argv
*** 483,496 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		/*
! 		 * These operations are really just a minimal subset of
! 		 * AbortTransaction().	We don't have very many resources to worry
! 		 * about, but we do have LWLocks.
! 		 */
! 		LWLockReleaseAll();
! 		AtEOXact_Files();
! 		AtEOXact_HashTables(false);
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
--- 486,507 
  		/* Report the error to the server log */
  		EmitErrorReport();
  
! 		if (in_transaction)
! 		{
! 			AbortCurrentTransaction();
! 			in_transaction = false;
! 		}
! 		else
! 		{
! 			/*
! 			 * These operations are really just a minimal subset of
! 			 * AbortTransaction().	We don't have very many resources to worry
! 			 * about, but we do have LWLocks.
! 			 */
! 			LWLockReleaseAll();
! 			AtEOXact_Files();
! 			AtEOXact_HashTables(false);
! 		}
  
  		/*
  		 * Now return to normal top-level context and clear ErrorContext for
*** AutoVacWorkerMain(int argc, char *argv[]
*** 1620,1626 
  		 * Note: if we have selected a j

Re: [HACKERS] set_client_encoding is broken

[Tom Lane]

Zdenek Kotala  writes:
> [4a9ae815.696e:1] LOG:  connection received: host=[local]
> [4a9ae815.696e:2] LOG:  connection authorized: user=postgres database=postgres
> [4a9ae815.696e:3] LOG:  conversion between UTF8 and LATIN2 is not supported
> [4a9ae815.696e:4] FATAL:  invalid value for parameter "client_encoding": 
> "UTF8"

> The assign_client_encoding->SetClientEncoding fails to find conversion 
> function.

Hmm.  The reason this used to work is that SetClientEncoding does
no real work if it's invoked before InitializeClientEncoding.  The
old method of handling client_encoding in the client's startup
message had the setting get processed before InitPostgres, then in
InitPostgres we'd call InitializeClientEncoding within the startup
transaction, and it would complete the unfinished catalog lookup.
In CVS HEAD we postpone the GUC processing till after InitPostgres,
but it's still outside of any transaction, so SetClientEncoding just
fails.

There are a number of possible solutions:

1. We could revert the changes in GUC handling, ie go back to applying
non-SUSET GUCs before InitPostgres and SUSET ones afterwards.  I don't
much care for this; that code was really ugly, and I'm still worried
about the possible security exposure of letting not-yet-authenticated
users set GUCs, even ones we think are harmless.

2. Move the InitializeClientEncoding call out of InitPostgres and put
it in PostgresMain after the GUC variables are all set.  This is pretty
bad from a performance point of view, though, since it appears to
require a second startup-time transaction.

3. Push the startup-packet GUC processing (approx. lines 3340..3395 of
postgres.c, as of CVS HEAD) into InitPostgres, so it can be run during
the startup transaction.  This is not too unclean, though it would
mean exporting process_postgres_switches() from postgres.c; I guess
the main thing I don't like about it is that InitPostgres has enough
weird responsibilities already.

I'm leaning to the third choice, but I wonder if anyone has any comments
or better ideas.

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] Feature request : add REMAP_SCHEMA-like option to pg_restore

[Andrew Dunstan]

Robert Haas wrote:

On Mon, Aug 31, 2009 at 9:59 AM, Jean-Paul
Argudo wrote:
  

Hope you find the idea interesting. I'm willing to test anything or add
more specification to the feature. I must admit too this is a patch I'd
like to write too (it would be my very first) but I don't know if my C
skills are good enough to do so.



Well, you have a much better chance of having it happen if you write
the patch...  people are usually willing to tell you what you did
wrong and give a few hints out to fix it.


  



Or pay someone to do it. I gather this requirement is from a commercial 
user. There are plenty of hired guns available.


AIUI the requirement doesn't seem very difficult or complex

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] combined indexes with Gist - planner issues?

[Hans-Juergen Schoenig -- PostgreSQL]

Martijn van Oosterhout wrote:

On Mon, Aug 31, 2009 at 04:06:22PM +0200, Hans-Juergen Schoenig -- PostgreSQL 
wrote:
  

ok, i thought it would be something gist specific i was not aware of.
the golden question now is: i am looking for the cheapest products given  
a certain text in an insane amount of data.
how to do it? other quals which could narrow down the amount of data  
would not help.


i cannot see an option with regular "weapons" ...
maybe you can an idea how to fix core to make it work? maybe there is a  
mechanism we could need.

we really have to make this work - no matter what it takes.
we are willing to put effort into that.



The way I usually attack such a problem is to think of a data
structure+algorithm that could produce the output you want. Once you've
got that it's usually clear how you can make postgres do it and what
changes would need to be made.

At first glance I don't see any nice data structure specific for your
problem. But it occurs to me that maybe you could just have a (btree)
index on the price and just scan in asceding order until you have
enough records. Expensive if the first record is expensive.

Another possibility is to change your query to use the price in the
GiST index: execute multiple queries of the form:

... AND display_price >= 0.01 and display_price < 1;
... AND display_price >= 1 and display_price < 10;

  


hello ...

i had a similar idea here but the problem is: prices will pretty much 
depends on products.
to get to some critical example: "book" is a horribly frequent word and 
you will find just too many in a too narrow price range.
using a price index is alone is not a good idea. how many products which 
cost USD 9.95 do you know and how many of them are books? :(
i did some experiments which PL/proxy to scale out a little and i wrote 
some C code to explicitly cache data from the start and so on.

this is all shit, however - it is too much data and I have too many request.
i don't want to fallback to some java-based stuff such as solr. it would 
totally ruin my credibility and the stand postgres has at this customer.

whatever it takes - a PG based solution has to be found and implemented.

my knowledge of how gist works internally is not too extensive. any 
"kickstart" idea would be appreciated.


   many thanks,

  hans

--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


--
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] combined indexes with Gist - planner issues?

[Martijn van Oosterhout]

On Mon, Aug 31, 2009 at 04:06:22PM +0200, Hans-Juergen Schoenig -- PostgreSQL 
wrote:
> ok, i thought it would be something gist specific i was not aware of.
> the golden question now is: i am looking for the cheapest products given  
> a certain text in an insane amount of data.
> how to do it? other quals which could narrow down the amount of data  
> would not help.
>
> i cannot see an option with regular "weapons" ...
> maybe you can an idea how to fix core to make it work? maybe there is a  
> mechanism we could need.
> we really have to make this work - no matter what it takes.
> we are willing to put effort into that.

The way I usually attack such a problem is to think of a data
structure+algorithm that could produce the output you want. Once you've
got that it's usually clear how you can make postgres do it and what
changes would need to be made.

At first glance I don't see any nice data structure specific for your
problem. But it occurs to me that maybe you could just have a (btree)
index on the price and just scan in asceding order until you have
enough records. Expensive if the first record is expensive.

Another possibility is to change your query to use the price in the
GiST index: execute multiple queries of the form:

... AND display_price >= 0.01 and display_price < 1;
... AND display_price >= 1 and display_price < 10;

Because you match less records the sort won't be so expensive and you
can stop once you have enough records.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [HACKERS] Feature request : add REMAP_SCHEMA-like option to pg_restore

[Robert Haas]

On Mon, Aug 31, 2009 at 9:59 AM, Jean-Paul
Argudo wrote:
> Hope you find the idea interesting. I'm willing to test anything or add
> more specification to the feature. I must admit too this is a patch I'd
> like to write too (it would be my very first) but I don't know if my C
> skills are good enough to do so.

Well, you have a much better chance of having it happen if you write
the patch...  people are usually willing to tell you what you did
wrong and give a few hints out to fix it.

...Robert

-- 
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] Add YAML option to explain

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-   
Hash: RIPEMD160  


> Greg, can we see a few examples of the YAML output 
> compared to both json and text?

Sure. Be warned it will make this email long. Because email may wrap things 
funny, I'll post the same thing here:   

Query 1:
http://pgsql.privatepaste.com/298pqiSwdH

Note that YAML quotes things like JSON does, but only when the quotes are 
needed.
Query 2:
 
http://pgsql.privatepaste.com/610uDDyMu6
 


greg=# explain (format text, analyze on) select * from pg_class where relname ~ 
'x' order by 1,2,3;
  QUERY PLAN
   
- 
--
 Sort  (cost=12.82..13.10 rows=111 width=185) (actual time=1.176..1.401 
rows=105 loops=1)   
   Sort Key: relname, relnamespace, reltype 

   Sort Method:  quicksort  Memory: 44kB

   ->  Seq Scan on pg_class  (cost=0.00..9.05 rows=111 width=185) (actual 
time=0.066..0.828 rows=105 loops=1)   
 Filter: (relname ~ 'x'::text)  

 Total runtime: 1.676 ms



greg=# explain (format json, analyze on) select * from pg_class where relname ~ 
'x' order by 1,2,3;
QUERY PLAN  
   
- ---   
   
 [  
   
   {
   
 "Plan": {  
   
   "Node Type": "Sort", 
   
   "Startup Cost": 12.82,   
   
   "Total Cost": 13.10, 
   
   "Plan Rows": 111,
   
   "Plan Width": 185,   
   
   "Actual Startup Time": 1.152,
   
   "Actual Total Time": 1.373,  
   
   "Actual Rows": 105,  
   
   "Actual Loops": 1,   
   
   "Sort Key": ["relname", "relnamespace", "reltype"],  
   
   "Sort Method": "quicksort",  
   
   "Sort Space Used": 44,   
   
   "Sort Space Type": "Memory", 
   
   "Plans": [   
   
 {  
   
   "Node Type": "Seq Scan", 
   
   "Parent Relationship": "Outer",  
   
   "Relation Name": "pg_class", 
   
   "Alias": "pg_class", 
   
   "Startup Cost": 0.00,
   
   "Total Cost": 9.05,  
   
   "Plan Rows": 111,
   
   "Plan Width": 185,   
   
   "Actual Startup Time": 0.067,
   
   "Actual Total Time": 0.817,  
   
   "Actual Rows": 105,  
   
   "Actual Loops": 1,   

Re: [HACKERS] Bison crashes postgresql

[Hans-Juergen Schoenig -- PostgreSQL]

Andrew Dunstan wrote:



Werner Echezuria wrote:

Hi, I have a code in which I translate some code from sqlf to sql, but
when it comes to yy_parse the server crashes, I have no idea why,
because it works fine in other situations.
  


I don't understand why you're doing what you're doing this way. 
Wouldn't it be better to patch the main postgres parser and make your 
functionality first class rather than having it run via an SQL string 
and a function that calls a secondary parser?


cheers

andrew



yes, this is the thing i had in mind as well.
what is your ultimate goal?

   many thanks,

  hans


--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


--
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] combined indexes with Gist - planner issues?

[Hans-Juergen Schoenig -- PostgreSQL]

Tom Lane wrote:

Hans-Juergen Schoenig -- PostgreSQL  writes:
  
what we basically expected here is that Postgres will scan the table 
using the index to give us the cheapest products containing the words we 
are looking for.
i am totally surprised to see that we have to fetch all products given 
the words, sort and then do the limit.



I don't know why you'd find that surprising.  GIST indexes have no
support for ordering.

regards, tom lane

  


ok, i thought it would be something gist specific i was not aware of.
the golden question now is: i am looking for the cheapest products given 
a certain text in an insane amount of data.
how to do it? other quals which could narrow down the amount of data 
would not help.


i cannot see an option with regular "weapons" ...
maybe you can an idea how to fix core to make it work? maybe there is a 
mechanism we could need.

we really have to make this work - no matter what it takes.
we are willing to put effort into that.

   many thanks,

  hans

--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


--
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] Bison crashes postgresql

[Andrew Dunstan]

Werner Echezuria wrote:

Hi, I have a code in which I translate some code from sqlf to sql, but
when it comes to yy_parse the server crashes, I have no idea why,
because it works fine in other situations.
  


I don't understand why you're doing what you're doing this way. Wouldn't 
it be better to patch the main postgres parser and make your 
functionality first class rather than having it run via an SQL string 
and a function that calls a secondary parser?


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

[HACKERS] Feature request : add REMAP_SCHEMA-like option to pg_restore

[Jean-Paul Argudo]

Hi there,


I searched the wiki and the lists about the REMAP_SCHEMA option's idea
of a well known RDBMS, since its 10g version.

Here's the short description:

REMAP_SCHEMA : Objects from one schema are loaded into another schema.

The idea is when we have a given schema (let's say this schema is
"prod") in a dump (custom format, done with pg_dump -Fc) we want to
restore it in another schema with a different name (let's say this
target schema is "prod_copy").

With the "clean" option this would give a user restoring a "production"
schema into a "test" (or dev, whatever) schema an easy way to refresh
the schema.

For sure at the moment there are workarounds, like creating the same
"prod" schema in the "development" database, and then rename it with an
alter. But this could be done automatically with options to pg_restore like:

  pg_restore [...] --remap_schema=source_schema:target_schema [...]

or something like

  pg_restore [...] --from_schema=source_schema --to_schema=target_schema
[...]

Well, I think you get the idea. What do you think of it ?

No need to say that the need comes from a big company using this RDBMS
in version 10g willing to migrate to PostgreSQL and
not-adapt-that-much-things to have their habits with this RDBMS not-that
much changed.

(yes, they use REMAP_SCHEMA a lot)

Hope you find the idea interesting. I'm willing to test anything or add
more specification to the feature. I must admit too this is a patch I'd
like to write too (it would be my very first) but I don't know if my C
skills are good enough to do so.

Cheers,

-- 
Jean-Paul Argudo
www.PostgreSQL.fr
www.Dalibo.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] combined indexes with Gist - planner issues?

[Tom Lane]

Hans-Juergen Schoenig -- PostgreSQL  writes:
> what we basically expected here is that Postgres will scan the table 
> using the index to give us the cheapest products containing the words we 
> are looking for.
> i am totally surprised to see that we have to fetch all products given 
> the words, sort and then do the limit.

I don't know why you'd find that surprising.  GIST indexes have no
support for ordering.

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

[HACKERS] Bison crashes postgresql

[Werner Echezuria]

Hi, I have a code in which I translate some code from sqlf to sql, but
when it comes to yy_parse the server crashes, I have no idea why,
because it works fine in other situations.

This is the code (the problem is in parse_sqlf, when I call  sqlf_yyparse):

#include "postgres.h"
#include "gram.h"
#include "utils/builtins.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "access/heapam.h"
#include "fmgr.h"
#include "miscadmin.h"

extern Datum sqlf(PG_FUNCTION_ARGS);
char *parse_sqlf();

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(sqlf);

Datum
sqlf(PG_FUNCTION_ARGS)
{
char*query = text_to_cstring(PG_GETARG_TEXT_PP(0));
char*sql;
ReturnSetInfo   *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
Tuplestorestate *tupstore;
TupleDesc   tupdesc;
int call_cntr;
int max_calls;
AttInMetadata   *attinmeta;
SPITupleTable   *spi_tuptable;
TupleDesc   spi_tupdesc;
boolfirstpass;
char*lastrowid;
int i;
int num_categories;
MemoryContext   per_query_ctx;
MemoryContext   oldcontext;
int ret;
int proc;

sql=(char *)palloc(strlen(query)*sizeof(char *));

sql=parse_sqlf(query);

/* check to see if caller supports us returning a tuplestore */
if (rsinfo == NULL || !IsA(rsinfo, ReturnSetInfo))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 errmsg("set-valued function called in context
that cannot accept a set")));
if (!(rsinfo->allowedModes & SFRM_Materialize))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 errmsg("materialize mode required, but it is not " \
"allowed in this context")));

per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;

/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
/* internal error */
elog(ERROR, "SPI_connect returned %d", ret);


/* Retrieve the desired rows */
ret = SPI_execute(sql, true, 0);
proc = SPI_processed;

/* If no qualifying tuples, fall out early */
if (ret != SPI_OK_SELECT || proc <= 0)
{
SPI_finish();
rsinfo->isDone = ExprEndResult;
PG_RETURN_NULL();
}

spi_tuptable = SPI_tuptable;
spi_tupdesc = spi_tuptable->tupdesc;

/* get a tuple descriptor for our result type */
switch (get_call_result_type(fcinfo, NULL, &tupdesc))
{
case TYPEFUNC_COMPOSITE:
/* success */
break;
case TYPEFUNC_RECORD:
/* failed to determine actual type of RECORD */
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 errmsg("function returning record called
in context "
"that cannot accept type record")));
break;
default:
/* result type isn't composite */
elog(ERROR, "return type must be a row type");
break;
}


/*
 * switch to long-lived memory context
 */
oldcontext = MemoryContextSwitchTo(per_query_ctx);

/* make sure we have a persistent copy of the result tupdesc */
tupdesc = CreateTupleDescCopy(tupdesc);

/* initialize our tuplestore in long-lived context */
tupstore =
tuplestore_begin_heap(rsinfo->allowedModes &
SFRM_Materialize_Random,
  false, work_mem);

MemoryContextSwitchTo(oldcontext);


/*
 * Generate attribute metadata needed later to produce tuples from raw C
 * strings
 */
attinmeta = TupleDescGetAttInMetadata(tupdesc);

/* total number of tuples to be examined */
max_calls = proc;

/* the return tuple always must have 1 rowid + num_categories columns */
num_categories = tupdesc->natts;

firstpass = true;
lastrowid = NULL;


for (call_cntr = 0; call_cntr < max_calls; call_cntr++)
{
char**values;
HeapTuple   spi_tuple;
HeapTuple   tuple;

/* allocate and zero space */
values = (char **) palloc0((1 + num_categories) * sizeof(char *));

/* get the next sql result tuple */
spi_tuple = spi_tuptable->vals[call_cntr];

/*
 * now loop through the sql results and assign each value in sequence
 * to the next category
 */
for (i = 0; i < num_categories; i++)
{
/* see if we've gone too far already */
if (call_cntr >= max_calls)
break;

values[i] = SPI_getvalue(spi_tuple, spi_tupdesc, i+1);
 

Re: [HACKERS] Add YAML option to explain

[Greg Sabino Mullane]

On 08/28/2009 02:16 PM, Greg Sabino Mullane wrote:
> Attached patch adds YAML output option to explain:
> 
> explain (format YAML) select * from information_schema.columns;

Updated version of the patch attached, fixes two small errors.


--
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 200908310847
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Index: contrib/auto_explain/auto_explain.c
===
RCS file: /projects/cvsroot/pgsql/contrib/auto_explain/auto_explain.c,v
retrieving revision 1.7
diff -c -r1.7 auto_explain.c
*** contrib/auto_explain/auto_explain.c	10 Aug 2009 05:46:49 -	1.7
--- contrib/auto_explain/auto_explain.c	31 Aug 2009 13:36:41 -
***
*** 29,34 
--- 29,35 
  {"text", EXPLAIN_FORMAT_TEXT, false},
  {"xml", EXPLAIN_FORMAT_XML, false},
  {"json", EXPLAIN_FORMAT_JSON, false},
+ {"yaml", EXPLAIN_FORMAT_YAML, false},
  {NULL, 0, false}
  };
  
Index: doc/src/sgml/auto-explain.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/auto-explain.sgml,v
retrieving revision 1.4
diff -c -r1.4 auto-explain.sgml
*** doc/src/sgml/auto-explain.sgml	10 Aug 2009 05:46:50 -	1.4
--- doc/src/sgml/auto-explain.sgml	31 Aug 2009 13:36:41 -
***
*** 114,120 
auto_explain.log_format selects the
EXPLAIN output format to be used.
The allowed values are text, xml,
!   and json.  The default is text.
Only superusers can change this setting.
   
  
--- 114,120 
auto_explain.log_format selects the
EXPLAIN output format to be used.
The allowed values are text, xml,
!   json, and yaml.  The default is text.
Only superusers can change this setting.
   
  
Index: doc/src/sgml/release-8.5.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/release-8.5.sgml,v
retrieving revision 1.4
diff -c -r1.4 release-8.5.sgml
*** doc/src/sgml/release-8.5.sgml	19 Aug 2009 08:18:48 -	1.4
--- doc/src/sgml/release-8.5.sgml	31 Aug 2009 13:36:41 -
***
*** 96,102 

  

! EXPLAIN allows output of plans in XML or JSON format for automated
  processing of explain plans by analysis or visualization tools.

  
--- 96,102 

  

! EXPLAIN allows output of plans in XML, JSON, or YAML format for automated
  processing of explain plans by analysis or visualization tools.

  
Index: doc/src/sgml/ref/explain.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v
retrieving revision 1.46
diff -c -r1.46 explain.sgml
*** doc/src/sgml/ref/explain.sgml	10 Aug 2009 05:46:50 -	1.46
--- doc/src/sgml/ref/explain.sgml	31 Aug 2009 13:36:41 -
***
*** 31,37 
  
   
  
! EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | FORMAT { TEXT | XML | JSON } } [, ...] ) ] statement
  EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
  
   
--- 31,37 
  
   
  
! EXPLAIN [ ( { ANALYZE boolean | VERBOSE boolean | COSTS boolean | FORMAT { TEXT | XML | JSON | YAML } } [, ...] ) ] statement
  EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
  
   
***
*** 143,150 
  FORMAT
  
   
!   Specify the output format, which can be TEXT, XML, or JSON.
!   XML or JSON output contains the same information as the text output
format, but is easier for programs to parse.  This parameter defaults to
TEXT.
   
--- 143,150 
  FORMAT
  
   
!   Specify the output format, which can be TEXT, XML, JSON, or YAML.
!   Non-text output contains the same information as the text output
format, but is easier for programs to parse.  This parameter defaults to
TEXT.
   
Index: src/backend/commands/explain.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/explain.c,v
retrieving revision 1.190
diff -c -r1.190 explain.c
*** src/backend/commands/explain.c	22 Aug 2009 02:06:32 -	1.190
--- src/backend/commands/explain.c	31 Aug 2009 13:36:41 -
***
*** 96,101 
--- 96,102 
  static void ExplainXMLTag(const char *tagname, int flags, ExplainState *es);
  static void ExplainJSONLineEnding(ExplainState *es);
  static void escape_json(StringInfo buf, const char *str);
+ static void escape_yaml(StringInfo buf, const char *str);
  
  
  /*
***
*** 137,142 
--- 138,145 
  es.format = EXPLAIN_FORMAT_XML;
  			else if (strcmp(p, "json") == 0)
  es.format = EXPLAIN_FORMAT_JSON;
+ 			else if (strcmp(p, "yaml") == 0)
+ es.fo

[HACKERS] combined indexes with Gist - planner issues?

[Hans-Juergen Schoenig -- PostgreSQL]

hello everybody,

we are seriously fighting with some planner issue which seems to be 
slightly obscure to us.

we have a table which is nicely indexed (several GB in size).
i am using btree_gist operator classes to use a combined index including 
an FTI expression along with a number:


db=# \d product.t_product
  Table "product.t_product"
   Column | Type  |   
Modifiers   
---+---+
id| bigint| not null default 
nextval('product.t_product_id_seq'::regclass)

shop_id   | integer   |
art_number| text  |
title | text  |
description   | text  |
display_price | numeric(10,4) |

Indexes:
   "t_product_pkey" PRIMARY KEY, btree (id)
   "idx_test" gist (display_price, to_tsvector('german'::regconfig, 
(title || ' '::text) || description))
*"idx_test2" gist (to_tsvector('german'::regconfig, (title || ' 
'::text) || description), display_price)*



what we basically expected here is that Postgres will scan the table 
using the index to give us the cheapest products containing the words we 
are looking for.
i am totally surprised to see that we have to fetch all products given 
the words, sort and then do the limit.
this totally kills performance because some words simply show up 
millions of times. this totally kills everything.


the plans look like this:

db=#  explain analyze SELECT art_number, title
   FROM product.t_product
   WHERE to_tsvector('german'::regconfig, (title || ' '::text) || 
description) @@ plainto_tsquery('harddisk')

   ORDER BY display_price
   LIMIT 10;
  QUERY 
PLAN  

Limit  (cost=108340.08..108340.10 rows=10 width=54) (actual 
time=1328.900..1328.909 rows=10 loops=1)
  ->  Sort  (cost=108340.08..108422.48 rows=32961 width=54) (actual 
time=1328.899..1328.905 rows=10 loops=1)

Sort Key: display_price
Sort Method:  top-N heapsort  Memory: 18kB
->  Bitmap Heap Scan on t_product  (cost=2716.62..107627.80 
rows=32961 width=54) (actual time=1052.706..1328.772 rows=55 loops=1)
  Recheck Cond: (to_tsvector('german'::regconfig, ((title 
|| ' '::text) || description)) @@ plainto_tsquery('harddisk'::text))
  ->  Bitmap Index Scan on idx_test2  (cost=0.00..2708.38 
rows=32961 width=0) (actual time=1052.576..1052.576 rows=55 loops=1)
Index Cond: (to_tsvector('german'::regconfig, 
((title || ' '::text) || description)) @@ plainto_tsquery('harddisk'::text))

Total runtime: 1328.942 ms
(9 rows)


runtime increases badly if words start to be more likely ...


db=#  explain analyze SELECT art_number, title
   FROM product.t_product
   WHERE to_tsvector('german'::regconfig, (title || ' '::text) || 
description) @@ plainto_tsquery('spiel')

   ORDER BY display_price
   LIMIT 10;
 QUERY 
PLAN 
--
Limit  (cost=108340.08..108340.10 rows=10 width=54) (actual 
time=33489.675..33489.682 rows=10 loops=1)
  ->  Sort  (cost=108340.08..108422.48 rows=32961 width=54) (actual 
time=33489.675..33489.675 rows=10 loops=1)

Sort Key: display_price
Sort Method:  top-N heapsort  Memory: 18kB
->  Bitmap Heap Scan on t_product  (cost=2716.62..107627.80 
rows=32961 width=54) (actual time=774.923..33408.522 rows=56047 loops=1)
  Recheck Cond: (to_tsvector('german'::regconfig, ((title 
|| ' '::text) || description)) @@ plainto_tsquery('spiel'::text))
  ->  Bitmap Index Scan on idx_test2  (cost=0.00..2708.38 
rows=32961 width=0) (actual time=759.078..759.078 rows=56047 loops=1)
Index Cond: (to_tsvector('german'::regconfig, 
((title || ' '::text) || description)) @@ plainto_tsquery('spiel'::text))

Total runtime: 33489.906 ms
(9 rows)

i am wondering why postgres is not able to use a combined index here?
is this some obscure thing related to gist, a logical problem or a 
planner deficiency?


ideas are welcome.

   many thanks,

  hans



--
Cybertec Schoenig & Schoenig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de


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

[HACKERS] Hot Standby, conflict cache

[Heikki Linnakangas]

I'm looking at the most recent version of the Hot Standby patch at
Robert Haas' GIT repository. The conflict cache code is broken:

> +void
> +SetDeferredRecoveryConflicts(TransactionId latestRemovedXid, RelFileNode 
> node,
> +XLogRecPtr conflict_lsn)
> +{
> +   ProcArrayStruct *arrayP = procArray;
> +   int index;
> +   Oid dbOid = node.dbNode;
> +
> +   Assert(InRecovery);
> +
> +   if (!LatestRemovedXidAdvances(latestRemovedXid))
> +   return;
> +

The idea of LatestRemoveXidAdvances() is to exit quickly when we're
called with a latestRemovedXid value <= the previous latestRemovedXid
value. However, the conflict caches store information per relation. If
you first call e.g "SetDeferredRecoveryConflicts(1000, 'rel_A', 1234)",
followed by "SetDeferredRecoveryConflicts(1000, 'rel_B', 1234)", the
latter call exits quickly. If a transaction that holds a "too old"
snapshot then accesses rel_B, it won't fail as it should.

Something else must be severly broken in the conflict resolution code as
well: while testing with just one tiny table, I can easily reproduce a
violation of serializable snapshot:

postgres=# begin ISOLATION LEVEL serializable;
BEGIN
postgres=# SELECT * FROM foo;
 id
-
 101
 102
(2 rows)

(In master: UPDATE foo SET id = id + 10; VACUUM foo; SELECT
pg_xlog_switch())

postgres=# SELECT * FROM foo;
 id

(0 rows)

And it looks like the recovery cache is not reset properly: when I start
a new backend after one that just got a "canceling statement due to
recent buffer changes during recovery" error, and run a query, I get
that error again:

psql (8.5devel)
Type "help" for help.

postgres=# SELECT * FROM foo;
postgres=# begin ISOLATION LEVEL serializable;
BEGIN
postgres=# SELECT * FROM foo;
ERROR:  canceling statement due to recent buffer changes during recovery

I haven't dug deeper into those, but before I do, I want to ask if we
really need to bother with a per-relation conflict cache at all? I'd
really like to keep it simple for now, and tracking the conflicts
per-relation only alleviates the situation somewhat. The nature of the
cache is such that it's quite unpredictable to a regular user when it
will save you, so you can't rely on it. You need to set
max_standby_delay and/or other such settings correctly anyway, so it
doesn't really help with usability.

Another thing:
I'm quite surprised to see that the logic in WAL redo to stop the redo
and wait for read-only queries to finish before applying a WAL record
that would cause conflicts, and thus cause a read-only query to be
killed, is only used with a few WAL record types like database or
tablespace creation. Not the usual VACUUM records. I was under the
impression that max_standby_delay option and logic would apply to all
operations.

-- 
  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] Tightening binary receive functions

[Greg Stark]

On Mon, Aug 31, 2009 at 12:01 PM, Heikki
Linnakangas wrote:
> Hmm, perhaps we should follow what we did to chr() and ascii(): map the
> integer to unicode code points if the database encoding is UTF-8, and
> restrict the range to 0..127 for other multi-byte encodings.

I don't think we even have to worry about the database's encoding.
Just make the textual representation of "char" be \xxx (or perhaps we
could switch to \xHH now) if the value isn't a printable ascii
character. As long as "char" reads that in properly it doesn't matter
if it's not a reasonable multibyte character.

That allows people to treat it as a 1-byte integer type which happens
to allow input or output as a single ascii character which is
convenient sometimes.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] Tightening binary receive functions

[Heikki Linnakangas]

Greg Stark wrote:
> On Mon, Aug 31, 2009 at 9:12 AM, Heikki
> Linnakangas wrote:
>> The most notable of these is the change to "char" datatype. The patch
>> tightens it so that it no longer accepts values >127 with a multi-byte
>> database encoding.
> 
> That doesn't sound right to me. We accept casts from integer to "char"
> for all values in range (-128..127). 

The patch limits that range to 0..127, with multibyte encodings.

> The question should be what the
> text representation should be since the raw bytes aren't valid mb
> encodings.

Hmm, perhaps we should follow what we did to chr() and ascii(): map the
integer to unicode code points if the database encoding is UTF-8, and
restrict the range to 0..127 for other multi-byte encodings.

-- 
  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] Tightening binary receive functions

[Greg Stark]

On Mon, Aug 31, 2009 at 9:12 AM, Heikki
Linnakangas wrote:
> The most notable of these is the change to "char" datatype. The patch
> tightens it so that it no longer accepts values >127 with a multi-byte
> database encoding.

That doesn't sound right to me. We accept casts from integer to "char"
for all values in range (-128..127). The question should be what the
text representation should be since the raw bytes aren't valid mb
encodings.


-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
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] hot standby - further cleanup of recovery procs stuff

[Heikki Linnakangas]

Robert Haas wrote:
> I've made a few further cleanups to the hot standby patch:

Thanks!

> I am not sure why we have a single GUC to size both the number of
> PGPROC structures we allow and the size of UnobservedXids.  A
> read-only slave might only need to allow a few connections for
> reporting purposes, while the master needs to allow many.

Yeah, it is true that the two don't necessarily have much in common. We
could well make it a separate GUC. We'd still need to find a reasonable
default, though.

It should be noted that UnobservedXids array is allocated in shared
memory, but goes completely unused after the recovery, becoming a waste
of memory. It's only a few hundred kB at most, so I think that's
acceptable, but it would be nice to be able to release that memory
somehow. Perhaps it should be backed with a file, which would also have
the benefit that it could grow as needed, eliminating the need for the
GUC. Storing it in a new SLRU might be a good idea.

I started to look at the subtrans.c changes. The patch changes the role
of pg_subtrans substantially. It is no longer simply cleared at startup,
but we require it to contain valid data when we start recovery for
transactions that have overflowed the in-memory cache. That makes me a
bit uncomfortable, although I don't see anything obviously wrong with
it. The comment in CheckpointSUBTRANS() claiming that flushing
pg_subtrans is just a debugging aid is now wrong, however.

I think there's also a bug in ExtendSUBTRANS(): it will zap the first
page it touches in recovery, but right after we start recovery, and
replay the first RunningXacts WAL record, we need to have pg_subtrans
correctly set for the transactions in that RunningXacts record (that
have overflowed the in memory subxid cache). Zapping the pg_subtrans
page can destroy that information.

-- 
  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] [PATCH] Largeobject access controls

[KaiGai Kohei]

The attached patch is the revised version of largeobject access controls.

It reverts pg_largeobject system catalog, and adds new pg_largeobject_meta
system catalog to store the owner identifier and its ACLs.

The definition of pg_largeobject_meta:

  #define LargeObjectMetaRelationId  2336

  CATALOG(pg_largeobject_meta,2336)
  {
  Oid lomowner;   /* OID of the largeobject owner */
  aclitem lomacl[1];  /* access permissions */
  } FormData_pg_largeobject_meta;

The pg_largeobject system catalog is still used to store data chunks of
largeobjects, and its pg_largeobject.loid is associated with OID of the
pg_largeobject_meta system catalog.

* It also supports case handling in DROP ROLE and REASSIGN/DROP OWNED
  using existing dependency mechanism.
* A new "ALTER LARGE OBJECT  OWNER TO " statement was added.
* Permission checks on creation of largeobjects are dropped. It implicitly
  allows everyone to create a new largeobject.
  (CREATE USER LARGEOBJECT/NOLARGEOBJECT is also dropped.)
* The default ACL allows public to read/write new largeobjects as long as
  owner does not revoke permissions. (MEMO: It might be configurable
  using GUC whether the default allows public to read/write, or not.)

[Performance measurement]
We measured the time to execute \lo_import with two large files (the one
is well compressible, the other is not so) and \lo_export them.
In the result, it seems to me there are no significant regression here.

* Environment
  CPU: Pentium4 3.20GHz
  Mem: 512MB
  Kernel: 2.6.30-6.fc12.i586
  PostgreSQL configuration: all parameters are in default.

* Base PostgreSQL
  - Import/Export an uncompressible file
  [kai...@saba ~]$ time -p psql postgres -c '\lo_import 512MB_Rnd'
  lo_import 16386
  real 132.33
  user 1.01
  sys 5.06
  [kai...@saba ~]$ time -p psql postgres -c '\lo_export 16386 /dev/null'
  lo_export
  real 77.57
  user 0.79
  sys 3.76

  - Import/Export well compressible file
  [kai...@saba ~]$ time -p psql postgres -c '\lo_import 512MB_Zero'
  lo_import 16387
  real 45.84
  user 0.91
  sys 5.38
  [kai...@saba ~]$ time -p psql postgres -c '\lo_export 16387 /dev/null'
  lo_export
  real 13.51
  user 0.62
  sys 2.98

* with Largeobject access control patch
  - Import/Export an uncompressible file
  [kai...@saba ~]$ time -p psql postgres -c '\lo_import 512MB_Rnd'
  lo_import 16384
  real 132.49
  user 1.13
  sys 5.10
  [kai...@saba ~]$ time -p psql postgres -c '\lo_export 16384 /dev/null'
  lo_export
  real 76.14
  user 0.81
  sys 3.63

  - Import/Export well compressible file
  [kai...@saba ~]$ time -p psql postgres -c '\lo_import 512MB_Zero'
  lo_import 16385
  real 44.21
  user 0.91
  sys 5.51
  [kai...@saba ~]$ time -p psql postgres -c '\lo_export 16385 /dev/null'
  lo_export
  real 14.27
  user 0.66
  sys 3.11

Thanks,

[kai...@saba blob]$ diffstat sepgsql-02-blob-8.5devel-r2272.patch.gz
 doc/src/sgml/ref/allfiles.sgml |1
 doc/src/sgml/ref/alter_large_object.sgml   |   75 
 doc/src/sgml/ref/grant.sgml|8
 doc/src/sgml/ref/revoke.sgml   |6
 doc/src/sgml/reference.sgml|1
 src/backend/catalog/Makefile   |6
 src/backend/catalog/aclchk.c   |  247 ++
 src/backend/catalog/dependency.c   |   14 +
 src/backend/catalog/pg_largeobject.c   |  270 +!!!
 src/backend/catalog/pg_shdepend.c  |8
 src/backend/commands/alter.c   |5
 src/backend/commands/comment.c |   14 !
 src/backend/commands/tablecmds.c   |1
 src/backend/libpq/be-fsstubs.c |   49 ++--
 src/backend/parser/gram.y  |   20 ++
 src/backend/storage/large_object/inv_api.c |  115 +++-
 src/backend/tcop/utility.c |3
 src/backend/utils/adt/acl.c|5
 src/backend/utils/cache/syscache.c |   13 +
 src/include/catalog/dependency.h   |1
 src/include/catalog/indexing.h |3
 src/include/catalog/pg_largeobject_meta.h  |   66 +++
 src/include/nodes/parsenodes.h |1
 src/include/utils/acl.h|6
 src/include/utils/syscache.h   |1
 src/test/regress/expected/privileges.out   |  162 +
 src/test/regress/expected/sanity_check.out |3
 src/test/regress/sql/privileges.sql|   65 ++
 28 files changed, 859 insertions(+), 73 deletions(-), 237 modifications(!)

-- 
OSS Platform Development Division, NEC
KaiGai Kohei 


sepgsql-02-blob-8.5devel-r2272.patch.gz
Description: application/gzip

-- 
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] LWLock Queue Jumping

[Stefan Kaltenbrunner]

Jeff Janes wrote:
On Sun, Aug 30, 2009 at 11:01 AM, Stefan Kaltenbrunner 
 wrote:


Jeff Janes wrote:

   -- Forwarded message --
   From: Stefan Kaltenbrunner 
   To: Heikki Linnakangas mailto:heikki.linnakan...@enterprisedb.com>
   >>
   Date: Sun, 30 Aug 2009 11:48:47 +0200
   Subject: Re: LWLock Queue Jumping
   Heikki Linnakangas wrote:


   I don't have any pointers right now, but WALInsertLock does
   often show
   up as a bottleneck in write-intensive benchmarks.


   yeah I recently ran accross that issue with testing
concurrent COPY
   performance:

 
 http://www.kaltenbrunner.cc/blog/index.php?/archives/27-Benchmarking-8.4-Chapter-2bulk-loading.html

   discussed here:

   http://archives.postgresql.org/pgsql-hackers/2009-06/msg01019.php


It looks like this is the bulk loading of data into unindexed
tables.  How good is that as a target for optimization?  I can
see several (quite difficult to code and maintain) ways to make
bulk loading into unindexed tables faster, but they would not
speed up the more general cases.


well bulk loading into unindexed tables is quite a common workload -
apart from dump/restore cycles (which we can now do in parallel) a
lot of analytic workloads are that way.
Import tons of data from various sources every night/weeek/month,
index, analyze & aggregate, drop again.


In those cases where you end by dropping the tables, we should be 
willing to bypass WAL altogether, right?  Is the problem we can bypass 
WAL (by doing the COPY in the same transaction that created or truncated 
the table), or we can COPY in parallel, but we can't do both simultaneously?


well yes that is part of the problem - if you bulk load into one or few 
tables concurrently you can only sometimes make use of the WAL bypass 
optimization. This is especially interesting if you consider that COPY 
alone is more or less CPU bottlenecked these days so using multiple 
cores makes sense to get higher load rates.



Stefan

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

[HACKERS] set_client_encoding is broken

[Zdenek Kotala]

If you look on gothic_moth and comet_moth

http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=gothic_moth&dt=2009-08-30%2020:06:00
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=comet_moth&dt=2009-08-29%2021:06:00

you can see following error:

../../src/test/regress/pg_regress --inputdir=.
--psqldir=/zfs_data/home/postgres/buildfarm/gothic_moth/HEAD/inst/bin
--dbname=contrib_regression --multibyte=UTF8 --no-locale  unaccent
(using postmaster on Unix socket, default port)
== dropping database "contrib_regression" ==
psql: FATAL:  invalid value for parameter "client_encoding": "UTF8"
command failed: 
"/zfs_data/home/postgres/buildfarm/gothic_moth/HEAD/inst/bin/psql" -X -c "DROP 
DATABASE IF EXISTS \"contrib_regression\"" "postgres"
gmake[1]: *** [installcheck] Error 2
gmake[1]: Leaving directory 
`/zfs_data/home/postgres/buildfarm/gothic_moth/HEAD/pgsql.4092/contrib/unaccent'


[4a9ae815.696e:1] LOG:  connection received: host=[local]
[4a9ae815.696e:2] LOG:  connection authorized: user=postgres database=postgres
[4a9ae815.696e:3] LOG:  conversion between UTF8 and LATIN2 is not supported
[4a9ae815.696e:4] FATAL:  invalid value for parameter "client_encoding": "UTF8"

The assign_client_encoding->SetClientEncoding fails to find conversion function.
http://doxygen.postgresql.org/backend_2commands_2variable_8c.html#7f2d0624e7b7fb46644c5ce284e6479c
http://doxygen.postgresql.org/mbutils_8c.html#8eeff4ecab443ba7073c426fcd4bc4d6

I guess that flat auth file patch
http://archives.postgresql.org/pgsql-committers/2009-08/msg00301.php
is culprint.

It seems that backend does not have loaded pg_encoding yet when
SetClientEncoding is processed.

Zdenek



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

[HACKERS] Tightening binary receive functions

[Heikki Linnakangas]

After the thread started by Andrew McNamara a while ago:

http://archives.postgresql.org/message-id/e419f08d-b908-446d-9b1e-f3520163c...@object-craft.com.au

the question was left hanging if other binary recv functions are missing
sanity checks that the corresponding text input functions have, and
whether they might pose a security issue. Tom Lane went through all the
recv functions after that, and found a few missing checks but nothing
that would present a security issue, but posted them to the
pgsql-security list for a double check. I just went through them again,
and found no security issues either.

We should nevertheless fix the recv functions so that they don't accept
any values that the corresponding text functions reject. While the
server itself handles them, such values will throw an error on
pg_dump/restore, for example. Attached patch adds the required sanity
checks. I'm thinking of applying this to CVS HEAD, but not back-patch,
just like Tom did with the original problem reported with time_recv()
and timetz_recv().

The most notable of these is the change to "char" datatype. The patch
tightens it so that it no longer accepts values >127 with a multi-byte
database encoding. Also, the handling of encoding in xml_recv() was bogus.

Here's the list of issues found:

Tom Lane wrote:
> array_recv:
>
> Allows zero-length dimensions (because ArrayGetNItems doesn't complain);
> whereas array_in does not.  Not sure if this is an issue.  We have had
> -hackers discussions about the behavior of zero-length arrays, so I
> think there may be other ways to get them into the system anyway.
>
> Doesn't check lower bounds at all, so it's possible that lowerbound plus
> length overflows an int.  Not sure if this is a security problem either,
> but it seems like something that should be rejected.

Yes, that seems dangerous.

I note that the handling of large bounds isn't very rigid in the text
input function either:

postgres=# SELECT '[:9] = {1}'::integer[];
  int4
-
 [2147483647:2147483647]={1}
(1 row)

We use plain atoi() to convert the subscripts to integers, which returns
INT_MAX for an overflow. I didn't do anything about that now, but the
patch adds a check for the upper bound overflow in array_recv().

> charrecv
>
> Doesn't bother its pretty little head with maintaining encoding validity.
> Of course the entire datatype doesn't, so this is hardly the fault of
> the recv routine in particular.  It might be that the type is fine but we
> ought to constrain char_text() to fail on high-bit-set char values unless
> DB encoding is single-byte.

Constraining char_text() seems like a good idea. The current behavior of
char_text() with a high-bit-set byte is not useful, while using the full
range of char can be. However, we have to constrain charout() as well,
or we're back to square one with charout(c)::text. And if we constrain
charout(), then we should constrain charin() as well. Which brings us
back to forbidding such values altogether.

The patch constrains all the functions that you can use to get a "char"
into the system: charin(), charrecv(), i4tochar() and text_char(). They
now reject values with high bit set when using a multi-byte database
encoding

> date_recv
>
> Fails to do any range check, so the value might cause odd behavior later.
> Should probably limit to the values date_in would accept.

Yep.

> float4recv, float8recv, and geometric and other types depending on
> pq_getmsgfloatN
>
> These all accept any bit pattern whatever for a float.  Now I know of no
> machine where float doesn't consider all bitpatterns "valid", but
> nonetheless there are some issues here:
>
> * We don't currently allow any other way to inject an IEEE signaling NaN
> into the database.  As far as I can think, this could only lead to float
> traps in places where one might perhaps not have expected one, so I
> don't think this is a real problem.

Agreed. This is frankly the first time I even hear about signaling NaNs,
and after reading up on that a bit, I get the impression that even on
platforms that support them, you need a #define or a compiler flag to
enable them.

> * Some of these types probably aren't expecting NaNs or Infinities at all.
> Can anything really bad happen if they get one?

Geometric types seem to handle NaNs and Infs gracefully, although I
wonder what it means to have e.g a box with the X-coordinate of one
corner being NaN. I think it's ok from a security point of view.

timestamp_recv (with float timestamps) accepts NaN, while timestamp_in
does not. I'm not sure what happens if you pass a NaN timestamp to the
system, but we should forbid that anyway.

> interval_recv
>
> Fails to do any range check, but I think it's okay since we don't have any
> a-priori restrictions on the range of intervals.

Should disallow Infs and NaNs.

> numeric_recv
>
> Allows any weight or dscale value.  Can this cause problems?

It seems OK to me. make_result