Re: [HACKERS] setlocale and gettext in Postgres
On Tue, Feb 1, 2011 at 5:36 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: 2011/1/27 Hiroshi Inoue in...@tpf.co.jp: I see now the following lines in libintl.h of version 0.18.1.1 which didn't exist in 0.17 version. The macro may cause a trouble especially on Windows. Attached is a patch to disable the macro on Windows. Can anyone test the fix? I added the patch to the current commitfest for reminder. https://commitfest.postgresql.org/action/patch_view?id=528 The QA team in EDB have tested the patch for me. It works as designed and allows us to upgrade gettext to fix Japanese localisation on Win64. Upgrading gettext without the patch will fix Japanese, but break other translations (eg. French). -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error code for terminating connection due to conflict with recovery
On Mon, 2011-01-31 at 20:27 -0500, Robert Haas wrote: So I don't see why one particular kind of recovery conflict should be in a different class than all the others. This point has been explained many times and is very clear in the code. It has a clear functional purpose, not decoration or mere personal opinion. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] wildcard search support for pg_trgm
On Tue, Feb 1, 2011 at 5:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: AFAICT that would break on-disk compatibility of pg_trgm GIST indexes. I don't believe we have adequate evidence to justify doing that, and in any case it ought to be a separate patch rather than buried inside a mostly unrelated feature patch. Ok. Actually, I don't think just increasement of SIGLENINT as a solution. I beleive that we need to have it as index parameter. I'll try to provide more of tests in order to motivate this. With best regards, Alexander Korotkov.
Re: [HACKERS] Error code for terminating connection due to conflict with recovery
On Mon, 2011-01-31 at 20:52 -0500, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jan 31, 2011 at 7:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Seems a little weird to me, since the administrator hasn't done anything. Sure he has: he issued the DROP DATABASE command that's causing the system to disconnect standby sessions. Well, I'm not sure how much this matters - as long as it's a dedicated error code, the user can write code to DTRT somehow. But I don't buy your argument. Ultimately, user activity causes any kind of recovery conflict. Well, yeah, but the predictability of the failure is pretty variable. In this case we can say that the error definitely would not have occurred if somebody hadn't done a DROP DATABASE on the master while there were live sessions in that DB on the slave. I think that's a sufficiently close coupling to say that the error is the result of an operator action. OTOH, the occurrence of deadlocks is (usually) a lot more dependent on random-chance timing of different transactions, and you usually can't point to any action that intentionally caused a deadlock. ERRCODE_DATABASE_DROPPED57P04 looks best The previous code was 57P01 so this is least change, if nothing else. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] bad links in messages from commits
On Tue, Feb 1, 2011 at 07:56, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/2/1 Magnus Hagander mag...@hagander.net: On Tue, Feb 1, 2011 at 05:53, Pavel Stehule pavel.steh...@gmail.com wrote: Hello There are broken links inside messages from commiters. projects / 404 - No such project Are you using gmail? They have made some changes recently that breaks the viewing of the URLs. Haven't heard any non-gmail user complain, and not entirely sure how to fix it. a workaround is to use copy link location or whatever it's called in your browser and then paste that - that works without errors. yes, you has true. It's gmail bug. I'm actually not entirely sure it's a gmail bug - it looks more like it's at least partially gitweb's fault, but I'm not entirely sure... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] setlocale and gettext in Postgres
On Tue, Feb 1, 2011 at 09:08, Dave Page dp...@pgadmin.org wrote: On Tue, Feb 1, 2011 at 5:36 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: 2011/1/27 Hiroshi Inoue in...@tpf.co.jp: I see now the following lines in libintl.h of version 0.18.1.1 which didn't exist in 0.17 version. The macro may cause a trouble especially on Windows. Attached is a patch to disable the macro on Windows. Can anyone test the fix? I added the patch to the current commitfest for reminder. https://commitfest.postgresql.org/action/patch_view?id=528 The QA team in EDB have tested the patch for me. It works as designed and allows us to upgrade gettext to fix Japanese localisation on Win64. Upgrading gettext without the patch will fix Japanese, but break other translations (eg. French). Do we need to backpatch this? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] setlocale and gettext in Postgres
On Tue, Feb 1, 2011 at 8:29 AM, Magnus Hagander mag...@hagander.net wrote: On Tue, Feb 1, 2011 at 09:08, Dave Page dp...@pgadmin.org wrote: On Tue, Feb 1, 2011 at 5:36 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: 2011/1/27 Hiroshi Inoue in...@tpf.co.jp: I see now the following lines in libintl.h of version 0.18.1.1 which didn't exist in 0.17 version. The macro may cause a trouble especially on Windows. Attached is a patch to disable the macro on Windows. Can anyone test the fix? I added the patch to the current commitfest for reminder. https://commitfest.postgresql.org/action/patch_view?id=528 The QA team in EDB have tested the patch for me. It works as designed and allows us to upgrade gettext to fix Japanese localisation on Win64. Upgrading gettext without the patch will fix Japanese, but break other translations (eg. French). Do we need to backpatch this? We've only seen the problem on 64 bit builds - but I guess it may occur on 32 bit too, given the right version of gettext (they come form different places for win32 vs. win64, so it's not entirely straightforward to figure out). So, it certainly needs to go to 9.0, and probably wouldn't hurt to put it in 8.3/8.4 too. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error code for terminating connection due to conflict with recovery
On Tue, 2011-02-01 at 07:35 +0100, Magnus Hagander wrote:
On Tue, Feb 1, 2011 at 03:29, Robert Haas robertmh...@gmail.com wrote:
On Mon, Jan 31, 2011 at 8:52 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Then again - in theory, there's no reason why we couldn't drop a
database on the master when it's in use, kicking out everyone using it
with this very same error code. We don't happen to handle it that way
right now, but...
Yeah, that was in the back of my mind too. DROP DATABASE foo FORCE,
maybe?
I have to think some people would find that useful.
Yes.
If nothing else, it would save some typing :-)
I like it also. It allows me to get rid of the concept of non-retryable
recovery conflicts, so we just have one code path that works in both
normal mode and standby. Sweet.
Here's the basic patch, will work on the refactoring if no objections.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 9a9b4cb..8b1878c 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -732,7 +732,7 @@ createdb_failure_callback(int code, Datum arg)
* DROP DATABASE
*/
void
-dropdb(const char *dbname, bool missing_ok)
+dropdb(const char *dbname, bool missing_ok, bool force)
{
Oid db_id;
bool db_istemplate;
@@ -800,11 +800,16 @@ dropdb(const char *dbname, bool missing_ok)
* As in CREATE DATABASE, check this after other error conditions.
*/
if (CountOtherDBBackends(db_id, notherbackends, npreparedxacts))
- ereport(ERROR,
+ {
+ if (force)
+ ResolveRecoveryConflictWithDatabase(db_id);
+ else
+ ereport(ERROR,
(errcode(ERRCODE_OBJECT_IN_USE),
errmsg(database \%s\ is being accessed by other users,
dbname),
errdetail_busy_db(notherbackends, npreparedxacts)));
+ }
/*
* Remove the database's tuple from pg_database.
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index fb9da83..ee595af 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3074,6 +3074,7 @@ _copyDropdbStmt(DropdbStmt *from)
COPY_STRING_FIELD(dbname);
COPY_SCALAR_FIELD(missing_ok);
+ COPY_SCALAR_FIELD(force);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 2ef1a33..60d4e8c 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1523,6 +1523,7 @@ _equalDropdbStmt(DropdbStmt *a, DropdbStmt *b)
{
COMPARE_STRING_FIELD(dbname);
COMPARE_SCALAR_FIELD(missing_ok);
+ COMPARE_SCALAR_FIELD(force);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 660947c..ec67cf5 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6973,18 +6973,20 @@ alterdb_opt_item:
* This is implicitly CASCADE, no need for drop behavior
*/
-DropdbStmt: DROP DATABASE database_name
+DropdbStmt: DROP DATABASE database_name opt_force
{
DropdbStmt *n = makeNode(DropdbStmt);
n-dbname = $3;
n-missing_ok = FALSE;
+ n-force = $4;
$$ = (Node *)n;
}
- | DROP DATABASE IF_P EXISTS database_name
+ | DROP DATABASE IF_P EXISTS database_name opt_force
{
DropdbStmt *n = makeNode(DropdbStmt);
n-dbname = $5;
n-missing_ok = TRUE;
+ n-force = $6;
$$ = (Node *)n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 9500037..28dfbe2 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -995,7 +995,7 @@ standard_ProcessUtility(Node *parsetree,
DropdbStmt *stmt = (DropdbStmt *) parsetree;
PreventTransactionChain(isTopLevel, DROP DATABASE);
-dropdb(stmt-dbname, stmt-missing_ok);
+dropdb(stmt-dbname, stmt-missing_ok, stmt-force);
}
break;
diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h
index 8097547..eea939b 100644
--- a/src/include/commands/dbcommands.h
+++ b/src/include/commands/dbcommands.h
@@ -53,7 +53,7 @@ typedef struct xl_dbase_drop_rec
} xl_dbase_drop_rec;
extern void createdb(const CreatedbStmt *stmt);
-extern void dropdb(const char *dbname, bool missing_ok);
+extern void dropdb(const char *dbname, bool missing_ok, bool force);
extern void RenameDatabase(const char *oldname, const char *newname);
extern void AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel);
extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3d2ae99..f104a1b 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2293,6 +2293,7 @@ typedef struct DropdbStmt
NodeTag type;
char *dbname; /* database to drop */
bool missing_ok; /* skip error if db is missing? */
+ bool
Re: [HACKERS] SSI patch version 14
On Mon, 2011-01-31 at 17:55 -0600, Kevin Grittner wrote: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=6360b0d4ca88c09cf590a75409cd29831afff58b With confidence that it works, I looked it over some more and now like this a lot. It is definitely more readable and should be less fragile in the face of changes to MVCC bit-twiddling techniques. Of course, any changes to the HTSV_Result enum will require changes to this code, but that seems easier to spot and fix than the alternative. Thanks for the suggestion! One thing that confused me a little about the code is the default case at the end. The enum is exhaustive, so the default doesn't really make sense. The compiler warning you are silencing is the uninitialized variable xid (right?), which is clearly a spurious warning. Since you have the Assert(TransactionIdIsValid(xid)) there anyway, why not just initialize xid to InvalidTransactionId and get rid of the default case? I assume the Assert(false) is there to detect if someone adds a new enum value, but the compiler should issue a warning in that case anyway (and the comment next to Assert(false) is worded in a confusing way). This is all really minor stuff, obviously. Also, from a code standpoint, it might be possible to early return in the HEAPTUPLE_RECENTLY_DEAD case where visible=false. It looks like it will be handled quickly afterward (at TransactionIdPrecedes), so you don't have to change anything, but I thought I would mention it. Having gotten my head around it, I embellished here: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=f9307a41c198a9aa4203eb529f9c6d1b55c5c6e1 Do those changes look reasonable? None of that is really *necessary*, but it seemed cleaner and clearer that way once I looked at the code with the changes you suggested. Yes, I like those changes. Regards, Jeff Davis -- 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_upgrade fails for non-postgres user
On Tue, Feb 1, 2011 at 02:25, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: I just tried doing pg_upgrade on a database when logged in as user mha rather than postgres on my system. And it failed. Even though the db was initialized with superuser mha. The reason for this was that pg_upgrade tried to connect to the database mha (hardcoded to be the db username), and that certainly didn't exist. When that was fixed, I realized the psql command to create the datanbases connect to database template1 only to immediately switch to database postgres, which also seems rather pointless. Attach patch makes it connect to the postgres database instead of $USER, and then also changes the psql command to actually use it. I know way too little about pg_upgrade to tell if this is fully safe, but it does fix the problem in my installation. I have found that this problem only affects PG 9.1 and is not part of released PG 9.0 because we don't restore pg_authid in 9.0 (we don't need to because we have no pg_largeobject_metadata table in PG 8.4). Ah, that explains why we haven't seen reports on this before. I have applied a modified version of your patch to always retore into the 'postgres' database rather than the OS user. I thought we created an os-user-named database, but it seems that database is always called 'postgres' but is owned by the OS user. That seems kind of inconsistent, but no matter. The whole reason for the postgres database is to provide a *predictable* name for people and tools to connect to, and possibly store things in. template1 works reasonably well for connect to, but not for store in - because it gets duplicated out to all new databases after that. Which is also why it's a good reason to have it the default fo rconnect to either - because people will create object there by mistake, and then get it duplicated out to all new databases. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] bad links in messages from commits
2011/2/1 Magnus Hagander mag...@hagander.net: On Tue, Feb 1, 2011 at 07:56, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/2/1 Magnus Hagander mag...@hagander.net: On Tue, Feb 1, 2011 at 05:53, Pavel Stehule pavel.steh...@gmail.com wrote: Hello There are broken links inside messages from commiters. projects / 404 - No such project Are you using gmail? They have made some changes recently that breaks the viewing of the URLs. Haven't heard any non-gmail user complain, and not entirely sure how to fix it. a workaround is to use copy link location or whatever it's called in your browser and then paste that - that works without errors. yes, you has true. It's gmail bug. I'm actually not entirely sure it's a gmail bug - it looks more like it's at least partially gitweb's fault, but I'm not entirely sure... Hgweb (the Mercurial equivalent of gitweb) has the same problem. I have the impression that the semicolons in the URLs are initially not escaped correctly or something. Both hgweb and gitweb use semicolons in the URL; hgweb shows no such method: changeset;node=69b90bdd52d1 when following such a ...?cmd=changeset;node=69b90bdd52d1 link from GMail (using Chrome, in case that matters). Workaround: When I press enter on the URL in the address bar while the 404 is showing, the page reloads correctly without 404-ing. Nicolas -- 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 reference to client_encoding parameter
On 1 February 2011 05:31, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Tue, Feb 1, 2011 at 00:37, Thom Brown t...@linux.com wrote: I've attached a small patch for the docs which adds a reference to the client_encoding parameter description. This is in response to someone attempting to submit a comment which explains where available encodings can be found. Thanks. It's a reasonable reference. But I reworded it as below, that we are using in other a few places. The character sets supported by the PostgreSQL server are described in ... Thanks Itagaki-san. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] setlocale and gettext in Postgres
On 02/01/2011 03:47 AM, Dave Page wrote: On Tue, Feb 1, 2011 at 8:29 AM, Magnus Hagandermag...@hagander.net wrote: On Tue, Feb 1, 2011 at 09:08, Dave Pagedp...@pgadmin.org wrote: On Tue, Feb 1, 2011 at 5:36 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: 2011/1/27 Hiroshi Inouein...@tpf.co.jp: I see now the following lines in libintl.h of version 0.18.1.1 which didn't exist in 0.17 version. The macro may cause a trouble especially on Windows. Attached is a patch to disable the macro on Windows. Can anyone test the fix? I added the patch to the current commitfest for reminder. https://commitfest.postgresql.org/action/patch_view?id=528 The QA team in EDB have tested the patch for me. It works as designed and allows us to upgrade gettext to fix Japanese localisation on Win64. Upgrading gettext without the patch will fix Japanese, but break other translations (eg. French). Do we need to backpatch this? We've only seen the problem on 64 bit builds - but I guess it may occur on 32 bit too, given the right version of gettext (they come form different places for win32 vs. win64, so it's not entirely straightforward to figure out). So, it certainly needs to go to 9.0, and probably wouldn't hurt to put it in 8.3/8.4 too. Why are we only disabling the macro for WIN32 and not for the other platforms that the macro is defined for? Do we know it's not also a problem on Apple or Cygwin? 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] out of memory during COPY .. FROM
List, Can anyone suggest where the below error comes from, given I'm attempting to load HTTP access log data with reasonably small row and column value lengths? logs=# COPY raw FROM '/path/to/big/log/file' DELIMITER E'\t' CSV; ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073712650 bytes by 65536 more bytes. CONTEXT: COPY raw, line 613338983 It was suggested in #postgresql that I'm reaching the 1GB MaxAllocSize - but I would have thought this would only be a constraint against either large values for specific columns or for whole rows. It's worth noting that this is after 613 million rows have already been loaded (somewhere around 100GB of data) and that I'm running this COPY after the CREATE TABLE raw ... in a single transaction. I've looked at line 613338983 in the file being loaded (+/- 10 rows) and can't see anything out of the ordinary. Disclaimer: I know nothing of PostgreSQL's internals, please be gentle! Regards, Tom -- 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] setlocale and gettext in Postgres
On Tue, Feb 1, 2011 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote: Why are we only disabling the macro for WIN32 and not for the other platforms that the macro is defined for? Do we know it's not also a problem on Apple or Cygwin? No, not as far as I know. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add support for logging the current role
Updated patch attached. I think we need to improve postgresql.conf.sample a bit more, especially the long line for #log_csv_fields = '...'. 330 characters in it! #1. Leave the long line because it is needed. #2. Hide the variable from the default conf. #3. Use short %x mnemonic both in log_line_prefix and log_csv_fields. (It might require many additional mnemonics.) Which is better, or another idea? On Sat, Jan 29, 2011 at 13:06, Stephen Frost sfr...@snowman.net wrote: * log_csv_fields's GUC context is PGC_POSTMASTER. Is it by design? Doing SIGHUP would require addressing how to get all of the backends to close the old log file and open the new one, because we don't want to have a given log file which has two different CSV formats in it (we wouldn't be able to load it into the database...). This was specifically addressed in the thread leading up to this patch... I think it depends default log filename, that contains %S (seconds) suffix. We can remove %S from log_filename; if we use a log per-day, those log might contain different columns even after restart. If we cannot avoid zigged csv fields completely, SIGHUP seems reasonable for it. * What objects do you want to allocate in TopMemoryContext in assign_log_csv_fields() ? I just moved the switch to Top to be after those are allocated. How about changing the type of csv_log_fields from List* to fixed array of LogCSVFields? If so, we can use an array-initializer instead of build_default_csvlog_list() ? The code will be simplified. Fixed length won't be a problem because it would be rare that the same field are specified many times. * Docs need some tags for itemized elements or pre-formatted codes. They looks itemized in the sgml files, but will be flattened in complied HTML files. Not sure what you're referring to here...? Can you elaborate? I'm not great with the docs. :/ Could you try to make html in the doc directory? Your new decumentation after | These columns may be included in the CSV output: will be unaligned plain text without some tags. -- Itagaki Takahiro -- 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] Transaction-scope advisory locks
On Fri, Jan 28, 2011 at 17:12, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: I still didn't address the issue with pg_advisory_unlock_all() releasing transaction scoped locks, I guess you don't want independent locks, right? If an user object is locked by session locks, it also blocks backends trying to lock it with transaction locks. If so, I think an ideal behavior is below: - The transaction-or-session property is overwritten by the last lock function call. We can promote session locks from/to transaction locks. - Shared and exclusive locks are managed independently. We could have shared session lock and exclusive transaction lock on the same resource in a transaction. - Unlock functions releases both transaction and session locks. - unlock_all() releases all both locks. Those might be odd in DBMS-perspective, but would be natural as programming languages. I guess advisory locks are often used in standard programming like flows. Another issue I found while testing the behaviour here: http://archives.postgresql.org/pgsql-hackers/2011-01/msg01939.php is that if a session holds both a transaction level and a session level lock on the same resource, only one of them will appear in pg_locks. Is that going to be a problem from the user's perspective? Could it be an indication of a well-hidden bug? Based on my tests it seems to work, but I'm not at all confident with the code. In the above proposal, we won't have both session and transaction lock on the same resource at the same time, though we still need to show exclusive and shared locks in different lines. -- Itagaki Takahiro -- 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] setlocale and gettext in Postgres
On Tue, Feb 1, 2011 at 12:08, Dave Page dp...@pgadmin.org wrote: On Tue, Feb 1, 2011 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote: Why are we only disabling the macro for WIN32 and not for the other platforms that the macro is defined for? Do we know it's not also a problem on Apple or Cygwin? No, not as far as I know. I've applied the patch with an update to the comment that explains why it happens more clearly, which should also make it obvious why it won't happen on apple or cygwin (because they don't link to different versions of the microsoft runtime..) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Transaction-scope advisory locks
On Tue, Feb 1, 2011 at 7:28 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Fri, Jan 28, 2011 at 17:12, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: I still didn't address the issue with pg_advisory_unlock_all() releasing transaction scoped locks, I guess you don't want independent locks, right? If an user object is locked by session locks, it also blocks backends trying to lock it with transaction locks. If so, I think an ideal behavior is below: - The transaction-or-session property is overwritten by the last lock function call. We can promote session locks from/to transaction locks. No. The lock manager already supports session-locks. This patch should be worried about making sure that LockAcquire() gets called with the flags the user wants, NOT with redefining the interaction between transaction locks and session locks. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
On Mon, Jan 31, 2011 at 9:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: It would help if you were a bit more specific. Do you mean you want to write something like foo.bar(baz) and have that mean call the bar method of foo and pass it baz as an argument? If so, that'd certainly be possible to implement for purposes of a college course, if you're so inclined - after all it's free software - but we'd probably not make such a change to core PG, because right now that would mean call the function bar in schema baz and pass it foo as an argument. We try not to break people's code to when adding nonstandard features. You would probably have better luck shoehorning in such a feature if the syntax looked like this: (foo).bar(baz) foo being a value of some type that has methods, and bar being a method name. Another possibility is foo-bar(baz) I agree with Robert's opinion that it'd be unlikely the project would accept such a patch into core, but if you're mainly interested in it for research purposes that needn't deter you. Using an arrow definitely seems less problematic than using a dot. Dot means too many things already. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
On Mon, Jan 31, 2011 at 11:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: CREATE METHOD next_color (n INT) RETURNS INT FOR colored_part_t RETURN SELF.color_id + n SELECT partno, color_id, DEREF(oid).next_color(1) AS next FROM colored_parts DEREF(oid)? That's just bizarre. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
2011/2/1 Robert Haas robertmh...@gmail.com: On Mon, Jan 31, 2011 at 9:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: It would help if you were a bit more specific. Do you mean you want to write something like foo.bar(baz) and have that mean call the bar method of foo and pass it baz as an argument? If so, that'd certainly be possible to implement for purposes of a college course, if you're so inclined - after all it's free software - but we'd probably not make such a change to core PG, because right now that would mean call the function bar in schema baz and pass it foo as an argument. We try not to break people's code to when adding nonstandard features. You would probably have better luck shoehorning in such a feature if the syntax looked like this: (foo).bar(baz) foo being a value of some type that has methods, and bar being a method name. Another possibility is foo-bar(baz) I agree with Robert's opinion that it'd be unlikely the project would accept such a patch into core, but if you're mainly interested in it for research purposes that needn't deter you. Using an arrow definitely seems less problematic than using a dot. Dot means too many things already. sure, but it's out of standard :( Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions support for pg_dump, patch v27
Itagaki Takahiro itagaki.takah...@gmail.com writes: Hi, the attached is a further cleanup of the latest commit (1db20cdd36cb1c2cc5ef2210a23b3c09f5058690). Thanks! Given that the patch contains some merging from master's branch, I'm not sure if I should apply it to my repository then handle conflicts, or let you manage the patch now? * Accept paths under $PGSHARE during CREATE EXTENSION in addition to normal paths in convert_and_check_filename(). I think we don't have to disallow normal file accesses in the case. * Rewrite pg_available_extensions() to use materialize mode. * Add a protection for nested CREATE EXTENSION calls. * Removed some unneeded changes in the patch: - utils/genfile.h (use DIR directly) - missing merges from master in guc.c - only #include changes in a few files Comments and documentation would need to be checked native English speakers. I cannot help you In this area, sorry. Thanks. I don't see the PATH modifications when reading the patch, though. There are last two issues before it goes to ready for committer. On Mon, Jan 31, 2011 at 19:21, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: * relocatable and schema seems to be duplicated options. They are not, really. I'd suggest to remove relocatable option if you won't add additional meanings to the relocatable but having schema case. The schema option only makes sense when the extension is *NOT* relocatable. Or, I'm still not sure why only adminpack is not relocatable. Is it possible to make all extensions to relocatable and add default_schema = pg_catalog to adminpack for backward-compatibility? The adminpack SQL script is hard-coding pg_catalog, so user won't be able to choose where to install it. Technically, they could still move the functions to another schema, but that would break pgAdmin AFAIUI, so the extension's author here *wants* to forbid the user to relocate the extension. And want to prevent to user from installing it where he wants in the first place. The option relocatable is allowing ALTER EXTENSION … SET SCHEMA, when the control files also specify the schema, then you can't choose where to install the extension in the first place. I don't think we can go to only 1 options here. From older mails: * Should we support absolute control file paths? Well I don't see no harm in allowing non-core compliant extension packaging, If you want to support absolute paths, you also need to adjust convert_and_check_filename() because it only allows to read files in $PGSHARE. So, absolute path support doesn't work actually. I prefer to remove absolute path support from script option because absolute paths are just unportable. I have no strong opinion here, ok for me. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
2011/2/1 Robert Haas robertmh...@gmail.com: On Mon, Jan 31, 2011 at 11:41 PM, Pavel Stehule pavel.steh...@gmail.com wrote: CREATE METHOD next_color (n INT) RETURNS INT FOR colored_part_t RETURN SELF.color_id + n SELECT partno, color_id, DEREF(oid).next_color(1) AS next FROM colored_parts DEREF(oid)? That's just bizarre. have to look on this topic more complex :). There are some papers. It's sadly so these features wasn't used more and world is controlled by ORMs like Hibernate and company :( We did a some OOP meta language - PL/pgSQL translator and lot of tasks was processed simply without deep SQL programming. It was a strange tool :) - compiler to PL/pgSQL in PL/pgSQL :) Regards Pavel Stehule -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Named restore points
Em 14-01-2011 17:41, Jaime Casanova escreveu:
Here is a patch that implements named restore points.
Sorry, I was swamped with work. :(
Your patch no longer applied so I rebased it and slightly modified it. Review
is below...
+ The default is to recover to the end of the WAL log.
+ The precise stopping point is also influenced by
+ xref linkend=recovery-target-inclusive.
+/para
This isn't valid. recovery_target_name are not influenced by
recovery_target_inclusive. Sentence removed.
+ static char recoveryStopNamedRestorePoint[MAXFNAMELEN];
Is MAXFNAMELEN appropriate? AFAICS it is used for file name length. [Looking
at code...] It seems to be used for backup label too so it is not so
inappropriate.
+ typedef struct xl_named_restore_points
+ {
+ TimestampTz xtime;
+ charname[MAXFNAMELEN];
+ } xl_named_restore_points;
+
I prefixed those struct members so it won't get confused elsewhere.
+ else if (recoveryTarget == RECOVERY_TARGET_NAME)
+ snprintf(buffer, sizeof(buffer),
+%s%u\t%s\t%s named restore point %s\n,
+(srcfd 0) ? : \n,
+parentTLI,
+xlogfname,
+recoveryStopAfter ? after : before,
+recoveryStopNamedRestorePoint);
It doesn't matter if it is after or before the restore point. After/Before
only make sense when we're dealing with transaction or time. Removed.
else if (strcmp(item-name, recovery_target_xid) == 0)
{
+ /*
+* if recovery_target_name specified, then this
overrides
+* recovery_target_xid
+*/
+ if (recoveryTarget == RECOVERY_TARGET_NAME)
+ continue;
+
IMHO the right recovery precedence is xid - name - time. If you're
specifying xid that's because you know what you are doing. Name takes
precedence over time because it is easier to remember a name than a time. I
implemented this order in the updated patch.
+ recoveryTargetName = pstrdup(item-value);
I also added a check for long names.
+ if ((record-xl_rmid == RM_XLOG_ID) (record_info ==
XLOG_RESTORE_POINT))
+ couldStop = true;
+
+ if (!couldStop)
+ return false;
+
I reworked this code path because it seems confusing.
+ recordNamedRestorePoint = (xl_named_restore_points *)
XLogRecGetData(record);
+ recordXtime = recordNamedRestorePoint-xtime;
Why don't you store the named restore point here too? You will need it a few
lines below.
+ char name[MAXFNAMELEN];
+
+ memcpy(xlrec, rec, sizeof(xl_named_restore_points));
+ strncpy(name, xlrec.name, MAXFNAMELEN);
Is it really necessary? I removed it.
+ Datum
+ pg_create_restore_point(PG_FUNCTION_ARGS)
+ {
You should have added a check for long restore point names. Added in the
updated patch.
+ ereport(NOTICE,
+ (errmsg(WAL archiving is not enabled; you must ensure that
WAL segments are copied through other means for restore points to be usefull
for you)));
+
Sentence was rewritten as WAL archiving is not enabled; you must ensure that
WAL segments are copied through other means to recover up to named restore point.
Finally, this is a nice feature iif we have a way to know what named restore
points are available. DBAs need to take note of this list (that is not good)
and the lazy ones will have a hard time to recover the right name (possibly
with a xlog dump tool).
So how could we store this information? Perhaps a file in
$PGDATA/pg_xlog/restore_label that contains the label (and possibly the WAL
location). Also it must have a way to transmit the restore_label when we add
another restore point. I didn't implement this part (Jaime?) and it seems as
important as the new xlog record type that is in the patch. It seems
complicate but I don't have ideas. Anyone? The restore point names could be
obtained by querying a function (say, pg_restore_point_names or
pg_restore_point_list).
Someone could argue that this feature could be reached if we store label and
WAL location in a file (say restore_label). This mechanism doesn't need a new
WAL record but the downside is that if we lost restore_label we are dead. I'm
not in favor of this approach because it seems too fragile.
I will mark this patch waiting on author because of those open issues.
This patch needs to bump catalog version because of the new function. I'm not
sure if the new record type requires bumping the xlog magic number.
I'm attaching the updated patch and two scripts that I used to play with the
patch.
--
Euler Taveira de Oliveira
http://www.timbira.com/
a.sh
Description:
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
On Tue, Feb 1, 2011 at 10:11 AM, Peter Eisentraut pete...@gmx.net wrote: The SQL standard has the method invocation clause that appears to allow: ...something.column.method(args) Good luck finding out how to interpret the dots, but it's specified somewhere. My head just exploded. It'd be kind of nice as a syntax and namespacing alternative, actually, but figuring out the compatibility problems would be a headache. No joke. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
On mån, 2011-01-31 at 21:53 -0500, Tom Lane wrote: You would probably have better luck shoehorning in such a feature if the syntax looked like this: (foo).bar(baz) foo being a value of some type that has methods, and bar being a method name. The SQL standard has the method invocation clause that appears to allow: ...something.column.method(args) Good luck finding out how to interpret the dots, but it's specified somewhere. It'd be kind of nice as a syntax and namespacing alternative, actually, but figuring out the compatibility problems would be a headache. Another possibility is foo-bar(baz) This is in the SQL standard under attribute or method reference, but it requires the left side to be of a reference type, which is something that we don't have. -- 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] Spread checkpoint sync
Greg Smith wrote:
I think the right way to compute relations to sync is to finish the
sorted writes patch I sent over a not quite right yet update to already
Attached update now makes much more sense than the misguided patch I
submitted two weesk ago. This takes the original sorted write code,
first adjusting it so it only allocates the memory its tag structure is
stored in once (in a kind of lazy way I can improve on right now). It
then computes a bunch of derived statistics from a single walk of the
sorted data on each pass through. Here's an example of what comes out:
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11809.0_0
DEBUG: BufferSync 2 dirty blocks in relation.segment_fork 11811.0_0
DEBUG: BufferSync 3 dirty blocks in relation.segment_fork 11812.0_0
DEBUG: BufferSync 3 dirty blocks in relation.segment_fork 16496.0_0
DEBUG: BufferSync 28 dirty blocks in relation.segment_fork 16499.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11638.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11640.0_0
DEBUG: BufferSync 2 dirty blocks in relation.segment_fork 11641.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11642.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11644.0_0
DEBUG: BufferSync 2048 dirty blocks in relation.segment_fork 16508.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11645.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11661.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11663.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11664.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11672.0_0
DEBUG: BufferSync 1 dirty blocks in relation.segment_fork 11685.0_0
DEBUG: BufferSync 2097 buffers to write, 17 total dirty segment file(s)
expected to need sync
This is the first checkpoint after starting to populate a new pgbench
database. The next four show it extending into new segments:
DEBUG: BufferSync 2048 dirty blocks in relation.segment_fork 16508.1_0
DEBUG: BufferSync 2048 buffers to write, 1 total dirty segment file(s)
expected to need sync
DEBUG: BufferSync 2048 dirty blocks in relation.segment_fork 16508.2_0
DEBUG: BufferSync 2048 buffers to write, 1 total dirty segment file(s)
expected to need sync
DEBUG: BufferSync 2048 dirty blocks in relation.segment_fork 16508.3_0
DEBUG: BufferSync 2048 buffers to write, 1 total dirty segment file(s)
expected to need sync
DEBUG: BufferSync 2048 dirty blocks in relation.segment_fork 16508.4_0
DEBUG: BufferSync 2048 buffers to write, 1 total dirty segment file(s)
expected to need sync
The fact that it's always showing 2048 dirty blocks on these makes me
think I'm computing something wrong still, but the general idea here is
working now. I had to use some magic from the md layer to let bufmgr.c
know how its writes were going to get mapped into file segments and
correspondingly fsync calls later. Not happy about breaking the API
encapsulation there, but don't see an easy way to compute that data at
the per-segment level--and it's not like that's going to change in the
near future anyway.
I like this approach for a providing a map of how to spread syncs out
for a couple of reasons:
-It computes data that could be used to drive sync spread timing in a
relatively short amount of simple code.
-You get write sorting at the database level helping out the OS.
Everything I've been seeing recently on benchmarks says Linux at least
needs all the help it can get in that regard, even if block order
doesn't necessarily align perfectly with disk order.
-It's obvious how to take this same data and build a future model where
the time allocated for fsyncs was proportional to how much that
particular relation was touched.
Benchmarks of just the impact of the sorting step and continued bug
swatting to follow.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index 1f89e52..ef9df7d 100644
*** a/src/backend/storage/buffer/bufmgr.c
--- b/src/backend/storage/buffer/bufmgr.c
***
*** 48,53
--- 48,63
#include utils/rel.h
#include utils/resowner.h
+ /*
+ * Checkpoint time mapping between the buffer id values and the associated
+ * buffer tags of dirty buffers to write
+ */
+ typedef struct BufAndTag
+ {
+ int buf_id;
+ BufferTag tag;
+ BlockNumber segNum;
+ } BufAndTag;
/* Note: these two macros only work on shared buffers, not local ones! */
#define BufHdrGetBlock(bufHdr) ((Block) (BufferBlocks + ((Size) (bufHdr)-buf_id) * BLCKSZ))
*** int target_prefetch_pages = 0;
*** 78,83
--- 88,96
static volatile BufferDesc *InProgressBuf = NULL;
Re: [HACKERS] [NOVICE] systable_getnext_ordered
hi,
I wrote:
y...@mwd.biglobe.ne.jp (YAMAMOTO Takashi) writes:
after systable_getnext_ordered returned NULL, is it ok to call it again?
I wouldn't rely on it working.
i'm wondering because inv_truncate seems to do it and expecting NULL.
Hmm, that may well be a bug. Have you tested it?
I looked at this a bit more closely, and basically the answer is that
inv_truncate is accidentally failing to fail. What will actually happen
if systable_getnext_ordered is called another time, at least when using
a btree index, is that it will start the same requested scan over again,
ie deliver all the tuples it did the first time (which is none, in this
case). That's an implementation artifact, but since the behavior is
undefined in the first place, it's not wrong.
Now, if inv_truncate's initial call on systable_getnext_ordered returns
NULL (ie, the truncation point is past the current EOF page), it will
fall through to the Write a brand new page code, which will create and
insert a partial page at the truncation point. It then goes to the
delete-all-remaining-pages loop. Because that starts a fresh scan with
the very same scan key conditions, you might expect that it would find
and delete the page it just inserted --- causing the apparent EOF of the
blob to be wrong afterwards. It accidentally fails to do that because
the new tuple postdates the snapshot it's scanning with. So the loop
terminates having found no matching tuples, and all is well.
So this code is confusing, inefficient (performing a useless search of
the index), only works because of an obscure consideration not explained
in the comments, and sets a bad precedent for people to follow. I'm
going to go change it to explicitly not do the final loop if the initial
search failed. It's not a bug, exactly, but it's sure lousy coding.
Thanks for pointing it out.
thanks for the quick investigation and fix.
the attached patch is to avoid unnecessary detoast'ing and EOF marker pages
when possible. does it make sense?
YAMAMOTO Takashi
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
diff --git a/src/backend/storage/large_object/inv_api.c
b/src/backend/storage/large_object/inv_api.c
index 01e3492..60a9c69 100644
--- a/src/backend/storage/large_object/inv_api.c
+++ b/src/backend/storage/large_object/inv_api.c
@@ -178,10 +178,14 @@ myLargeObjectExists(Oid loid, Snapshot snapshot)
static int32
getbytealen(bytea *data)
{
- Assert(!VARATT_IS_EXTENDED(data));
- if (VARSIZE(data) VARHDRSZ)
- elog(ERROR, invalid VARSIZE(data));
- return (VARSIZE(data) - VARHDRSZ);
+ Size size;
+
+ size = toast_raw_datum_size(PointerGetDatum(data));
+ if (size VARHDRSZ)
+ elog(ERROR, invalid size);
+ if (size VARHDRSZ + LOBLKSIZE)
+ elog(ERROR, too large page);
+ return (size - VARHDRSZ);
}
@@ -359,22 +363,12 @@ inv_getsize(LargeObjectDesc *obj_desc)
{
Form_pg_largeobject data;
bytea *datafield;
- boolpfreeit;
if (HeapTupleHasNulls(tuple)) /* paranoia */
elog(ERROR, null field found in pg_largeobject);
data = (Form_pg_largeobject) GETSTRUCT(tuple);
datafield = (data-data); /* see note at top of
file */
- pfreeit = false;
- if (VARATT_IS_EXTENDED(datafield))
- {
- datafield = (bytea *)
- heap_tuple_untoast_attr((struct varlena *)
datafield);
- pfreeit = true;
- }
lastbyte = data-pageno * LOBLKSIZE + getbytealen(datafield);
- if (pfreeit)
- pfree(datafield);
}
systable_endscan_ordered(sd);
@@ -724,8 +718,9 @@ inv_write(LargeObjectDesc *obj_desc, const char *buf, int
nbytes)
void
inv_truncate(LargeObjectDesc *obj_desc, int len)
{
- int32 pageno = (int32) (len / LOBLKSIZE);
- int off;
+ const int32 pageno = (int32) (len / LOBLKSIZE);
+ int32 reqpageno;
+ const int off = len % LOBLKSIZE; /* offset in the page */
ScanKeyData skey[2];
SysScanDesc sd;
HeapTuple oldtuple;
@@ -741,6 +736,7 @@ inv_truncate(LargeObjectDesc *obj_desc, int len)
Datum values[Natts_pg_largeobject];
boolnulls[Natts_pg_largeobject];
boolreplace[Natts_pg_largeobject];
+ boolprevpagefull;
CatalogIndexState indstate;
Assert(PointerIsValid(obj_desc));
@@ -770,10 +766,20 @@ inv_truncate(LargeObjectDesc *obj_desc, int len)
Re: [HACKERS] [NOVICE] systable_getnext_ordered
hi, thanks for taking a look. y...@mwd.biglobe.ne.jp (YAMAMOTO Takashi) writes: the attached patch is to avoid unnecessary detoast'ing and EOF marker pages when possible. does it make sense? The blob page size is already chosen not to allow for out-of-line storage, not to mention that pg_largeobject doesn't have a TOAST table. So I think avoiding detoasting is largely a waste of time. doesn't detoasting involve decompression? I'm unexcited about the other consideration too --- it looks to me like it just makes truncation slower, more complicated, and hence more bug-prone, in return for a possible speedup that probably nobody will ever notice. slower? it depends, i guess. my primary motivation of that part of the patch was to save some space for certain workloads. (besides that, leaving unnecessary rows isn't neat, but it might be a matter of taste.) YAMAMOTO Takashi regards, tom lane -- Sent via pgsql-novice mailing list (pgsql-nov...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice -- 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] Error code for terminating connection due to conflict with recovery
Simon Riggs si...@2ndquadrant.com writes: Here's the basic patch, will work on the refactoring if no objections. ResolveRecoveryConflictWithDatabase works when you're not in recovery? That seems pretty fragile at best. In any case, this is a 9.2 feature at the earliest, please do not expect people to spend time on it now. 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] Optimize PL/Perl function argument passing [PATCH]
On Mon, Jan 31, 2011 at 12:22, Andrew Dunstan and...@dunslane.net wrote: This looks pretty good. But why are we bothering to keep $prolog at all any more, if all we're going to pass it is PL_sv_no all the time? Maybe we'll have a use for it in the future, but right now we don't appear to unless I'm missing something. I don't see any reason to keep it around. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI patch version 14
Jeff Davis pg...@j-davis.com wrote: One thing that confused me a little about the code is the default case at the end. The enum is exhaustive, so the default doesn't really make sense. The compiler warning you are silencing is the uninitialized variable xid (right?) Right. Since you have the Assert(TransactionIdIsValid(xid)) there anyway, why not just initialize xid to InvalidTransactionId and get rid of the default case? I feel a little better keeping even that trivial work out of the code path if possible, and it seems less confusing to me on the default case than up front. I'll improve the comment. I assume the Assert(false) is there to detect if someone adds a new enum value, but the compiler should issue a warning in that case anyway My compiler doesn't. Would it make sense to elog here, rather than Assert? I'm not clear on the rules for that. I'll push something that way for review and comment. If it's wrong, I'll change it. This is all really minor stuff, obviously. In a million line code base, I hate to call anything which affects readability minor. ;-) Also, from a code standpoint, it might be possible to early return in the HEAPTUPLE_RECENTLY_DEAD case where visible=false. Yeah, good point. It seems worth testing a bool there. A small push dealing with all the above issues and adding a little to comments: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=538ff57691256de0341e22513f59e9dc4dfd998f Let me know if any of that still needs work to avoid confusion and comply with PostgreSQL coding conventions. Like I said, I'm not totally clear whether elog is right here, but it seems to me a conceptually similar case to some I found elsewhere that elog was used. -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] arrays as pl/perl input arguments [PATCH]
On Mon, Jan 31, 2011 at 01:34, Alexey Klyukin al...@commandprompt.com wrote: I've looked at the patch and added a test for arrays exceeding or equal maximum dimensions to check, whether the recursive function won't bring surprises there. I've also added check_stack_depth calls to both split_array and plperl_hash_from_tuple. Note that the regression fails currently due to the incorrect error reporting in PostgreSQL, per http://archives.postgresql.org/pgsql-hackers/2011-01/msg02888.php. Looks good. Marked as Ready for committer -- 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] Extensions support for pg_dump, patch v27
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Itagaki Takahiro itagaki.takah...@gmail.com writes: Hi, the attached is a further cleanup of the latest commit (1db20cdd36cb1c2cc5ef2210a23b3c09f5058690). Thanks! Given that the patch contains some merging from master's branch, I'm not sure if I should apply it to my repository then handle conflicts, or let you manage the patch now? Actually, I was about to pick up and start working on the whole extensions patch series, but now I'm confused as to what and where is the latest version. 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] Authentication Enhancement Proposal
Christopher Hotchkiss christopher.hotchk...@gmail.com writes: I would like to propose (and volunteer to do if its considered to be a decent idea) to extend the mapping of users to roles in the pg_ident.conf to incorporate groups. Um ... there isn't any real distinction between users and groups anymore, they're all roles. So it's not clear to me what you're proposing that doesn't work now. Or at least could be made to work, possibly not in quite the way you're thinking, but using the already existing features. 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] FPI
Robert Haas robertmh...@gmail.com writes: So I'm back to proposing that we just apply FPI-free WAL records unconditionally, without regard to the LSN. This could potentially corrupt the page, of course. Yes. So you're still assuming that there will be a later FPI-containing WAL record to fix up the mess you created. What if there isn't? 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] Spread checkpoint sync
On Mon, Jan 31, 2011 at 4:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Back to the idea at hand - I proposed something a bit along these lines upthread, but my idea was to proactively perform the fsyncs on the relations that had gone the longest without a write, rather than the ones with the most dirty data. Yeah. What I meant to suggest, but evidently didn't explain well, was to use that or something much like it as the rule for deciding *what* to fsync next, but to use amount-of-unsynced-data-versus-threshold as the method for deciding *when* to do the next fsync. Oh, I see. Yeah, that could be a good algorithm. I also think Bruce's idea of calling fsync() on each relation just *before* we start writing the pages from that relation might have some merit. (I'm assuming here that we are sorting the writes.) That should tend to result in the end-of-checkpoint fsyncs being quite fast, because we'll only have as much dirty data floating around as we actually wrote during the checkpoint, which according to Greg Smith is usually a small fraction of the total data in need of flushing. Also, if one of the pre-write fsyncs takes a long time, then that'll get factored into our calculations of how fast we need to write the remaining data to finish the checkpoint on schedule. Of course there's still the possibility that the I/O system literally can't finish a checkpoint in X minutes, but even in that case, the I/O saturation will hopefully be more spread out across the entire checkpoint instead of falling like a hammer at the very end. Back to your idea: One problem with trying to bound the unflushed data is that it's not clear what the bound should be. I've had this mental model where we want the OS to write out pages to disk, but that's not always true, per Greg Smith's recent posts about Linux kernel tuning slowing down VACUUM. A possible advantage of the Momjian algorithm (as it's known in the literature) is that we don't actually start forcing anything out to disk until we have a reason to do so - namely, an impending checkpoint. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FPI
On Tue, Feb 1, 2011 at 12:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So I'm back to proposing that we just apply FPI-free WAL records unconditionally, without regard to the LSN. This could potentially corrupt the page, of course. Yes. So you're still assuming that there will be a later FPI-containing WAL record to fix up the mess you created. What if there isn't? In that case, the page shouldn't be corrupted. The possibility of corruption comes from the fact that a future WAL record might rearrange the page contents so that the current WAL record is no longer applying to the set of tuples it expects to be seeing. But any such action would necessarily induce an FPI. If there is no such action, then how can the page get into a state where replaying a heap delete will corrupt it? For that to happen, the item pointer list has to have changed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI patch version 14
On Tue, 2011-02-01 at 11:01 -0600, Kevin Grittner wrote: My compiler doesn't. Strange. Maybe it requires -O2? Would it make sense to elog here, rather than Assert? I'm not clear on the rules for that. elog looks fine there to me, assuming we have the default case. I'm not 100% clear on the rules, either. I think invalid input/corruption are usually elog (so they can be caught in non-assert builds); but other switch statements have them as well (unrecognized node...). A small push dealing with all the above issues and adding a little to comments: http://git.postgresql.org/gitweb?p=users/kgrittn/postgres.git;a=commitdiff;h=538ff57691256de0341e22513f59e9dc4dfd998f Let me know if any of that still needs work to avoid confusion and comply with PostgreSQL coding conventions. Like I said, I'm not totally clear whether elog is right here, but it seems to me a conceptually similar case to some I found elsewhere that elog was used. Looks good. It also looks like it contains a bugfix for subtransactions, right? Regards, Jeff Davis -- 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] Spread checkpoint sync
Robert Haas robertmh...@gmail.com wrote: I also think Bruce's idea of calling fsync() on each relation just *before* we start writing the pages from that relation might have some merit. What bothers me about that is that you may have a lot of the same dirty pages in the OS cache as the PostgreSQL cache, and you've just ensured that the OS will write those *twice*. I'm pretty sure that the reason the aggressive background writer settings we use have not caused any noticeable increase in OS disk writes is that many PostgreSQL writes of the same buffer keep an OS buffer page from becoming stale enough to get flushed until PostgreSQL writes to it taper off. Calling fsync() right before doing one last push of the data could be really pessimal for some workloads. -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] log_hostname and pg_stat_activity
On tis, 2011-01-18 at 19:24 -0500, Steve Singer wrote: However if I connect with a line in pg_hba that matches on an IP network then my client_hostname is always null unless log_hostname is set to true. This is consistent with the behavior you describe but I think the average user will find it a bit confusing. Having a column that is always null unless a GUC is set is less than ideal but I understand why log_hostname isn't on by default. Well, we have all these track_* variables, which also control what appears in the statistics views. After thinking about this some more, I think it might be better to be less cute and forget about the interaction with the pg_hba.conf hostname behavior. That is, the host name is set if and only if log_hostname is on. Otherwise you will for example have an inconsistency between the statistics views and the server log, unless you want to argue that we can override the log_hostname setting based on what happens in pg_hba.conf. That's just getting too weird. -- 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_upgrade fails for non-postgres user
Magnus Hagander wrote: On Tue, Feb 1, 2011 at 02:25, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: I just tried doing pg_upgrade on a database when logged in as user mha rather than postgres on my system. And it failed. Even though the db was initialized with superuser mha. The reason for this was that pg_upgrade tried to connect to the database mha (hardcoded to be the db username), and that certainly didn't exist. When that was fixed, I realized the psql command to create the datanbases connect to database template1 only to immediately switch to database postgres, which also seems rather pointless. Attach patch makes it connect to the postgres database instead of $USER, and then also changes the psql command to actually use it. I know way too little about pg_upgrade to tell if this is fully safe, but it does fix the problem in my installation. I have found that this problem only affects PG 9.1 and is not part of released PG 9.0 because we don't restore pg_authid in 9.0 (we don't need to because we have no pg_largeobject_metadata table in PG 8.4). Ah, that explains why we haven't seen reports on this before. Yes. I wisely did not backpatch this: http://archives.postgresql.org/pgsql-hackers/2011-01/msg00531.php If I had, we might not have found the bug until we released a minor version, and then it might have taken months for another minor release to fix it, which would have cause pg_upgrade users months of problems. I have applied a modified version of your patch to always retore into the 'postgres' database rather than the OS user. ?I thought we created an os-user-named database, but it seems that database is always called 'postgres' but is owned by the OS user. ?That seems kind of inconsistent, but no matter. The whole reason for the postgres database is to provide a *predictable* name for people and tools to connect to, and possibly store things in. template1 works reasonably well for connect to, but not for store in - because it gets duplicated out to all new databases after that. OK, that makes sense. pg_upgrade _mostly_ just issues queries, both in the new and old cluster, and because the old cluster might not have a 'postgres' database (deleted), it seems best to do connections to template1 unless I need to create something. Which is also why it's a good reason to have it the default fo rconnect to either - because people will create object there by mistake, and then get it duplicated out to all new databases. OK. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ALTER EXTENSION UPGRADE, v3
Hi, PFA version 3 of the ALTER EXTENSION PATCH, cleaned and merged against recent HEAD and extension's branch from which I just produced the v30 patch. It includes a way to upgrade from null, that is from pre-9.1, and the specific upgrade files to achieve that for all contribs. That goes like this: dim=# \i ~/pgsql/exts/share/contrib/lo.sql CREATE DOMAIN CREATE FUNCTION CREATE FUNCTION dim=# create wrapper extension lo; CREATE EXTENSION dim=# alter extension lo upgrade; ALTER EXTENSION dim=# alter extension lo set schema utils; ALTER EXTENSION dim=# \dx lo Objects in extension lo Object Description - function utils.lo_manage() function utils.lo_oid(utils.lo) type utils.lo (3 rows) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support upgrade_extension.v3.patch.gz Description: ALTER EXTENSION UPGRADE, v3 -- 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] Spread checkpoint sync
Robert Haas wrote: Back to your idea: One problem with trying to bound the unflushed data is that it's not clear what the bound should be. I've had this mental model where we want the OS to write out pages to disk, but that's not always true, per Greg Smith's recent posts about Linux kernel tuning slowing down VACUUM. A possible advantage of the Momjian algorithm (as it's known in the literature) is that we don't actually start forcing anything out to disk until we have a reason to do so - namely, an impending checkpoint. My trivial idea was: let's assume we checkpoint every 10 minutes, and it takes 5 minutes for us to write the data to the kernel. If no one else is writing to those files, we can safely wait maybe 5 more minutes before issuing the fsync. If, however, hundreds of writes are coming in for the same files in those final 5 minutes, we should fsync right away. My idea is that our delay between writes and fsync should somehow be controlled by how many writes to the same files are coming to the kernel while we are considering waiting because the only downside to delay is the accumulation of non-critical writes coming into the kernel for the same files we are going to fsync later. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI patch version 14
Jeff Davis pg...@j-davis.com wrote: On Tue, 2011-02-01 at 11:01 -0600, Kevin Grittner wrote: My compiler doesn't. Strange. Maybe it requires -O2? That's not it; I see -O2 in my compiles. At any rate, I think the default clause is the best place to quash the warning because that leaves us with a warning if changes leave a path through the other options without setting xid. In other words, unconditionally setting a value before the switch could prevent warnings on actual bugs, and I don't see such a risk when it's on the default. A small push dealing with all the above issues and adding a little to comments: Looks good. It also looks like it contains a bugfix for subtransactions, right? I think it fixes a bug introduced in the push from late yesterday. In reviewing what went into the last push yesterday, it looked like I might have introduced an assertion failure for the case where there is a write to a row within a subtransaction and then row is read again after leaving the subtransaction. I didn't actually confirm that through testing, because it looked like the safe approach was better from a performance standpoint, anyway. That last check for our own xid after finding the top level xid comes before acquiring the LW lock and doing an HTAB lookup which aren't necessary in that case. Re-reading a row within the same transaction seems likely enough to make it worth that quick test before doing more expensive things. It did throw a scare into me, though. The last thing I want to do is destabilize things at this juncture. I'll try to be more conservative with changes from here out. -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] Spread checkpoint sync
Greg Smith wrote: Greg Smith wrote: I think the right way to compute relations to sync is to finish the sorted writes patch I sent over a not quite right yet update to already Attached update now makes much more sense than the misguided patch I submitted two weesk ago. This takes the original sorted write code, first adjusting it so it only allocates the memory its tag structure is stored in once (in a kind of lazy way I can improve on right now). It then computes a bunch of derived statistics from a single walk of the sorted data on each pass through. Here's an example of what comes out: In that patch, I would like to see a meta-comment explaining why the sorting is happening and what we hope to gain. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Spread checkpoint sync
On Tue, Feb 1, 2011 at 12:58 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: I also think Bruce's idea of calling fsync() on each relation just *before* we start writing the pages from that relation might have some merit. What bothers me about that is that you may have a lot of the same dirty pages in the OS cache as the PostgreSQL cache, and you've just ensured that the OS will write those *twice*. I'm pretty sure that the reason the aggressive background writer settings we use have not caused any noticeable increase in OS disk writes is that many PostgreSQL writes of the same buffer keep an OS buffer page from becoming stale enough to get flushed until PostgreSQL writes to it taper off. Calling fsync() right before doing one last push of the data could be really pessimal for some workloads. I was thinking about what Greg reported here: http://archives.postgresql.org/pgsql-hackers/2010-11/msg01387.php If the amount of pre-checkpoint dirty data is 3GB and the checkpoint is writing 250MB, then you shouldn't have all that many extra writes... but you might have some, and that might be enough to send the whole thing down the tubes. InnoDB apparently handles this problem by advancing the redo pointer in small steps instead of in large jumps. AIUI, in addition to tracking the LSN of each page, they also track the first-dirtied LSN. That lets you checkpoint to an arbitrary LSN by flushing just the pages with an older first-dirtied LSN. So instead of doing a checkpoint every hour, you might do a mini-checkpoint every 10 minutes. Since the mini-checkpoints each need to flush less data, they should be less disruptive than a full checkpoint. But that, too, will generate some extra writes. Basically, any idea that involves calling fsync() more often is going to tend to smooth out the I/O load at the cost of some increase in the total number of writes. If we don't want any increase at all in the number of writes, spreading out the fsync() calls is pretty much the only other option. I'm worried that even with good tuning that won't be enough to tamp down the latency spikes. But maybe it will be... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Spread checkpoint sync
Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: I also think Bruce's idea of calling fsync() on each relation just *before* we start writing the pages from that relation might have some merit. What bothers me about that is that you may have a lot of the same dirty pages in the OS cache as the PostgreSQL cache, and you've just ensured that the OS will write those *twice*. I'm pretty sure that the reason the aggressive background writer settings we use have not caused any noticeable increase in OS disk writes is that many PostgreSQL writes of the same buffer keep an OS buffer page from becoming stale enough to get flushed until PostgreSQL writes to it taper off. Calling fsync() right before doing one last push of the data could be really pessimal for some workloads. OK, maybe my idea needs to be adjusted and we should trigger an early fsync if non-fsync writes are coming in for blocks _other_ than the ones we already wrote for that checkpoint. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] log_hostname and pg_stat_activity
On Tue, Feb 1, 2011 at 1:09 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-01-18 at 19:24 -0500, Steve Singer wrote: However if I connect with a line in pg_hba that matches on an IP network then my client_hostname is always null unless log_hostname is set to true. This is consistent with the behavior you describe but I think the average user will find it a bit confusing. Having a column that is always null unless a GUC is set is less than ideal but I understand why log_hostname isn't on by default. Well, we have all these track_* variables, which also control what appears in the statistics views. After thinking about this some more, I think it might be better to be less cute and forget about the interaction with the pg_hba.conf hostname behavior. That is, the host name is set if and only if log_hostname is on. +1 for doing it that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Spread checkpoint sync
Bruce Momjian br...@momjian.us writes: My trivial idea was: let's assume we checkpoint every 10 minutes, and it takes 5 minutes for us to write the data to the kernel. If no one else is writing to those files, we can safely wait maybe 5 more minutes before issuing the fsync. If, however, hundreds of writes are coming in for the same files in those final 5 minutes, we should fsync right away. Huh? I would surely hope we could assume that nobody but Postgres is writing the database files? Or are you considering that the bgwriter doesn't know exactly what the backends are doing? That's true, but I still maintain that we should design the bgwriter's behavior on the assumption that writes from backends are negligible. Certainly the backends aren't issuing fsyncs. 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] Spread checkpoint sync
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: My trivial idea was: let's assume we checkpoint every 10 minutes, and it takes 5 minutes for us to write the data to the kernel. If no one else is writing to those files, we can safely wait maybe 5 more minutes before issuing the fsync. If, however, hundreds of writes are coming in for the same files in those final 5 minutes, we should fsync right away. Huh? I would surely hope we could assume that nobody but Postgres is writing the database files? Or are you considering that the bgwriter doesn't know exactly what the backends are doing? That's true, but I still maintain that we should design the bgwriter's behavior on the assumption that writes from backends are negligible. Certainly the backends aren't issuing fsyncs. Right, no one else is writing but us. When I said no one else I meant no other bgwrites writes are going to the files we wrote as part of the checkpoint, but have not fsync'ed yet. I assume we have two write streams --- the checkpoint writes, which we know at the start of the checkpoint, and the bgwriter writes that are happening in an unpredictable way based on database activity. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Authentication Enhancement Proposal
On Feb 1, 2011, at 12:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Christopher Hotchkiss christopher.hotchk...@gmail.com writes: I would like to propose (and volunteer to do if its considered to be a decent idea) to extend the mapping of users to roles in the pg_ident.conf to incorporate groups. Um ... there isn't any real distinction between users and groups anymore, they're all roles. So it's not clear to me what you're proposing that doesn't work now. Or at least could be made to work, possibly not in quite the way you're thinking, but using the already existing features. regards, tom lane I'm sorry I wasn't clear Tom. I was referring to allowing the mapping of operating system users/groups to postgres roles. Today as far as I can tell only os users are mappable not the groups. Thoughts? Thanks, Christopher Hotchkiss -- 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] Authentication Enhancement Proposal
* Christopher Hotchkiss wrote: I would like to propose (and volunteer to do if its considered to be a decent idea) to extend the mapping of users to roles in the pg_ident.conf to incorporate groups. This would allow any user who belonged to a particular group in certain authentication systems to be Be aware that of the ten authentication mechanisms PostgreSQL supports today, only SSPI (yes, really) can provide you a group list directly from the authentication result. For everything else, you would have to have a hook for plugging in system-specific code for determining the group memberships. mapped to a role using the existing regular expression support that exists today. This would also allow the offloading of the creation of So this would still result in only one active role? How about taking all the groups and using them as roles, without considering pg_auth_members at all? new users for the system to an external mechanism instead of needing to create a new role in the database for each person. At the same time by allowing the mapping to match based off of groups the offloading of authentication would still allow for restrictions of who could connect to the database. How? If you delegate the decision on what is a valid user to the external mechanism and take pg_authid out of the picture, then everyone must be let in, and have the privileges assigned to PUBLIC at least. Sure, pg_hba.conf would still apply, but in practice everybody would end up with all users. Look at what SQL Server does. I'm sure they would rather get rid of their own user management and leave it all to the OS. Yet even though they can grant privileges to OS groups, that still only works by explicitly mapping them to database-internal authentication IDs. I think this may well be the reason for that. A second enhancement that would be useful would be despite what role the database logs the user in as the server sets a read only session variable similar to application_name could store the system username or username plus groups for use in audit triggers. This rules out the use of connection pools, except if they reproduce the entire group mapping logic and collect client sessions based on what role they would end up in the database. -- Christian -- 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] Authentication Enhancement Proposal
On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrich ch...@chrullrich.net wrote: * Christopher Hotchkiss wrote: I would like to propose (and volunteer to do if its considered to be a decent idea) to extend the mapping of users to roles in the pg_ident.conf to incorporate groups. This would allow any user who belonged to a particular group in certain authentication systems to be Be aware that of the ten authentication mechanisms PostgreSQL supports today, only SSPI (yes, really) can provide you a group list directly from the authentication result. For everything else, you would have to have a hook for plugging in system-specific code for determining the group memberships. My environment is a SSPI environment and its api is where I got the idea from. I guess keeping Postgres portable would precluding using pam_groups (or another system specific method) get the same information out of the unix world. mapped to a role using the existing regular expression support that exists today. This would also allow the offloading of the creation of So this would still result in only one active role? How about taking all the groups and using them as roles, without considering pg_auth_members at all? I was planning to use a set of roles setup in postgres that could map to the os/sspi groups. Those roles would hold the grant information for the tables and functions. new users for the system to an external mechanism instead of needing to create a new role in the database for each person. At the same time by allowing the mapping to match based off of groups the offloading of authentication would still allow for restrictions of who could connect to the database. How? If you delegate the decision on what is a valid user to the external mechanism and take pg_authid out of the picture, then everyone must be let in, and have the privileges assigned to PUBLIC at least. Sure, pg_hba.conf would still apply, but in practice everybody would end up with all users. Look at what SQL Server does. I'm sure they would rather get rid of their own user management and leave it all to the OS. Yet even though they can grant privileges to OS groups, that still only works by explicitly mapping them to database-internal authentication IDs. I think this may well be the reason for that. Today as far as I can tell, when you setup SSPI if you have a valid user account the only way to restrict access to postgres is via the pg_hba or pg_ident files. This requires either configuring each user as a postgres role or mapping to a generic account (or many). This is driven entirely off of the user name and which makes administration painful since you have to tweak the pg_hba.conf or pg_ident.conf file for each user or you encode authorization information into the username which makes user removal equally painful. A second enhancement that would be useful would be despite what role the database logs the user in as the server sets a read only session variable similar to application_name could store the system username or username plus groups for use in audit triggers. This rules out the use of connection pools, except if they reproduce the entire group mapping logic and collect client sessions based on what role they would end up in the database. Thats true, in that case having the client set application_name would probably be a better route to communicate to the server the real user of the application. That approach makes sense for web applications where you can trust the code that is connecting to the database to communicate user information correctly. For a thick client however the user is logging into the system and could create a secondary database connection with a maliciously set username to fool an audit system. Thats why I thought this would be a useful enhancement. Thanks! -- Christopher Hotchkiss chotchki http://www.chotchki.us -- 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] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
On 02/01/2011 03:36 AM, Robert Haas wrote:
On Mon, Jan 31, 2011 at 5:40 PM, Nick Rudnickjoerg.rudn...@t-online.de wrote:
* In this regard it is of interest in how far there are principal efficiency
problems with the support of (deeply nested) object like structure by the
backend, or if the backend may be expected to do this job not terribly worse
then more specialized OODMS -- of course, I would be interested in any
discussions of these topics...
I simply don't know what a more-specialized OODBMS would do that is
similar to or different than what PostgreSQL does, so it's hard to
comment. I don't immediately see why we'd be any less efficient, but
without knowing what algorithms are in use on the other side, it's a
bit hard to say.
I assume this is a questions for experts in DB optimization -- I am
afraid that the indices or the query optimization might be suboptimal
for deeply nested structures -- on the other hand, it might be possible
that somebody would say that, with some WHISKY indices (;-)) or the
like, PostgreSQL would do good. After all, PostgreSQL (and I guess the
backend, too) is a very modular piece of software...
* The same question for doing rule bases on top of the PostgreSQL backend...
I'm not sure if you're referring to the type of rules added by the SQL
command CREATE RULE here, or some other kind of rule. But the rules
added by CREATE RULE are generally not too useful. Most serious
server programming is done using triggers.
For the kind usage of I am interested in please look:
http://en.wikipedia.org/wiki/Expert_system
http://en.wikipedia.org/wiki/Inference_engine
http://en.wikipedia.org/wiki/Deductive_database
http://en.wikipedia.org/wiki/Datalog
http://en.wikipedia.org/wiki/Forward_chaining
And yes, this can be done -- here an inelegant example (with many
obvious todos), demonstrating the simple «Colonel West example» of
Artificial Intelligence, a Modern Approach by Russell/Norvig in plain
PostgreSQL RULEs (in attachment, too):
= 8 ==
-- for primordial facts:
CREATE TABLE american(person text);
CREATE TABLE missile(thing text);
CREATE TABLE owns(owner text, property text);
CREATE TABLE enemy(person text, target text);
-- for derived facts:
CREATE TABLE weapon(thing text);
CREATE TABLE sells(seller text, thing text, buyer text);
CREATE TABLE hostile(person text);
CREATE TABLE criminal(person text);
-- rules:
CREATE RULE missile_is_a_weapon AS
ON INSERT TO missile
DO ALSO
INSERT INTO weapon SELECT NEW.thing;
CREATE RULE enemy_of_america_is_hostile AS
ON INSERT TO enemy WHERE NEW.target = 'America'
DO ALSO
INSERT INTO hostile SELECT NEW.person;
-- nono_can_get_missiles_only_from_west
CREATE RULE nono_can_get_missiles_only_from_west__missile AS
ON INSERT TO missile
DO ALSO
INSERT INTO sells
SELECT 'West' AS seller, NEW.thing, 'Nono' AS buyer
FROM owns WHERE owner='Nono' AND property=NEW.thing;
CREATE RULE nono_can_get_missiles_only_from_west__owns AS
ON INSERT TO owns WHERE NEW.owner='Nono'
DO ALSO
INSERT INTO sells
SELECT 'West' AS seller, NEW.property, 'Nono' AS buyer
FROM missile WHERE thing=NEW.property;
-- americans_selling_weapons_to_hostiles_are_criminal
CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__hostile AS
ON INSERT TO hostile
DO ALSO
INSERT INTO criminal
SELECT seller FROM sells, weapon, american
WHERE sells.buyer=NEW.person
AND sells.thing=weapon.thing
AND sells.seller=american.person;
CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__weapon AS
ON INSERT TO weapon
DO ALSO
INSERT INTO criminal
SELECT seller FROM sells, hostile, american
WHERE sells.buyer=hostile.person
AND sells.thing=NEW.thing
AND sells.seller=american.person;
CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__american AS
ON INSERT TO american
DO ALSO
INSERT INTO criminal
SELECT seller FROM sells, hostile, weapon
WHERE sells.buyer=hostile.person
AND sells.thing=weapon.thing
AND sells.seller=NEW.person;
CREATE RULE americans_selling_weapons_to_hostiles_are_criminal__sells AS
ON INSERT TO sells
DO ALSO
INSERT INTO criminal
SELECT NEW.seller FROM american, hostile, weapon
WHERE NEW.buyer=hostile.person
AND NEW.thing=weapon.thing
AND NEW.seller=american.person;
-- entering some facts now:
INSERT INTO missile VALUES('M1');
INSERT INTO enemy VALUES('Nono','America');
INSERT INTO owns VALUES('Nono','M1');
INSERT INTO american VALUES('West');
-- querying the database:
SELECT * FROM criminal;
= 8 ==
If this could be done efficiently, it would allow many interesting
applications -- I guess that
Re: [HACKERS] REVIEW: PL/Python validator function
On ons, 2011-01-19 at 10:16 +0900, Hitoshi Harada wrote: Thanks. I tested the new version and looks ok. I'll mark it Ready for Commiter. Committed. -- 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] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
Nick Rudnick joerg.rudn...@t-online.de wrote: here an inelegant example Based on that example, you should be sure to look at the INHERITS clause of CREATE TABLE: http://www.postgresql.org/docs/current/interactive/sql-createtable.html PostgreSQL has the is a structure built in. That may not get you all the way there, but between that and a few views, you might get close without needing a lot of low level infrastructure work. -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] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
Hi Pavel, I guess this represents most exactly what this professor is thinking about -- being able to create methods and types with methods which can be nested -- but syntactical details are of secondary importance. All the best, Nick On 02/01/2011 05:43 AM, Pavel Stehule wrote: Hello it is part of ANSi SQL 2003 http://savage.net.au/SQL/sql-2003-2.bnf.html#method%20specification%20designator 2011/2/1 Pavel Stehulepavel.steh...@gmail.com: 2011/2/1 Robert Haasrobertmh...@gmail.com: On Mon, Jan 31, 2011 at 5:09 PM, Nick Rudnickjoerg.rudn...@t-online.de wrote: Interesting... I remember that some years ago, I fiddled around with functions, operators etc. to allow a method like syntax -- but I ever was worried this approach would have serious weaknesses -- are there any principal hindrances to having methods, if no, can this be implemented in a straightforward way? It would help if you were a bit more specific. Do you mean you want to write something like foo.bar(baz) and have that mean call the bar method of foo and pass it baz as an argument? If so, that'd certainly be possible to implement for purposes of a college course, if you're so inclined - after all it's free software - but we'd probably not make such a change to core PG, because right now that would mean call the function bar in schema baz and pass it foo as an argument. We try not to break people's code to when adding nonstandard features. I has not a standard, so I am not sure what is in standard and what not. It was a popular theme about year 2000 and OOP was planed to SQL3. You can find a some presentation from this time. Oracle implemented these features. J. Melton: SQL:1999: Understanding Object-Relational and Other Advanced Features, Morgan Kaufmann, 2003. CREATE METHOD next_color (n INT) RETURNS INT FOR colored_part_t RETURN SELF.color_id + n SELECT partno, color_id, DEREF(oid).next_color(1) AS next FROM colored_parts some other databases implemented a dereferenced data (it's not only Oracle's subject) http://www.java2s.com/Code/Oracle/Object-Oriented-Database/DEREFDereferencetheRowAddresses.htm Probably DB2 implements this functionality too. See doc for CREATE TYPE statement, REF USING, NOT FINAL, method specification CREATE TYPE type-name ... METHOD attribute-name() RETURNS attribute-type these features are very nice - but is not well documented and probably not used. Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
Hi Peter, Another possibility is foo-bar(baz) This is in the SQL standard underattribute or method reference, but it requires the left side to be of a reference type, which is something that we don't have. I think this is the point where I stopped my efforts in the past -- I guessed that a reference, in PostgreSQL relational algebra, could be a pair of a pg_class oid together with the object's oid (having to query the pg_class oid each time seemed very expensive to me, then). I fiddled around with a little C programming, then I lost confidence in whether I was doing something reasonable -- I was afraid I did not know enough about the internals to predict a convincing outcome. All the best, Nick -- 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] [pgsql-general 2011-1-21:] Are there any projects interested in object functionality? (+ rule bases)
Hi Kevin, this example was for teaching AI students (with limited PostgreSQL knowledge) in a very basic lecture -- I did not want to tweak the SQL semantics too much; just demonstrate why SQL is rightfully called a 4GL language. ;-) Cheers, Nick On 02/01/2011 10:08 PM, Kevin Grittner wrote: Nick Rudnickjoerg.rudn...@t-online.de wrote: here an inelegant example Based on that example, you should be sure to look at the INHERITS clause of CREATE TABLE: http://www.postgresql.org/docs/current/interactive/sql-createtable.html PostgreSQL has the is a structure built in. That may not get you all the way there, but between that and a few views, you might get close without needing a lot of low level infrastructure work. -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] Authentication Enhancement Proposal
* Christopher Hotchkiss wrote: On Tue, Feb 1, 2011 at 2:49 PM, Christian Ullrichch...@chrullrich.net wrote: * Christopher Hotchkiss wrote: I would like to propose (and volunteer to do if its considered to be a decent idea) to extend the mapping of users to roles in the pg_ident.conf to incorporate groups. This would allow any user who belonged to a particular group in certain authentication systems to be Be aware that of the ten authentication mechanisms PostgreSQL supports today, only SSPI (yes, really) can provide you a group list directly from the authentication result. For everything else, you would have to have a hook for plugging in system-specific code for determining the group memberships. My environment is a SSPI environment and its api is where I got the idea from. I guess keeping Postgres portable would precluding using pam_groups (or another system specific method) get the same information out of the unix world. Well, I think if you build with PAM support there is no reason not to use PAM features to get the group membership information, as long as you can handle the situation where the PAM stack does not contain the module you need. mapped to a role using the existing regular expression support that exists today. This would also allow the offloading of the creation of So this would still result in only one active role? How about taking all the groups and using them as roles, without considering pg_auth_members at all? I was planning to use a set of roles setup in postgres that could map to the os/sspi groups. Those roles would hold the grant information for the tables and functions. So you want to separate authentication and authorization identity, and use the system user name as the authentication identity, but choose the authorization identity based on some ruleset applied to the group memberships of the authenticated system user. The purpose of pg_ident.conf currently is to declare combinations of authenticated user name and claimed database user name (from the hello packet) that are allowed to connect. What you would need is a ruleset that says if these conditions are met, the user will be assigned these roles. The claimed user name from the hello packet would be irrelevant, and the client would have no control over which identity it would use in the database, except that it could SET ROLE later. new users for the system to an external mechanism instead of needing to create a new role in the database for each person. At the same time by allowing the mapping to match based off of groups the offloading of authentication would still allow for restrictions of who could connect to the database. How? If you delegate the decision on what is a valid user to the external mechanism and take pg_authid out of the picture, then everyone must be let in, and have the privileges assigned to PUBLIC at least. Sure, pg_hba.conf would still apply, but in practice everybody would end up with all users. Today as far as I can tell, when you setup SSPI if you have a valid user account the only way to restrict access to postgres is via the pg_hba or pg_ident files. This requires either configuring each user Not exactly. I just remembered there is a CONNECT privilege for databases, granted by default to PUBLIC. A second enhancement that would be useful would be despite what role the database logs the user in as the server sets a read only session variable similar to application_name could store the system username or username plus groups for use in audit triggers. This rules out the use of connection pools, except if they reproduce the entire group mapping logic and collect client sessions based on what role they would end up in the database. Thats true, in that case having the client set application_name would probably be a better route to communicate to the server the real user of the application. That approach makes sense for web applications where you can trust the code that is connecting to the database to communicate user information correctly. For a thick client however the user is logging into the system and could create a secondary database connection with a maliciously set username to fool an audit system. Thats why I thought this would be a useful enhancement. Postgres already has session_user (authenticated user name) and current_user (what the user last SET ROLE to). This would just add another one (connected_user?) -- Christian -- 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] [PERFORM] Slow count(*) again...
Tom Lane wrote: At this point what we've got is 25% of the runtime in nodeAgg.c overhead, and it's difficult to see how to get any real improvement without tackling that. Rather than apply the patch shown above, I'm tempted to think about hard-wiring COUNT(*) as a special case in nodeAgg.c such that we don't go through advance_aggregates/advance_transition_function at all, but just increment a counter directly. However, that would very clearly be optimizing COUNT(*) and nothing else. Given the opinions expressed elsewhere in this thread that heavy reliance on COUNT(*) represents bad application design, I'm not sure that such a patch would meet with general approval. Actually the patch shown above is optimizing COUNT(*) and nothing else, too, since it's hard to conceive of any other zero-argument aggregate. Anyway, if anyone is hot to make COUNT(*) faster, that's where to look. I don't think any of the previous discussion in this thread is on-point at all, except for the parts where people suggested avoiding it. Do we want a TODO about optimizing COUNT(*) to avoid aggregate processing overhead? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [PERFORM] Slow count(*) again...
On 02/01/2011 05:47 PM, Bruce Momjian wrote: Tom Lane wrote: At this point what we've got is 25% of the runtime in nodeAgg.c overhead, and it's difficult to see how to get any real improvement without tackling that. Rather than apply the patch shown above, I'm tempted to think about hard-wiring COUNT(*) as a special case in nodeAgg.c such that we don't go through advance_aggregates/advance_transition_function at all, but just increment a counter directly. However, that would very clearly be optimizing COUNT(*) and nothing else. Given the opinions expressed elsewhere in this thread that heavy reliance on COUNT(*) represents bad application design, I'm not sure that such a patch would meet with general approval. Actually the patch shown above is optimizing COUNT(*) and nothing else, too, since it's hard to conceive of any other zero-argument aggregate. Anyway, if anyone is hot to make COUNT(*) faster, that's where to look. I don't think any of the previous discussion in this thread is on-point at all, except for the parts where people suggested avoiding it. Do we want a TODO about optimizing COUNT(*) to avoid aggregate processing overhead? Whether or not it's bad application design, it's ubiquitous, and we should make it work as best we can, IMNSHO. This often generates complaints about Postgres, and if we really plan for world domination this needs to be part of it. 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] [PERFORM] Slow count(*) again...
Andrew Dunstan and...@dunslane.net writes: On 02/01/2011 05:47 PM, Bruce Momjian wrote: Tom Lane wrote: At this point what we've got is 25% of the runtime in nodeAgg.c overhead, and it's difficult to see how to get any real improvement without tackling that. Do we want a TODO about optimizing COUNT(*) to avoid aggregate processing overhead? Whether or not it's bad application design, it's ubiquitous, and we should make it work as best we can, IMNSHO. This often generates complaints about Postgres, and if we really plan for world domination this needs to be part of it. I don't think that saving ~25% on COUNT(*) runtime will help that at all. The people who complain about it expect it to be instantaneous. If this sort of hack were free, I'd be all for doing it anyway; but I'm concerned that adding tests to enable a fast path will slow down every other aggregate, or else duplicate a lot of code that we'll then have to maintain. 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] [PERFORM] Slow count(*) again...
On 2/1/2011 5:47 PM, Bruce Momjian wrote: Do we want a TODO about optimizing COUNT(*) to avoid aggregate processing overhead? Definitely not. In my opinion, and I've seen more than a few database designs, having count(*) is almost always an error. If I am counting a large table like the one below, waiting for 30 seconds more is not going to make much of a difference. To paraphrase Kenny Rogers, it will be time enough for counting when the application is done. Timing is on. news= select count(*) from moreover_documents_y2011m01; count -- 20350907 (1 row) Time: 124142.437 ms news= -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] A postgres parser related question
Hi everyone, I just want to know something about postgresql parser, because I want to add a new feature for pgAdmin graphical query builder (GQB) that allow an user to create a query graphical model from a sql statment, and I just want to reuse postgres parser code (reuse this) to do the task of create an abstract tree like structure for the sql statment. I just remember that read at some place that I don't remember that the syntax and semantic checks are done in two different stages, and as I can understand, then is possible to just reuse only the parser stage in an easy way, but before even trying to do this task, I want some advices from people that know a lot more than me from postgres internals. Thanks in advance. Regards, Luis Ochoa.
Re: [HACKERS] pg_dump directory archive format / parallel pg_dump
On Sun, Jan 30, 2011 at 5:26 PM, Robert Haas robertmh...@gmail.com wrote: The parallel pg_dump portion of this patch (i.e. the still-uncommitted part) no longer applies. Please rebase. Here is a rebased version with some minor changes as well. I haven't tested it on Windows now but will do so as soon as the Unix part has been reviewed. Joachim parallel_pg_dump.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
