Re: [HACKERS] Singleton range constructors versus functional coercion notation
On 21 November 2011 14:55, Tom Lane wrote: > Robert Haas writes: >> On Nov 20, 2011, at 10:24 PM, Jeff Davis wrote: >>> Well, if there were a good shorter notation, then probably so. But it >>> doesn't look like we have a good idea, so I'm fine with dropping it. > >> We should also keep in mind that people who use range types can and likely >> will define their own convenience functions. If people use singletons, or >> open ranges, or closed ranges, or one-hour timestamp ranges frequently, they >> can make their own notational shorthand with a 3-line CREATE FUNCTION >> statement. We don't need to have it all in core. > > But if you believe that, what syntax do you think people are likely to > try if they want a singleton range constructor? Leaving the user to > discover the problem and try to invent a workaround is not better than > doing it ourselves ... > In the field of mathematics, a standard shorthand notation for the degenerate interval [x,x] is {x} - the singleton set - so that's one possibility. Dean -- 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] explain analyze query execution time
Rudyar wrote: > I try to get the execution time of a query workload. I try using > explain analyze but this time is allways higher than the execution > time of a query across a client like pgadmin3 > > what is the reason about that difference? It's the "observer effect" -- there is a cost to the timing, counting, measuring, and reporting which is done by EXPLAIN ANALYZE, which distorts what is being measured. It's just about impossible to avoid entirely. -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] Rename a database that has connections
On 22/11/11 17:24, Mark Kirkwood wrote: On 22/11/11 16:41, Tom Lane wrote: Mark Kirkwood writes: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to restore a dump into new database and then rename the to-be-retired db out of the way and rename the newly restored one to take over. In many cases such scripts would be simplified if a database could be renamed without requiring its connections terminated. I've been asked several times if this could be added... so I've caved in a done a patch that allows this. The default behavior is unchanged - it is required to specify an additional trailing FORCE keyword to elicit the more brutal behavior. Note that existing connections to the renamed database are unaffected, but obviously SELECT current_database() returns the new name (in the next transaction). This patch seems to me to be pretty thoroughly misguided. Either renaming a database with open connections is safe, or it isn't. If it is safe, we should just allow it. If it isn't, making people write an extra FORCE keyword does not make it safe. It's particularly silly to allow someone to rename the database out from under other sessions (which won't know what happened) but not rename it out from under his own session (which would or at least could know it). What you need to be doing is investigating whether the comments about this in RenameDatabase() are really valid concerns or not. The reason I added FORCE was to preserve backwards compatibility - for any people out there that like the way it behaves right now. I am certainly willing to be convinced that such a concern is unneeded. You are quite right about the patch being inconsistent with respect to the renaming the current database, it should allow that too (will change if this overall approach makes sense). With respect to the concerns in RenameDatabase(), that seems to boil down to applications stashing the current dbname somewhere and caring about it. This was not viewed as a issue by any of the folks who I talked to about this (they are all application developers/architects etc so they understand that issue). However there may well be application frameworks out there that do care... which seemed to me to be another reason for making the forced rename require an extra keyword. I have not been able to find any other problems caused by this... renaming a db (many times) with hundreds of pgbench connections does not give rise to any issues. Minor change to be allow current database to be renamed as well if FORCE is used, which makes more sense. Cheers Mark diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml new file mode 100644 index 360732f..98ea473 *** a/doc/src/sgml/ref/alter_database.sgml --- b/doc/src/sgml/ref/alter_database.sgml *** ALTER DATABASE connlimit ! ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO new_owner --- 27,33 CONNECTION LIMIT connlimit ! ALTER DATABASE name RENAME TO new_name [ FORCE ] ALTER DATABASE name OWNER TO new_owner *** ALTER DATABASE newname = $6; $$ = (Node *)n; } ! | ALTER DATABASE database_name RENAME TO database_name { RenameStmt *n = makeNode(RenameStmt); n->renameType = OBJECT_DATABASE; n->subname = $3; n->newname = $6; $$ = (Node *)n; } | ALTER FUNCTION function_with_argtypes RENAME TO name --- 6437,6449 n->newname = $6; $$ = (Node *)n; } ! | ALTER DATABASE database_name RENAME TO database_name opt_force { RenameStmt *n = makeNode(RenameStmt); n->renameType = OBJECT_DATABASE; n->subname = $3; n->newname = $6; + n->force = $7; $$ = (Node *)n; } | ALTER FUNCTION function_with_argtypes RENAME TO name *** opt_column: COLUMN { $$ = COLUMN *** 6675,6680 --- 6673,6681 opt_set_data: SET DATA_P
[HACKERS] dblink: enable parameters
Hello I know so dblink is "deprecated" interface - but it has necessary functionality still - it support a writable statements. Very simple enhancing should be enable params to query - it's analogy to USING clause in EXECUTE statement. Regards Pavel Stehule -- 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] Rename a database that has connections
On 22/11/11 17:24, Mark Kirkwood wrote: I have not been able to find any other problems caused by this... renaming a db (many times) with hundreds of pgbench connections does not give rise to any issues. One point I did miss - the ps listing still uses the old dbname. pg_stat_activity is correct however. regards Mark -- 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] Rename a database that has connections
On 22/11/11 16:41, Tom Lane wrote: Mark Kirkwood writes: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to restore a dump into new database and then rename the to-be-retired db out of the way and rename the newly restored one to take over. In many cases such scripts would be simplified if a database could be renamed without requiring its connections terminated. I've been asked several times if this could be added... so I've caved in a done a patch that allows this. The default behavior is unchanged - it is required to specify an additional trailing FORCE keyword to elicit the more brutal behavior. Note that existing connections to the renamed database are unaffected, but obviously SELECT current_database() returns the new name (in the next transaction). This patch seems to me to be pretty thoroughly misguided. Either renaming a database with open connections is safe, or it isn't. If it is safe, we should just allow it. If it isn't, making people write an extra FORCE keyword does not make it safe. It's particularly silly to allow someone to rename the database out from under other sessions (which won't know what happened) but not rename it out from under his own session (which would or at least could know it). What you need to be doing is investigating whether the comments about this in RenameDatabase() are really valid concerns or not. The reason I added FORCE was to preserve backwards compatibility - for any people out there that like the way it behaves right now. I am certainly willing to be convinced that such a concern is unneeded. You are quite right about the patch being inconsistent with respect to the renaming the current database, it should allow that too (will change if this overall approach makes sense). With respect to the concerns in RenameDatabase(), that seems to boil down to applications stashing the current dbname somewhere and caring about it. This was not viewed as a issue by any of the folks who I talked to about this (they are all application developers/architects etc so they understand that issue). However there may well be application frameworks out there that do care... which seemed to me to be another reason for making the forced rename require an extra keyword. I have not been able to find any other problems caused by this... renaming a db (many times) with hundreds of pgbench connections does not give rise to any issues. regards Mark -- 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] explain analyze query execution time
On 11/21/11, Rudyar wrote: > Hello, > > I try to get the execution time of a query workload. I try using explain > analyze but this time is allways higher than > the execution time of a query across a client like pgadmin3 > > what is the reason about that difference? Analyze has to do a lot of gettimeofday calls and other bookkeeping, and that takes time. If the data queried is all in memory, this analyze overhead can be far more than the query itself. Jeff -- 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] strange nbtree corruption report
I wrote: > Noah Misch writes: >> Just a suspicion ... when looking at the B-tree page reclamation algorithm, I >> had a thought that the logic in _bt_page_recyclable() was obsolete as of the >> introduction (in 8.3) of xid-free read-only transactions. A transaction >> without a persistent xid does not hold back RecentXmin, so how could waiting >> for a RecentXmin window to pass prove that no scan still holds a link to the >> page? Similarly, running VACUUMs do not hold back RecentXmin. > Uh, sure they do. It's their advertised snapshot xmin that counts, not > their own xid (if any). No, wait a second, I think you're right. The existing mechanism should protect against transactions that might be updating the btree, so the worst possible consequences can't happen; but it seems possible that a read-only transaction in flight to the page could get confused and give wrong answers. That would only explain transient failures not persistent ones, though. 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] strange nbtree corruption report
Alvaro Herrera writes: > We got a very strange nbtree corruption report some time ago. This was > a btree index on a vey high churn table -- entries are updated and > deleted very quickly, so the index grows very large and also shrinks > quickly (AFAICT this is a work queue of sorts). > The most strange thing of all is that there was this error: > ERROR: left link changed unexpectedly in block 3378 of index "index_name" > CONTEXT: automatic vacuum of table "table_name" > This was reported not once, but several dozens of times, by each new > autovacuum worker that tried to vacuum the table. > As far as I can see, there is just no way for this to happen ... much > less happen repeatedly. It's not hard to believe that that would happen repeatedly given a corrupted set of sibling links, eg deletable page A links left to page B, which links right to C, which links right to A. The question is how the index got into such a state. A dropped update during a page split would explain it (ie, B used to be A's left sibling, then at some point B got split into B and C, but A's left-link never got updated on disk). I wonder how reliable their disk+filesystem is ... 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] Rename a database that has connections
Mark Kirkwood wrote: > On 22/11/11 16:38, Bruce Momjian wrote: > > Mark Kirkwood wrote: > >> I've been helping out several customers recently who all seem to be > >> wrestling with the same issue: wanting to update/refresh non-production > >> databases from the latest corresponding prod version. Typically they > >> have (fairly complex) scripts that at some point attempt to restore a > >> dump into new database and then rename the to-be-retired db out of the > >> way and rename the newly restored one to take over. > >> > >> In many cases such scripts would be simplified if a database could be > >> renamed without requiring its connections terminated. I've been asked > >> several times if this could be added... so I've caved in a done a patch > >> that allows this. > >> > >> The default behavior is unchanged - it is required to specify an > >> additional trailing FORCE keyword to elicit the more brutal behavior. > >> Note that existing connections to the renamed database are unaffected, > >> but obviously SELECT current_database() returns the new name (in the > >> next transaction). > > Uh, it isn't save to copy a database when someone else is connected. > > How does this address that issue? > > > > Copying a database is quite a different matter (compare with copying an > open unix file vs mv'ing it... the latter is quite safe as the inode > does not change). Oh, I see, you are just renaming. Well, Tom is right that either it is safe, or it isn't --- a 'force' flag makes no sense. -- Bruce Momjian http://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] Rename a database that has connections
On 22/11/11 16:38, Bruce Momjian wrote: Mark Kirkwood wrote: I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to restore a dump into new database and then rename the to-be-retired db out of the way and rename the newly restored one to take over. In many cases such scripts would be simplified if a database could be renamed without requiring its connections terminated. I've been asked several times if this could be added... so I've caved in a done a patch that allows this. The default behavior is unchanged - it is required to specify an additional trailing FORCE keyword to elicit the more brutal behavior. Note that existing connections to the renamed database are unaffected, but obviously SELECT current_database() returns the new name (in the next transaction). Uh, it isn't save to copy a database when someone else is connected. How does this address that issue? Copying a database is quite a different matter (compare with copying an open unix file vs mv'ing it... the latter is quite safe as the inode does not change). regards Mark -- 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] strange nbtree corruption report
Noah Misch writes: > Just a suspicion ... when looking at the B-tree page reclamation algorithm, I > had a thought that the logic in _bt_page_recyclable() was obsolete as of the > introduction (in 8.3) of xid-free read-only transactions. A transaction > without a persistent xid does not hold back RecentXmin, so how could waiting > for a RecentXmin window to pass prove that no scan still holds a link to the > page? Similarly, running VACUUMs do not hold back RecentXmin. Uh, sure they do. It's their advertised snapshot xmin that counts, not their own xid (if any). 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] Rename a database that has connections
Mark Kirkwood writes: > I've been helping out several customers recently who all seem to be > wrestling with the same issue: wanting to update/refresh non-production > databases from the latest corresponding prod version. Typically they > have (fairly complex) scripts that at some point attempt to restore a > dump into new database and then rename the to-be-retired db out of the > way and rename the newly restored one to take over. > In many cases such scripts would be simplified if a database could be > renamed without requiring its connections terminated. I've been asked > several times if this could be added... so I've caved in a done a patch > that allows this. > The default behavior is unchanged - it is required to specify an > additional trailing FORCE keyword to elicit the more brutal behavior. > Note that existing connections to the renamed database are unaffected, > but obviously SELECT current_database() returns the new name (in the > next transaction). This patch seems to me to be pretty thoroughly misguided. Either renaming a database with open connections is safe, or it isn't. If it is safe, we should just allow it. If it isn't, making people write an extra FORCE keyword does not make it safe. It's particularly silly to allow someone to rename the database out from under other sessions (which won't know what happened) but not rename it out from under his own session (which would or at least could know it). What you need to be doing is investigating whether the comments about this in RenameDatabase() are really valid concerns or not. 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] Rename a database that has connections
Mark Kirkwood wrote: > I've been helping out several customers recently who all seem to be > wrestling with the same issue: wanting to update/refresh non-production > databases from the latest corresponding prod version. Typically they > have (fairly complex) scripts that at some point attempt to restore a > dump into new database and then rename the to-be-retired db out of the > way and rename the newly restored one to take over. > > In many cases such scripts would be simplified if a database could be > renamed without requiring its connections terminated. I've been asked > several times if this could be added... so I've caved in a done a patch > that allows this. > > The default behavior is unchanged - it is required to specify an > additional trailing FORCE keyword to elicit the more brutal behavior. > Note that existing connections to the renamed database are unaffected, > but obviously SELECT current_database() returns the new name (in the > next transaction). Uh, it isn't save to copy a database when someone else is connected. How does this address that issue? -- Bruce Momjian http://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] strange nbtree corruption report
On Mon, Nov 21, 2011 at 08:00:21PM -0300, Alvaro Herrera wrote: > We got a very strange nbtree corruption report some time ago. This was > a btree index on a vey high churn table -- entries are updated and > deleted very quickly, so the index grows very large and also shrinks > quickly (AFAICT this is a work queue of sorts). > > The most strange thing of all is that there was this error: > > ERROR: left link changed unexpectedly in block 3378 of index "index_name" > CONTEXT: automatic vacuum of table "table_name" Just a suspicion ... when looking at the B-tree page reclamation algorithm, I had a thought that the logic in _bt_page_recyclable() was obsolete as of the introduction (in 8.3) of xid-free read-only transactions. A transaction without a persistent xid does not hold back RecentXmin, so how could waiting for a RecentXmin window to pass prove that no scan still holds a link to the page? Similarly, running VACUUMs do not hold back RecentXmin. I had made a note to try to reproduce a concrete problem, but I haven't tried yet. > This was reported not once, but several dozens of times, by each new > autovacuum worker that tried to vacuum the table. > > As far as I can see, there is just no way for this to happen ... much > less happen repeatedly. I thought it might be related to concurrent > insertions somehow managing to split the page under deletion very > quickly (given the load these systems are under, this is plausible). > But I can't find how. Yes, nothing comes to mind explaining that duration of persistence. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Rename a database that has connections
I've been helping out several customers recently who all seem to be wrestling with the same issue: wanting to update/refresh non-production databases from the latest corresponding prod version. Typically they have (fairly complex) scripts that at some point attempt to restore a dump into new database and then rename the to-be-retired db out of the way and rename the newly restored one to take over. In many cases such scripts would be simplified if a database could be renamed without requiring its connections terminated. I've been asked several times if this could be added... so I've caved in a done a patch that allows this. The default behavior is unchanged - it is required to specify an additional trailing FORCE keyword to elicit the more brutal behavior. Note that existing connections to the renamed database are unaffected, but obviously SELECT current_database() returns the new name (in the next transaction). regards Mark diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml new file mode 100644 index 360732f..5200523 *** a/doc/src/sgml/ref/alter_database.sgml --- b/doc/src/sgml/ref/alter_database.sgml *** ALTER DATABASE connlimit ! ALTER DATABASE name RENAME TO new_name ALTER DATABASE name OWNER TO new_owner --- 27,33 CONNECTION LIMIT connlimit ! ALTER DATABASE name RENAME TO new_name [ FORCE ] ALTER DATABASE name OWNER TO new_owner *** ALTER DATABASE newname = $6; $$ = (Node *)n; } ! | ALTER DATABASE database_name RENAME TO database_name { RenameStmt *n = makeNode(RenameStmt); n->renameType = OBJECT_DATABASE; n->subname = $3; n->newname = $6; $$ = (Node *)n; } | ALTER FUNCTION function_with_argtypes RENAME TO name --- 6437,6449 n->newname = $6; $$ = (Node *)n; } ! | ALTER DATABASE database_name RENAME TO database_name opt_force { RenameStmt *n = makeNode(RenameStmt); n->renameType = OBJECT_DATABASE; n->subname = $3; n->newname = $6; + n->force = $7; $$ = (Node *)n; } | ALTER FUNCTION function_with_argtypes RENAME TO name *** opt_column: COLUMN { $$ = COLUMN *** 6675,6680 --- 6673,6681 opt_set_data: SET DATA_P { $$ = 1; } | /*EMPTY*/ { $$ = 0; } ; + opt_force:FORCE { $$ = TRUE; } + | /* EMPTY */ { $$ = FALSE; } + ; /* * diff --git a/src/include/commands/dbcommands.h b/src/include/commands/dbcommands.h new file mode 100644 index 21dacff..68141b8 *** a/src/include/commands/dbcommands.h --- b/src/include/commands/dbcommands.h *** typedef struct xl_dbase_drop_rec *** 54,60 extern void createdb(const CreatedbStmt *stmt); extern void dropdb(const char *dbname, bool missing_ok); ! extern void RenameDatabase(const char *oldname, const char *newname); extern void AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel); extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt); extern void AlterDatabaseOwner(const char *dbname, Oid newOwnerId); --- 54,60 extern void createdb(const CreatedbStmt *stmt); extern void dropdb(const char *dbname, bool missing_ok); ! extern void RenameDatabase(const char *oldname, const char *newname, bool force); extern void AlterDatabase(AlterDatabaseStmt *stmt, bool isTopLevel); extern void AlterDatabaseSet(AlterDatabaseSetStmt *stmt); extern void AlterDatabaseOwner(const char *dbname, Oid newOwnerId); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h new file mode 100644 index af6565e..8d30af9 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *** typedef struct RenameStmt *** 2193,2198 --- 2193,2199
Re: [HACKERS] Removing postgres -f command line option
Tom Lane wrote: > Bruce Momjian writes: > > I think it is hard to argue that such a user-visible flag is > > reasonable, even if it helps backend developers avoid some keystrokes. > > > I think flags used only by backend developers should be things that can > > _only_ be done with flags. > > Huh? By that argument, we should remove every single postmaster command > line switch except "-c var=value", because all the other ones are > shorthands for that --- Peter went to some lengths awhile ago to make > that true. I was talking about developer-use-only flags. -- Bruce Momjian http://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] [COMMITTERS] pgsql: Avoid marking buffer dirty when VACUUM has no work to do.
I wrote: > Simon Riggs writes: >> Avoid marking buffer dirty when VACUUM has no work to do. >> When wal_level = 'hot_standby' we touched the last page of the >> relation during a VACUUM, even if nothing else had happened. >> That would alter the LSN of the last block and set the mtime >> of the relation file unnecessarily. Noted by Thom Brown. > This doesn't look right to me --- you have not accounted for the > possibility that btpo_cycleid or BTP_HAS_GARBAGE is changed. > Also, I'm confused about the business of not setting the LSN. Thom > claimed that he was seeing the page not change at all (or at least > md5sum of the file didn't change) despite mtime changing. If we'd > been plastering a new LSN on the page each time, then that should > certainly not have been possible. So I now think maybe we've > mis-analyzed what was happening in his example. > I think this requires more careful analysis. Ping? If you don't respond, I'm going to take it on my own authority to revert this patch, because it's definitely broken as-is, and I don't think the consequences of not updating the page LSN have been thought through either. 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] Removing postgres -f command line option
Bruce Momjian writes: > I think it is hard to argue that such a user-visible flag is > reasonable, even if it helps backend developers avoid some keystrokes. > I think flags used only by backend developers should be things that can > _only_ be done with flags. Huh? By that argument, we should remove every single postmaster command line switch except "-c var=value", because all the other ones are shorthands for that --- Peter went to some lengths awhile ago to make that true. 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] btvacuumpage useless "orig_blkno"
Alvaro Herrera writes: > Excerpts from Simon Riggs's message of lun nov 21 19:11:21 -0300 2011: >> tail recursion - read comments at bottom of the function > Right, but we don't need to pass the value as a parameter, we can just > save it at the start of the function, as my proposed patch does, right? If you do this, it's not really tail recursion anymore, so the comments need to be adjusted. The patch sounds reasonable, but you have more work to do to fix the comments ... 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] EXPLAIN (plan off, rewrite off) for benchmarking
Robert Haas writes: > ... Maybe we could find a way to reduce the size of the parse > tree (i.e. fewer nodes), or the number of times that it has to be > walked/copied. We could eliminate some annoying tree-copy steps if we could institute the policy that parse analysis doesn't scribble on the raw parse tree, rewriter doesn't modify parse analysis output, and planner doesn't modify rewriter output. However, it would be a lot of work, and I'm not entirely sure that we'd end up with a significant speed benefit. In a lot of places, the only way to not scribble on the input is to copy it anyway ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] explain analyze query execution time
Hello, I try to get the execution time of a query workload. I try using explain analyze but this time is allways higher than the execution time of a query across a client like pgadmin3 what is the reason about that difference? -- 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] testing ProcArrayLock patches
Pavan Deolasee wrote: > It will be a great help if you could spare few minutes to also > test the patch to take out the frequently accessed PGPROC members > to a different array. We are seeing good improvements on HPUX IA > platform and the AMD Opteron and it will be interesting to know > what happens on the Intel platform too. For a read only comparison (which was run using the simple protocol), using identical settings to the previous master run, but with the PGPROC split patch: m32 tps = 201738.209348 (including connections establishing) p32 tps = 201620.966988 (including connections establishing) m128 tps = 352159.631878 (including connections establishing) p128 tps = 363998.703900 (including connections establishing) Clearly a win at 128 clients; not at 32. For updates: sm32 tps = 27392.393850 (including connections establishing) sp32 tps = 27995.784333 (including connections establishing) sm128 tps = 22261.902571 (including connections establishing) sp128 tps = 23690.408272 (including connections establishing) pm32 tps = 34983.352396 (including connections establishing) pp32 tps = 36076.373389 (including connections establishing) pm128 tps = 24164.441954 (including connections establishing) pp128 tps = 27070.824588 (including connections establishing) That's a pretty decisive win all around. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] strange nbtree corruption report
Hi, We got a very strange nbtree corruption report some time ago. This was a btree index on a vey high churn table -- entries are updated and deleted very quickly, so the index grows very large and also shrinks quickly (AFAICT this is a work queue of sorts). The most strange thing of all is that there was this error: ERROR: left link changed unexpectedly in block 3378 of index "index_name" CONTEXT: automatic vacuum of table "table_name" This was reported not once, but several dozens of times, by each new autovacuum worker that tried to vacuum the table. As far as I can see, there is just no way for this to happen ... much less happen repeatedly. I thought it might be related to concurrent insertions somehow managing to split the page under deletion very quickly (given the load these systems are under, this is plausible). But I can't find how. (There were other error reports of btree indexes going awry here, such as "ERROR: right sibling's left-link doesn't match: block 67 links to 2118 instead of expected 2228 in index "pg_depend_depender_index"). These guys are running 8.3.14 here, and this is a Londiste slave database. Sadly, it seems that the index files in our case are gone now. I see three independent reports of this error message in the archives (Ulrich Wisser, Mark Kirkwood, Gabriel Ferro), but no one seems to have carried the investigation forward enough to discover what is exactly going wrong. Any thoughts about this? -- Álvaro Herrera -- 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] Inlining comparators as a performance optimisation
On Tue, Sep 20, 2011 at 7:53 PM, Peter Geoghegan wrote: > I don't think that the fact that that happens is at all significant at > this early stage, and it never even occurred to me that you'd think > that it might be. I was simply disclosing a quirk of this POC patch. > The workaround is probably to use a macro instead. For the benefit of > those that didn't follow the other threads, the macro-based qsort > implementation, which I found to perform significantly better than > regular qsort(), runs like this on my laptop when I built at 02 with > GCC 4.6 just now: > > C stdlib quick-sort time elapsed: 2.092451 seconds > Inline quick-sort time elapsed: 1.587651 seconds Results on my machine, for what they're worth: [rhaas inline_compar_test]$ gcc -O0 qsort-inline-benchmark.c [rhaas inline_compar_test]$ ./a.out C stdlib quick-sort time elapsed: 2.366762 seconds Inline quick-sort time elapsed: 1.807951 seconds [rhaas inline_compar_test]$ gcc -O1 qsort-inline-benchmark.c [rhaas inline_compar_test]$ ./a.out C stdlib quick-sort time elapsed: 1.970473 seconds Inline quick-sort time elapsed: 1.002765 seconds [rhaas inline_compar_test]$ gcc -O2 qsort-inline-benchmark.c [rhaas inline_compar_test]$ ./a.out C stdlib quick-sort time elapsed: 1.966408 seconds Inline quick-sort time elapsed: 0.958999 seconds [rhaas inline_compar_test]$ gcc -O3 qsort-inline-benchmark.c [rhaas inline_compar_test]$ ./a.out C stdlib quick-sort time elapsed: 1.988693 seconds Inline quick-sort time elapsed: 0.975090 seconds -- 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] btvacuumpage useless "orig_blkno"
Excerpts from Simon Riggs's message of lun nov 21 19:11:21 -0300 2011: > On Mon, Nov 21, 2011 at 10:03 PM, Alvaro Herrera > wrote: > > I just noticed that btvacuumpage has two BlockNumber parameters -- blkno > > and orig_blkno. The only caller passes them as the same value; the > > header comments state that blkno would be different when recursing, but > > actually the function implements recursion internally by way of a cute > > "goto" trick. So it seems to me that the orig_blkno parameter is > > useless -- we could just remove it. > > > > Unless I'm completely missing something? > > tail recursion - read comments at bottom of the function Right, but we don't need to pass the value as a parameter, we can just save it at the start of the function, as my proposed patch does, right? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] btvacuumpage useless "orig_blkno"
On Mon, Nov 21, 2011 at 10:03 PM, Alvaro Herrera wrote: > I just noticed that btvacuumpage has two BlockNumber parameters -- blkno > and orig_blkno. The only caller passes them as the same value; the > header comments state that blkno would be different when recursing, but > actually the function implements recursion internally by way of a cute > "goto" trick. So it seems to me that the orig_blkno parameter is > useless -- we could just remove it. > > Unless I'm completely missing something? tail recursion - read comments at bottom of the function -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] btvacuumpage useless "orig_blkno"
I just noticed that btvacuumpage has two BlockNumber parameters -- blkno and orig_blkno. The only caller passes them as the same value; the header comments state that blkno would be different when recursing, but actually the function implements recursion internally by way of a cute "goto" trick. So it seems to me that the orig_blkno parameter is useless -- we could just remove it. Unless I'm completely missing something? -- Álvaro Herrera btvacuumscan.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Writeable FDWs?
All, When I present to people about the features of 9.1, one of the most frequent questions is when we will get writeable FDWs for data sources where it is appropriate. Is anyone working on this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Refactoring on DROP/ALTER SET SCHEMA/ALTER RENAME TO statement
On Sat, Nov 19, 2011 at 1:49 PM, Kohei KaiGai wrote: >> But I'm not sure why we do. My thought here was that we should >> extended the ObjectProperty array in objectaddress.c so that >> AlterObjectNamespace can get by with fewer arguments - specifically, >> it seems like the following ought to be things that can be looked up >> via the ObjectProperty mechanism: >> >> int oidCacheId, int nameCacheId, int Anum_name, int Anum_namespace, >> int Anum_owner, AclObjectKind acl_kind >> > Thanks for your reviewing, and sorry for not a timely response. > > I tried to add these items into ObjectProperty and replace existing caller of > AlterObjectNamespace, however, it seemed to me these members (especially > AclObjectKind) were too specific with current implementation of the > AlterObjectNamespace. Hmm, maybe so. But then we could still move over some things. oidCacheId is pretty much already there already, isn't it? > And, I also modified check_ownership() to eliminate objtype/object/objarg; > that > allows to invoke this function from code paths without these > information, such as > shdepReassignOwned() or AlterObjectNamespace_oid(). Yeah. I'm not sure I like that. It doesn't seem like a particularly good idea to throw away the information we have about the name the user entered and assume we'll be able to regenerate it from the system catalogs after the fact. >> Now, what you have here is a much broader reworking. And that's not >> necessarily bad, but at the moment I'm not really seeing how it >> benefits us. >> > In my point, if individual object types need to have its own handler for > alter commands, points of the code to check permissions are also > distributed for each object types. It shall be a burden to maintain hooks > that allows modules (e.g sepgsql) to have permission checks. Well, it's always nicer if you can just put a call to some hook in one place instead of many. But it's not worth sacrificing everything to make that happen. I think we need to compare the value of only needing a hook in one place against the disruption of changing a lot of code that is working fine as it is. In the case of the DROP commands, it seems to me that the refactoring you did came out a huge win, but this doesn't seem as clear to me. Note that DROP actually does dispatch the actual work of dropping the object to a type-specific function, unlike what you're trying to do here. -- 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] foreign key locks, 2nd attempt
Robert Haas writes: > On Sat, Nov 19, 2011 at 10:36 AM, Tom Lane wrote: >> It's already the case that RI triggers require access to special >> executor features that are not accessible at the SQL level. I don't >> think the above argument is a compelling reason for exposing more >> such features at the SQL level. All we need is that C-coded functions >> can get at them somehow. > > I kinda agree with Simon. In general, if we don't need to expose > something at the SQL level, then sure, let's not. But it seems weird > to me to say, well, we have four lock modes internally, and you can > get to three of them via SQL. To me, that sort of inconsistency feels > like a wart. +1 I know I've already rolled constraint triggers into production, being able to use FOR KEY SHARE locks would be good. 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] Core Extensions relocation
On Fri, Nov 18, 2011 at 9:35 AM, Tom Lane wrote: > Why do you figure that, exactly? The path of least resistance will > be precisely to leave everything packaged as it is, in a single > postgresql-contrib module. I'm pretty likely to do that myself for > Fedora and RHEL. Subdividing/rearranging contrib makes the packager's > life more complicated, *and* makes his users' lives more complicated, > if only because things aren't where they were before. It seems unlikely > to happen, at least in the near term. When we discussed this topic at the developer's meeting, I thought we had general consensus that it would be a good idea to package a limited number of important and stable debugging tools with the core server, and I had the impression that you (Tom) thought this was a reasonable thing to do. If you don't, or if you did then but don't now, then it seems to me that this conversation is dead in the water for so long as you're the one packaging for Red Hat, and we should just move on; you pretty much have unassailable personal veto power on this issue. But let's not pretend that the conversation is about what packagers in general will do, because I don't think it is. I think it's about what you personally will do. I think that if we move a few things into src/extension and set things up such that they get installed even if you just do "make install" rather than requiring "make install-world", packagers who don't have any terribly strong personal agenda will decide that means they ought to be shipped with the server. However, if you're personally committed to making sure that all of that stuff remains in postgresql-contrib in Red Hat/Fedora, regardless of where we move it to on our end, then that's where it's going to be, at least on all Red Hat-derived systems, which is a big enough chunk of the world to matter quite a lot. Note that I'm not necessarily saying anything about whether your reasons for such a decision might be good or bad; I'm just pointing out that a good deal of our ability to make a change in this area is within your personal control. -- 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] Core Extensions relocation
Greg Smith wrote: > On 11/21/2011 11:40 AM, Bruce Momjian wrote: > > I think a question is how often people are waiting for features that > > actually can be addressed in a contrib/plugin way. My gut feeling is > > that most missing features have to be added to the server core (e.g. > > index-only scans) and are not possible to add in a contrib/plugin way. > > > > Good question; let's talk about 9.0. We were building/distributing > three things for that version that poked into the server: > > -Replication monitoring tools that slipped from the 9.0 schedule, > similar to what became pg_stat_replication in 9.1 > -An early version of what became hot_standby_feedback in 9.1. > -pg_streamrecv > > While these weren't all packaged as extensions per se, all of them used > the PGXS interface. And they all provided deployment blocking features > to early adopters before those features were available in core, in some > cases after the issues they address had been encountered in production > deployments. As I was ranting on my blog recently, I'm seeing more > complaints recently about monitoring and management features--exactly > the sort of thing that you can improve as an extension, and that the > extensions I've proposed provide--than I am over missing big features. > > Index-only scans are a good example, as one of the most requested > performance feature you can only get in core (I'd put them at #2 behind > materialized views for the customers I talk to). I wouldn't bet that > they are considered more important by a typical deployment than good > built-in query profiling though. I get complaints about query > monitoring from every single PostgreSQL install, while complaints about > not having index-only scans only come from the bigger installations. > Note how demand is high enough that we have two pg_stat_statements > replacements submitted right now. Agreed much of the edge stuff, e.g. monitoring, can be done as plugins. -- Bruce Momjian http://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] Core Extensions relocation
On 11/21/2011 11:40 AM, Bruce Momjian wrote: I think a question is how often people are waiting for features that actually can be addressed in a contrib/plugin way. My gut feeling is that most missing features have to be added to the server core (e.g. index-only scans) and are not possible to add in a contrib/plugin way. Good question; let's talk about 9.0. We were building/distributing three things for that version that poked into the server: -Replication monitoring tools that slipped from the 9.0 schedule, similar to what became pg_stat_replication in 9.1 -An early version of what became hot_standby_feedback in 9.1. -pg_streamrecv While these weren't all packaged as extensions per se, all of them used the PGXS interface. And they all provided deployment blocking features to early adopters before those features were available in core, in some cases after the issues they address had been encountered in production deployments. As I was ranting on my blog recently, I'm seeing more complaints recently about monitoring and management features--exactly the sort of thing that you can improve as an extension, and that the extensions I've proposed provide--than I am over missing big features. Index-only scans are a good example, as one of the most requested performance feature you can only get in core (I'd put them at #2 behind materialized views for the customers I talk to). I wouldn't bet that they are considered more important by a typical deployment than good built-in query profiling though. I get complaints about query monitoring from every single PostgreSQL install, while complaints about not having index-only scans only come from the bigger installations. Note how demand is high enough that we have two pg_stat_statements replacements submitted right now. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.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] psql \ir filename normalization
Robert Haas wrote: > On Mon, Nov 21, 2011 at 2:30 PM, Bruce Momjian wrote: > > Robert Haas wrote: > >> On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian wrote: > >> > Robert Haas wrote: > >> >> Argh. ?The root of the problem here seems to be that > >> >> join_path_components() feels entitled to arbitrarily insert a pathname > >> >> separator at the front of the output string even if its first input > >> >> didn't begin with one originally. ?Lame! > >> > > >> > The attached patch fixes this report, I think. > >> > >> Looks sensible. ?Keep in mind we need to back-patch this. > > > > Oh. ?Well, with no bug reports about it, does that make sense? ?Do we > > have any code that relies on the old behavior? > > Oh, wait a minute. I was thinking \ir was in 9.1, but it's not: it > was committed after the branch. So I guess this only needs to be > fixed in master, which is much less scary. Agreed. I realize it is wrong but I have no idea what impact fixing it in back branches might have, or people who are relying on the broken behavior in some way. -- Bruce Momjian http://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] psql \ir filename normalization
On Mon, Nov 21, 2011 at 2:30 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian wrote: >> > Robert Haas wrote: >> >> Argh. ?The root of the problem here seems to be that >> >> join_path_components() feels entitled to arbitrarily insert a pathname >> >> separator at the front of the output string even if its first input >> >> didn't begin with one originally. ?Lame! >> > >> > The attached patch fixes this report, I think. >> >> Looks sensible. Keep in mind we need to back-patch this. > > Oh. Well, with no bug reports about it, does that make sense? Do we > have any code that relies on the old behavior? Oh, wait a minute. I was thinking \ir was in 9.1, but it's not: it was committed after the branch. So I guess this only needs to be fixed in master, which is much less scary. -- 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] psql \ir filename normalization
Robert Haas wrote: > On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian wrote: > > Robert Haas wrote: > >> Argh. ?The root of the problem here seems to be that > >> join_path_components() feels entitled to arbitrarily insert a pathname > >> separator at the front of the output string even if its first input > >> didn't begin with one originally. ?Lame! > > > > The attached patch fixes this report, I think. > > Looks sensible. Keep in mind we need to back-patch this. Oh. Well, with no bug reports about it, does that make sense? Do we have any code that relies on the old behavior? -- Bruce Momjian http://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] RFC: list API / memory allocations
On Sat, Nov 19, 2011 at 12:33 PM, Stephen Frost wrote: > You've mentioned that before and, to be honest, I could have sworn that > we're doing that already.. I tried to write a patch for that at one point, but it crashed and burned over the exact same set of issues discussed upthread, which I wasn't able to resolve satisfactorily. It's just really difficult to change the API for something like memory allocation after the fact; it's too hard to find the bits of code that do whatever naughty thing you don't want them to. One random idea... would there by any sense in having a palloc-like function that is defined to allocate multiple objects at once? In other words, if you need 4 list cells, then instead of asking palloc for them one at a time, you make one function call and get four pointers back at one go. I'm not sure whether that would help at all; palloc might not be able to take advantage of the additional information usefully. To some extent I feel like this is all optimizing something that's likely already so well-optimized that future gains, if any, are likely to be small. I feel like the only way we're likely to get much of a win here is if we can reduce the amount of memory that has to be allocated in the first place (allocate fewer data structures, don't copy them as much, etc.). -- 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] EXPLAIN (plan off, rewrite off) for benchmarking
On Sat, Nov 19, 2011 at 11:47 AM, Andres Freund wrote: > I absolutely cannot agree on the fact that the speed parse-analyze is > irrelevant though. Tom may be right that the speed of the parser *in isolation* is irrelevant, in the narrow sense that if we made the parser twice as slow but somehow by that change made up the time in the executor, nobody would care; in fact, it would be a net win for people using prepared statements. But I completely agree that parsing speed is something we need to worry about. Unfortunately, I don't have a lot of good ideas for improving it. A while back I tried ripping out most of the parser to see whether that would speed up performance parsing very simple statements, but the improvement was pretty small. Maybe a more thorough job than what I did is possible, but it didn't seem promising. Maybe we could find a way to reduce the size of the parse tree (i.e. fewer nodes), or the number of times that it has to be walked/copied. -- 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] proposal: better support for debugging of overloaded functions
On Sun, Nov 20, 2011 at 6:16 AM, Pavel Stehule wrote: > Is possible to add GUC variable plpgsql.log_function_signature (maybe > just log_function_signature (for all PL))? I am not sure about GUC > name. > > When this variable is true, then CONTEXT line will contain a qualified > function's signature instead function name Sure, but why? If it's possible to do that, I think we should just do it always. It might be a net reduction in readability for people who don't use overloading but do have functions with very long names and lots and lots of arguments, but even if you think that's good design, I think the general principle that an error message should uniquely identify the object responsible for the error ought to take precedence. -- 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] psql \ir filename normalization
On Mon, Nov 21, 2011 at 1:05 PM, Bruce Momjian wrote: > Robert Haas wrote: >> Argh. The root of the problem here seems to be that >> join_path_components() feels entitled to arbitrarily insert a pathname >> separator at the front of the output string even if its first input >> didn't begin with one originally. Lame! > > The attached patch fixes this report, I think. Looks sensible. Keep in mind we need to back-patch this. -- 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] COUNT(*) and index-only scans
On Sat, Nov 19, 2011 at 11:22 AM, Thom Brown wrote: > While I accept that maybe adapting the existing bitmap index scan > functionality isn't necessarily desirable, would it be feasible to > create a corresponding bitmap index-only scan method. I've been thinking about this a bit more; I wonder whether we could create yet another type of index scan - let's call it a Streaming Index Only Scan. A streaming index only scan uses a buffer, which holds index tuples and the corresponding CTIDs, and it has some maximum size, probably based on work_mem. Tuples in the buffer are kept sorted by CTID. The scan happens in two alternating phases: buffer fill, and buffer drain. In buffer fill mode, we scan the index and add matching tuples and their CTIDs to the buffer. When the buffer is full or the index AM reports that there are no more tuples in the scan, we switch to buffer drain mode. In buffer drain mode, we repeatedly select a heap block number and attempt to return all buffered tuples on that page. We maintain a counter, LastBlockNumber, initially zero, which tracks the last heap block number so selected. To select the next block number, we choose the first block number greater than or equal to LastBlockNumber referenced by any CTID in the buffer (it should be possible to design the buffer so that this value can be computed quickly); if there are none, we instead choose the first block number referenced by any CTID in the buffer, period. Having selected the block number, we check whether the page is all-visible. If so, we can return all the index tuples from that page without further ado. Otherwise, we fetch the heap block, check visibility for each tuple, and return only those index tuples for which the corresponding heap tuples are visible to the scan. If there's now enough buffer space available to be certain that the next index tuple will fit, we switch back to buffer fill mode; otherwise, we remain in buffer drain mode. As compared with a bitmap index scan, this doesn't have the advantage of being able to combine multiple indexes effectively; I don't really see any way to make that work with the index-only scan concept in general, except perhaps for the special case of a zero-argument aggregate. It also doesn't have the advantage that each heap page will be guaranteed to be visited only once. But in practice duplicate visits to the same page should be uncommon; they'll be avoided when either work_mem is sufficient to buffer the whole scan, or when there's some degree of table clustering with respect to the index. While I'm building castles in the sky, a further idea would be to try to optimize the case where there's a LIMIT node above the scan. If we could pass down a hint indicating how many rows are thought likely to be needed, we could enter buffer drain mode after about that many tuples, instead of waiting until the buffer was full. If the hint is right, we win (and if it's wrong, we can still go back and fetch some more tuples, at a cost of possible performance loss). -- 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
[HACKERS] Notes on implementing URI syntax for libpq
Hello, It was proposed a while ago for libpq to support URI syntax for specifying the connection information: http://archives.postgresql.org/message-id/1302114698.23164.17.camel@jd-desktop http://archives.postgresql.org/pgsql-hackers/2011-07/msg01144.php It appears to me that the consensus was that: 1) this feature is indeed going to be useful, and 2) that we would go by implementing a simple URI parser ourselves instead of adding dependency on any fancy external library. Now we're going to actually implement this. It is known that libpq (and, thus every utility using it to connect a database: psql, pg_dump, etc.) supports a way to specify some of the connection parameters (or all of them) via a single conninfo string, e.g: psql -d "dbname=mydb host=example.net port=5433" This, in my opinion, is very similar to what we would like to achieve with the URI syntax, so the above could also be specified using a URI parameter like this: psql -d postgresql://example.net:5433/mydb We can also support specifying extra parameters via the usual "?keyword=value&keyword2=other" syntax. As it was noted in the original discussion, sticking to what JDBC provides makes the most sense: http://jdbc.postgresql.org/documentation/head/connect.html So we should support 'user', 'password' and 'ssl' parameters (and probably just ignore the rest, at least for start.) Upon libpq code inspection I come to think that the best place to plug this seems to be conninfo_array_parse function (where dbname keyword is currently being checked for '=' symbol and expanded): http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/interfaces/libpq/fe-connect.c;h=ed9dce941e1d57cce51f2c21bf29769dfe2ee542;hb=HEAD#l4262 We could similarly check for "postgresql:" designator and if present, extract the connection options from the dbname keyword thought to be a connection URI. The check should obviously go before the current check for '=', if we're going support the extra parameters, as outlined above. I am going to sketch a work-in-progress patch in the background of a discussion here. Your thoughts on this are very welcome! -- Alex -- 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] Removing postgres -f command line option
Tom Lane wrote: > Heikki Linnakangas writes: > > While looking at Shigeru Hanada's foreign join pushdown patch, I noticed > > a command line option that I didn't know to exist: > > > $ postgres --help > > ... > > Developer options: > >-f s|i|n|m|hforbid use of some plan types > > Hmm, I thought I'd fixed that help message to match reality recently. > > > That seems completely useless to me, because you can also do "-c > > enable_seqscan=off". Any objections to removing the -f option altogether? > > I use it. See also src/test/regress/regressplans.sh, which would become > greatly less wieldy if it had to spell out the switches long-form. I think it is hard to argue that such a user-visible flag is reasonable, even if it helps backend developers avoid some keystrokes. I think flags used only by backend developers should be things that can _only_ be done with flags. -- Bruce Momjian http://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] Client library cross-compiling: Win32, Win64, MacOSX. Possible?
Pavel Golub wrote: > Hello. > > Are there any howto's or articles about building client access library > (libpq) for several target OSes, e.g. Win32, Win64, MacOS in the same > MinGW environment? > > And is it possible at all? I know that there is MinGW-w64 to produce > Win64 binaries, but I want to have one farm for all. > > If not, is there any opportunity to have needed binaries from some > postgresql build farms? I think we are just happy it works at all. I have rarely seen cross-compiling on that platform. -- Bruce Momjian http://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] psql \ir filename normalization
Robert Haas wrote: > Argh. The root of the problem here seems to be that > join_path_components() feels entitled to arbitrarily insert a pathname > separator at the front of the output string even if its first input > didn't begin with one originally. Lame! The attached patch fixes this report, I think. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/src/port/path.c b/src/port/path.c new file mode 100644 index 13ca4f3..9cb0b01 *** a/src/port/path.c --- b/src/port/path.c *** join_path_components(char *ret_path, *** 212,218 } if (*tail) snprintf(ret_path + strlen(ret_path), MAXPGPATH - strlen(ret_path), ! "/%s", tail); } --- 212,219 } if (*tail) snprintf(ret_path + strlen(ret_path), MAXPGPATH - strlen(ret_path), ! /* only add slash if there is something already in head */ ! "%s%s", head[0] ? "/" : "", tail); } -- 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] ISN was: Core Extensions relocation
Bruce, > I don't see any of this reaching the level that it needs to be > backpatched, so I think we have to accept that this will be 9.2-only > change. Agreed. If users encounter issues with the prefix in the field, it will be easy enough for them to back-patch. But we don't want to be responsible for it as a project. --Josh -- 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] testing ProcArrayLock patches
On Mon, Nov 21, 2011 at 11:01 PM, Kevin Grittner wrote: > Pavan Deolasee wrote: > >> It will be a great help if you could spare few minutes to also >> test the patch to take out the frequently accessed PGPROC members >> to a different array. We are seeing good improvements on HPUX IA >> platform and the AMD Opteron and it will be interesting to know >> what happens on the Intel platform too. >> >> > http://archives.postgresql.org/message-id/4eb7c4c9.9070...@enterprisedb.com > > It's going to be hard to arrange more of the 20-hours runs I've been > doing, but I can work in some more abbreviated tests. What would be > the best test for this? (I would hate to try and find out I didn't > exercise the right code path.) > I think 2-3 runs with 32 and 128 clients each with prepared statements should suffice to quickly compare with the other numbers you posted for the master. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing ProcArrayLock patches
Pavan Deolasee wrote: > It will be a great help if you could spare few minutes to also > test the patch to take out the frequently accessed PGPROC members > to a different array. We are seeing good improvements on HPUX IA > platform and the AMD Opteron and it will be interesting to know > what happens on the Intel platform too. > > http://archives.postgresql.org/message-id/4eb7c4c9.9070...@enterprisedb.com It's going to be hard to arrange more of the 20-hours runs I've been doing, but I can work in some more abbreviated tests. What would be the best test for this? (I would hate to try and find out I didn't exercise the right code path.) -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] testing ProcArrayLock patches
On Mon, Nov 21, 2011 at 10:44 PM, Kevin Grittner wrote: > "Kevin Grittner" wrote: > >> I can run one more set of tests tonight before I have to give it >> back to the guy who's putting it into production. It sounds like >> a set like the above except with synchronous_commit = off might be >> desirable? > > OK, that's what I did. This gave me my best numbers yet for an > updating run of pgbench: tps = 38039.724212 for prepared statements > using the flexlock patch. This patch is a clear win when you get to > 16 clients or more. > It will be a great help if you could spare few minutes to also test the patch to take out the frequently accessed PGPROC members to a different array. We are seeing good improvements on HPUX IA platform and the AMD Opteron and it will be interesting to know what happens on the Intel platform too. http://archives.postgresql.org/message-id/4eb7c4c9.9070...@enterprisedb.com Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ISN was: Core Extensions relocation
Peter Geoghegan wrote: > On 17 November 2011 03:54, Tom Lane wrote: > >?It's not reasonable to suppose > > that nobody is using it today. > > I didn't suppose that no one is using it, but that those that are > using it are unaware of the risks with prefix validation, and that > there will be a rude awakening for them. > > > Ergo, we can't just summarily break > > backwards compatibility on the grounds that we don't like the design. > > Heck, we don't even have a field bug report that the design limitation > > is causing any real problems for real users ... so IMO, the claims that > > this is dangerously broken are a bit overblown. > > I think that's it's rather unlikely that removing hyphenation and > prefix validation would adversely affect anyone, provided that it was > well documented and wasn't applied to stable branches. If it were up > to me, I might remove validation from stable branches but keep > hyphenation, while removing both for 9.2 . After all, hyphenation will > break anyway, so they're worse off continuing to rely on hyphenation > when it cannot actually be relied on. Clarification: Our policy for patching back-branches is that the bug has to affect many users, be serious, and the fix has to be easily tested. For a user-visible change (which this would be), the criteria is even more strict. I don't see any of this reaching the level that it needs to be backpatched, so I think we have to accept that this will be 9.2-only change. -- Bruce Momjian http://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] testing ProcArrayLock patches
"Kevin Grittner" wrote: > I can run one more set of tests tonight before I have to give it > back to the guy who's putting it into production. It sounds like > a set like the above except with synchronous_commit = off might be > desirable? OK, that's what I did. This gave me my best numbers yet for an updating run of pgbench: tps = 38039.724212 for prepared statements using the flexlock patch. This patch is a clear win when you get to 16 clients or more. sm1 tps = 1312.501168 (including connections establishing) sf1 tps = 1376.678293 (including connections establishing) sm2 tps = 2705.571856 (including connections establishing) sf2 tps = 2689.577938 (including connections establishing) sm4 tps = 5461.403557 (including connections establishing) sf4 tps = 5447.363103 (including connections establishing) sm8 tps = 10524.695338 (including connections establishing) sf8 tps = 10448.012069 (including connections establishing) sm16 tps = 18952.968472 (including connections establishing) sf16 tps = 18969.505631 (including connections establishing) sm32 tps = 27392.393850 (including connections establishing) sf32 tps = 29225.974112 (including connections establishing) sm64 tps = 28947.675549 (including connections establishing) sf64 tps = 31417.536816 (including connections establishing) sm80 tps = 28053.684182 (including connections establishing) sf80 tps = 29970.555401 (including connections establishing) sm96 tps = 25885.679957 (including connections establishing) sf96 tps = 28581.271436 (including connections establishing) sm128 tps = 22261.902571 (including connections establishing) sf128 tps = 24537.566960 (including connections establishing) pm1 tps = 2082.958841 (including connections establishing) pf1 tps = 2052.328339 (including connections establishing) pm2 tps = 4287.257860 (including connections establishing) pf2 tps = 4228.770795 (including connections establishing) pm4 tps = 8653.196863 (including connections establishing) pf4 tps = 8592.091631 (including connections establishing) pm8 tps = 16071.432101 (including connections establishing) pf8 tps = 16196.992207 (including connections establishing) pm16 tps = 27146.441216 (including connections establishing) pf16 tps = 27441.966562 (including connections establishing) pm32 tps = 34983.352396 (including connections establishing) pf32 tps = 38039.724212 (including connections establishing) pm64 tps = 33182.643501 (including connections establishing) pf64 tps = 34193.732669 (including connections establishing) pm80 tps = 30686.712607 (including connections establishing) pf80 tps = 6.011769 (including connections establishing) pm96 tps = 24692.015615 (including connections establishing) pf96 tps = 32907.472665 (including connections establishing) pm128 tps = 24164.441954 (including connections establishing) pf128 tps = 25742.670928 (including connections establishing) At lower client numbers the tps values within each set of five samples were very tightly grouped. With either protocol, and whether or not the patch was applied, the higher concurrency groups tended to be bifurcated within a set of five samples between "good" and "bad" numbers. The patch seemed to increase the number of clients which could be handled without collapse into the bad numbers. It really looks like there's some sort of performance "collapse" at higher concurrency which may or may not happen in any particular five minute run. Just as one example, running the simple protocol with the flexlock patch: tps = 24491.653873 (including connections establishing) tps = 24537.566960 (including connections establishing) tps = 28462.276323 (including connections establishing) tps = 24403.373002 (including connections establishing) tps = 28458.902549 (including connections establishing) -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] foreign key locks, 2nd attempt
On Sat, Nov 19, 2011 at 10:36 AM, Tom Lane wrote: > Simon Riggs writes: >> On Thu, Nov 3, 2011 at 6:12 PM, Alvaro Herrera >> wrote: >>> So Noah Misch proposed using the FOR KEY SHARE syntax, and that's what I >>> have implemented here. (There was some discussion that instead of >>> inventing new SQL syntax we could pass the necessary lock mode >>> internally in the ri_triggers code. That can still be done of course, >>> though I haven't done so in the current version of the patch.) > >> FKs are a good short hand, but they aren't the only constraint people >> implement. It can often be necessary to write triggers to enforce >> complex constraints. So user triggers need access to the same >> facilities that ri triggers uses. Please keep the syntax. > > It's already the case that RI triggers require access to special > executor features that are not accessible at the SQL level. I don't > think the above argument is a compelling reason for exposing more > such features at the SQL level. All we need is that C-coded functions > can get at them somehow. I kinda agree with Simon. In general, if we don't need to expose something at the SQL level, then sure, let's not. But it seems weird to me to say, well, we have four lock modes internally, and you can get to three of them via SQL. To me, that sort of inconsistency feels like a wart. -- 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] FlexLocks
Robert Haas wrote: > On Wed, Nov 16, 2011 at 12:25 PM, Kevin Grittner > wrote: > >> We could alternatively change one or the other of them to be a > >> struct with one member, but I think the cure might be worse than > >> the disease. ?By my count, we are talking about saving perhaps as > >> many as 34 lines of code changes here, and that's only if > >> complicating the type handling doesn't require any changes to > >> places that are untouched at present, which I suspect it would. > > > > So I stepped through all the changes of this type, and I notice that > > most of them are in areas where we've talked about likely benefits > > of creating new FlexLock variants instead of staying with LWLocks; > > if any of that is done (as seems likely), it further reduces the > > impact from 34 lines. ?If we take care of LWLockHeldByMe() as you > > describe, I'll concede the FlexLockId changes. > > Updated patches attached. It would be helpful if the patch included some text about how flexilocks are different from ordinary lwlocks. -- Bruce Momjian http://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] Inverse convertion for pg_mb2wchar
Hackers, I've a question about pg_mb2wchar function. Is there any way for inverse convertion pg_wchar* to char*? I've looked to pg_wchar_tbl table definition, and I didn't find anything about inverse transformation. So, any change to get inverse convertion? I'm experimenting with index support for regexp search and I'm trying to get some characters back from color map. -- With best regards, Alexander Korotkov.
Re: [HACKERS] Core Extensions relocation
Greg Smith wrote: > I've submitted two changes to this CommitFest that are enhancing > features in this "core extensions" set. Right now I have multiple > customers who are desperate for both of those features. With > extensions, I can give them changes that solve their immediate crisis > right now, almost a full year before they could possibly appear in a > proper release of PostgreSQL. And then I can push those back toward > community PostgreSQL, with any luck landing in the next major version. > Immediate gratification for the person funding development, and peer > reviewed code that goes through a long beta and release cycle. That's > the vision I have for a PostgreSQL that is simultaneously stable and > agile. The easiest way to get there it is to lead by example--by having > extensions that provide necessary, visible components to core, while > still being obviously separate components. That's the best approach for > proving this development model works and is suitable for everyone. I think a question is how often people are waiting for features that actually can be addressed in a contrib/plugin way. My gut feeling is that most missing features have to be added to the server core (e.g. index-only scans) and are not possible to add in a contrib/plugin way. I am not saying this would not help, but I am saying that this is going to address only a small part of the goal of getting features to users quicker. -- Bruce Momjian http://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] [PATCH] Support for foreign keys with arrays
On Fri, Nov 4, 2011 at 7:48 AM, Gabriele Bartolini wrote: > This patch adds basic support of arrays in foreign keys, by allowing to > define a referencing column as an array of elements having the same type as > the referenced column in the referenced table. > Every NOT NULL element in the referencing array is matched against the > referenced table. I like the idea of being able to define more flexible foreign keys, but are we gilding the lily here? The proposed solution is really quite specific to the nuances of arrays. Perhaps there is a more general expression based syntax that leaves the door open for other types conditions such as dealing fields dependent on other fields? merlin -- 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] Singleton range constructors versus functional coercion notation
Robert Haas writes: > On Nov 20, 2011, at 10:24 PM, Jeff Davis wrote: >> Well, if there were a good shorter notation, then probably so. But it >> doesn't look like we have a good idea, so I'm fine with dropping it. > We should also keep in mind that people who use range types can and likely > will define their own convenience functions. If people use singletons, or > open ranges, or closed ranges, or one-hour timestamp ranges frequently, they > can make their own notational shorthand with a 3-line CREATE FUNCTION > statement. We don't need to have it all in core. But if you believe that, what syntax do you think people are likely to try if they want a singleton range constructor? Leaving the user to discover the problem and try to invent a workaround is not better than doing it ourselves ... 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] [Review] Include detailed information about a row failing a CHECK constraint into the error message
=?UTF-8?B?SmFuIEt1bmRyw6F0?= writes: > On 11/16/11 23:13, Royce Ausburn wrote: >> Another super minor thing, postgres doesn't seem to put periods at the >> end of log messages, yet this new detail line does. > Again, I'm not familiar with the correct procedure. Shall I send a > revised patch for this one? Please read the message style guide (we do have one) http://developer.postgresql.org/pgdocs/postgres/error-style-guide.html 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] ToDo: pg_backup - using a conditional DROP
Hello There is a use case from GoodData's engineer --- We have many user projects, each project has his own database with granted permissions. We use pg_dump with option "--clean" which extends SQL dump with syntax like: "DROP INDEX tab1_idx1;" "DROP TABLE tab1;" When we load such dump into database we get a lot of error messages saying: "INDEX tab1_idx1 doesn't exists;" "TABLE tab1 doesn't exists;" We need "--clean" parameter, because we often need to load dump into database which is not empty. On the other hand, we want to get rid of ERROR messages in case some of tables are missing. Typically we are need to solve one of following situations: 1, dump user project, restore the data in new project. Each project, even empty one contains at least table "const". During load of SQL dump, const table from dump needs to overwrite the one in database. 2, restore the data in project after fail of previous restoration E.g. connection lost during restoration, no disc space left on device, ... 3, restore the data in project from backup For some reason, data in the project are messed up and they need to be overwritten from backup Vlada Vladimír Vacula (GoodData) --- Regards Pavel Stehule -- 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] [Review] Include detailed information about a row failing a CHECK constraint into the error message
On 11/16/11 23:13, Royce Ausburn wrote: > The patch fails the regression tests because it is outputting new DETAIL > line which four of tests aren't expecting. The tests will need to be > updated. Hi Royce, thanks for your time which you've put into this review. What is the suggested way to go form here? Shall I update the unit tests? > One comment I have on the output is that strings are not in quotes. > It's a little jarring, but might not be that big a deal. A contrived > case that is pretty confusing: > > test=# insert into test select 1, 2, '3, 4', 4; > ERROR: new row for relation "test" violates check constraint "test_b_check" > DETAIL: Failing row: (1, 2, 3, 4, 4). > > A select inserting 4 columns seemingly results in a 5 column row ;) Yes, I agree that the unescaped format of strings leads to ambiguous results here. The code was copy-pasted from the checks which handle the UNIQUE constraints, so if there's an obvious improvement, it should probably be applied in there as well. > Another super minor thing, postgres doesn't seem to put periods at the > end of log messages, yet this new detail line does. Again, I'm not familiar with the correct procedure. Shall I send a revised patch for this one? With kind regards, Jan -- Trojita, a fast e-mail client -- http://trojita.flaska.net/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Singleton range constructors versus functional coercion notation
On Nov 20, 2011, at 10:24 PM, Jeff Davis wrote: > On Sat, 2011-11-19 at 15:57 -0500, Tom Lane wrote: >>> I'm hesitant to remove them because the alternative is significantly >>> more verbose: >>> numrange(1.0, 1.0, '[]'); >> >> Right. The question is, does the case occur in practice often enough >> to justify a shorter notation? I'm not sure. > > Well, if there were a good shorter notation, then probably so. But it > doesn't look like we have a good idea, so I'm fine with dropping it. We should also keep in mind that people who use range types can and likely will define their own convenience functions. If people use singletons, or open ranges, or closed ranges, or one-hour timestamp ranges frequently, they can make their own notational shorthand with a 3-line CREATE FUNCTION statement. We don't need to have it all in core. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review for "Add permission check on SELECT INTO"
Kohei KaiGai wrote: > The attached patch is a revised version. > It fixed up this bug, and revised test cases to ensure permission > check error shall be raised due to the new table. Thanks. The second patch seems fine to me, I'll mark it "ready for committer". Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers