Re: [HACKERS] knngist - 0.8
On Sun, Dec 26, 2010 at 08:13:40PM -0500, Tom Lane wrote: [ thinks for a bit... ] One reason for having a different structure would be if we needed to represent abstract semantics for some operators that couldn't be associated with a btree opclass. This is clearly not an issue for what RANGE needs, since anything you can order by will surely have a btree opclass for that, and in fact we probably need to tie those operators to specific orderings if a datatype has more than one sort ordering. But maybe there could be some other situation where we'd need to describe operator behavior for a datatype independently of any sort ordering. Can anyone come up with a plausible example? One thing that comes to mind is the operators used for hash indexes, namely the hash() function. It is closely related to the collation but isn't actually used for sorting. For every btree class you can make a hash class with the same equality operator and an appropriate hash function. I've had the idea of defining a parent object and deriving the btree and hash operator classes from that, but it gets messy once you get into cross-type operators (i.e. operator families). With respect to the collation of strings I have thought it useful to be able to define a sortkey() function, which would map the input space to a 8 byte integer and satisfies the rule: sortkey(a) sortkey(b) implies a b The idea being that you can use this as an efficient first step to speed up sorting strings, since adding it to the sort list implicitly before the actual column doesn't change the result. In the case of strings the sortkey() could be generated with strxfrm(). A similar idea could be used with other expensive comparison operations, but I can't think of any at the moment. Actually, perhaps you could use it with ints/floats/etc as well, since you could skip the function call overhead. You'd be trading (n log n int compares + n sortkeys) with (n log n comparisions). Just some thoughts, Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Patriotism is when love of your own people comes first; nationalism, when hate for people other than your own comes first. - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] SQL/MED - core functionality
On 28.12.2010 05:16, Robert Haas wrote: On Sat, Dec 25, 2010 at 11:52 PM, Robert Haasrobertmh...@gmail.com wrote: In fact, basically all you can do with CREATE FOREIGN TABLE is set column names, types, and whether they're NOT NULL. But I think that's enough to get started. Even NOT NULL seems questionable. It might be interesting for the planner, but our cost estimates of remote queries are pretty bogus anyway. We can't enforce the NULLness of remote data, so I don't think we should allow NOT NULL, and should always choose plans that are safe if there are NULLs after all. - I removed all of the changes related to adding a HANDLER option to foreign data wrappers. I think that stuff properly belongs in the fdw scan patch. Instead, what I've done here is just prohibit foreign data wrappers from being used in queries. I'm generally pretty negative on syntax-only patches, but then foreign data wrappers have been basically syntax-only for two releases, and I think there's a good chance that if we get the syntax patch in soon we'll actually be able to make it work before we run out of time. So I'm feeling like it might be OK in this case, especially because even with all the trimming down I've done here, this is still a very big patch. +1, now that we have a patch for the rest of the feature as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED - core functionality
On 28.12.2010 05:16, Robert Haas wrote: I'd appreciate some review of what's attached, even though it's not totally final yet. This construct doesn't translate well: + appendStringInfo(allowed, table%s%s%s, + allowView ? or view : , + allowType ? or composite type : , + allowForeignTable ? or foreign table : ); Typo here: @@ -6883,7 +6962,7 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock default: ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), -errmsg(\%s\ is not a table, view, or sequence, +errmsg(\%s\ is not a table, view, sequence, or foreign tabl, or foreign tablee, NameStr(tuple_class-relname; } -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED - core functionality
On Tue, Dec 28, 2010 at 18:45, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: In fact, basically all you can do with CREATE FOREIGN TABLE is set column names, types, and whether they're NOT NULL. But I think that's enough to get started. Even NOT NULL seems questionable. It might be interesting for the planner, but our cost estimates of remote queries are pretty bogus anyway. We can't enforce the NULLness of remote data, so I don't think we should allow NOT NULL, and should always choose plans that are safe if there are NULLs after all. The same can be said for CHECK constraints, but CHECKs on foreign tables are very useful to support multi-nodes partitioning. So, I'd like to support CHECKs even though we cannot enforce the constraints at remove servers. Will we have CHECKs but drop NOT NULLs? Another idea is to have an option to apply those constraints at SELECT. We can find corrupted foreign data at that time. -- 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] autogenerating error code lists (was Re: [COMMITTERS] pgsql: Add foreign data wrapper error code values for SQL/MED.)
On 26/12/10 21:33, Jan Urbański wrote: On 26/12/10 21:17, Tom Lane wrote: =?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: Makes sense. Wait, no, errcodes.sgml includes the entries for success and warnings, but the plpgsql conditions list does not. So we need a separate column to differentiate. OK. But not 0/1 please. Maybe 'E', 'W', or 'S' ? And again, fixed width columns first, so something like sqlstate E/W/S errcode_macro_name plpgsql_condition_name where I guess we could still make the plpgsql condition name optional. All right, E/W/S sounds good. I'm actually faulty of a misnomer by calling the field plpgsql_condition_name. It's more like spec_name, and it will be used to generate plpgsql condition names for E entries and rows in errcodes.sgml for all entries. Remember that there will also be Section: lines there, because errcodes.sgml needs to know where particular the error classes start and end. Here's the basic errcodes.txt file and three scripts to generate errcodes.h, plerrcodes.h and part of errcodes.sgml. I tried wiring it into the build system, but failed, I can't figure out which Makefiles should be updated in order to make errcodes.h and plerrcodes.h generated headers. Could someone help with that? This will actually remove a few entries from plerrcodes.h, that were aliases of other entries (like array_element_error which was an alias of array_subscript_error). Since they did not appear in errcodes.sgml, it shouldn't be a problem. It also adds a forgotten entry for nonstandard_use_of_escape_character in plerrcodes.h. Cheers, Jan # # errcodes.txt # PostgreSQL error codes # # # This list serves a basis for generating source files containing error # codes. It is kept in a common format to make sure all these source files have # the same contents. # The files generated from this one are: # # src/include/utils/errcodes.h # macros defining errcode constants to be used in the rest of the source # # src/pl/plpgsql/src/plerrcodes.h # a list of PL/pgSQL condition names and their SQLSTATE codes # # doc/src/sgml/errcodes-list.sgml # a SGML table of error codes for inclusion in the documentation # # The format of this file is one error code per line, with the following # whitespace-separated fields: # # sqlstateE/W/Serrcode_macro_namespec_name # # where sqlstate is a five-character string following the SQLSTATE conventions, # the second field determines if the code means an error, a warning or success, # errcode_macro_name is the C macro name starting with ERRCODE that will be put # in errcodes.h and spec_name is a lowercase, underscore-separated name that # will be used as the PL/pgSQL condition name and will also be included in the # SGML list. The last field is optional, if not present the PL/pgSQL condition # and the SGML entry will not be generated. # # Empty lines and ones starting with a hash are comments. # # There are also special lines in the format of: # # Section: section description # # that is, lines starting with the string Section:. They are used to delimit # error classes as defined in the SQL spec, and are necessary for SGML output. # # # SQLSTATE codes for errors. # # The SQL99 code set is rather impoverished, especially in the area of # syntactical and semantic errors. We have borrowed codes from IBM's DB2 # and invented our own codes to develop a useful code set. # # When adding a new code, make sure it is placed in the most appropriate # class (the first two characters of the code value identify the class). # The listing is organized by class to make this prominent. # # The generic '000' subclass code should be used for an error only # when there is not a more-specific subclass code defined. # # The SQL spec requires that all the elements of a SQLSTATE code be # either digits or upper-case ASCII characters. # # Classes that begin with 0-4 or A-H are defined by the # standard. Within such a class, subclass values defined by the # standard must begin with 0-4 or A-H. To define a new error code, # ensure that it is either in an implementation-defined class (it # begins with 5-9 or I-Z), or its subclass falls outside the range of # error codes that could be present in future versions of the # standard (i.e. the subclass value begins with 5-9 or I-Z). # # The convention is that new error codes defined by PostgreSQL in a # class defined by the standard have a subclass value that begins # with 'P'. In addition, error codes defined by PostgreSQL clients # (such as ecpg) have a class value that begins with 'Y'. Section: Class 00 - Successful Completion 0SERRCODE_SUCCESSFUL_COMPLETION successful_completion Section: Class 01 - Warning # do not use this class for failure conditions 01000WERRCODE_WARNING warning 0100CWERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED
Re: [HACKERS] SQL/MED - core functionality
On Tue, Dec 28, 2010 at 1:52 AM, Shigeru HANADA han...@metrosystems.co.jp wrote: On Mon, 27 Dec 2010 22:16:42 -0500 Robert Haas robertmh...@gmail.com wrote: OK, here's the patch. Changes from the submitted fdw_syntax patch: In psql document, I found an inconsistency between \command-letter and object-type has been in the original patch. Attached patch would fix it. Thanks, I've applied this to my local branch. -- 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] SQL/MED - core functionality
On Tue, Dec 28, 2010 at 4:45 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.12.2010 05:16, Robert Haas wrote: On Sat, Dec 25, 2010 at 11:52 PM, Robert Haasrobertmh...@gmail.com wrote: In fact, basically all you can do with CREATE FOREIGN TABLE is set column names, types, and whether they're NOT NULL. But I think that's enough to get started. Even NOT NULL seems questionable. It might be interesting for the planner, but our cost estimates of remote queries are pretty bogus anyway. We can't enforce the NULLness of remote data, so I don't think we should allow NOT NULL, and should always choose plans that are safe if there are NULLs after all. It's true that we can't enforce the non-NULL-ness of data, but we also can't enforce that the remote columns have any particular type, or that the number of columns and their names match the remote side, or indeed that the remote table exists at all. I think that the right approach here is to declare that the entire foreign table definition is essentially a contract. The user is promising us that the returned data will match the supplied parameters. If it doesn't, the user should expect errors and/or wrong answers. On a practical level, getting rid of NOT NULL constraints will pessimize many queries, and even more complex table constraints have their uses. Adopting that as a project policy seems short-sighted. - I removed all of the changes related to adding a HANDLER option to foreign data wrappers. I think that stuff properly belongs in the fdw scan patch. Instead, what I've done here is just prohibit foreign data wrappers from being used in queries. I'm generally pretty negative on syntax-only patches, but then foreign data wrappers have been basically syntax-only for two releases, and I think there's a good chance that if we get the syntax patch in soon we'll actually be able to make it work before we run out of time. So I'm feeling like it might be OK in this case, especially because even with all the trimming down I've done here, this is still a very big patch. +1, now that we have a patch for the rest of the feature as well. I think it's going to need some pretty heavy rebasing, over my changes - but yes. -- 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] SQL/MED - core functionality
On Tue, Dec 28, 2010 at 4:59 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.12.2010 05:16, Robert Haas wrote: I'd appreciate some review of what's attached, even though it's not totally final yet. This construct doesn't translate well: Yeah, there are a bunch of remaining error message issues. See the and it's not a bunny rabbit, either thread. I want to get that patch committed first and then rebase this over it. -- 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] estimating # of distinct values
On Tue, Dec 28, 2010 at 1:39 AM, Josh Berkus j...@agliodbs.com wrote: While I don't want to discourage you from working on steam-based estimators ... I'd love to see you implement a proof-of-concept for PostgreSQL, and test it ... the above is a non-argument. It requires us to accept that sample-based estimates cannot ever be made to work, simply because you say so. This argument has been made on this mailing list and on pgsql-performance many times, so I have been assuming that this is settled mathematics. Admittedly, I don't have a citation for this... I would agree that it's impossible to get a decent estimate of n-distinct from a 1% sample. But there's a huge difference between 5% or 10% and a majority of the table. Considering the way that disks work, it's not that huge. If the table is small enough to fit in memory conveniently, it probably wouldn't be unacceptably costly even to read the whole thing. But if it's a terabyte on disk, reading every tenth block is probably going to carry most of the I/O cost of reading every block. Even reading every twentieth or hundredth block is going to be significantly more expensive than what we do now. -- 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] writable CTEs
On Tue, Dec 28, 2010 at 12:45 AM, David Fetter da...@fetter.org wrote: I don't see how people can be relying on links to 9.1-to-be's documentation. Well, it's always handy when the filenames are the same across versions. Ever looked at the 9.0 documentation for something and then modified the URL to see what it looked like in 8.1 or something? The main change I've made is: WITH queries, also referred to as Common table expressions or CTEs, provide a way to write subqueries for use as part of a larger query. I'm concerned that this might not be strictly correct, because the term WITH query may not be exactly equivalent to the term CTE - WITH queries are comprised of one or more CTEs, plus a main query. Or are they? They are. :) No, actually I think Peter has it right. A query with one or more common table expressions is a WITH-query. This is a subtle difference but could affect the way that things are phrased in the documentation. -- 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] UPDATE pg_catalog.pg_proc.prosrc OK?
n Mon, Dec 27, 2010 at 1:23 PM, Joel Jacobson j...@gluefinance.com wrote: I'm working on a tool to simplify updating the source code of database functions. To do a revert my plan is to store the values of pg_proc.* before updating, and then to restore pg_proc for the given oid if a revert is necessary. This raises the question, Is it safe to do, UPDATE pg_proc SET column = new source code WHERE oid = function's oid; instead of using the CREATE OR REPLACE FUNCTION command? I'm not immediately sure whether it's safe, but it seems like playing with fire, and I don't see any particular advantage to doing it this way over using CREATE OR REPLACE FUNCTION. -- 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] Libpq PGRES_COPY_BOTH - version compatibility
Part of this may be my C skills not being good enough - if so, please enlighten me :-) My pg_streamrecv no longer works with 9.1, because it returns PGRES_COPY_BOTH instead of PGRES_COPY_OUT when initating a copy. That's fine. So I'd like to make it work on both. Specifically, I would like it to check for PGRES_COPY_BOTH if the server is 9.1 and PGRES_COPY_OUT if it's 9.0. Which can be done by checking the server version. However, when built against a libpq 9.0, it doesn't even have the symbol PGRES_COPY_BOTH. And I can't check for the presence of said symbol using #ifdef, since it's an enum. Nor is there a #define available to check the version of the header. Is there any way to check this at compile time (so I know if I can use the symbol or not), without using autoconf (I don't want to bring in such a huge dependency for a tiny program)? Also, I notice that PGRES_COPY_BOTH was inserted in the middle of the enum. Doesn't that mean we can get incorrect values for e.g. PGRES_FATAL_ERROR if the client is built against one version of libpq but executes against another? Shouldn't all such enum values always be added at the end? Finaly, as long as I only use the 9.0 style replication, PGRES_COPY_BOTH is actually unnecessary, right? It will work exactly the same way as PGRES_COPY_OUT? -- 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] Libpq PGRES_COPY_BOTH - version compatibility
On Tue, Dec 28, 2010 at 7:13 AM, Magnus Hagander mag...@hagander.net wrote: Part of this may be my C skills not being good enough - if so, please enlighten me :-) My pg_streamrecv no longer works with 9.1, because it returns PGRES_COPY_BOTH instead of PGRES_COPY_OUT when initating a copy. That's fine. So I'd like to make it work on both. Specifically, I would like it to check for PGRES_COPY_BOTH if the server is 9.1 and PGRES_COPY_OUT if it's 9.0. Which can be done by checking the server version. However, when built against a libpq 9.0, it doesn't even have the symbol PGRES_COPY_BOTH. And I can't check for the presence of said symbol using #ifdef, since it's an enum. Nor is there a #define available to check the version of the header. Is there any way to check this at compile time (so I know if I can use the symbol or not), without using autoconf (I don't want to bring in such a huge dependency for a tiny program)? Adding a #define to our headers that you can test for seems like the way to go. Also, I notice that PGRES_COPY_BOTH was inserted in the middle of the enum. Doesn't that mean we can get incorrect values for e.g. PGRES_FATAL_ERROR if the client is built against one version of libpq but executes against another? Shouldn't all such enum values always be added at the end? I think you are right, and that we should fix this. Finaly, as long as I only use the 9.0 style replication, PGRES_COPY_BOTH is actually unnecessary, right? It will work exactly the same way as PGRES_COPY_OUT? So far, the protocol message is all we've changed. I keep hoping some update synchronous replication patches are going to show up, but so far they haven't. -- 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] Libpq PGRES_COPY_BOTH - version compatibility
On Tue, Dec 28, 2010 at 13:18, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 28, 2010 at 7:13 AM, Magnus Hagander mag...@hagander.net wrote: Part of this may be my C skills not being good enough - if so, please enlighten me :-) My pg_streamrecv no longer works with 9.1, because it returns PGRES_COPY_BOTH instead of PGRES_COPY_OUT when initating a copy. That's fine. So I'd like to make it work on both. Specifically, I would like it to check for PGRES_COPY_BOTH if the server is 9.1 and PGRES_COPY_OUT if it's 9.0. Which can be done by checking the server version. However, when built against a libpq 9.0, it doesn't even have the symbol PGRES_COPY_BOTH. And I can't check for the presence of said symbol using #ifdef, since it's an enum. Nor is there a #define available to check the version of the header. Is there any way to check this at compile time (so I know if I can use the symbol or not), without using autoconf (I don't want to bring in such a huge dependency for a tiny program)? Adding a #define to our headers that you can test for seems like the way to go. That's kind of what I was going for ;) Since it's libpq-fe.h, I think it would have to be another one of those edited by src/tools/version_stamp.pl that Tom doesn't like ;) I don't see another way though - since we don't pull the configure output files into libpq-fe.h (and shouldn't). Also, I notice that PGRES_COPY_BOTH was inserted in the middle of the enum. Doesn't that mean we can get incorrect values for e.g. PGRES_FATAL_ERROR if the client is built against one version of libpq but executes against another? Shouldn't all such enum values always be added at the end? I think you are right, and that we should fix this. Phew, at least I'm not completely lost :-) Will you take care of it? Finaly, as long as I only use the 9.0 style replication, PGRES_COPY_BOTH is actually unnecessary, right? It will work exactly the same way as PGRES_COPY_OUT? So far, the protocol message is all we've changed. I keep hoping some update synchronous replication patches are going to show up, but so far they haven't. Well, assuming I run it in async mode, would the protocol be likely to change even then? -- 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] system views for walsender activity
On Tue, Jun 22, 2010 at 06:18, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Magnus Hagander mag...@hagander.net wrote: The downside is that version 1 will require an initdb, and not version 2, right? Unfortunately, 2 also requires initdb because pg_stat_activity will use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0. All of them are items for 9.1. Did this one end up on the floor? We definitely need the very basic level for 9.1, and we can always improve on it later :-) Do you want to keep working on it, or do you want me to pick it up? Any of the suggestions that includes the master showing data from the slaves requires some kind of feedback going in from the slave, making things a lot more complex (the slave is no longer passive) - let's leave those for now. (you can use the 2ndquadrant replmgr to get some of that :P) I'm not sure it makes much sense to add walsenders to pg_stat_activity - a lot of the fields would no longer make any sense (statement start? query start?) - I think we're better off with a separate view for pg_stat_walsender. It would then only need the columns for procpid, usesysid, usename, client_addr, client_port, and the WALsender specific fields. -- 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
[HACKERS] Function for dealing with xlog data
Currently, a number of functions return data in a really calculation-unfriendly format, e.g: postgres=# select * from pg_current_xlog_location(); pg_current_xlog_location -- 0/3013158 (1 row) It would be very useful to have a way to convert this to a bigint - so we can do differences between different values easily. And it's AFAIUI easily converted to a 64-bit integer. Would others find this useful as well? What's the best way of doing it? Should we have a function that takes text as input, or should the functions in question be made to return a new datatype that could then be casted? -- 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] system views for walsender activity
On Tue, Dec 28, 2010 at 21:46, Magnus Hagander mag...@hagander.net wrote: Unfortunately, 2 also requires initdb because pg_stat_activity will use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0. All of them are items for 9.1. Did this one end up on the floor? We definitely need the very basic level for 9.1, and we can always improve on it later :-) Do you want to keep working on it, or do you want me to pick it up? OK, I'll work for it. I'm not sure it makes much sense to add walsenders to pg_stat_activity - a lot of the fields would no longer make any sense (statement start? query start?) - I think we're better off with a separate view for pg_stat_walsender. It would then only need the columns for procpid, usesysid, usename, client_addr, client_port, and the WALsender specific fields. +1 for the separate view. backend_start (or replication_start?) might be also reasonable. -- 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] writable CTEs
Excerpts from David Fetter's message of mar dic 28 02:45:11 -0300 2010: On Tue, Dec 28, 2010 at 03:49:16AM +, Peter Geoghegan wrote: Attached documentation patch should make things clearer. I haven't changed the queries-with section to queries-common-table-expression per David's suggestion for the sake of stability. I hesitate to change it without reaching a consensus - will this break a lot of links? I don't see how people can be relying on links to 9.1-to-be's documentation. If you change it to give it a new name, the old name disappears. It's a little thing but we've gotten complaints about links disappearing from one version to another. (Personally this has given me reason to think that the /current link to docs are a bad idea). -- Álvaro Herrera alvhe...@commandprompt.com 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] system views for walsender activity
On Tue, Dec 28, 2010 at 14:14, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Tue, Dec 28, 2010 at 21:46, Magnus Hagander mag...@hagander.net wrote: Unfortunately, 2 also requires initdb because pg_stat_activity will use LEFT JOIN instead of normal JOIN not to hide rows with databaseid = 0. All of them are items for 9.1. Did this one end up on the floor? We definitely need the very basic level for 9.1, and we can always improve on it later :-) Do you want to keep working on it, or do you want me to pick it up? OK, I'll work for it. Great. I'm not sure it makes much sense to add walsenders to pg_stat_activity - a lot of the fields would no longer make any sense (statement start? query start?) - I think we're better off with a separate view for pg_stat_walsender. It would then only need the columns for procpid, usesysid, usename, client_addr, client_port, and the WALsender specific fields. +1 for the separate view. backend_start (or replication_start?) might be also reasonable. Yeah, agreed. backend_start is probably the best one - replication_start may be considered conceptually different if the connection was dropped and reconnected. backend_start is more explicit. -- 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] UPDATE pg_catalog.pg_proc.prosrc OK?
2010/12/28 Robert Haas robertmh...@gmail.com I'm not immediately sure whether it's safe, but it seems like playing with fire, and I don't see any particular advantage to doing it this way over using CREATE OR REPLACE FUNCTION. While I understand some of the SQL commands affecting pg_catalog have side effects, such as CREATE DATABASE, others seems to lack side effects. To use CREATE OR REPLACE FUNCTION, I would have to assemble SQL from the data in pg_proc.* for each function, which is quite a complex task (e.g., pg_dump does this, and it's far from trivial, due to differences between different versions etc). I understand it's not a good idea to read/write pg_proc between different databases, but this is not my case. My plan: 1. Take snapshot of pg_catalog.pg_proc.* 2. Update existing/install new source code of functions 3. Monitor how the live system behaves (might take 30 minutes or something like that) 4. If problems occurr, revent to the old state by removing the new pg_proc entries and restoring the modified existing ones. Problems are not expected since the new code has been tested locally in a database with identical schema, but I've learned you can never be one hundred percent sure everything always works. Until now, I've been creating a revent .sql-file manually, which drops the new functions and restores the replaced functions with their old source code. This is quite time consuming and of course prone to human errors. Alternative approach: It would be good if pg_dump could split a plaintext schema dump into separate files. That would allow you to only restore the functions, which would solve part of my problem, but would still cause problems for functions where you alter the arguments, in which case the existing function with the same name needs to be dropped first, before creating the new function with different arguments. For such scenarios, I would need to drop the new functions first, before restoring the old functions from the dump. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden
Re: [HACKERS] UPDATE pg_catalog.pg_proc.prosrc OK?
On 28.12.2010 15:19, Joel Jacobson wrote: 2010/12/28 Robert Haasrobertmh...@gmail.com I'm not immediately sure whether it's safe, but it seems like playing with fire, and I don't see any particular advantage to doing it this way over using CREATE OR REPLACE FUNCTION. While I understand some of the SQL commands affecting pg_catalog have side effects, such as CREATE DATABASE, others seems to lack side effects. To use CREATE OR REPLACE FUNCTION, I would have to assemble SQL from the data in pg_proc.* for each function, which is quite a complex task (e.g., pg_dump does this, and it's far from trivial, due to differences between different versions etc). Since 8.4, there is a function called pg_get_functiondef() which does all the hard work. Or you could use pg_dump. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_primary_conninfo
Attached patch implements a function called pg_primary_conninfo() that
returns, well, the primary_conninfo used on the standby when in
streaming replication mode (otherwise NULL).
Objections?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 14098,14103 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 14098,14106
indexterm
primarypg_last_xact_replay_timestamp/primary
/indexterm
+indexterm
+ primarypg_primary_conninfo/primary
+/indexterm
para
The functions shown in xref
***
*** 14168,14173 postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
--- 14171,14187
the function returns NULL.
/entry
/row
+ row
+entry
+ literalfunctionpg_primary_conninfo()/function/literal
+/entry
+entrytypetext/type/entry
+entryGets the connection string used to connect to the primary
+ when using streaming replication. When the server has been started
+ normally without recovery, or when file based recovery is in
+ progress, the function returns NULL.
+/entry
+ /row
/tbody
/tgroup
/table
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 8829,8834 pg_last_xlog_replay_location(PG_FUNCTION_ARGS)
--- 8829,8857
}
/*
+ * Report the connection info that the walreceiver is using to talk to the
+ * primary.
+ */
+ Datum
+ pg_primary_conninfo(PG_FUNCTION_ARGS)
+ {
+ /* use volatile pointer to prevent code rearrangement */
+ volatile WalRcvData *walrcv = WalRcv;
+ XLogRecPtr recptr;
+ char conninfo[MAXCONNINFO];
+
+ SpinLockAcquire(walrcv-mutex);
+ recptr = walrcv-receivedUpto;
+ memcpy(conninfo, walrcv-conninfo, MAXCONNINFO);
+ SpinLockRelease(walrcv-mutex);
+
+ if (recptr.xlogid == 0 recptr.xrecoff == 0 conninfo[0] != '\0')
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(cstring_to_text(conninfo));
+ }
+
+ /*
* Compute an xlog file name and decimal byte offset given a WAL location,
* such as is returned by pg_stop_backup() or pg_xlog_switch().
*
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***
*** 3386,3392 DESCR(xlog filename, given an xlog location);
DATA(insert OID = 3810 ( pg_is_in_recovery PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 _null_ _null_ _null_ _null_ pg_is_in_recovery _null_ _null_ _null_ ));
DESCR(true if server is in recovery);
!
DATA(insert OID = 3820 ( pg_last_xlog_receive_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_xlog_receive_location _null_ _null_ _null_ ));
DESCR(current xlog flush location);
DATA(insert OID = 3821 ( pg_last_xlog_replay_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_xlog_replay_location _null_ _null_ _null_ ));
--- 3386,3393
DATA(insert OID = 3810 ( pg_is_in_recovery PGNSP PGUID 12 1 0 0 f f f t f v 0 0 16 _null_ _null_ _null_ _null_ pg_is_in_recovery _null_ _null_ _null_ ));
DESCR(true if server is in recovery);
! DATA(insert OID = 3819 ( pg_primary_conninfo PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_primary_conninfo _null_ _null_ _null_ ));
! DESCR(connection string for primary);
DATA(insert OID = 3820 ( pg_last_xlog_receive_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_xlog_receive_location _null_ _null_ _null_ ));
DESCR(current xlog flush location);
DATA(insert OID = 3821 ( pg_last_xlog_replay_location PGNSP PGUID 12 1 0 0 f f f t f v 0 0 25 _null_ _null_ _null_ _null_ pg_last_xlog_replay_location _null_ _null_ _null_ ));
--
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] UPDATE pg_catalog.pg_proc.prosrc OK?
On Tue, Dec 28, 2010 at 8:19 AM, Joel Jacobson j...@gluefinance.com wrote: My plan: 1. Take snapshot of pg_catalog.pg_proc.* 2. Update existing/install new source code of functions 3. Monitor how the live system behaves (might take 30 minutes or something like that) 4. If problems occurr, revent to the old state by removing the new pg_proc entries and restoring the modified existing ones. Problems are not expected since the new code has been tested locally in a database with identical schema, but I've learned you can never be one hundred percent sure everything always works. Until now, I've been creating a revent .sql-file manually, which drops the new functions and restores the replaced functions with their old source code. I think there's not much getting around the fact that you will have to grovel through pg_proc to get information about the current definitions. All I'm saying is, once you've done that, generate CREATE/DROP FUNCTION commands rather than UPDATE statements. That way, if there ARE relevant side effects of CREATE OR REPLACE FUNCTION, you'll get them. IOW, reading pg_proc is fine. Writing it is probably better avoided (and not that hard to avoid). -- 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] pg_primary_conninfo
On Tue, Dec 28, 2010 at 8:31 AM, Magnus Hagander mag...@hagander.net wrote: Attached patch implements a function called pg_primary_conninfo() that returns, well, the primary_conninfo used on the standby when in streaming replication mode (otherwise NULL). +1. Let's make sure to explicitly document what this function returns when recovery was previous in progress, but we are now in normal running. -- 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] Function for dealing with xlog data
On Tue, Dec 28, 2010 at 7:49 AM, Magnus Hagander mag...@hagander.net wrote: Currently, a number of functions return data in a really calculation-unfriendly format, e.g: postgres=# select * from pg_current_xlog_location(); pg_current_xlog_location -- 0/3013158 (1 row) It would be very useful to have a way to convert this to a bigint - so we can do differences between different values easily. And it's AFAIUI easily converted to a 64-bit integer. Would others find this useful as well? Yes. What's the best way of doing it? Should we have a function that takes text as input, or should the functions in question be made to return a new datatype that could then be casted? The new datatype seems more elegant, but a conversion function would be a lot less work. -- 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] pg_primary_conninfo
On Tue, Dec 28, 2010 at 14:38, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 28, 2010 at 8:31 AM, Magnus Hagander mag...@hagander.net wrote: Attached patch implements a function called pg_primary_conninfo() that returns, well, the primary_conninfo used on the standby when in streaming replication mode (otherwise NULL). +1. Let's make sure to explicitly document what this function returns when recovery was previous in progress, but we are now in normal running. Oh, didn't think of that scenario. Is that intended behaviour though? I tend to think that it is (since you can check with pg_is_in_recovery) as long as it's documented, but might it make more sense to have it return NULL in this case? -- 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] estimating # of distinct values
The simple truth is
1) sampling-based estimators are a dead-end
The Charikar and Chaudhuri paper does not, in fact, say that it is
impossible to improve sampling-based estimators as you claim it does. In
fact, the authors offer several ways to improve sampling-based
estimators. Further, 2000 was hardly the end of sampling-estimation
paper publication; there are later papers with newer ideas.
Well, the paper states that there is a lower bound of the possible error
of a sampling based estimator, depending on the sample size. The actual
inequality is
error(d) = sqrt( (n-r)/2r * 1/q)
where error(D) is ratio error (d - estimated number of distinct values,
D - actual number of distinct values)
error(d) = max{ D/d, d/D }
And all this is with probability q = e^{-1000} (you can choose this).
Say you have a table with 1.000.000 rows and you use a sample of 1.000
rows to do an estimate. In that case you get
erorr(d) = 99 with q = 0.05
erorr(d) = 70 with q = 0.1
error(d) = 31 with q = 0.5
if you can 10% of the table, you get this
error(d) = 9.5 with q = 0.05
error(d) = 6.7 with q = 0.1
error(d) = 3 with q = 0.5
So even with 10% of the table, there's a 10% probability to get an
estimate that's 7x overestimated or underestimated. With lower probability
the interval is much wider.
For example, I still think we could tremendously improve our current
sampling-based estimator without increasing I/O by moving to block-based
estimation*. The accuracy statistics for block-based samples of 5% of
the table look quite good.
Well, that's certainly possible. But you can only achieve the error(d)
lower boundary consistently (for all datasets), you can't do better. And
they've already presented an estimator that does exactly this (called AE -
Adaptive Estimator in the paper).
I would agree that it's impossible to get a decent estimate of
n-distinct from a 1% sample. But there's a huge difference between 5%
or 10% and a majority of the table.
Sure we can do better. But there's a limit we can't cross no matter what
estimator we choose and how large the sample will be.
I've seen several post-2000 paper on sample-based estimators, but those
are mostly hybrid estimators, i.e. estimators composed of several simple
estimators. So in the end it's pretty complicated, you need to gather a
lot of different stats, and still you can't get better error than the
lower bound :-(
So yes, we can improve the current estimator (making it more complex), but
it does not solve the problem actually.
Again, don't let this discourage you from attempting to write a
steam-based estimator. But do realize that you'll need to *prove* its
superiority, head-to-head, against sxampling-based estimators.
[* http://www.jstor.org/pss/1391058 (unfortunately, no longer
public-access)]
It's available here http://www.stat.washington.edu/research/reports/1990s/
But it does not present an estimator contradicting the Charikar and
Chaudhuri paper - it's still 'just' a sample-based estimator, alough they
draw the sample at the block level. But yes, that's good idea and I've
already mentioned it in the cross-column stats thread I think.
The question is if a sample obtained in this way will be as good as the
current samples. This way you could get quite separate 'clusters' of
values, one cluster for each block, although in reality the values are
uniformly distributed. And the resulting histogram would be crippled by
this I guess too.
But if you know about interesting papers on sample-based estimators
(especially post-2000), let me know. I've searched for them, but those I
found were not very interesting IMHO.
Tomas
--
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] Function for dealing with xlog data
On Tue, Dec 28, 2010 at 14:39, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 28, 2010 at 7:49 AM, Magnus Hagander mag...@hagander.net wrote: Currently, a number of functions return data in a really calculation-unfriendly format, e.g: postgres=# select * from pg_current_xlog_location(); pg_current_xlog_location -- 0/3013158 (1 row) It would be very useful to have a way to convert this to a bigint - so we can do differences between different values easily. And it's AFAIUI easily converted to a 64-bit integer. Would others find this useful as well? Yes. What's the best way of doing it? Should we have a function that takes text as input, or should the functions in question be made to return a new datatype that could then be casted? The new datatype seems more elegant, but a conversion function would be a lot less work. Well, yeah, that was obvious ;) The question is, how much do we prefer the more elegant method? ;) -- 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] Function for dealing with xlog data
Excerpts from Magnus Hagander's message of mar dic 28 10:46:31 -0300 2010: On Tue, Dec 28, 2010 at 14:39, Robert Haas robertmh...@gmail.com wrote: What's the best way of doing it? Should we have a function that takes text as input, or should the functions in question be made to return a new datatype that could then be casted? The new datatype seems more elegant, but a conversion function would be a lot less work. Well, yeah, that was obvious ;) The question is, how much do we prefer the more elegant method? ;) If we go the new type route, do we need it to have an implicit cast to text, for backwards compatibility? -- Álvaro Herrera alvhe...@commandprompt.com 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] writable CTEs
On 28 December 2010 12:09, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 28, 2010 at 12:45 AM, David Fetter da...@fetter.org wrote: I don't see how people can be relying on links to 9.1-to-be's documentation. Well, it's always handy when the filenames are the same across versions. Ever looked at the 9.0 documentation for something and then modified the URL to see what it looked like in 8.1 or something? I do this all the time. Anyway, I intend for this doc patch to be backported to 8.4 as a bugfix, which is part of the reason why it isn't invasive - it's just a clarification. Clearly if it makes sense for 9.1, it makes just as much sense for 9.0 and 8.4. No, actually I think Peter has it right. A query with one or more common table expressions is a WITH-query. This is a subtle difference but could affect the way that things are phrased in the documentation. Attached is a new patch written with this consideration in mind. It also has an acronym.sgml entry for CTE, which was absent from my earlier patch. I think David actually agreed that I was right to have doubts. -- Regards, Peter Geoghegan diff --git a/doc/src/sgml/acronyms.sgml b/doc/src/sgml/acronyms.sgml index 23ab3b4..5dad0db 100644 --- a/doc/src/sgml/acronyms.sgml +++ b/doc/src/sgml/acronyms.sgml @@ -99,6 +99,15 @@ /varlistentry varlistentry +termacronymCTE/acronym/term +listitem + para + link linkend=queries-withCommon Table Expression/link + /para +/listitem + /varlistentry + + varlistentry termacronymCVE/acronym/term listitem para diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index f6e081e..97e3977 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1525,7 +1525,7 @@ SELECT replaceableselect_list/replaceable FROM replaceabletable_expression sect1 id=queries-with - titleliteralWITH/literal Queries/title + titleliteralWITH/literal Queries (Common Table Expressions)/title indexterm zone=queries-with primaryWITH/primary @@ -1539,7 +1539,8 @@ SELECT replaceableselect_list/replaceable FROM replaceabletable_expression para literalWITH/ provides a way to write subqueries for use in a larger - query. The subqueries can be thought of as defining + query. The subqueries, which are often referred to as Common table + expressions or acronymCTE/acronyms, can be thought of as defining temporary tables that exist just for this query. One use of this feature is to break down complicated queries into simpler parts. An example is: -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] small table left outer join big table
Hi, Please see the following plan: postgres=# explain select * from small_table left outer join big_table using (id); QUERY PLAN Hash Left Join (cost=126408.00..142436.98 rows=371 width=12) Hash Cond: (small_table.id = big_table.id) - Seq Scan on small_table (cost=0.00..1.09 rows=9 width=8) - Hash (cost=59142.00..59142.00 rows=410 width=8) - Seq Scan on big_table (cost=0.00..59142.00 rows=410 width=8) (5 rows) Here I have a puzzle, why not choose the small table to build hash table? It can avoid multiple batches thus save significant I/O cost, isn't it? We can perform this query in two phases: 1) inner join, using the small table to build hash table. 2) check whether each tuple in the hash table has matches before, which can be done with another flag bit The only compromise is the output order, due to the two separate phases. Not sure whether the SQL standard requires it. Thanks, Li Jie
Re: [HACKERS] writable CTEs
On tis, 2010-12-28 at 00:19 +, Peter Geoghegan wrote: It's worth noting that officially (i.e. in the docs), we don't even call CTEs CTEs at any point. We call them WITH queries. I think that that's a mistake because we call them CTEs everywhere else. I think WITH query or WITH clause is more understandable than CTE, which to me is a term that has no relationship with anything else. -- 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] Libpq PGRES_COPY_BOTH - version compatibility
Robert Haas robertmh...@gmail.com writes: On Tue, Dec 28, 2010 at 7:13 AM, Magnus Hagander mag...@hagander.net wrote: Also, I notice that PGRES_COPY_BOTH was inserted in the middle of the enum. Doesn't that mean we can get incorrect values for e.g. PGRES_FATAL_ERROR if the client is built against one version of libpq but executes against another? Shouldn't all such enum values always be added at the end? I think you are right, and that we should fix this. Yes, that was a completely wrong move :-( 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] estimating # of distinct values
t...@fuzzy.cz wrote: So even with 10% of the table, there's a 10% probability to get an estimate that's 7x overestimated or underestimated. With lower probability the interval is much wider. Hmmm... Currently I generally feel I'm doing OK when the estimated rows for a step are in the right order of magnitude -- a 7% error would be a big improvement in most cases. Let's not lose track of the fact that these estimates are useful even when they are not dead-on accurate. -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] writable CTEs
Peter Eisentraut pete...@gmx.net writes: On tis, 2010-12-28 at 00:19 +, Peter Geoghegan wrote: It's worth noting that officially (i.e. in the docs), we don't even call CTEs CTEs at any point. We call them WITH queries. I think that that's a mistake because we call them CTEs everywhere else. I think WITH query or WITH clause is more understandable than CTE, which to me is a term that has no relationship with anything else. I'm with Peter on that. CTE is a completely meaningless term to most users. As for the problem at hand, couldn't we use WITH ... RETURNING, or some other phrase based on what users actually see/write? DML has the same problem as CTE, namely it's just another damn TLA. It may be one that more people have heard of, but that doesn't make it particularly attractive. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
Magnus Hagander mag...@hagander.net writes: Attached patch implements a function called pg_primary_conninfo() that returns, well, the primary_conninfo used on the standby when in streaming replication mode (otherwise NULL). Objections? What's the use case? And aren't there security reasons to NOT expose that? It might contain a password for instance. + if (recptr.xlogid == 0 recptr.xrecoff == 0 conninfo[0] != '\0') + PG_RETURN_NULL(); This test seems a bit incoherent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
On Dec 28, 2010 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Attached patch implements a function called pg_primary_conninfo() that returns, well, the primary_conninfo used on the standby when in streaming replication mode (otherwise NULL). Objections? What's the use case? And aren't there security reasons to NOT expose that? It might contain a password for instance. Good point - should be made superuser only. + if (recptr.xlogid == 0 recptr.xrecoff == 0 conninfo[0] != '\0') + PG_RETURN_NULL(); This test seems a bit incoherent. I used that to test that streaming repl is enabled at all. Is there a better way? /Magnus
[HACKERS] page compression
I know its been discussed before, and one big problem is license and patent problems. Would this project be a problem: http://oldhome.schmorp.de/marc/liblzf.html -Andy -- 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] small table left outer join big table
On Tue, Dec 28, 2010 at 5:13 AM, Jie Li jay23j...@gmail.com wrote:
Hi,
Please see the following plan:
postgres=# explain select * from small_table left outer join big_table
using (id);
QUERY PLAN
Hash Left Join (cost=126408.00..142436.98 rows=371 width=12)
Hash Cond: (small_table.id = big_table.id)
- Seq Scan on small_table (cost=0.00..1.09 rows=9 width=8)
- Hash (cost=59142.00..59142.00 rows=410 width=8)
- Seq Scan on big_table (cost=0.00..59142.00 rows=410
width=8)
(5 rows)
Here I have a puzzle, why not choose the small table to build hash table?
It can avoid multiple batches thus save significant I/O cost, isn't it?
We can perform this query in two phases:
1) inner join, using the small table to build hash table.
2) check whether each tuple in the hash table has matches before, which can
be done with another flag bit
The only compromise is the output order, due to the two separate phases.
Not sure whether the SQL standard requires it.
SQL standard does not require the result to be in any particular order
unless an ORDER BY is used.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] Libpq PGRES_COPY_BOTH - version compatibility
Magnus Hagander mag...@hagander.net writes: On Tue, Dec 28, 2010 at 13:18, Robert Haas robertmh...@gmail.com wrote: Adding a #define to our headers that you can test for seems like the way to go. That's kind of what I was going for ;) I don't see the point. You're going to need a *run time* test on PQserverVersion to figure out what the server will return, no? Also, if you really do need to figure out which PG headers you're compiling against, looking at catversion.h is the accepted way to do it. There's no need for yet another symbol. 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] Function for dealing with xlog data
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Magnus Hagander's message of mar dic 28 10:46:31 -0300 2010: Well, yeah, that was obvious ;) The question is, how much do we prefer the more elegant method? ;) If we go the new type route, do we need it to have an implicit cast to text, for backwards compatibility? I'd argue not. Probably all existing uses are just selecting the function value. What comes back to the client will just be the text form anyway. I'm of the opinion that a new type isn't worth the work, myself, but it would mostly be up to whoever was doing the work. 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] page compression
On Tue, Dec 28, 2010 at 10:10 AM, Andy Colson a...@squeakycode.net wrote: I know its been discussed before, and one big problem is license and patent problems. Would this project be a problem: http://oldhome.schmorp.de/marc/liblzf.html It looks like even liblzf is not going to be accepted. I have proposed to only link against liblzf if available for pg_dump and have somehow failed, see: http://archives.postgresql.org/pgsql-hackers/2010-11/msg00824.php Remember that PostgreSQL has toast tables to compress large values and store them externally, so it still has to be proven that page compression has the same benefit for PostgreSQL as for other databases. Ironically we also use an LZ compression algorithm for toast compression (defined in pg_lzcompress.c). I am still failing to understand why linking against liblzf would bring us deeper into the compression patents mine field than we already are by hardwiring and shipping this other algorithm in pg_lzcompress.c. Joachim -- 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_primary_conninfo
Magnus Hagander mag...@hagander.net writes: On Dec 28, 2010 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: What's the use case? And aren't there security reasons to NOT expose that? It might contain a password for instance. Good point - should be made superuser only. I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? 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] page compression
On Dec 28, 2010, at 10:33 AM, Joachim Wieland j...@mcknight.de wrote: On Tue, Dec 28, 2010 at 10:10 AM, Andy Colson a...@squeakycode.net wrote: I know its been discussed before, and one big problem is license and patent problems. Would this project be a problem: http://oldhome.schmorp.de/marc/liblzf.html It looks like even liblzf is not going to be accepted. I have proposed to only link against liblzf if available for pg_dump and have somehow failed, see: I thought that was mostly about not wanting multiple changes in one patch. I don't see why liblzf would be objectionable in general. ...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] estimating # of distinct values
t...@fuzzy.cz wrote: So even with 10% of the table, there's a 10% probability to get an estimate that's 7x overestimated or underestimated. With lower probability the interval is much wider. Hmmm... Currently I generally feel I'm doing OK when the estimated rows for a step are in the right order of magnitude -- a 7% error would be a big improvement in most cases. Let's not lose track of the fact that these estimates are useful even when they are not dead-on accurate. Well, but that's not 7%, thats 7x! And the theorem says 'greater or equal' so this is actually the minimum - you can get a much bigger difference with lower probability. So you can easily get an estimate that is a few orders off. Anyway I really don't want precise values, just a reasonable estimate. As I said, we could use the AE estimate they proposed in the paper. It has the nice feature that it actually reaches the low boundary (thus the inequality changes to equality). The downside is that there are estimators with better behavior on some datasets. Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump --split patch
Dear fellow hackers, Problem: A normal diff of two slightly different schema dump files (pg_dump -s), will not produce a user-friendly diff, as you get all changes in the same file. Solution: I propose a new option to pg_dump, --split, which dumps each object to a separate file in a user friendly directory structure: [-f filename] : main dump file, imports each splitted part using \i [-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid Example: If the filename (-f) is pg.dump, the following directory structure would be created: $ ./pg_dump -f /crypt/pg.dump --split -F p -s glue /crypt/pg.dump-split/VIEW/ /crypt/pg.dump-split/TYPE/ /crypt/pg.dump-split/TRIGGER/ /crypt/pg.dump-split/TABLE/ /crypt/pg.dump-split/SEQUENCE/ /crypt/pg.dump-split/SCHEMA/ /crypt/pg.dump-split/PROCEDURAL_LANGUAGE/ /crypt/pg.dump-split/INDEX/ /crypt/pg.dump-split/FUNCTION/ /crypt/pg.dump-split/FK_CONSTRAINT/ /crypt/pg.dump-split/CONSTRAINT/ /crypt/pg.dump-split/AGGREGATE/ In each such directory, one directory per object name is created. If we would have a function foobar with oid 12345 it would be saved to: /crypt/pg.dump-split/FUNCTION/foobar/12345.sql In the pg.dump plain text file, the files are linked in using the \i psql command, e.g.: \i /crypt/pg.dump-split/FUNCTION/foobar/12345.sql Potential use-case scenarios: *) Version control your database schema, by exporting it daily (using --split) and commiting the differences. *) Compare differences of schema dumps created in different points in time. Since objects are stored in separate files, it is easier to see what areas were modified, compared to looking at the diff of two entire schemas. *) Restore only some objects, based on type (e.g., only the functions) or name (e.g. only fucntions of certain name/names). I've tested the patch for both the latest HEAD (9.1devel) as well as 8.4.6. Feedback welcome. -- Best regards, Joel Jacobson Glue Finance pg-dump-split-plain-text-files-8.4.6.patch Description: Binary data pg-dump-split-plain-text-files-9.1devel.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
Re: [HACKERS] writable CTEs
On 28 December 2010 14:53, Tom Lane t...@sss.pgh.pa.us wrote: I'm with Peter on that. CTE is a completely meaningless term to most users. I don't believe that that's the case. If CTE is a completely meaningless term to most users, WITH query is even more meaningless. I never refer to WITH queries in conversation, and I have never heard someone else do so. I have often talked about CTEs though. Besides, I'm not suggesting that we should completely change the title, or change the section name at all, or change any existing text from the docs. The doc patch is just a clarification that I believe will be useful. If I search for common table expressions on Wikipedia, I am sent to the common table expressions article, without any re-direction. The article doesn't mention with query as a synonym of CTE at any point. If I search for With query, the first page of results (20 articles) doesn't have anything about CTEs at all. The situation with Google is similar. The situation with postgresql.org is similar, except that searching for CTE there is fairly useless too. Granted, all of this may have something to do with the ambiguity of the term with query in a more general context, but the fact that I never hear the term in conversation probably has something to do with that too. -- Regards, Peter Geoghegan -- 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] knngist - 0.8
Martijn van Oosterhout klep...@svana.org writes: On Sun, Dec 26, 2010 at 08:13:40PM -0500, Tom Lane wrote: [ thinks for a bit... ] One reason for having a different structure would be if we needed to represent abstract semantics for some operators that couldn't be associated with a btree opclass. One thing that comes to mind is the operators used for hash indexes, namely the hash() function. The hash opclasses handle that fine. I cannot conceive of any reason for shoehorning hash functions into btree opclasses. With respect to the collation of strings I have thought it useful to be able to define a sortkey() function, which would map the input space to a 8 byte integer and satisfies the rule: sortkey(a) sortkey(b) implies a b I'm pretty dubious about the workability of that one, but again, there isn't any obvious reason why we'd need a new catalog structure to support it. If we did want it, it could be an optional support function in btree opclasses. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
Joel Jacobson j...@gluefinance.com writes: Dear fellow hackers, Problem: A normal diff of two slightly different schema dump files (pg_dump -s), will not produce a user-friendly diff, as you get all changes in the same file. Solution: I propose a new option to pg_dump, --split, which dumps each object to a separate file in a user friendly directory structure: Um ... how does that solve the claimed problem exactly? [-f filename] : main dump file, imports each splitted part using \i [-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid This particular choice seems remarkably *un* friendly, since two dumps from different DBs will inevitably not share the same OIDs, making it practically impossible to compare them even if they are logically identical. But even without the choice to use OIDs in the filenames I'm unconvinced that file-per-object is a good idea in any way shape or form. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
Le 28/12/2010 16:34, Tom Lane a écrit : Magnus Hagander mag...@hagander.net writes: On Dec 28, 2010 3:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: What's the use case? And aren't there security reasons to NOT expose that? It might contain a password for instance. Good point - should be made superuser only. I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? This is something I wanted to have in 9.0 when I coded in pgAdmin some features related to the HotStandby. Knowing on which IP is the master can help pgAdmin offer the user to register the master node. It's also interesting to get lag between master and slave. As soon as I'm connected to a slave, I can connect to the master and get the lag between them. Something I can't do right now in pgAdmin. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2010 16:34, Tom Lane a écrit : I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? This is something I wanted to have in 9.0 when I coded in pgAdmin some features related to the HotStandby. Knowing on which IP is the master can help pgAdmin offer the user to register the master node. It's also interesting to get lag between master and slave. As soon as I'm connected to a slave, I can connect to the master and get the lag between them. Something I can't do right now in pgAdmin. The proposed primary_conninfo seems like a pretty awful solution to those problems, though. 1. It'll have to be restricted to superusers, therefore ordinary users on the slave can't actually make use of it. 2. It's not what you want, since you don't want to connect as the replication user. Therefore, you'd have to start by parsing out the parts you do need. Expecting every client to include conninfo parsing logic doesn't seem cool to me. I can see the point of, say, a primary_host_address() function returning inet, which would be way better on both those dimensions than the current proposal. But I'm not sure what else would be needed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
2010/12/28 Tom Lane t...@sss.pgh.pa.us Joel Jacobson j...@gluefinance.com writes: Dear fellow hackers, Problem: A normal diff of two slightly different schema dump files (pg_dump -s), will not produce a user-friendly diff, as you get all changes in the same file. Solution: I propose a new option to pg_dump, --split, which dumps each object to a separate file in a user friendly directory structure: Um ... how does that solve the claimed problem exactly? Because then you can do, $ diff -r old schema dump dir new schema dump dir, instead of, $ diff old entire schema dump new entire schema dump which will nicely reveal each individual object modified, as opposed to a huge global diff of everything [-f filename] : main dump file, imports each splitted part using \i [-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid This particular choice seems remarkably *un* friendly, since two dumps from different DBs will inevitably not share the same OIDs, making it practically impossible to compare them even if they are logically identical. But even without the choice to use OIDs in the filenames I'm unconvinced that file-per-object is a good idea in any way shape or form. Good point! To compare two different database, perhaps it's possible to use a sequence, 1,2,...,n for each file in each directory, i.e., /[desc]/[tag]/[n], and to sort them by something distinct which will ensure the same numbering between different databases, such as the arguments for functions, or other properties for other kind of objects. Any ideas? (In my case, I didn't need to compare schemas between different database. I needed to compare two dumps created at different points in time of the same database, which do share the same oids for objects existing in both versions.) regards, tom lane -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden
Re: [HACKERS] pg_primary_conninfo
Le 28/12/2010 17:36, Tom Lane a écrit : Guillaume Lelarge guilla...@lelarge.info writes: Le 28/12/2010 16:34, Tom Lane a écrit : I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? This is something I wanted to have in 9.0 when I coded in pgAdmin some features related to the HotStandby. Knowing on which IP is the master can help pgAdmin offer the user to register the master node. It's also interesting to get lag between master and slave. As soon as I'm connected to a slave, I can connect to the master and get the lag between them. Something I can't do right now in pgAdmin. The proposed primary_conninfo seems like a pretty awful solution to those problems, though. I would say not the best one, but better than what I have now :) 1. It'll have to be restricted to superusers, therefore ordinary users on the slave can't actually make use of it. pgAdmin's users usually connect as superusers. 2. It's not what you want, since you don't want to connect as the replication user. Therefore, you'd have to start by parsing out the parts you do need. Expecting every client to include conninfo parsing logic doesn't seem cool to me. I can see the point of, say, a primary_host_address() function returning inet, which would be way better on both those dimensions than the current proposal. But I'm not sure what else would be needed. Yeah, it would be better that way. I'm actually interested in Magnus's patch because, during 9.0 development phase, I had in mind to parse the primary_conninfo till I found I could not get this value with SHOW or current_setting(). But, actually, what I really need is host and port. This way, I could connect to the master node, with the same user and password that was used on the slave node. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
On Tue, Dec 28, 2010 at 11:36 AM, Tom Lane t...@sss.pgh.pa.us wrote:
I can see the point of, say, a primary_host_address() function returning
inet, which would be way better on both those dimensions than the
current proposal. But I'm not sure what else would be needed.
+1, since it bypasses security risks associated with exposing
username/password.
Ability to see port number will be a useful addition.
Another case to consider is what if slave is connected to a local server
over unix-domain sockets? Returning NULL might make it ambiguous with the
case where the instance has been promoted out of standby.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] pg_dump --split patch
Joel Jacobson j...@gluefinance.com writes: 2010/12/28 Tom Lane t...@sss.pgh.pa.us Joel Jacobson j...@gluefinance.com writes: Solution: I propose a new option to pg_dump, --split, which dumps each object to a separate file in a user friendly directory structure: Um ... how does that solve the claimed problem exactly? Because then you can do, $ diff -r old schema dump dir new schema dump dir, instead of, $ diff old entire schema dump new entire schema dump which will nicely reveal each individual object modified, as opposed to a huge global diff of everything That has at least as many failure modes as the other representation. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
2010/12/28 Tom Lane t...@sss.pgh.pa.us That has at least as many failure modes as the other representation. I don't follow, what do you mean with failure modes? The oid in the filename? I suggested to use a sequence instead but you didn't comment on that. Are there any other failure modes which could cause a diff -r between two different databases to break? (This might be a bad idea for some other reason, but I noticed a few other users requesting the same feature when I googled pg_dump split.) -- Best regards, Joel Jacobson Glue Finance
Re: [HACKERS] pg_primary_conninfo
Le 28/12/2010 17:50, Gurjeet Singh a écrit : On Tue, Dec 28, 2010 at 11:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: I can see the point of, say, a primary_host_address() function returning inet, which would be way better on both those dimensions than the current proposal. But I'm not sure what else would be needed. +1, since it bypasses security risks associated with exposing username/password. Ability to see port number will be a useful addition. Another case to consider is what if slave is connected to a local server over unix-domain sockets? Returning NULL might make it ambiguous with the case where the instance has been promoted out of standby. The host should be the socket file path. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] knngist - 0.8
I've applied all of this, and written documentation for all of it, Thank you a lot except for the contrib/btree_gist additions which still need to be redone for the revised API (and then documented!). My patience ran out Done, btree_gist is reworked for a new API. I'm very sorry, but I'm rather busy now and will be accessible only after January, 10. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ builtin_knngist_contrib_btree_gist-0.9.gz Description: Unix tar archive -- 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 to add table function support to PL/Tcl (Todo item)
Excerpts from Karl Lehenbauer's message of mar dic 28 12:33:42 -0300 2010: Project name: Add table function support to PL/Tcl (Todo item) What the patch does: This patch adds table function support (returning record and SETOF record) to PL/Tcl. This patch also updates PL/Tcl to use the Tcl object-style interface instead of the older string-style one, increasing performance. While I don't use PL/Tcl myself, this seems a reasonable idea. However, I think this patch does too many things in one step. It also contains numerous superfluous whitespace changes that make it hard to assess its real size. I'd recommend splitting it up and dropping the whitespace changes (which would be reverted by pgindent anyway). -- Álvaro Herrera alvhe...@commandprompt.com 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] pg_dump --split patch
On 12/28/2010 11:59 AM, Joel Jacobson wrote: 2010/12/28 Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us That has at least as many failure modes as the other representation. I don't follow, what do you mean with failure modes? The oid in the filename? I suggested to use a sequence instead but you didn't comment on that. Are there any other failure modes which could cause a diff -r between two different databases to break? (This might be a bad idea for some other reason, but I noticed a few other users requesting the same feature when I googled pg_dump split.) A better approach to the problem might be to have a tool which did a comparison of structures rather than a textual comparison of dumps. For extra credit, such a tool might even try to produce a sync script for you ... Of course, that task might involve more effort than you want to devote to 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] writable CTEs
On Tue, Dec 28, 2010 at 04:35:26PM +0200, Peter Eisentraut wrote: On tis, 2010-12-28 at 00:19 +, Peter Geoghegan wrote: It's worth noting that officially (i.e. in the docs), we don't even call CTEs CTEs at any point. We call them WITH queries. I think that that's a mistake because we call them CTEs everywhere else. I think WITH query or WITH clause is more understandable than CTE, which to me is a term that has no relationship with anything else. Common Table Expression, or CTE for short, is the standard terminology, and I don't just mean SQL:2008. It's standard in DB2, Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and Sybase SQL Anywhere, at a minimum. WITH query is a pure PostgreSQL invention, and not a super helpful one to the vast majority of users. Please bear in mind that if this is to remain a successful project, the vast majority of users are *future* users, not current or past ones. We don't talk about Subquery queries or FULL JOIN queries and give them their own doc section, nor should we. We should instead refactor the docs to point to CTEs in the appropriate places, and it's my hope that those places will increase over time. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
Joel Jacobson j...@gluefinance.com writes: 2010/12/28 Tom Lane t...@sss.pgh.pa.us That has at least as many failure modes as the other representation. I don't follow, what do you mean with failure modes? The oid in the filename? I suggested to use a sequence instead but you didn't comment on that. Are there any other failure modes which could cause a diff -r between two different databases to break? AFAIK the primary failure modes for diff'ing text dumps are (1) randomly different ordering of objects from one dump to another. Your initial proposal would avoid that problem as long as the object OIDs didn't change, but since it falls down completely across a dump and reload, or delete and recreate, I can't really see that it's a step forward. Using a sequence number generated by pg_dump doesn't change this at all --- the sequence would be just as unpredictable. (2) randomly different ordering of rows within a table. Your patch didn't address that, unless I misunderstood quite a bit. I think the correct fix for (1) is to improve pg_dump's method for sorting objects. It's not that bad now, but it does have issues with random ordering of similarly-named objects. IIRC Peter Eisentraut proposed something for this last winter but it seemed a mite too ugly, and he got beaten down to just this: commit 1acc06a1f4ae752793d2199d8d462a6708c8acc2 Author: Peter Eisentraut pete...@gmx.net Date: Mon Feb 15 19:59:47 2010 + When sorting functions in pg_dump, break ties (same name) by number of argum ents Maybe you can do better, but I'd suggest going back to reread the discussion that preceded that patch. (This might be a bad idea for some other reason, but I noticed a few other users requesting the same feature when I googled pg_dump split.) AFAIR what those folk really wanted was a selective dump with more selectivity knobs than exist now. I don't think their lives would be improved by having to root through a twisty little maze of numbered files to find the object they wanted. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
On Tue, Dec 28, 2010 at 11:00 AM, Joel Jacobson j...@gluefinance.comwrote:
Dear fellow hackers,
Problem: A normal diff of two slightly different schema dump files (pg_dump
-s), will not produce a user-friendly diff, as you get all changes in the
same file.
Solution: I propose a new option to pg_dump, --split, which dumps each
object to a separate file in a user friendly directory structure:
[-f filename] : main dump file, imports each splitted part using \i
[-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid
Example: If the filename (-f) is pg.dump, the following directory
structure would be created:
$ ./pg_dump -f /crypt/pg.dump --split -F p -s glue
/crypt/pg.dump-split/VIEW/
/crypt/pg.dump-split/TYPE/
/crypt/pg.dump-split/TRIGGER/
/crypt/pg.dump-split/TABLE/
/crypt/pg.dump-split/SEQUENCE/
/crypt/pg.dump-split/SCHEMA/
/crypt/pg.dump-split/PROCEDURAL_LANGUAGE/
/crypt/pg.dump-split/INDEX/
/crypt/pg.dump-split/FUNCTION/
/crypt/pg.dump-split/FK_CONSTRAINT/
/crypt/pg.dump-split/CONSTRAINT/
/crypt/pg.dump-split/AGGREGATE/
In each such directory, one directory per object name is created.
If we would have a function foobar with oid 12345 it would be saved to:
/crypt/pg.dump-split/FUNCTION/foobar/12345.sql
In the pg.dump plain text file, the files are linked in using the \i
psql command, e.g.:
\i /crypt/pg.dump-split/FUNCTION/foobar/12345.sql
Potential use-case scenarios:
*) Version control your database schema, by exporting it daily (using
--split) and commiting the differences.
*) Compare differences of schema dumps created in different points in time.
Since objects are stored in separate files, it is easier to see what areas
were modified, compared to looking at the diff of two entire schemas.
*) Restore only some objects, based on type (e.g., only the functions) or
name (e.g. only fucntions of certain name/names).
I've tested the patch for both the latest HEAD (9.1devel) as well as 8.4.6.
I would suggest the directory structure as:
/crypt/pg.dump-split/schema-name-1/VIEWS/view-name-1.sql
/crypt/pg.dump-split/schema-name-1/TABLES/table-name-1.sql
...
/crypt/pg.dump-split/schema-name-2/VIEWS/view-name-1.sql
/crypt/pg.dump-split/schema-name-2/TABLES/table-name-1.sql
This might n be more amenable to diff'ing the different dumps. Schemas are
logical grouping of other objects and hence making that apparent in your
dump's hierarchy makes more sense.
Most importantly, as Tom suggested, don't use or rely on OIDs. I think
function overloading is the only case where you can have more than one
object with the same name under a schema. That can be resolved if you
included function signature in filename:
/crypt/pg.dump-split/emp/FUNCTIONS/myfunc-int-char.sql
/crypt/pg.dump-split/emp/FUNCTIONS/myfunc-int-int.sql
/crypt/pg.dump-split/emp/FUNCTIONS/myfunc-int.sql
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] pg_dump --split patch
On Tue, Dec 28, 2010 at 11:59 AM, Joel Jacobson j...@gluefinance.com wrote: I don't follow, what do you mean with failure modes? The oid in the filename? I suggested to use a sequence instead but you didn't comment on that. Are there any other failure modes which could cause a diff -r between two different databases to break? Both OID and sequence mean that your likely to get a diff which is nothing more than complete files removed from 1 side and added to the othe rside with different names (i.e. oid's don't match, or an added/removed object changes all following sequence assingments). If you're going to try and split, I really think the only usefull filename has to be similar to something like: schema/type/name/part If you want to use diff, you pretty much have to make sure that the *path* will be identical for similary named objects, irrespective of anything else in the database. And path has to be encoding aware. And you want names that glob well, so for instance, you could exclude *.data (or a schema) from the diff. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- 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_primary_conninfo
On Dec 28, 2010, at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? SQL access is frequently more convenient, though. Although maybe now that we've made recovery.conf use the GUC lexer we oughta continue in that vein and expose those parameters as PGC_INTERNAL GUCs rather than inventing a new function for it... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
Le 28/12/2010 18:12, Robert Haas a écrit : On Dec 28, 2010, at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: I'm still wondering what's the actual use-case for exposing this inside SQL. Those with a legitimate need-to-know can look at the slave server's config files, no? SQL access is frequently more convenient, though. Although maybe now that we've made recovery.conf use the GUC lexer we oughta continue in that vein and expose those parameters as PGC_INTERNAL GUCs rather than inventing a new function for it... That was the first thing I wanted. Knowing the trigger file for example would be quite useful for pgAdmin and pgPool for example. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
On Tue, Dec 28, 2010 at 12:12 PM, Robert Haas robertmh...@gmail.com wrote:
On Dec 28, 2010, at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote:
I'm still wondering what's the actual use-case for exposing this inside
SQL. Those with a legitimate need-to-know can look at the slave
server's config files, no?
SQL access is frequently more convenient, though. Although maybe now that
we've made recovery.conf use the GUC lexer we oughta continue in that vein
and expose those parameters as PGC_INTERNAL GUCs rather than inventing a new
function for it...
+1 for SQL access, but exposing it via pg_settings opens up the security
problem as there might be sensitive info in those GUCs.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] TODO item for pg_ctl and server detection
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
While I am working on pg_ctl, I saw this TODO item:
Have the postmaster write a random number to a file on startup that
pg_ctl checks against the contents of a pg_ping response on its initial
connection (without login)
This will protect against connecting to an old instance of the
postmaster in a different or deleted subdirectory.
http://archives.postgresql.org/pgsql-bugs/2009-10/msg00110.php
http://archives.postgresql.org/pgsql-bugs/2009-10/msg00156.php
Based on our new PQPing(), do we ever want to implement this or should I
remove the TODO item? It seems this would require a server connection,
which is something we didn't want to force pg_ctl -w to do in case
authentication is broken.
Well, rereading that old thread makes me realize that what you just
implemented is still pretty far short of what was discussed. In
particular, this implementation entirely fails to cope with the
possibility that a Windows postmaster is using a specialized
listen_addresses setting that has to be taken into account in order to
get a TCP connection. I wonder whether we should revert this patch and
have another go at the idea of a separate postmaster.ports status file
with a line for each active port.
I had forgotten about having to use TCP and needing to honor
listen_address restrictions. We only need one valid listen_address so I
went ahead and added a line to the postmaster.pid file.
I am not sure what a separate file will buy us except additional files
to open/manage.
The business with a magic number can't be implemented unless we actually
add a new separate pg_ping protocol. PQping() has removed a lot of the
pressure to have that, namely all the authentication-failure problem
cases. I'm not sure that the case where you're looking at an inactive
data directory but there's a live postmaster someplace else with the
same port number is important enough to justify new protocol all by
itself.
Yes, that was my calculus too. I realized that we create session ids by
merging the process id and backend start time, so I went ahead and added
the postmaster start time epoch to the postmaster.pid file. While there
is no way to pass back the postmaster start time from PQping, I added
code to pg_ctl to make sure the time in the postmaster.pid file is not
_before_ pg_ctl started running. We only check PQping() after we have
started the postmaster ourselves, so it fits our needs.
Patch attached.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index cda7f64..86bc5a6 100644
*** /tmp/pgdiff.11857/wrhSFb_storage.sgml Tue Dec 28 12:51:36 2010
--- doc/src/sgml/storage.sgml Tue Dec 28 11:57:56 2010
*** last started with/entry
*** 117,124
row
entryfilenamepostmaster.pid//entry
entryA lock file recording the current postmaster process id (PID),
! cluster data directory, port number, Unix domain socket directory,
! and shared memory segment ID/entry
/row
/tbody
--- 117,125
row
entryfilenamepostmaster.pid//entry
entryA lock file recording the current postmaster process id (PID),
! postmaster start time, cluster data directory, port number, user-specified
! Unix domain socket directory, first valid listen_address host, and
! shared memory segment ID/entry
/row
/tbody
diff --git a/src/backend/port/ipc_test.c b/src/backend/port/ipc_test.c
index a003dc9..461a7a6 100644
*** /tmp/pgdiff.11857/QXAQWd_ipc_test.c Tue Dec 28 12:51:36 2010
--- src/backend/port/ipc_test.c Tue Dec 28 09:38:50 2010
*** on_exit_reset(void)
*** 104,110
}
void
! RecordSharedMemoryInLockFile(unsigned long id1, unsigned long id2)
{
}
--- 104,110
}
void
! AddToLockFile(int target_line, const char *str)
{
}
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index d970eb2..ff77099 100644
*** /tmp/pgdiff.11857/KqzRVc_sysv_shmem.c Tue Dec 28 12:51:36 2010
--- src/backend/port/sysv_shmem.c Tue Dec 28 09:54:14 2010
*** InternalIpcMemoryCreate(IpcMemoryKey mem
*** 198,206
/* Register on-exit routine to detach new segment before deleting */
on_shmem_exit(IpcMemoryDetach, PointerGetDatum(memAddress));
! /* Record key and ID in lockfile for data directory. */
! RecordSharedMemoryInLockFile((unsigned long) memKey,
! (unsigned long) shmid);
return memAddress;
}
--- 198,214
/* Register on-exit routine to detach new segment before deleting */
on_shmem_exit(IpcMemoryDetach, PointerGetDatum(memAddress));
! /*
! * Append record key and ID in lockfile for data directory. Format
! * to try to keep it the same length.
! */
! {
! char line[32];
!
!
Re: [HACKERS] pg_primary_conninfo
Gurjeet Singh singh.gurj...@gmail.com writes: On Tue, Dec 28, 2010 at 12:12 PM, Robert Haas robertmh...@gmail.com wrote: SQL access is frequently more convenient, though. Although maybe now that we've made recovery.conf use the GUC lexer we oughta continue in that vein and expose those parameters as PGC_INTERNAL GUCs rather than inventing a new function for it... +1 for SQL access, but exposing it via pg_settings opens up the security problem as there might be sensitive info in those GUCs. IIRC we do have a GUC property that hides the value from non-superusers, so we could easily have a GUC that is equivalent to the proposed pg_primary_conninfo function. Of course this does nothing for my objections to the function. Also, I'm not sure how we'd deal with the state-dependency aspect of it (ie, value changes once you exit recovery mode). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the number of file descriptors when using POSIX semaphore
flyusa2010 fly wrote: Hi, folks, in src/template/darwin: # Select appropriate semaphore support. Darwin 6.0 (Mac OS X 10.2) and up # support System V semaphores; before that we have to use POSIX semaphores, # which are less good for our purposes because they eat a file descriptor # per backend per max_connection slot. To my understanding, the number of descriptors created by POSIX semaphores would be # of actual clients times max_connection. However, I monitor the number of open files using sysctl, and I find that kern.num_files doesn't match the result calculated by the formula that is inferred by me... So, what would the number of file descriptors be, when using POSIX semaphore? I thought the text was wrong, but this commit reinforces that text: commit 93407d3998cc8717993dbc102f854a478985bc19 Author: Tom Lane t...@sss.pgh.pa.us Date: Wed Sep 26 00:32:46 2007 + Use SYSV semaphores rather than POSIX on Darwin = 6.0 (i.e., OS X 10.2 and up), per Chris Marcellino. This avoids consuming O(N^2) file descriptors to support N backends. Tests suggest it's about a wash for small installations, but large ones would have a problem. What numbers are you seeing? -- 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] pg_primary_conninfo
Le 28/12/2010 19:30, Tom Lane a écrit : Gurjeet Singh singh.gurj...@gmail.com writes: On Tue, Dec 28, 2010 at 12:12 PM, Robert Haas robertmh...@gmail.com wrote: SQL access is frequently more convenient, though. Although maybe now that we've made recovery.conf use the GUC lexer we oughta continue in that vein and expose those parameters as PGC_INTERNAL GUCs rather than inventing a new function for it... +1 for SQL access, but exposing it via pg_settings opens up the security problem as there might be sensitive info in those GUCs. IIRC we do have a GUC property that hides the value from non-superusers, so we could easily have a GUC that is equivalent to the proposed pg_primary_conninfo function. Of course this does nothing for my objections to the function. Also, I'm not sure how we'd deal with the state-dependency aspect of it (ie, value changes once you exit recovery mode). We already have superuser GUC. b1= show data_directory; ERROR: must be superuser to examine data_directory We only need to do the same for primary_conninfo and trigger_file (as I remember it, there are the only ones needing this). -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_primary_conninfo
On Tue, Dec 28, 2010 at 1:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Gurjeet Singh singh.gurj...@gmail.com writes:
On Tue, Dec 28, 2010 at 12:12 PM, Robert Haas robertmh...@gmail.com
wrote:
SQL access is frequently more convenient, though. Although maybe now
that
we've made recovery.conf use the GUC lexer we oughta continue in that
vein
and expose those parameters as PGC_INTERNAL GUCs rather than inventing a
new
function for it...
+1 for SQL access, but exposing it via pg_settings opens up the security
problem as there might be sensitive info in those GUCs.
IIRC we do have a GUC property that hides the value from non-superusers,
so we could easily have a GUC that is equivalent to the proposed
pg_primary_conninfo function. Of course this does nothing for my
objections to the function. Also, I'm not sure how we'd deal with the
state-dependency aspect of it (ie, value changes once you exit recovery
mode).
I would vote for making host:port part visible to non-superusers. This info
is definitely usable in combination with pg_current_xlog_location() and
pg_last_xlog_receive_location() to allow non-superusers to monitor streaming
replication.
Given that primary_conninfo is already parsed by libpq, how difficult would
it be to extract and store/display those host:port components.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
[HACKERS] SLRU overview
Is there an overview of SLRU anywhere? I've looked over the code enough to know that it'll save me a day or two if I can get an overview of correct usage, rather than to reverse engineer it from source code. There is no README with useful information, and searches of the Wiki and the docs have come up dry. Barring that, any tips or warnings welcome. -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] pg_dump --split patch
2010/12/28 Gurjeet Singh singh.gurj...@gmail.com I would suggest the directory structure as: /crypt/pg.dump-split/schema-name-1/VIEWS/view-name-1.sql /crypt/pg.dump-split/schema-name-1/TABLES/table-name-1.sql ... /crypt/pg.dump-split/schema-name-2/VIEWS/view-name-1.sql /crypt/pg.dump-split/schema-name-2/TABLES/table-name-1.sql This might n be more amenable to diff'ing the different dumps. Schemas are logical grouping of other objects and hence making that apparent in your dump's hierarchy makes more sense. Thanks Gurjeet and Tom for good feedback! I've made some changes and attached new patches. Looks much better now I think! This is what I've changed, *) Not using oid anymore in the filename *) New filename/path structure: [-f filename]-split/[schema]/[desc]/[tag].sql *) If two objects share the same name tag for the same [schema]/[desc], -2, -3, etc is appended to the name. Example: ~/pg.dump-split/public/FUNCTION/foobar.sql ~/pg.dump-split/public/FUNCTION/foobar-2.sql ~/pg.dump-split/public/FUNCTION/barfoo.sql ~/pg.dump-split/public/FUNCTION/barfoo-2.sql ~/pg.dump-split/public/FUNCTION/barfoo-3.sql I think you are right about functions (and aggregates) being the only desc-type where two objects can share the same name in the same schema. This means the problem of dumping objects in different order is a very limited problem, only affecting overloaded functions. I didn't include the arguments in the file name, as it would lead to very long file names unless truncated, and since the problem is very limited, I think we shouldn't include it. It's cleaner with just the name part of the tag in the file name. -- Best regards, Joel Jacobson Glue Finance pg-dump-split-plain-text-files-8.4.6.patch Description: Binary data pg-dump-split-plain-text-files-9.1devel.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
Re: [HACKERS] writable CTEs
On tis, 2010-12-28 at 16:04 +, Peter Geoghegan wrote: If I search for common table expressions on Wikipedia, I am sent to the common table expressions article, without any re-direction. The article doesn't mention with query as a synonym of CTE at any point. If I search for With query, the first page of results (20 articles) doesn't have anything about CTEs at all. Apparently, the term common table expression comes from Microsoft and IBM. If you search for SELECT WITH clause you get a bunch of Oracle links. -- 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] writable CTEs
On tis, 2010-12-28 at 09:31 -0800, David Fetter wrote: Common Table Expression, or CTE for short, is the standard terminology, and I don't just mean SQL:2008. It's standard in DB2, Drizzle, Firebird, HSQLDB, Informix, Microsoft SQL Server, Oracle, and Sybase SQL Anywhere, at a minimum. WITH query is a pure PostgreSQL invention, and not a super helpful one to the vast majority of users. The phrase common table expression does not appear anywhere in the SQL standard. The standard uses the grammar symbol with clause. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the number of file descriptors when using POSIX semaphore
Bruce Momjian br...@momjian.us writes: flyusa2010 fly wrote: in src/template/darwin: # Select appropriate semaphore support. Darwin 6.0 (Mac OS X 10.2) and up # support System V semaphores; before that we have to use POSIX semaphores, # which are less good for our purposes because they eat a file descriptor # per backend per max_connection slot. So, what would the number of file descriptors be, when using POSIX semaphore? I thought the text was wrong, but this commit reinforces that text: The text is correct, or at least as correct as it's possible to get without expending three times the verbiage. If you look in InitProcGlobal you will find that the postmaster creates MaxConnections plus autovacuum_max_workers + 1 plus NUM_AUXILIARY_PROCS semaphores. In a POSIX-semaphores implementation, each of these eats a file descriptor in the postmaster, plus another file descriptor in each child process of the postmaster. The OP claims the text is wrong, but since he failed to state what he observed, it's pretty hard to comment further. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
On Tue, Dec 28, 2010 at 2:39 PM, Joel Jacobson j...@gluefinance.com wrote:
2010/12/28 Gurjeet Singh singh.gurj...@gmail.com
I would suggest the directory structure as:
/crypt/pg.dump-split/schema-name-1/VIEWS/view-name-1.sql
/crypt/pg.dump-split/schema-name-1/TABLES/table-name-1.sql
...
/crypt/pg.dump-split/schema-name-2/VIEWS/view-name-1.sql
/crypt/pg.dump-split/schema-name-2/TABLES/table-name-1.sql
This might n be more amenable to diff'ing the different dumps. Schemas are
logical grouping of other objects and hence making that apparent in your
dump's hierarchy makes more sense.
Thanks Gurjeet and Tom for good feedback!
I've made some changes and attached new patches.
Looks much better now I think!
This is what I've changed,
*) Not using oid anymore in the filename
*) New filename/path structure: [-f
filename]-split/[schema]/[desc]/[tag].sql
*) If two objects share the same name tag for the same [schema]/[desc], -2,
-3, etc is appended to the name. Example:
~/pg.dump-split/public/FUNCTION/foobar.sql
~/pg.dump-split/public/FUNCTION/foobar-2.sql
~/pg.dump-split/public/FUNCTION/barfoo.sql
~/pg.dump-split/public/FUNCTION/barfoo-2.sql
~/pg.dump-split/public/FUNCTION/barfoo-3.sql
I think you are right about functions (and aggregates) being the only
desc-type where two objects can share the same name in the same schema.
This means the problem of dumping objects in different order is a very
limited problem, only affecting overloaded functions.
I didn't include the arguments in the file name, as it would lead to very
long file names unless truncated, and since the problem is very limited, I
think we shouldn't include it. It's cleaner with just the name part of the
tag in the file name.
I haven't seen your code yet, but we need to make sure that in case of name
collision we emit the object definitions in a sorted order so that the dump
is always deterministic: func1(char) should be _always_ dumped before
func1(int), that is, output file names are always deterministic.
The problem I see with suffixing a sequence id to the objects with name
collision is that one day the dump may name myfunc(int) as myfunc.sql and
after an overloaded version is created, say myfunc(char, int), then the same
myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.
Also, it is a project policy that we do not introduce new features in back
branches, so spending time on an 8.4.6 patch may not be the best use of your
time.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
[HACKERS] 9.1alpha3 bundled -- please verify
Alpha3 has been bundled and is available at http://developer.postgresql.org/~petere/alpha/ Please check that it is sane. If there are no concerns, I will move them to the FTP site tomorrow (Wednesday) and send out announcements on Thursday. -- 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_dump --split patch
Sent from my iPhone
On 28 dec 2010, at 21:45, Gurjeet Singh singh.gurj...@gmail.com wrote:
The problem I see with suffixing a sequence id to the objects with name
collision is that one day the dump may name myfunc(int) as myfunc.sql and
after an overloaded version is created, say myfunc(char, int), then the same
myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.
I agree, good point!
Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to
reduce the need of truncating filenames.
Also, it is a project policy that we do not introduce new features in back
branches, so spending time on an 8.4.6 patch may not be the best use of your
time.
My company is using 8.4 and needs this feature, so I'll have to patch it
anyway :)
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] Patch to add table function support to PL/Tcl (Todo item)
Hmm, I ran the code through pgindent so I don't understand why there are whitespace changes. OK I'll see what the problem is with the whitespace and instead produce two patches, one that converts to using Tcl objects and one on top of that that adds returning records and setof records. On Dec 28, 2010, at 12:12 PM, Alvaro Herrera wrote: Excerpts from Karl Lehenbauer's message of mar dic 28 12:33:42 -0300 2010: Project name: Add table function support to PL/Tcl (Todo item) What the patch does: This patch adds table function support (returning record and SETOF record) to PL/Tcl. This patch also updates PL/Tcl to use the Tcl object-style interface instead of the older string-style one, increasing performance. While I don't use PL/Tcl myself, this seems a reasonable idea. However, I think this patch does too many things in one step. It also contains numerous superfluous whitespace changes that make it hard to assess its real size. I'd recommend splitting it up and dropping the whitespace changes (which would be reverted by pgindent anyway). -- Álvaro Herrera alvhe...@commandprompt.com 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] pg_dump --split patch
On 12/28/2010 04:44 PM, Joel Jacobson wrote: The problem I see with suffixing a sequence id to the objects with name collision is that one day the dump may name myfunc(int) as myfunc.sql and after an overloaded version is created, say myfunc(char, int), then the same myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic. I agree, good point! Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to reduce the need of truncating filenames. I think that's just horrible. Does the i stand for integer or inet? And it will get *really* ugly for type names with spaces in them ... 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] SSI SLRU strategy choices
I'm now deep enough into the SLRU techniques to see what my options are for storing the data appropriate for SLRU. This consists of uint64 commitSeqNo (which is overkill enough that I'd be comfortable stealing a bit or two from the high end in SLRU usage) which needs to be associated with an xid. The xids would have gaps, since we only need to track committed serializable transactions which still matter because of a long-running transaction weren't subject to early cleanup based on previously posted rules. These will be looked up by xid. The options I see are: (1) Store the xid and commitSeqNo in each SLRU entry -- with alignment, that's 16 bytes per entry. Simple, but requires sequential search for the xid. Wouldn't scale well. (2) Use 8 byte SLRU entries and map the xid values over the SLRU space, with each spot allowing two different xid values. At first blush that looks good, because transaction ID wrap-around techniques mean that the two values for any one spot couldn't be active at the same time. The high bit could flag that the xid is present with the rest of the bits being from the commitSeqNo. The problem is that the SLRU code appears to get confused about there being wrap-around when the SLRU space is half-full, so we would get into trouble if we burned through more than 2^30 transactions during one long-running serializable read write transaction. I still like this option best, with resort to killing the long-running transaction at that point. (3) Use two SLRU spaces. You'd look up randomly into the first one based on xid, and get a position in the second one which would hold the commitSeqNo, which would be assigned to sequential slots. This would potentially allow us to burn through more transactions because some are likely to be subject to early cleanup. The marginal extension of the failure point doesn't seem like it merits the extra complexity. (4) Change SLRU to tolerate more entries. At most this raises the number of transactions we can burn through during a long-running transaction from 2^30 to 2^31. That hardly seems worth the potential to destabilize a lot of critical code. Does (2) sound good to anyone else? Other ideas? Does it sound like I'm totally misunderstanding anything? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
2010/12/28 Andrew Dunstan and...@dunslane.net I think that's just horrible. Does the i stand for integer or inet? And it will get *really* ugly for type names with spaces in them ... True, true. But while c is too short, I think character varying is too long. Is there some convenient lookup table to convert between the long names to the short names? E.g., character varying = varchar timestamp with time zone = timestamptz etc. -- Best regards, Joel Jacobson Glue Finance
[HACKERS] Fixing pg_upgrade's check of available binaries
I've been fooling around with creating upgrade-in-place support for the
Fedora/RHEL RPMs. What I want to have is a separate postgresql-upgrade
RPM containing just the minimum possible set of previous-release files,
together with pg_upgrade itself. Experimenting with this convinced me
that pg_upgrade is a few bricks shy of a load in its tests for whether
the old and new clusters have the right binaries available:
* it insists on pg_dumpall and psql being present in the old cluster,
though they are not in fact called
* it fails to check for pg_resetxlog, even though it needs it in both
old and new clusters
* it fails to check for pg_config, which it does need in the new
cluster. It does not however really need it in the old cluster,
because it has no use for the old cluster's --pkglibdir path.
I propose the attached patch to clean these things up. Any objections?
regards, tom lane
*** contrib/pg_upgrade/exec.c~ Sat Dec 11 14:05:02 2010
--- contrib/pg_upgrade/exec.c Tue Dec 28 16:44:26 2010
***
*** 14,20
static void check_data_dir(const char *pg_data);
! static void check_bin_dir(ClusterInfo *cluster);
static int check_exec(const char *dir, const char *cmdName);
static const char *validate_exec(const char *path);
--- 14,20
static void check_data_dir(const char *pg_data);
! static void check_bin_dir(ClusterInfo *cluster, Cluster whichCluster);
static int check_exec(const char *dir, const char *cmdName);
static const char *validate_exec(const char *path);
***
*** 99,105
check_ok();
prep_status(Checking old bin directory (%s), old_cluster.bindir);
! check_bin_dir(old_cluster);
check_ok();
prep_status(Checking new data directory (%s), new_cluster.pgdata);
--- 99,105
check_ok();
prep_status(Checking old bin directory (%s), old_cluster.bindir);
! check_bin_dir(old_cluster, CLUSTER_OLD);
check_ok();
prep_status(Checking new data directory (%s), new_cluster.pgdata);
***
*** 107,113
check_ok();
prep_status(Checking new bin directory (%s), new_cluster.bindir);
! check_bin_dir(new_cluster);
check_ok();
}
--- 107,113
check_ok();
prep_status(Checking new bin directory (%s), new_cluster.bindir);
! check_bin_dir(new_cluster, CLUSTER_NEW);
check_ok();
}
***
*** 158,169
* exit().
*/
static void
! check_bin_dir(ClusterInfo *cluster)
{
check_exec(cluster-bindir, postgres);
- check_exec(cluster-bindir, psql);
check_exec(cluster-bindir, pg_ctl);
! check_exec(cluster-bindir, pg_dumpall);
}
--- 158,175
* exit().
*/
static void
! check_bin_dir(ClusterInfo *cluster, Cluster whichCluster)
{
check_exec(cluster-bindir, postgres);
check_exec(cluster-bindir, pg_ctl);
! check_exec(cluster-bindir, pg_resetxlog);
! if (whichCluster == CLUSTER_NEW)
! {
! /* these are only needed in the new cluster */
! check_exec(cluster-bindir, pg_config);
! check_exec(cluster-bindir, psql);
! check_exec(cluster-bindir, pg_dumpall);
! }
}
*** contrib/pg_upgrade/option.c~ Wed Dec 15 21:48:52 2010
--- contrib/pg_upgrade/option.c Tue Dec 28 16:45:24 2010
***
*** 310,316
static void
get_pkglibdirs(void)
{
! old_cluster.libpath = get_pkglibdir(old_cluster.bindir);
new_cluster.libpath = get_pkglibdir(new_cluster.bindir);
}
--- 310,320
static void
get_pkglibdirs(void)
{
! /*
! * we do not need to know the libpath in the old cluster, and might not
! * have a working pg_config to ask for it anyway.
! */
! old_cluster.libpath = NULL;
new_cluster.libpath = get_pkglibdir(new_cluster.bindir);
}
--
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_dump --split patch
Andrew Dunstan and...@dunslane.net writes: On 12/28/2010 04:44 PM, Joel Jacobson wrote: Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to reduce the need of truncating filenames. I think that's just horrible. Does the i stand for integer or inet? And it will get *really* ugly for type names with spaces in them ... You think spaces are bad, try slashes ;-) Not to mention the need for including schemas in typenames sometimes. I think you're going to have a real problem trying to fully describe a function's signature in a file name of reasonable max length. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
On Tue, Dec 28, 2010 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote:
On 12/28/2010 04:44 PM, Joel Jacobson wrote:
The problem I see with suffixing a sequence id to the objects with name
collision is that one day the dump may name myfunc(int) as myfunc.sql and
after an overloaded version is created, say myfunc(char, int), then the same
myfunc(int) may be dumped in myfunc-2.sql, which again is non-deterministic.
I agree, good point!
Perhaps abbreviations are to prefer, e.g., myfunc_i, myfunc_i_c, etc to
reduce the need of truncating filenames.
I think that's just horrible. Does the i stand for integer or inet? And it
will get *really* ugly for type names with spaces in them ...
Do you mean using data type names in filename is a bad idea, or is
abbreviating the type names is a bad idea?
Maybe we can compute a hash based on the type names and use that in the
file's name?
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [HACKERS] pg_dump --split patch
On Tue, Dec 28, 2010 at 2:39 PM, Joel Jacobson j...@gluefinance.com wrote: I think you are right about functions (and aggregates) being the only desc-type where two objects can share the same name in the same schema. This means the problem of dumping objects in different order is a very limited problem, only affecting overloaded functions. I didn't include the arguments in the file name, as it would lead to very long file names unless truncated, and since the problem is very limited, I think we shouldn't include it. It's cleaner with just the name part of the tag in the file name. Why not place all overloads of a function within the same file? Then, assuming you order them deterministically within that file, we sidestep the file naming issue and maintain useful diff capabilities, since a diff of the function's file will show additions or removals of various overloaded versions. -- - David T. Wilson david.t.wil...@gmail.com
Re: [HACKERS] writable CTEs
On 28 December 2010 20:07, Peter Eisentraut pete...@gmx.net wrote: The phrase common table expression does not appear anywhere in the SQL standard. The standard uses the grammar symbol with clause. I think we're losing sight of the issue a bit here. No one is proposing that we call WITH queries common table expressions. As I think we all agree, the term WITH query and common table expression are not synonymous. A WITH query is comprised of one or more common table expressions, plus a conventional SELECT query. All that I'm asking is that we /specify/ that the subqueries already mentioned in the docs are common table expressions. This terminology is less confusing and ambiguous, is demonstrably already in widespread use, and will probably lay the groundwork for whatever name we choose for wCTEs. I think that it makes sense to change the title of the relevant section from WITH Queries to WITH Queries (Common Table Expressions) because CTEs are the defining characteristic of WITH queries, and, as I've said, the term common table expression has mindshare in a way that WITH query clearly doesn't. -- Regards, Peter Geoghegan -- 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_dump --split patch
2010/12/29 David Wilson david.t.wil...@gmail.com Why not place all overloads of a function within the same file? Then, assuming you order them deterministically within that file, we sidestep the file naming issue and maintain useful diff capabilities, since a diff of the function's file will show additions or removals of various overloaded versions. Good suggestion. I agree, trying to put variations of the same function in different files simply becomes too ugly and the problem it solves is not big enough. Then we just need to make sure pg_dump dumps objects in the same order, and let diff take care of the rest of the challenge. :) Brb with a new patch. -- Best regards, Joel Jacobson Glue Finance
Re: [HACKERS] SSI SLRU strategy choices
On 29.12.2010 00:10, Kevin Grittner wrote: (2) Use 8 byte SLRU entries and map the xid values over the SLRU space, with each spot allowing two different xid values. At first blush that looks good, because transaction ID wrap-around techniques mean that the two values for any one spot couldn't be active at the same time. The high bit could flag that the xid is present with the rest of the bits being from the commitSeqNo. The problem is that the SLRU code appears to get confused about there being wrap-around when the SLRU space is half-full, so we would get into trouble if we burned through more than 2^30 transactions during one long-running serializable read write transaction. I still like this option best, with resort to killing the long-running transaction at that point. If you burn through more than 2^30 XIDs while a long-running transaction is still running, you have bigger problems. 2^30 is the maximum number of XIDs you can burn through before you reach XID wrap-around anyway. GetNewTransaction() will stop assigning new XIDs when you approach that limit. (I'm not sure how you arrived at that number, though. ISTM that the slru code can handle 2^30 *pages* before getting into trouble, assuming the PagePrecedes function can handle that.) The only issue I can see with that is that you allocate those 8 bytes for every xid, even if it's a non-serializable transaction or a subtransaction. But the overhead is probably not significant in practice. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
David Wilson david.t.wil...@gmail.com writes: On Tue, Dec 28, 2010 at 2:39 PM, Joel Jacobson j...@gluefinance.com wrote: I didn't include the arguments in the file name, as it would lead to very long file names unless truncated, and since the problem is very limited, I think we shouldn't include it. It's cleaner with just the name part of the tag in the file name. Why not place all overloads of a function within the same file? Then, assuming you order them deterministically within that file, we sidestep the file naming issue and maintain useful diff capabilities, since a diff of the function's file will show additions or removals of various overloaded versions. If you've solved the deterministic-ordering problem, then this entire patch is quite useless. You can just run a normal dump and diff it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Revised patches to add table function support to PL/Tcl (TODO item)
Karl Lehenbauer karllehenba...@gmail.com writes: The first patch, pltcl-karl-try2-1-of-3-pgindent.patch, does nothing but conform HEAD's pltcl.c with pgindent. Applying this patch should have exactly the same effect as running src/tools/pgindent/pgindent src/tools/pgindent/typedefs.list src/pl/tcl/pltcl.c This patch appears to be changing a whole lot of stuff that in fact pg_indent has never changed, so there's something wrong with the way you are doing it. It looks like a bad typedef list from here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump --split patch
2010/12/29 Tom Lane t...@sss.pgh.pa.us If you've solved the deterministic-ordering problem, then this entire patch is quite useless. You can just run a normal dump and diff it. No, that's only half true. Diff will do a good job minimizing the size of the diff output, yes, but such a diff is still quite useless if you want to quickly grasp the context of the change. If you have a hundreds of functions, just looking at the changed source code is not enough to figure out which functions were modified, unless you have the brain power to memorize every single line of code and are able to figure out the function name just by looking at the old and new line of codes. To understand a change to my database functions, I would start by looking at the top-level, only focusing on the names of the functions modified/added/removed. At this stage, you want as little information as possible about each change, such as only the names of the functions. To do this, get a list of changes functions, you cannot compare two full schema plain text dumps using diff, as it would only reveal the lines changed, not the name of the functions, unless you are lucky to get the name of the function within the (by default) 3 lines of copied context. While you could increase the number of copied lines of context to a value which would ensure you would see the name of the function in the diff, that is not feasible if you want to quickly get a picture of the code areas modified, since you would then need to read through even more lines of diff output. For a less database-centric system where you don't have hundreds of stored procedures, I would agree it's not an issue to keep track of changes by diffing entire schema files, but for extremely database-centric systems, such as the one we have developed at my company, it's not possible to get the whole picture of a change by analyzing diffs of entire schema dumps. The patch has been updated: *) Only spit objects with a namespace (schema) not being null *) Append all objects of same tag (name) of same type (desc) of same namespace (schema) to the same file (i.e., do not append -2, -3, like before) (Suggested by David Wilson, thanks.) I also tested to play around with ORDER BY pronargs and ORDER BY pronargs DESC to the queries in getFuncs() in pg_dump.c, but it had no effect to the order the functions of same name but different number of arguments were dumped. Perhaps functions are already sorted? Anyway, it doesn't matter that much, keeping all functions of the same name in the same file is a fair trade-off I think. The main advantage is the ability to quickly get a picture of the names of all changed functions, secondly to optimize the actual diff output. -- Best regards, Joel Jacobson Glue Finance E: j...@gluefinance.com T: +46 70 360 38 01 Postal address: Glue Finance AB Box 549 114 11 Stockholm Sweden Visiting address: Glue Finance AB Birger Jarlsgatan 14 114 34 Stockholm Sweden pg-dump-split-plain-text-files-9.1devel.patch Description: Binary data pg-dump-split-plain-text-files-9.1alpha2.patch Description: Binary data pg-dump-split-plain-text-files-8.4.6.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
Re: [HACKERS] pg_dump --split patch
On 12/28/2010 08:18 PM, Joel Jacobson wrote: 2010/12/29 Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us If you've solved the deterministic-ordering problem, then this entire patch is quite useless. You can just run a normal dump and diff it. No, that's only half true. Diff will do a good job minimizing the size of the diff output, yes, but such a diff is still quite useless if you want to quickly grasp the context of the change. If you have a hundreds of functions, just looking at the changed source code is not enough to figure out which functions were modified, unless you have the brain power to memorize every single line of code and are able to figure out the function name just by looking at the old and new line of codes. try: diff -F '^CREATE' ... 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] and it's not a bunny rabbit, either
On Mon, Dec 27, 2010 at 2:06 PM, Robert Haas robertmh...@gmail.com wrote:
The problem is that alter table actions AT_AddIndex and
AT_AddConstraint don't tie neatly back to a particular piece of
syntax. The message as written isn't incomprehensible (especially if
you're reading it in English) but it definitely leaves something to be
desired.
Ideas?
Here's a somewhat more complete patch implementing this concept, plus
adding additional messages for non-support of constraints, rules, and
triggers. More could be done in this vein, but this picks a decent
fraction of the low-hanging fruit.
I've had to change some of the heap_open(rv) calls to
relation_open(rv) to avoid having the former throw the wrong error
message before the latter kicks in. I think there might be stylistic
objections to that, but I'm not sure what else to propose. I'm
actually pretty suspicious that many of the heap_open(rv) calls I
*didn't* change are either already a little iffy or likely to become
so once the SQL/MED stuff for foreign tables goes in. They make it
easy to forget that we've got a whole pile of relkinds and you
actually need to really think about which ones you can handle.
For example, on unpatched head:
rhaas=# create view v as select 1 as a;
CREATE VIEW
rhaas=# cluster v;
ERROR: there is no previously clustered index for table v
The error message is demonstrably correct in the sense that, first,
there isn't any table v, only a view v, so surely table v has no
clustered index - or anything else; and second, even if we construe
table v to mean view v, it is certainly right to say it has no
clustered index because it does not - and can not - have any indexes
at all. But as undeniably true as that error message is, it's a bad
error message. With the patch:
rhaas=# cluster v;
ERROR: views do not support CLUSTER
That's more like it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 249067f..1555b61 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -113,7 +113,9 @@ cluster(ClusterStmt *stmt, bool isTopLevel)
Relation rel;
/* Find and lock the table */
- rel = heap_openrv(stmt-relation, AccessExclusiveLock);
+ rel = relation_openrv(stmt-relation, AccessExclusiveLock);
+ if (rel-rd_rel-relkind != RELKIND_RELATION)
+ ErrorWrongRelkind(rel, WRONG_RELKIND_FOR_COMMAND, CLUSTER);
tableOid = RelationGetRelid(rel);
diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c
index b578818..66df9f8 100644
--- a/src/backend/commands/comment.c
+++ b/src/backend/commands/comment.c
@@ -22,6 +22,7 @@
#include catalog/pg_shdescription.h
#include commands/comment.h
#include commands/dbcommands.h
+#include commands/tablecmds.h
#include libpq/be-fsstubs.h
#include miscadmin.h
#include parser/parse_func.h
@@ -583,10 +584,8 @@ CheckAttributeComment(Relation relation)
if (relation-rd_rel-relkind != RELKIND_RELATION
relation-rd_rel-relkind != RELKIND_VIEW
relation-rd_rel-relkind != RELKIND_COMPOSITE_TYPE)
- ereport(ERROR,
-(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg(\%s\ is not a table, view, or composite type,
- RelationGetRelationName(relation;
+ ErrorWrongRelkind(relation, WRONG_RELKIND_FOR_COMMAND,
+ COMMENT ON COLUMN);
}
/*
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 7b8bee8..488cc80 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -27,6 +27,7 @@
#include catalog/pg_type.h
#include commands/copy.h
#include commands/defrem.h
+#include commands/tablecmds.h
#include commands/trigger.h
#include executor/executor.h
#include libpq/libpq.h
@@ -998,8 +999,19 @@ DoCopy(const CopyStmt *stmt, const char *queryString)
cstate-queryDesc = NULL;
/* Open and lock the relation, using the appropriate lock type. */
- cstate-rel = heap_openrv(stmt-relation,
+ cstate-rel = relation_openrv(stmt-relation,
(is_from ? RowExclusiveLock : AccessShareLock));
+ if (cstate-rel-rd_rel-relkind != RELKIND_RELATION)
+ {
+ if (!is_from cstate-rel-rd_rel-relkind == RELKIND_VIEW)
+ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg(views do not support COPY TO),
+ errhint(Try the COPY (SELECT ...) TO variant.)));
+ else
+ErrorWrongRelkind(cstate-rel, WRONG_RELKIND_FOR_COMMAND,
+ is_from ? COPY FROM : COPY TO);
+ }
tupDesc = RelationGetDescr(cstate-rel);
@@ -1225,29 +1237,6 @@ DoCopyTo(CopyState cstate)
{
bool pipe = (cstate-filename == NULL);
- if (cstate-rel)
- {
- if (cstate-rel-rd_rel-relkind != RELKIND_RELATION)
- {
- if (cstate-rel-rd_rel-relkind == RELKIND_VIEW)
-ereport(ERROR,
- (errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg(cannot copy from view \%s\,
-RelationGetRelationName(cstate-rel)),
- errhint(Try the COPY (SELECT ...) TO
Re: [HACKERS] pg_dump --split patch
2010/12/29 Andrew Dunstan and...@dunslane.net try: diff -F '^CREATE' ... cheers andrew Embarrasing, I'm sure I've done `man diff` before, must have missed that one, wish I'd known about that feature before, would have saved me many hours! :-) Thanks for the tip! There are some other real-life use-cases where I think splitting would be nice and save a lot of time: a) if you don't have a perfect 1:1 relationship between all the SPs in your database and your source code repository (handled by your favorite version control system), i.e. if you suspect some SPs in the database might differ compared to the source code files in your repo. In this scenario, it might be simpler to start over and continue developing on a repo built from a pg_dump --split export. You would lose all history, but it might still be worth it if the compare everything in database against source code files in repo-project would take a lot of man hours. b) quick branching - perhaps you are a consultant at a company where they don't even have the SPs stored in separate files, they might have been magically installed by some consultant before you without any trace. :-) To get up to speed solving the problem you've been assigned, which in this example involves a lot of SP coding and modifications of existing functions, it would save a lot of time if you had all functions in separate files before you started coding, then you would use git or any other nice version control system to track your changes and figure out what you've done once you get everything to work. c) automatically saving daily snapshots of your production database schema to your version control system. While the best version control system (git) does not track individual files, many of the ancient ones still very popular ones like svn do so. If every function in the production database schema would be saved automatically to the VCS, you would be guaranteed to have a tack of all deployed changes affecting each function, which is probably a lot fewer changes compared to the entire history for each function, assuming developers commit things while developing and not only when deploying. d) while pg_dump offers some options to limit the output content, such as -s for schema only and -t/-T to limit which tables to dump, it lacks options to export functions only or these functions only. It would require quite a lot of such options to provide the same flexibility as a split dump, highly reducing the need for such options as you could then compose your own restore script based on the dump. Of course, not all of these scenarios are relevant for everybody. -- Best regards, Joel Jacobson Glue Finance
