Re: [HACKERS] SE-PostgreSQL and row level security
Pavel Stehule wrote: 2009/2/17 Josh Berkus j...@agliodbs.com: All, I thought we'd agreed to compromise on having SE without row-level in 8.4, and working on SE with row-level in 8.5. Why are we revisiting this argument? 8.4 is *already* late; arguing further about the terms of SE simply risk us being forced to reject it entirely. I absolutely agree. It nonsense open again and again closed question. I also agree. What we should do now is to make progress the proposed feature for v8.4, not a upcoming feature. BogDan, As I noted before, I can understand your requirement, but we already decided to postpone a part of features within originally proposed, because we're paying effort to develop v8.4 within reasonable schedule. I'd like you to guess who wanted the row-level stuff to be merged most. I understand postgresql 8.4 should be shipped within reasonable schedule, but you see, row-level security will help more people then integration with se-linux. Please don't understand me wrong, I appreciate what you do, but for me se-linux is a little science fiction (and I ensure you, they are many like me). I see a lot of new useful features on postgresql 8.4 and row-level should be the cherry on the cake. I hope row level security will be accepted soon on 8.5 and you'll continue to provide a patch for 8.4.x too. A patch against 8.4 will help many of us to start working with this feature on a stable server until 8.5 will be out. Thanks for your hard work, BogDan, -- 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] vacuumdb --freeze
I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. Exactly what do you think the upgrade utility is going to do with it? Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade is going to be fast. As far as I can see this is a solution looking for a problem. I didn't go into the use-case. The way pg_migrator works is to copy the _schema_ from the old database and load it into the new database. We then need to run vacuum freeze on the schema-only databases because we then move pg_clog from the old database to the new one; so, it is needed, and it will not take long to run. My first impulse was the same as Tom's, thanks for the explanation. To the filled database case: Would it make sense to enhance --table to allow wildcards and remove the cannot vacuum a specific table in all databases check ? One more question I have though is: How do you make sure noone (e.g. autovacuum analyze) unfreezes tuples after the vacuum freeze ? Andreas -- 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] vacuumdb --freeze
On Tue, 2009-02-17 at 18:52 -0500, Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. Exactly what do you think the upgrade utility is going to do with it? Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade is going to be fast. As far as I can see this is a solution looking for a problem. I didn't go into the use-case. The way pg_migrator works is to copy the _schema_ from the old database and load it into the new database. We then need to run vacuum freeze on the schema-only databases because we then move pg_clog from the old database to the new one; so, it is needed, and it will not take long to run. So you don't actually want to VACUUM the whole database anyway, just the system tables? I'd like to see VACUUM SYSTEM, just like we have REINDEX SYSTEM. That way you can then do a --freeze --system on vacuumdb, which is all you want to do anyway. If the code is there for REINDEX SYSTEM it should be pretty easy to move it across to VACUUM. I've had times when I just wanted to VACUUM the catalog tables, so to go through them all one by one is tedious and missing one isn't possible with a special command. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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: [Pljava-dev] [HACKERS] Re: Should creating a new base type require superuser status?
Kris Jurka wrote:
Thomas Hallgren wrote:
Kris Jurka wrote:
3) By value: pljava does not correctly handle passed by value types
correctly, allowing access to random memory.
This is simply not true. There's no way a Java developer can access
random memory through PL/Java.
No, the point is that the Java developer can provide some data which
can convince postgresql to fetch random data for the user.
Consider the attached type which is simply an int4 equivalent.
Depending on how you define it as passed by value or passed by
reference it will or will not work (attached).
This looks like it works:
jurka=# select '1'::intbyref, '2'::intbyval;
intbyref | intbyval
--+--
1| 2
(1 row)
But it doesn't really:
jurka=# create table inttest (a intbyref, b intbyval);
CREATE TABLE
jurka=# insert into inttest values ('1', '2');
INSERT 0 1
jurka=# select * from inttest;
a | b
---+
1 | 2139062143
(1 row)
It seems the pointer is confused for the actual value which means that
writing the value back will corrupt the pointer. That's bad of course
but I would classify this as a bug rather then a general security problem.
PL/Java is designed to do handle all types securely and completely hide
the concept of 'by value' or 'by reference' from the Java developer
since such concepts are meaningless in Java.
- thomas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Redefine _() to dgettext() instead of gettext() so that it uses
Alvaro Herrera wrote: Peter Eisentraut wrote: Log Message: --- Redefine _() to dgettext() instead of gettext() so that it uses the plpgsql text domain, instead of the postgres one (or whatever the default may be). Hmm, so is this needed on all other PLs too? In principle yes. Or call dgettext() explicitly, which is also done in some cases. However, in most cases messages are issued through ereport(), which handles this automatically (which you implemented, I recall). -- 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] Restore frozen xids for binary upgrades
Bruce Momjian wrote: The attached patch adds to pg_dumpall --binary-upgrade by restoring information about frozen xids for relations and databases. I think this is the last patch I need to complete my TODO items for the pg_migrator binary upgrade utility. Applied. -- Bruce Momjian br...@momjian.ushttp://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] vacuumdb --freeze
Bruce Momjian wrote: I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. I could also accomplish with with PGOPTIONs but this seem like a cleaner solution. Applied. -- Bruce Momjian br...@momjian.ushttp://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] vacuumdb --freeze
Zeugswetter Andreas OSB sIT wrote: I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. Exactly what do you think the upgrade utility is going to do with it? Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade is going to be fast. As far as I can see this is a solution looking for a problem. I didn't go into the use-case. The way pg_migrator works is to copy the _schema_ from the old database and load it into the new database. We then need to run vacuum freeze on the schema-only databases because we then move pg_clog from the old database to the new one; so, it is needed, and it will not take long to run. My first impulse was the same as Tom's, thanks for the explanation. To the filled database case: Would it make sense to enhance --table to allow wildcards and remove the cannot vacuum a specific table in all databases check ? One more question I have though is: How do you make sure noone (e.g. autovacuum analyze) unfreezes tuples after the vacuum freeze ? I will start a new thread to answer this question, but the short answer is that the freeze only needs to happen in a fresh initdb database, and once clog is copied over, new transactions can be created normally. -- Bruce Momjian br...@momjian.ushttp://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] Hot standby, recovery infra
On Mon, 2009-02-09 at 17:13 +0200, Heikki Linnakangas wrote: Attached is an updated patch that does that, and I've fixed all the other outstanding issues I listed earlier as well. Now I'm feeling again that this is in pretty good shape. UpdateMinRecoveryPoint() issues a DEBUG2 message even when we have not updated the control file, leading to log filling behaviour on an idle system. DEBUG: updated min recovery point to ... We should just tuck the message into the if section above it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Hot standby, recovery infra
Simon Riggs wrote: On Mon, 2009-02-09 at 17:13 +0200, Heikki Linnakangas wrote: Attached is an updated patch that does that, and I've fixed all the other outstanding issues I listed earlier as well. Now I'm feeling again that this is in pretty good shape. UpdateMinRecoveryPoint() issues a DEBUG2 message even when we have not updated the control file, leading to log filling behaviour on an idle system. DEBUG: updated min recovery point to ... We should just tuck the message into the if section above it. The outer if should ensure that it isn't printed repeatedly on an idle system. But I agree it belongs inside the inner if section. -- 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] Hot standby, recovery infra
On Wed, 2009-02-18 at 14:26 +0200, Heikki Linnakangas wrote: The outer if should ensure that it isn't printed repeatedly on an idle system. Regrettably not. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] The science of optimization in practical terms?
On Wed, Feb 18, 2009 at 1:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'm interested to know whether anyone else shares my belief that nested loops are the cause of most really bad plans. What usually happens to me is that the planner develops some unwarranted optimism about the number of rows likely to be generated by the outer side of the join and decides that it's not worth sorting the inner side or building a hash table or using an index, and that the right thing to do is just rescan the inner node on every pass. When the outer side returns three or four orders of magnitude more results than expected, ka-pow! And then there is the other half of the world, who complain because it *didn't* pick a nestloop for some query that would have run in much less time if it had. Well, that's my question: is that really the other half of the world, or is it the other 5% of the world? And how does it happen? In my experience, most bad plans are caused by bad selectivity estimates, and the #1 source of bad selectivity estimates is selectivity estimates for unknown expressions. (Now it appears that Josh is having problems that are caused by overestimating the cost of a page fetch, perhaps due to caching effects. Those are discussed upthread, and I'm still interested to see whether we can arrive at any sort of consensus about what might be a reasonable approach to attacking that problem. My own experience has been that this problem is not quite as bad, because it can throw the cost off by a factor of 5, but not by a factor of 800,000, as in my example of three unknown expressions with a combined selectivity of 0.1.) ...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] pg_migrator progress
I have completed all the outstanding pg_migratory TODO items.
I still have more work to do in cleanup and testing, but if people want
to look at my progress, now is a good time.
You can download the current CVS here:
http://pgfoundry.org/scm/?group_id=1000235
and you can subscribe to the general email list here:
http://pgfoundry.org/mail/?group_id=1000235
I am attaching main() so you can get an idea of how pg_migrator works.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
int
main(int argc, char **argv)
{
migratorContext ctx = {0};
int ntablespaces = 0;
int i = 0;
char **tablespaces = NULL; /* array to store table space
paths of
* old data */
struct timezone tz;
parseCommandLine(ctx, argc, argv);
setup(ctx, argv[0]);
/* -- OLD -- */
start_postmaster(ctx, Starting postmaster to service old cluster,
CLUSTER_OLD);
/* Add any required support functions to the old cluster */
create_support_functions(ctx, Adding support functions to old
cluster,
ctx.m_oldlibpath,
ctx.m_oldbindir, CLUSTER_OLD);
/* Get the pg_database and pg_largeobject relation OID's */
get_db_LO_relfilenodes(ctx, CLUSTER_OLD);
/* Extract a list of databases and tables from the old cluster */
gen_db_info(ctx, ctx.m_olddbarr, CLUSTER_OLD);
dump_old_schema(ctx, DUMP_FILE);
tablespaces = get_tablespace_paths(ctx, ntablespaces, CLUSTER_OLD);
stop_postmaster(ctx, CLUSTER_OLD);
/* Rename all tablespace paths */
rename_tablespaces(ctx, tablespaces, ntablespaces);
/* -- NEW -- */
start_postmaster(ctx, Starting postmaster to service new cluster,
CLUSTER_NEW);
/* XXX check that new database is empty */
/*
* It would make more sense to freeze after loading the schema, but
* that would cause us to lose the frozenids restored by the load.
*/
prepStatus(ctx, Freezing all rows on the new server);
exec_prog(ctx, true, %s/vacuumdb --port %d --all --frozen --full
%s 21,
ctx.m_newbindir, ctx.m_newport, ctx.m_logfile);
check_ok(ctx);
stop_postmaster(ctx, CLUSTER_NEW);
ask_continue(ctx);
copy_clog_xlog_xid(ctx);
/* New now using xid of old system */
/* -- NEW -- */
start_postmaster(ctx, Starting postmaster to service new cluster,
CLUSTER_NEW);
create_support_functions(ctx, Adding support functions to new
cluster,
ctx.m_newlibpath,
ctx.m_newbindir, CLUSTER_NEW);
get_db_LO_relfilenodes(ctx, CLUSTER_NEW);
/*
* Although the schema load will create all the databases, we need to
perform
* this step first in order to create toast table placeholder relfiles.
*/
create_databases(ctx, ctx.m_olddbarr, ctx.m_newbindir);
prepStatus(ctx, Creating placeholder relfiles for toast relations);
create_placeholder_relfiles(ctx, ctx.m_olddbarr, ctx.m_newpgdata);
check_ok(ctx);
prepStatus(ctx, Restoring database schema);
exec_prog(ctx, false, %s/%s --port %d --dbname template1 %s/%s
%s 21,
ctx.m_newbindir, ctx.m_newpsql_command, ctx.m_newport,
ctx.m_homedir, DUMP_FILE, ctx.m_logfile);
check_ok(ctx);
process_relfiles(ctx);
stop_postmaster(ctx, CLUSTER_NEW);
#ifdef NOT_USED
/* XXX do we need this at all? */
/*
* Assuming OIDs are only used in system tables, there is no need
to
* restore the OID counter because we have not transfered any OIDs
* from the old system.
*/
prepStatus(ctx, Setting next oid for new cluster);
exec_prog(ctx, true, %s/pg_resetxlog -o %u %s 1/dev/null,
ctx.m_newbindir, ctx.m_oldctrldata.chkpnt_nxtoid,
ctx.m_newpgdata);
check_ok(ctx);
#endif
cleanup(ctx);
if (gettimeofday(ctx.m_endtime, tz) == -1)
pg_log(ctx, PG_FATAL, Unable to get time);
pg_log(ctx, PG_REPORT, \nThe data migration completed in %d
seconds\n,
ctx.m_endtime.tv_sec - ctx.m_starttime.tv_sec);
return 0;
}
--
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] pg_migrator progress
You've moved fast on this! #ifdef NOT_USED /* XXX do we need this at all? */ /* * Assuming OIDs are only used in system tables, there is no need to * restore the OID counter because we have not transfered any OIDs * from the old system. */ prepStatus(ctx, Setting next oid for new cluster); exec_prog(ctx, true, %s/pg_resetxlog -o %u %s 1/dev/null, ctx.m_newbindir, ctx.m_oldctrldata.chkpnt_nxtoid, ctx.m_newpgdata); check_ok(ctx); #endif It's certainly not impossible for someone to be using OIDs on user tables, is it? I mean, I'm not, but... ...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] WIP: hooking parser
Pavel Stehule wrote: 2009/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. regards, tom lane it's maybe too much simple :). It is sample that have to show possibility. I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. -- 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] pg_migrator progress
Robert Haas robertmh...@gmail.com writes: /* XXX do we need this at all? */ /* * Assuming OIDs are only used in system tables, there is no need to * restore the OID counter because we have not transfered any OIDs * from the old system. */ It's certainly not impossible for someone to be using OIDs on user tables, is it? No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb 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] The science of optimization in practical terms?
On Wed, Feb 18, 2009 at 01:34:25AM -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I'm interested to know whether anyone else shares my belief that nested loops are the cause of most really bad plans. What usually happens to me is that the planner develops some unwarranted optimism about the number of rows likely to be generated by the outer side of the join and decides that it's not worth sorting the inner side or building a hash table or using an index, and that the right thing to do is just rescan the inner node on every pass. When the outer side returns three or four orders of magnitude more results than expected, ka-pow! And then there is the other half of the world, who complain because it *didn't* pick a nestloop for some query that would have run in much less time if it had. There's something called interval arithmetic I heard about recently that may help here. The basic idea is to store two values, representing the upper and lower bounds of a number, instead of its absolute value. That way you know that the number is going to be somewhere in the middle and round off becomes a non-less because you know when it's happened (e.g. the FPU is set up to always round the lower bound down and the upper bound up). Round-off isn't a problem here, but it's one of the algebra's nice properties. If the planning was done with some sort of interval then you'd be able to encode information about how well your stats characterized the underlying data. Traditionally awkward things like amount of cache would serve to drop the lower bound, but not alter the upper. The planner then automatically propagate performance information through the calculations, i.e. a nested loop with a tight estimate on a small number of rows joined to a table with a wider estimate of a small number of rows would keep the low lower bound but the upper-bound would tend to make the planner stay away. That said, I can't decide if that would help in any meaningful way! A good counter argument seems to be why not just always go with the upper bound? There are extensions to vanilla interval arithmetic that allow distributions to be modeled instead of just an unknown interval. You'd then be able to use some sort of 95% confidence interval instead of a horribly pessimistic worst case. -- Sam http://samason.me.uk/ -- 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] pg_migrator progress
Bruce Momjian wrote: I have completed all the outstanding pg_migratory TODO items. I still have more work to do in cleanup and testing, but if people want to look at my progress, now is a good time. Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're going to mess with pg_clog? -- 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] PQinitSSL broken in some use casesf
Andrew Chernow wrote:
Maybe better, have it return a zero/nonzero error code; where one of the
possibilities for failure is you passed a bit I didn't understand.
Why not just return those bit(s) instead of an arbitrary code? How about:
-1 = error (if it ever does anything that can fail)
0 = success (all bits known)
0 = unknown bits (remaining known bits *have* been set)
I attached a patch that implements the above, using PQinitSecure as the
function name.
--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/
Index: doc/src/sgml/libpq.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.278
diff -C6 -r1.278 libpq.sgml
*** doc/src/sgml/libpq.sgml 11 Feb 2009 04:08:47 - 1.278
--- doc/src/sgml/libpq.sgml 18 Feb 2009 15:22:04 -
***
*** 6174,6185
--- 6174,6215
applicationlibpq/application that the acronymSSL/ library
has already been initialized by your application.
!-- If this URL changes replace it with a URL to www.archive.org. --
See ulink
url=http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html;/ulink
for details on the SSL API.
+
+variablelist
+ varlistentry
+ term
+ functionPQinitSecure/function
+ indexterm
+primaryPQinitSecure/primary
+ /indexterm
+ /term
+
+ listitem
+ para
+Allows applications to select which secure components to initialize.
+synopsis
+ int PQinitSecure(int flags);
+/synopsis
+ /para
+
+ para
+The flags argument can be any of the following: PG_SECURE_SSL,
+PG_SECURE_CRYPTO. PG_SECURE_SSL will initialize the SSL portion of
+the OpenSSL library. PG_SECURE_CRYPTO will initialize the crypto
+portion of the OpenSSL library. The function returns the bits it
+did not understand or zero indicating it understood all bits in flags.
+If an error occurs, such as calling this function without SSL
+support enabled, -1 is returned.
+ /para
+ /listitem
+ /varlistentry
+/variablelist
/para
table id=libpq-ssl-file-usage
titleLibpq/Client SSL File Usage/title
tgroup cols=3
thead
Index: src/interfaces/libpq/exports.txt
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/exports.txt,v
retrieving revision 1.22
diff -C6 -r1.22 exports.txt
*** src/interfaces/libpq/exports.txt22 Sep 2008 13:55:14 - 1.22
--- src/interfaces/libpq/exports.txt18 Feb 2009 15:22:04 -
***
*** 149,154
--- 149,155
PQinstanceData147
PQsetInstanceData 148
PQresultInstanceData 149
PQresultSetInstanceData 150
PQfireResultCreateEvents 151
PQconninfoParse 152
+ PQinitSecure 153
\ No newline at end of file
Index: src/interfaces/libpq/fe-secure.c
===
RCS file: /projects/cvsroot/pgsql/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.119
diff -C6 -r1.119 fe-secure.c
*** src/interfaces/libpq/fe-secure.c28 Jan 2009 15:06:47 - 1.119
--- src/interfaces/libpq/fe-secure.c18 Feb 2009 15:22:04 -
***
*** 96,107
--- 96,108
static PostgresPollingStatusType open_client_SSL(PGconn *);
static void close_SSL(PGconn *);
static char *SSLerrmessage(void);
static void SSLerrfree(char *buf);
static bool pq_initssllib = true;
+ static bool pq_initcryptolib = true;
static SSL_CTX *SSL_context = NULL;
#ifdef ENABLE_THREAD_SAFETY
static int ssl_open_connections = 0;
#ifndef WIN32
***
*** 175,186
--- 176,205
#ifdef USE_SSL
pq_initssllib = do_init;
#endif
}
/*
+ *Exported function to allow application to tell us which secure
+ * components to initialize.
+ */
+ int
+ PQinitSecure(int flags)
+ {
+ int code = -1;
+
+ #ifdef USE_SSL
+ pq_initssllib = flags PG_SECURE_SSL ? true : false;
+ pq_initcryptolib = flags PG_SECURE_CRYPTO ? true : false;;
+ code = flags ~(PG_SECURE_SSL | PG_SECURE_CRYPTO);
+ #endif
+
+ return code;
+ }
+
+ /*
*Initialize global context
*/
int
pqsecure_initialize(PGconn *conn)
{
int r = 0;
***
*** 820,831
--- 839,852
* message - no connection local setup is made.
*/
static int
init_ssl_system(PGconn *conn)
{
#ifdef ENABLE_THREAD_SAFETY
+ int num_ssl_conns = 0;
+
#ifdef WIN32
/* Also see similar code in fe-connect.c, default_threadlock() */
if (ssl_config_mutex == NULL)
{
while (InterlockedExchange(win32_ssl_create_mutex, 1) == 1)
/* loop, another thread own the lock */ ;
***
***
Re: [HACKERS] WIP: hooking parser
Peter Eisentraut pete...@gmx.net writes: I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. There's some interesting comments here: http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null 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] SE-PostgreSQL and row level security
Greg Stark wrote: On Mon, Feb 16, 2009 at 4:14 PM, Robert Haas robertmh...@gmail.com wrote: I'm not sure I understand what you mean by that. I expect that if I deny a particular user access to SELECT from a particular table the system will throw a permissions error if that user later enters SELECT * FROM table-name. I don't expect that the system will foresee every possible alternative way that a user might able to infer something about the contents of that table and block it. I similarly expect that if I install SE-PostgreSQL and configure it to filter out certain rows from accesses to certain tables, those rows will in fact be filtered. I still don't expect it to foresee every possible alternative way that a user might be able to infer something about the contents of the data to which the user does not have direct access. Is this fundamentally a semantic issue? If there's an asymmetry here in what is being claimed, I'm not seeing it. Well the asymmetry is that in the former case the verb is deny and the latter it's filter... I had talked to some knowledgeable people involved in designing operating system security systems about this. A major design principle for adding advanced security features was to minimize the impact on existing interfaces. This makes a lot of sense here. The problem is that in SQL you can uniquely address columns, but not rows. So to avoid getting permission denied errors in the face of column-level privileges, you simply omit the off-limits columns from the select list and restriction clauses. You mostly do this anyway, selecting only the columns that you are legitimately interested in. So the interface impact is low. With row-level privileges, you can't do that. Using the deny approach, you'd immediately get permission denied errors for almost all queries in your application as soon as you start setting row-level restrictions. The fix would be to change all queries to add a restriction clause to get only the rows you have access to. So the interface impact would be pretty high. With a filter approach, the impact is much lower. As you try to SELinux or SEWhatever-equip a complete operating system, including kernel, file system, networking, X windows, etc., you have to apply this deny vs filter tradeoff over and over again, to avoid upsetting existing interfaces. So, according to my state of knowledge, the purpose of row-level security is actually *not* primarily to hide the existence of information, but merely to hide the information itself under a standard access-control mechanism while minimizing the impact on existing interfaces. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SIMILAR TO bug?
Folks, Perhaps I've misunderstood this, but in PostgreSQL 8.3.5, I get disparate results from ~ and SIMILAR TO. For example: This gives an unexpected result: davidfet...@davidfetter=# SELECT 'abc' SIMILAR TO '^[a]'; ?column? -- f (1 row) This one is what I expected. davidfet...@davidfetter=# SELECT 'abc' ~ '^[a]'; ?column? -- t (1 row) Did I misunderstand what SIMILAR TO does, or...? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] pg_migrator progress
Tom Lane t...@sss.pgh.pa.us writes: No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb anyway. Well it was a time bomb but it wasn't necessarily about to go off... He may very well know how close or far he is from oid wraparound and have contingency plans in place. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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] SIMILAR TO bug?
David Fetter wrote: Folks, Perhaps I've misunderstood this, but in PostgreSQL 8.3.5, I get disparate results from ~ and SIMILAR TO. For example: Did you read the docs? ^ is not an anchor. -- 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] SIMILAR TO bug?
David Fetter wrote: Folks, Perhaps I've misunderstood this, but in PostgreSQL 8.3.5, I get disparate results from ~ and SIMILAR TO. For example: This gives an unexpected result: davidfet...@davidfetter=# SELECT 'abc' SIMILAR TO '^[a]'; ?column? -- f (1 row) This one is what I expected. davidfet...@davidfetter=# SELECT 'abc' ~ '^[a]'; ?column? -- t (1 row) Did I misunderstand what SIMILAR TO does, or...? Well, SIMILAR TO is not the same thing as ~. For example, ^ is not a special character in its pattern language. -- 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] pg_migrator progress
Gregory Stark st...@enterprisedb.com writes: Tom Lane t...@sss.pgh.pa.us writes: No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb anyway. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. I think that argument is a red herring. In the first place, it's unlikely that there'd be a huge run of consecutive OIDs *in the same table*. In the second place, if he does have such runs, the claim that he can't possibly have dealt with OID wraparound before seems pretty untenable --- he's obviously been eating lots of OIDs. But having said that, there isn't any real harm in fixing the OID counter to match what it was. You need to run pg_resetxlog to set the WAL position and XID counter anyway, and it can set the OID counter too. 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] The science of optimization in practical terms?
If the planning was done with some sort of interval then you'd be able to encode information about how well your stats characterized the underlying data. Traditionally awkward things like amount of cache would serve to drop the lower bound, but not alter the upper. The planner then automatically propagate performance information through the calculations, i.e. a nested loop with a tight estimate on a small number of rows joined to a table with a wider estimate of a small number of rows would keep the low lower bound but the upper-bound would tend to make the planner stay away. Yeah, I thought about this too, but it seems like overkill for the problem at hand, and as you say it's not clear you'd get any benefit out of the upper bound anyway. I was thinking of something simpler: instead of directly multiplying 0.005 into the selectivity every time you find something incomprehensible, keep a count of the number of incomprehensible things you saw and at the end multiply by 0.005/N. That way more unknown quals look more restrictive than fewer, but things only get linearly wacky instead of exponentially wacky. ...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] Hot standby, recovery infra
Simon Riggs wrote: On Wed, 2009-02-18 at 14:26 +0200, Heikki Linnakangas wrote: The outer if should ensure that it isn't printed repeatedly on an idle system. Regrettably not. Ok, committed. I fixed that and some comment changes. I also renamed IsRecoveryProcessingMode() to RecoveryInProgress(), to avoid confusion with the real processing modes defined in miscadmin.h. That will probably cause you merge conflicts in the hot standby patch, but it should be a matter of search-replace to fix. The changes need to be documented. At least the removal of log_restartpoints is a clear user-visible change. -- 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] pg_migrator progress
Tom Lane t...@sss.pgh.pa.us writes: Gregory Stark st...@enterprisedb.com writes: Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. I think that argument is a red herring. In the first place, it's unlikely that there'd be a huge run of consecutive OIDs *in the same table*. Really? Wouldn't all it take be a single large COPY loading data into a table with one or more columns receiving large data which need to be toasted? In the second place, if he does have such runs, the claim that he can't possibly have dealt with OID wraparound before seems pretty untenable --- he's obviously been eating lots of OIDs. Well there's a pretty wide margin between millions and 4 billion. I suppose you could say it would only be a one-time cost (or a few separate one-time costs until the oid counter passed the old value). So a few minutes after doing an in-place upgrade while the oid counter skimmed past all the existing values would be bearable. But having said that, there isn't any real harm in fixing the OID counter to match what it was. You need to run pg_resetxlog to set the WAL position and XID counter anyway, and it can set the OID counter too. Yeah, if it was massive amounts of code I could see arguing that it's not justified, but given that it's about the same degree of complexity either way it seems clear to me that it's better to do it than not to do it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS 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] Hot standby, recovery infra
On Wed, 2009-02-18 at 18:01 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Wed, 2009-02-18 at 14:26 +0200, Heikki Linnakangas wrote: The outer if should ensure that it isn't printed repeatedly on an idle system. Regrettably not. Ok, committed. Cool. I fixed that and some comment changes. I also renamed IsRecoveryProcessingMode() to RecoveryInProgress(), to avoid confusion with the real processing modes defined in miscadmin.h. That will probably cause you merge conflicts in the hot standby patch, but it should be a matter of search-replace to fix. Yep, good change, agree with reasons. The changes need to be documented. At least the removal of log_restartpoints is a clear user-visible change. Yep. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] graph representation of data structures in optimizer
Adriano Lange adri...@c3sl.ufpr.br writes: Hi, I'm interested in data representation and debug of optimizer routines. Thus, I've changed the debug functions of allpaths.c to make a graphviz-like output of RelOptInfo structure. Any idea about this? Is there some project or improvement like this? Several people have asked about ways to see what possible plans were considered and why the were rejected, it was one of the repeat offenders in the recent Postgres Pet Peeves thread so this is a very interesting area to explore. However I have to say this graph you've generated is amazingly hard to decipher :) It took me a while to even figure out what information it was presenting. Worse, it's not useful unless you add a lot more information to it such as what relations are actually being scanned or joined at each path which is going to make it a hell of a lot harder to read. I'm not sure how to do any better but I would be fascinated to see any new images you generate :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Multi calendar system for pgsql
Hi everyone, I want to try to add a multi calendar system for pgsql. I want to know if it will be accepted as a patch to pgsql? More details: Multi calendar systems are useful for several languages and countries using different calendar: Hijri, Persian, Hebrew, etc. For implementation I think it is better to add this support in date fields like this: create table tb (dt date calendar persian); if no calendar is defined, it will be Gregorian so no problem is made for old sql commands. I am new in pgsql dev but if I know this patch will be accepted, I am happy to work on it. Regards, -- __ \ /_\\_-//_ Mohsen Alimomeni -- __ \ /_\\_-//_ Mohsen Alimomeni
Re: [HACKERS] pg_migrator progress
On Wednesday 18 February 2009 10:47:25 Tom Lane wrote: Gregory Stark st...@enterprisedb.com writes: Tom Lane t...@sss.pgh.pa.us writes: No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb anyway. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. I think that argument is a red herring. In the first place, it's unlikely that there'd be a huge run of consecutive OIDs *in the same table*. In the second place, if he does have such runs, the claim that he can't possibly have dealt with OID wraparound before seems pretty untenable --- he's obviously been eating lots of OIDs. Yeah, but its not just lots... it's lots and lots of lots. Sure, I have multi-billion row _tables_ now, but I know I ran systems for years back in the day when we used oids in user tables, and they never made it to oid wraparound terratory, because they just didn't churn through that much data. But having said that, there isn't any real harm in fixing the OID counter to match what it was. You need to run pg_resetxlog to set the WAL position and XID counter anyway, and it can set the OID counter too. +1 for doing this, otherwise we need some strong warnings in the migrator docs about this case imho. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Multi calendar system for pgsql
Mohsen Alimomeni m.alimom...@gmail.com writes: I want to try to add a multi calendar system for pgsql. I want to know if it will be accepted as a patch to pgsql? There's probably about zero chance of accepting such a thing into core, but maybe you could do it as an add-on (pgfoundry project). For implementation I think it is better to add this support in date fields like this: create table tb (dt date calendar persian); Consider specifying the calendar as a typmod, eg create table tb (dt cdate(persian)); since the necessary extension hooks already exist for that. 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] The science of optimization in practical terms?
Robert Haas robertmh...@gmail.com writes: Yeah, I thought about this too, but it seems like overkill for the problem at hand, and as you say it's not clear you'd get any benefit out of the upper bound anyway. I was thinking of something simpler: instead of directly multiplying 0.005 into the selectivity every time you find something incomprehensible, keep a count of the number of incomprehensible things you saw and at the end multiply by 0.005/N. That way more unknown quals look more restrictive than fewer, but things only get linearly wacky instead of exponentially wacky. clauselist_selectivity could perhaps apply such a heuristic, although I'm not sure how it could recognize default estimates from the various specific estimators, since they're mostly all different. Personally I've not seen all that many practical cases where the estimator simply hasn't got a clue at all. What's far more commonly complained of IME is failure to handle *correlated* conditions in an accurate fashion. Maybe we should just discount the product selectivity all the time, not only when we think the components are default estimates. 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] Multi calendar system for pgsql
I want to try to add a multi calendar system for pgsql. I want to know if it
will be accepted as a patch to pgsql?
More details:
Multi calendar systems are useful for several languages and countries using
different calendar: Hijri, Persian, Hebrew,
etc.
For implementation I think it is better to add this support in date fields like
this:Â
create table tb (dt date calendar persian);
if no calendar is defined, it will be Gregorian so no problem is made for old
sql commands.
I don't think that new keywords should be added for that if it does not
belong to the SQL standard, especially with something as open ended and
sensitive as a cultural related keyword: there are dozens calendars listed
on wikipedia...
ISTM that this is either a localization problem, possibly fully
independent from pg, or a conversion issue with a simple function which
may be develop as an extension outside pg, say:
SELECT PersianDate('2008-02-18'::DATE);
--
Fabien.
--
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] The science of optimization in practical terms?
On Wed, Feb 18, 2009 at 11:46 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yeah, I thought about this too, but it seems like overkill for the problem at hand, and as you say it's not clear you'd get any benefit out of the upper bound anyway. I was thinking of something simpler: instead of directly multiplying 0.005 into the selectivity every time you find something incomprehensible, keep a count of the number of incomprehensible things you saw and at the end multiply by 0.005/N. That way more unknown quals look more restrictive than fewer, but things only get linearly wacky instead of exponentially wacky. clauselist_selectivity could perhaps apply such a heuristic, although I'm not sure how it could recognize default estimates from the various specific estimators, since they're mostly all different. Presumably the estimators would need to be modified to provide some information on their level of confidence in their estimate (possibly this could be more general than whether the number is a default or not, though I'm not sure what we'd do with that information). But it may not be necessary to go through that pain if we implement your idea below. Personally I've not seen all that many practical cases where the estimator simply hasn't got a clue at all. What's far more commonly complained of IME is failure to handle *correlated* conditions in an accurate fashion. Maybe we should just discount the product selectivity all the time, not only when we think the components are default estimates. That has something going for it, although off the top of my head I'm not sure exactly what formula would make sense. Presumably we want the overall selectivity estimate to be less than the estimate for individual clause taken individually, but how much less? It doesn't seem right to estimate the selectivity of S_1...S_n as MIN(S_1 ... S_n) / n, because that will give you weird results with things like a = 1 AND a != 2. You might need to divide the estimates into two buckets: those that reduce selectivity by a lot, and those that reduce it only slightly, then multiply the latter bucket and, say, divide through by the cardinality of the former bucket. But the exact details of the math are not obvious to me. I'm talking off the top of my head here, maybe you have a more clear thought as to how this would work? ...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] pg_migrator progress
Alvaro Herrera wrote: Bruce Momjian wrote: I have completed all the outstanding pg_migratory TODO items. I still have more work to do in cleanup and testing, but if people want to look at my progress, now is a good time. Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're going to mess with pg_clog? Yes, that happens in copy_clog_xlog_xid(). -- Bruce Momjian br...@momjian.ushttp://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] pg_migrator progress
Robert Treat wrote: On Wednesday 18 February 2009 10:47:25 Tom Lane wrote: Gregory Stark st...@enterprisedb.com writes: Tom Lane t...@sss.pgh.pa.us writes: No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb anyway. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. I think that argument is a red herring. In the first place, it's unlikely that there'd be a huge run of consecutive OIDs *in the same table*. In the second place, if he does have such runs, the claim that he can't possibly have dealt with OID wraparound before seems pretty untenable --- he's obviously been eating lots of OIDs. Yeah, but its not just lots... it's lots and lots of lots. Sure, I have multi-billion row _tables_ now, but I know I ran systems for years back in the day when we used oids in user tables, and they never made it to oid wraparound terratory, because they just didn't churn through that much data. But having said that, there isn't any real harm in fixing the OID counter to match what it was. You need to run pg_resetxlog to set the WAL position and XID counter anyway, and it can set the OID counter too. +1 for doing this, otherwise we need some strong warnings in the migrator docs about this case imho. One compromise is outputting the pg_resetxlog command to the terminal, and suggesting they run it only if they need to. -- Bruce Momjian br...@momjian.ushttp://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] graph representation of data structures in optimizer
On Wed, Feb 18, 2009 at 10:22 AM, Adriano Lange adri...@c3sl.ufpr.br wrote: Hi, I'm interested in data representation and debug of optimizer routines. Thus, I've changed the debug functions of allpaths.c to make a graphviz-like output of RelOptInfo structure. Any idea about this? Is there some project or improvement like this? That is pretty cool. It would help a lot to label the baserels with their names. You might also want to move the RestrictInfo out of line so that it's easier to see where the inner and outer joinpath arrows are going. It would be really sweet if there were some compact way to see the pathkeys. ...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] The science of optimization in practical terms?
On Wed, 2009-02-18 at 07:50 -0500, Robert Haas wrote: (Now it appears that Josh is having problems that are caused by overestimating the cost of a page fetch, perhaps due to caching effects. Those are discussed upthread, and I'm still interested to see whether we can arrive at any sort of consensus about what might be a reasonable approach to attacking that problem. My own experience has been that this problem is not quite as bad, because it can throw the cost off by a factor of 5, but not by a factor of 800,000, as in my example of three unknown expressions with a combined selectivity of 0.1.) Well a very big problem with any solution is that we are creating a solution for a 2% problem. 98% of the postgresql installations out there will never need to adjust cpu_tuple_cost, cpu_index_tuple_cost, cpu_operator_cost, random_page_cost etc... They can get by just fine with a tweak to shared_buffers, work_mem, effective_cache_size and default_statistics_target. What I think should happen is to do some testing one normal installs and see if upping those parameters to .5 (or other amount) hinders those 98% installs. If it doesn't hinder those then we should up the default and walk away. Joshua D. Drake ...Robert -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Multi calendar system for pgsql
On Wed, Feb 18, 2009 at 07:50:31PM +0330, Mohsen Alimomeni wrote: Multi calendar systems are useful for several languages and countries using different calendar: Hijri, Persian, Hebrew, etc. When would the differences between these calenders actually show up? I can only think of it affecting input/output routines and the date_part,date_trunc,to_date and to_char routines. But am I missing something? If that's all, then how about just treating the current PG date types as Julian days (as far as I know, that's how it's treated internally anyway) and providing a multi-calender set of date_part,date_trunc,to_date and to_char routines. I.e. leave out the input/output routines. Doing this would be much easier, but less fun, than creating whole new types and having to modify the parser as well to recognize the new syntax. -- Sam http://samason.me.uk/ -- 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] graph representation of data structures in optimizer
Gregory Stark st...@enterprisedb.com writes: Adriano Lange adri...@c3sl.ufpr.br writes: I've changed the debug functions of allpaths.c to make a graphviz-like output of RelOptInfo structure. However I have to say this graph you've generated is amazingly hard to decipher :) It took me a while to even figure out what information it was presenting. Worse, it's not useful unless you add a lot more information to it such as what relations are actually being scanned or joined at each path which is going to make it a hell of a lot harder to read. Labeling the bottom-level scan paths with their relations would help a lot. The label at the top level isn't real helpful. But really I think the problem with this approach is that the information density is too low --- imagine what it would look like in a six-or-more-way join. I don't think the graphical approach is helpful at all here. Also, showing the final Path data structure has the problem that a lot of the information someone might want is already gone, because we throw away Paths that are determined to be dominated by other paths. The question someone usually wants answered is was a path of this structure considered at all, and if so what was the estimated cost?. In a large fraction of cases, that's not answerable from the paths that remain at the end of the search. I think some sort of on-the-fly tracing of all add_path calls might be a more useful approach. 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] pg_restore new option -m
hi, i've been testing new -m option of pg_restore with great pleasure. first, let me thank the developpers, it cut restoring time by half. is it normal that -m doesn't cope well with -C? createdb db pg_restore -m 4 -d db db.dmp works like a charm while pg_restore -C -m 4 -d template1 db.dmp gives numerous errors, mostly no such relation at index creation time. TIA -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: o...@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) -- 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] pg_migrator progress
Bruce Momjian br...@momjian.us writes: One compromise is outputting the pg_resetxlog command to the terminal, and suggesting they run it only if they need to. Er, what? pg_resetxlog is certainly not optional in this process. 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] pg_migrator progress
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: One compromise is outputting the pg_resetxlog command to the terminal, and suggesting they run it only if they need to. Er, what? pg_resetxlog is certainly not optional in this process. The oid setting part is. -- Bruce Momjian br...@momjian.ushttp://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] pg_migrator progress
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Er, what? pg_resetxlog is certainly not optional in this process. The oid setting part is. Yeah, but if you have to run it anyway it certainly isn't going to be any more work to make it set the OID counter too. 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] pg_restore new option -m
o...@pyrenet.fr writes: pg_restore -C -m 4 -d template1 db.dmp gives numerous errors, mostly no such relation at index creation time. You sure you don't get exactly the same without -m? 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] Multi calendar system for pgsql
m.alimom...@gmail.com (Mohsen Alimomeni) writes:
I want to try to add a multi calendar system for pgsql. I want to
know if it will be accepted as a patch to pgsql?
I would expect there to be nearly zero chance of such, at least in the
form of a change to how dates are stored.
As long as there is commonality in epochs and some continuity of
calculations of dates relative to epochs, there shouldn't be any
fundamental problem in adding on functions to do the following sorts
of things:
- Calculate what the UNIX date is for a given date in a given
calendar
- Output a UNIX date in the form indicated by a given calendar
You should avail yourself of the book, _Calendrical Calculations_, by
Edward M Reingold and Nachum Deerschowitz; it presents details of
calculations and conversions of dates between the following calendars:
- Gregorian
- Julian
- Coptic
- Ethiopic
- ISO
- Islamic
- Hebrew
- Ecclesiastical Calendars, for dates of Christian holidays such as Easter
- Old Hindu
- Modern Hindu
- Mayan
- Balinese Pawukon
- Persian
- Baha'i
- French Revolution
- Chinese
It would seem a whole lot preferable to create functions like (and
there may be better names!):
create function parse_date (locale, text) returns timestamp
create function output_date (local, timestamp) returns text
Thus, you might expect the following:
select parse_date('Islamic', 'Miharram 1, AH 1');
parse_date
-
622-07-16 00:00:00
Or
select output_date('Persian', '622-03-19'::timestamp);
output_date
-
1 Farvardin AH 1
(It is entirely likely that I'm fracturing spellings of things!
Apologies if I am!)
http://emr.cs.uiuc.edu/home/reingold/calendar-book/index.shtml
--
(reverse (concatenate 'string moc.enworbbc @ enworbbc))
http://cbbrowne.com/info/x.html
Thank you for calling PIXAR! If you have a touch tone phone, you can
get information or reach anybody here easily! If your VCR at home is
still blinking '12:00', press '0' at any time during this message and
an operator will assist you.
-- PIXAR'S toll-free line (1-800-888-9856)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Remove the special cases to prevent minus-zero results in float4
Tom Lane wrote: We don't, however, expect that all platforms will produce minus zero, so we need to adjust the one affected regression test to allow both results. Do we know if and what platforms wouldn't produce minus zero? It would be interesting to not have the alternative expected output for a few days and see which buildfarm animals break. -- 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] The science of optimization in practical terms?
Robert Haas wrote: experience, most bad plans are caused by bad selectivity estimates, and the #1 source of bad selectivity estimates is selectivity estimates for unknown expressions. ISTM unknown expressions should be modeled as a range of values rather than one single arbitrary value. For example, rather than just guessing 1000 rows, if an unknown expression picked a wide range (say, 100 - 1 rows; or maybe even 1 - table_size), the planner could choose a plan which wouldn't be pathologically slow regardless of if the guess was too low or too high. For that matter, it seems if all estimates used a range rather than a single value, ISTM less in general we would product less fragile plans. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove the special cases to prevent minus-zero results in float4
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Tom Lane wrote: We don't, however, expect that all platforms will produce minus zero, so we need to adjust the one affected regression test to allow both results. Do we know if and what platforms wouldn't produce minus zero? It would be interesting to not have the alternative expected output for a few days and see which buildfarm animals break. Well, my old HPUX box definitely doesn't print minus zeroes here. What I'm really more interested in is whether there are any third or fourth opinions among the buildfarm members ... 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] The science of optimization in practical terms?
On Wed, Feb 18, 2009 at 2:46 PM, Ron Mayer rm...@cheapcomplexdevices.com wrote: Robert Haas wrote: experience, most bad plans are caused by bad selectivity estimates, and the #1 source of bad selectivity estimates is selectivity estimates for unknown expressions. ISTM unknown expressions should be modeled as a range of values rather than one single arbitrary value. For example, rather than just guessing 1000 rows, if an unknown expression picked a wide range (say, 100 - 1 rows; or maybe even 1 - table_size), the planner could choose a plan which wouldn't be pathologically slow regardless of if the guess was too low or too high. For that matter, it seems if all estimates used a range rather than a single value, ISTM less in general we would product less fragile plans. It would be interesting to find out if something like this could be made to work, but it's more than I'd be willing to bite off. I think this would require reworking large portions of the planner, and I am doubtful that it could be done without a substantial loss of performance. The existing code considers A LOT of plans, to the point where even a few more or fewer floating-point operations per plan result in a measurable change in planning time that can be measured in macro-benchmarks. If we could somehow tamp down the amount of time considering plans that turn out to be dead ends, it might free up some time to perform some of these other computations. But I'm not sure how to go about that. The best ideas I've come up with so far involve refactoring joinpath.c to eliminate some of the duplicate computation and/or somehow be more intelligent about which nested loops we generate. But I haven't come up with anything yet that's demonstrably better than the add_path patch that I submitted a few weeks ago, which is not bad but not earth-shattering either. At any rate, we'd need to save quite a bit to pay for carting around best and worst case costs for every plan we consider. ...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] The science of optimization in practical terms?
Robert Haas robertmh...@gmail.com writes: ... At any rate, we'd need to save quite a bit to pay for carting around best and worst case costs for every plan we consider. Another problem with this is it doesn't really do anything to solve the problem we were just discussing, namely having an intelligent way of combining inaccurate estimates for WHERE clauses. If you just take a range of plausible values and multiply then it doesn't take very many clauses to get to a range of [0,1] --- or at least a range of probabilities wide enough to be unhelpful. An idea that I think has been mentioned before is to try to identify cases where we can *prove* there is at most one row emitted by a sub-path (eg, because of a unique index, DISTINCT subplan, etc). Then we could penalize nestloops with outer relations that weren't provably a single row. This is basically restricting the notion of estimation confidence to a special case that's particularly important for SQL. 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] [COMMITTERS] pgsql: Start background writer during archive recovery.
hei...@postgresql.org (Heikki Linnakangas) writes: Log Message: --- Start background writer during archive recovery. Might that have anything to do with this? http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dungbeetledt=2009-02-18%2019:44:01 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] The science of optimization in practical terms?
On Wed, Feb 18, 2009 at 3:32 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: ... At any rate, we'd need to save quite a bit to pay for carting around best and worst case costs for every plan we consider. Another problem with this is it doesn't really do anything to solve the problem we were just discussing, namely having an intelligent way of combining inaccurate estimates for WHERE clauses. If you just take a range of plausible values and multiply then it doesn't take very many clauses to get to a range of [0,1] --- or at least a range of probabilities wide enough to be unhelpful. Yeah. An idea that I think has been mentioned before is to try to identify cases where we can *prove* there is at most one row emitted by a sub-path (eg, because of a unique index, DISTINCT subplan, etc). Then we could penalize nestloops with outer relations that weren't provably a single row. This is basically restricting the notion of estimation confidence to a special case that's particularly important for SQL. I thought about this, too, and I agree. Having this information available would also be very helpful for join removal. I believe that you did some work on this for SEMI/ANTI-join support in the form of query_is_distinct_for, but I'm not sure if that takes the right sort of inputs for what we need here. (It also doesn't seem to consider the case of a baserel with a unique index for some reason...) ...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] Re: [COMMITTERS] pgsql: Start background writer during archive recovery.
On Wed, 2009-02-18 at 15:43 -0500, Tom Lane wrote: hei...@postgresql.org (Heikki Linnakangas) writes: Log Message: --- Start background writer during archive recovery. Might that have anything to do with this? http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dungbeetledt=2009-02-18%2019:44:01 Hmmm, looks very probable. But not anything that jumps out quickly at me. Will continue to check. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Re: [COMMITTERS] pgsql: Start background writer during archive recovery.
On Wed, 2009-02-18 at 21:28 +, Simon Riggs wrote: On Wed, 2009-02-18 at 15:43 -0500, Tom Lane wrote: hei...@postgresql.org (Heikki Linnakangas) writes: Log Message: --- Start background writer during archive recovery. Might that have anything to do with this? http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dungbeetledt=2009-02-18%2019:44:01 Hmmm, looks very probable. But not anything that jumps out quickly at me. Will continue to check. Finger points that way, but still can't see any specific reason for that. More likely to be an uncommon race condition, rather than a error specific to dungbeetle. If startup process death is slow, this could happen, though hasn't occurred in other tests. Given the shape of the patch, the likely fix is to bump NUM_AUXILIARY_PROCS by one. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] The science of optimization in practical terms?
On Wed, 2009-02-18 at 15:32 -0500, Tom Lane wrote: An idea that I think has been mentioned before is to try to identify cases where we can *prove* there is at most one row emitted by a sub-path (eg, because of a unique index, DISTINCT subplan, etc). Then we could penalize nestloops with outer relations that weren't provably a single row. This is basically restricting the notion of estimation confidence to a special case that's particularly important for SQL. Proof seems best way forward. IIRC the reason we didn't do this before HOT is that unique index scans did often return many more than one row. Now we have a much better chance of it being true. As you say, propagation of error makes an error bars approach pointless too quickly to be worth pursuing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Re: [COMMITTERS] pgsql: Start background writer during archive recovery.
Simon Riggs si...@2ndquadrant.com writes: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=dungbeetledt=2009-02-18%2019:44:01 More likely to be an uncommon race condition, rather than a error specific to dungbeetle. I agree, that's what it looks like, especially since I couldn't duplicate it on Fedora 9 x86_64 which is presumably fairly close to what dungbeetle is running. I tried to duplicate it by putting the box under extra load, but no luck. 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] The science of optimization in practical terms?
Simon Riggs si...@2ndquadrant.com writes: On Wed, 2009-02-18 at 15:32 -0500, Tom Lane wrote: An idea that I think has been mentioned before is to try to identify cases where we can *prove* there is at most one row emitted by a sub-path (eg, because of a unique index, DISTINCT subplan, etc). Proof seems best way forward. IIRC the reason we didn't do this before HOT is that unique index scans did often return many more than one row. But those extra tuples didn't make it up to the point of the join, so they weren't really a problem for nestloop speed. IMO the reason this hasn't been done to date is that until recently we didn't have any mechanism to ensure a plan got invalidated if some constraint it was depending on (like uniqueness) went away. Now we do, so it would be safe to rely on the constraint for proof purposes. 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] graph representation of data structures in optimizer
Tom Lane escreveu: Gregory Stark st...@enterprisedb.com writes: Adriano Lange adri...@c3sl.ufpr.br writes: I've changed the debug functions of allpaths.c to make a graphviz-like output of RelOptInfo structure. However I have to say this graph you've generated is amazingly hard to decipher :) It took me a while to even figure out what information it was presenting. Worse, it's not useful unless you add a lot more information to it such as what relations are actually being scanned or joined at each path which is going to make it a hell of a lot harder to read. Labeling the bottom-level scan paths with their relations would help a lot. The label at the top level isn't real helpful. But really I think the problem with this approach is that the information density is too low --- imagine what it would look like in a six-or-more-way join. I don't think the graphical approach is helpful at all here. Certainly. That example had only three relations. Six relations in a RelOptInfo will make a big graph and too hard to understand. So, I will think about this for a while. A interesting thing for me is to identify the in-degree pointers of each structure. Also, showing the final Path data structure has the problem that a lot of the information someone might want is already gone, because we throw away Paths that are determined to be dominated by other paths. The question someone usually wants answered is was a path of this structure considered at all, and if so what was the estimated cost?. In a large fraction of cases, that's not answerable from the paths that remain at the end of the search. I think some sort of on-the-fly tracing of all add_path calls might be a more useful approach. regards, tom lane Humm. This temporal approach may be dificult to represent in this graphical mode. I guess that the text-like pretty_format_node_dump() representation and diff are yet more usefull for this. -- Adriano Lange C3SL/UFPR - www.c3sl.ufpr.br -- 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] graph representation of data structures in optimizer
Robert Haas escreveu: That is pretty cool. It would help a lot to label the baserels with their names. You might also want to move the RestrictInfo out of line so that it's easier to see where the inner and outer joinpath arrows are going. Humm. Maybe this is not easy to do in dot command line graph generator. Perhaps I should to try this in other application. The output generated by debug is only a text plain description of vertex and edges, without any information about position or path. See the attached file. It would be really sweet if there were some compact way to see the pathkeys. Several attributes and objects are missing yet, but I will add them. Thanks, Adriano Lange C3SL/UFPR - www.c3sl.ufpr.br RelOptInfo_graph.dot Description: application/crossover-dot -- 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] GIN fast insert
On Tue, Feb 17, 2009 at 2:28 PM, Teodor Sigaev teo...@sigaev.ru wrote: Hi there, we present two variants of GIN fast insert patch, since we're not sure what is a the best solution: v0.28.1 - remove disable cost in gincostestimate - per http://archives.postgresql.org/message-id/499466d2.4010...@sigaev.ru gingettuple could force cleanup of pending list if it got a lossy tidbitmap. If such cleanup occurs the gingettuple will rescan pending list again. v0.28.2 - remove disable cost in gincostestimate - per http://archives.postgresql.org/message-id/12795.1234379...@sss.pgh.pa.us AM can now have only one search method: amgettuple or amgetbitmap. - GIN now has only amgetbitmap interface I reviewed v0.28.1. I see that disable_cost is gone from gincostestimate, but you're still using the pending list to set costs, and I still think that's bogus. It seems clear that this is going to change much faster than plans are going to be invalidated, and if autovacuum is doing its job, the pending list won't get long enough to matter much anyway, right? I don't think this patch should be touching gincostestimate at all. I am thinking that it is may not be necessary to remove the gingettuple interface (as you did in v0.28.2). Forcing a cleanup of the pending list seems like a reasonable workaround. We don't expect this situation to come up frequently, so if the method we use to handle it is not terribly efficient, oh well. The one thing that concerns me is - what will happen in a hot standby environment, when that patch is committed? In that situation, I believe that we can't call modify any heap or index pages, so... Some other assorted minor comments on v0.28.1... 1. The description of the fastupdate reloption should be reworded for consistency with other options: Enables fast update feature for this GIN index 2. Why is this implemented as a string reloption rather than a boolean reloption? It seems like you want to model this on autovacuum_enabled. 3. Documentation wordsmithing. You have the following paragraph: As of productnamePostgreSQL/productname 8.4, this problem is alleviated by postponing most of the work until the next commandVACUUM/. Newly inserted index entries are temporarily stored in an unsorted list of pending entries commandVACUUM/ inserts all pending entries into the main acronymGIN/acronym index data structure, using the same bulk insert techniques used during initial index creation. This greatly improves acronymGIN/acronym index update speed, even counting the additional vacuum overhead. Here is my proposed rewrite: As of productnamePostgreSQL/productname 8.4, acronymGIN/ is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When the table is vacuumed, or in some cases when the pending list becomes too large, the entries are moved to the main acronymGIN/acronym data structure using the same bulk insert techniques used during initial index creation. This greatly improves acronymGIN/acronym index update speed, even counting the additional vacuum overhead. 4. More wordsmithing. In the following paragraph, you have: It's recommended to use properly-configured autovacuum with tables having acronymGIN/acronym indexes, to keep this overhead to reasonable levels. I think it is clearer and more succinct to write simply: Proper use of autovacuum can minimize this problem. 5. In textsearch.sgml, you say that GIN indexes are moderately slower to update, but about 10x slower without fastupdate. Can we provide a real number in place of moderately? I don't know whether to think this means 20% or 2x. 6. One of the comment changes in startScanEntry is simply a correction of a typographical error (deletion for deletition). You might as well commit this change separately and remove it from this patch. 7. pg_stat_get_fresh_inserted_tuples. I am not crazy about the fact that we call this the pending list in some places, fast update in some places, and now, here, fresh tuples. Let's just call it fast insert tuples. 8. tbm_check_tuple. The opening curly brace should be uncuddled. The curly braces around wordnum = bitnum = 0 are superfluous. 9. gincostestimate. There are a lot of superfluous whitespace changes here, and some debugging code that obviously wasn't fully removed. 10. GinPageOpaqueData. Surely we can come up with a better name than GIN_LIST. This is yet another name for the same concept. Why not call this GIN_FAST_INSERT_LIST? 11. ginInsertCleanup. Inserion is a typo. Unfortunately, I don't understand all of this patch well enough to give it as thorough a going-over as it deserves, so my apologies for whatever I've missed. ...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] Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets
On Wed, Jan 7, 2009 at 9:14 AM, Joshua Tolley eggyk...@gmail.com wrote:
On Tue, Jan 06, 2009 at 11:49:57PM -0500, Robert Haas wrote:
Josh / eggyknap -
Can you rerun your performance tests with this version of the patch?
...Robert
Will do, as soon as I can.
Josh,
Have you been able to do anything further with this?
I'm attaching a rebased version of this patch with a few further
whitespace cleanups.
...Robert
*** a/src/backend/executor/nodeHash.c
--- b/src/backend/executor/nodeHash.c
***
*** 53,58 ExecHash(HashState *node)
--- 53,222
return NULL;
}
+ /*
+ *
+ * ExecHashGetIMBucket
+ *
+ * Returns the index of the in-memory bucket for this
+ * hashvalue, or IM_INVALID_BUCKET if the hashvalue is not
+ * associated with any unfrozen bucket (or if skew
+ * optimization is not being used).
+ *
+ * It is possible for a tuple whose join attribute value is
+ * not a MCV to hash to an in-memory bucket due to the limited
+ * number of hash values but it is unlikely and everything
+ * continues to work even if it does happen. We would
+ * accidentally cache some less optimal tuples in memory
+ * but the join result would still be accurate.
+ *
+ * hashtable-imBucket is an open addressing hashtable of
+ * in-memory buckets (HashJoinIMBucket).
+ *
+ */
+ int
+ ExecHashGetIMBucket(HashJoinTable hashtable, uint32 hashvalue)
+ {
+ int bucket;
+
+ if (!hashtable-enableSkewOptimization)
+ return IM_INVALID_BUCKET;
+
+ /* Modulo the hashvalue (using bitmask) to find the IM bucket. */
+ bucket = hashvalue (hashtable-nIMBuckets - 1);
+
+ /*
+ * While we have not hit a hole in the hashtable and have not hit the
+ * actual bucket we have collided in the hashtable so try the next
+ * bucket location.
+ */
+ while (hashtable-imBucket[bucket] != NULL
+ hashtable-imBucket[bucket]-hashvalue != hashvalue)
+ bucket = (bucket + 1) (hashtable-nIMBuckets - 1);
+
+ /*
+ * If the bucket exists and has been correctly determined return
+ * the bucket index.
+ */
+ if (hashtable-imBucket[bucket] != NULL
+ hashtable-imBucket[bucket]-hashvalue == hashvalue)
+ return bucket;
+
+ /*
+ * Must have run into an empty location or a frozen bucket which means the
+ * tuple with this hashvalue is not to be handled as if it matches with an
+ * in-memory bucket.
+ */
+ return IM_INVALID_BUCKET;
+ }
+
+ /*
+ *
+ * ExecHashFreezeNextIMBucket
+ *
+ * Freeze the tuples of the next in-memory bucket by pushing
+ * them into the main hashtable. Buckets are frozen in order
+ * so that the best tuples are kept in memory the longest.
+ *
+ */
+ static bool
+ ExecHashFreezeNextIMBucket(HashJoinTable hashtable)
+ {
+ int bucketToFreeze;
+ int bucketno;
+ int batchno;
+ uint32 hashvalue;
+ HashJoinTuple hashTuple;
+ HashJoinTuple nextHashTuple;
+ HashJoinIMBucket *bucket;
+ MinimalTuple mintuple;
+
+ /* Calculate the imBucket index of the bucket to freeze. */
+ bucketToFreeze = hashtable-imBucketFreezeOrder
+ [hashtable-nUsedIMBuckets - 1 - hashtable-nIMBucketsFrozen];
+
+ /* Grab a pointer to the actual IM bucket. */
+ bucket = hashtable-imBucket[bucketToFreeze];
+ hashvalue = bucket-hashvalue;
+
+ /*
+ * Grab a pointer to the first tuple in the soon to be frozen IM bucket.
+ */
+ hashTuple = bucket-tuples;
+
+ /*
+ * Calculate which bucket and batch the tuples belong to in the main
+ * non-IM hashtable.
+ */
+ ExecHashGetBucketAndBatch(hashtable, hashvalue, bucketno, batchno);
+
+ /* until we have read all tuples from this bucket */
+ while (hashTuple != NULL)
+ {
+ /*
+ * Some of this code is very similar to that of ExecHashTableInsert.
+ * We do not call ExecHashTableInsert directly as
+ * ExecHashTableInsert expects a TupleTableSlot and we already have
+ * HashJoinTuples.
+ */
+ mintuple = HJTUPLE_MINTUPLE(hashTuple);
+
+ /* Decide whether to put the tuple in the hash table or a temp file. */
+ if (batchno == hashtable-curbatch)
+ {
+ /* Put the tuple in hash table. */
+ nextHashTuple = hashTuple-next;
+ hashTuple-next = hashtable-buckets[bucketno];
+ hashtable-buckets[bucketno] = hashTuple;
+ hashTuple = nextHashTuple;
+ hashtable-spaceUsedIM -= HJTUPLE_OVERHEAD + mintuple-t_len;
+ }
+ else
+ {
+ /* Put the tuples into a temp file for later batches. */
+ Assert(batchno hashtable-curbatch);
+ ExecHashJoinSaveTuple(mintuple, hashvalue,
+ hashtable-innerBatchFile[batchno]);
+ /*
+ * Some memory has been freed up. This must be done before we
+ * pfree the hashTuple of we lose access to the tuple size.
+ */
+ hashtable-spaceUsed -= HJTUPLE_OVERHEAD +
Re: [HACKERS] Proposed Patch to Improve Performance of Multi-BatchHash Join for Skewed Data Sets
At this point, we await further feedback on what is necessary to get this patch accepted. We would also like to thank Josh and Robert again for their review time. I think what we need here is some very simple testing to demonstrate that this patch demonstrates a speed-up even when the inner side of the join is a joinrel rather than a baserel. Can you suggest a single query against the skewed TPCH dataset that will result in two or more multi-batch hash joins? If so, it should be a simple matter to run that query with and without the patch and verify that the former is faster than the latter. Thanks, ...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] Re: [COMMITTERS] pgsql: Start background writer during archive recovery.
Simon Riggs wrote: More likely to be an uncommon race condition, rather than a error specific to dungbeetle. If startup process death is slow, this could happen, though hasn't occurred in other tests. True, the startup process can live for a short while concurrently with bgwriter, walwriter and autovacuum launcher, before it exits. Given the shape of the patch, the likely fix is to bump NUM_AUXILIARY_PROCS by one. Not sure what you mean by the shape of the patch, but agreed. -- 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] WIP: hooking parser
2009/2/18 Peter Eisentraut pete...@gmx.net: Pavel Stehule wrote: 2009/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. regards, tom lane it's maybe too much simple :). It is sample that have to show possibility. I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that + 1 regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
