Re: [HACKERS] Cannot compile Pg 9.0.2 with MinGW under Windows
Thanks, Andrew. I'll check my environment one more time. You wrote: AD On 12/23/2010 07:11 AM, Pavel Golub wrote: Hello, Pgsql-bugs. Tried to use MinGw under windows to build client libraries at least. However failed on ./configure --withou-zlib stage. Please find attached log file, stdout and stderr outputs. The main problem here I suppose is configure: WARNING:someheader.h: present but cannot be compiled Please five me advice on this. Thanks in advance AD Your gcc doesn't look like others we have: AD You have: AD gcc (GCC) 3.4.4 (msys special) AD Copyright (C) 2004 Free Software Foundation, Inc. AD This is free software; see the source for copying conditions. There AD is NO AD warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR AD PURPOSE. AD configure:3252: $? = 0 AD configure:3259: gcc -v 5 AD Reading specs from /usr/lib/gcc/i686-pc-msys/3.4.4/specs AD Configured with: /home/cstrauss/build/gcc3/gcc-3.4.4/configure AD --prefix=/usr --sysconfdir=/etc --localstatedir=/var AD --infodir=/share/info --mandir=/share/man --libexecdir=/lib AD --enable-languages=c,c++ --disable-nls --enable-threads=posix AD --enable-sjlj-exceptions --enable-hash-synchronization AD --enable-libstdcxx-debug --with-newlib AD Thread model: posix AD Buildfarm narwhal has: AD gcc.exe (GCC) 3.4.2 (mingw-special) AD Copyright (C) 2004 Free Software Foundation, Inc. AD This is free software; see the source for copying conditions. There is NO AD warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. AD configure:3252: $? = 0 AD configure:3259: gcc -v5 AD Reading specs from c:/MinGW/bin/../lib/gcc/mingw32/3.4.2/specs AD Configured with: ../gcc/configure --with-gcc --with-gnu-ld AD --with-gnu-as --host=mingw32 --target=mingw32 --prefix=/mingw AD --enable-threads --disable-nls AD --enable-languages=c,c++,f77,ada,objc,java AD --disable-win32-registry --disable-shared --enable-sjlj-exceptions AD --enable-libgcj --disable-java-awt --without-x AD --enable-java-gc=boehm --disable-libgcj-debug --enable-interpreter AD --enable-hash-synchronization --enable-libstdcxx-debug AD Thread model: win32 AD gcc version 3.4.2 (mingw-special) AD Buildfarm frogmouth has: AD gcc.exe (GCC) 4.5.0 AD Copyright (C) 2010 Free Software Foundation, Inc. AD This is free software; see the source for copying conditions. There is NO AD warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. AD configure:3252: $? = 0 AD configure:3259: gcc -v5 AD Using built-in specs. AD COLLECT_GCC=c:\mingw\bin\gcc.exe AD AD COLLECT_LTO_WRAPPER=c:/mingw/bin/../libexec/gcc/mingw32/4.5.0/lto-wrapper.exe AD Target: mingw32 AD Configured with: ../gcc-4.5.0/configure AD --enable-languages=c,c++,ada,fortran,objc,obj-c++ AD --disable-sjlj-exceptions --with-dwarf2 --enable-shared AD --enable-libgomp --disable-win32-registry --enable-libstdcxx-debug AD --enable-version-specific-runtime-libs --disable-werror --build=mingw32 --prefix=/mingw AD Thread model: win32 AD gcc version 4.5.0 (GCC) AD cheers AD andrew -- With best wishes, Pavel mailto:pa...@gf.microolap.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] proposal : cross-column stats
2010/12/24 Florian Pflug f...@phlo.org: On Dec23, 2010, at 20:39 , Tomas Vondra wrote: I guess we could use the highest possible value (equal to the number of tuples) - according to wiki you need about 10 bits per element with 1% error, i.e. about 10MB of memory for each million of elements. Drat. I had expected these number to come out quite a bit lower than that, at least for a higher error target. But even with 10% false positive rate, it's still 4.5MB per 1e6 elements. Still too much to assume the filter will always fit into memory, I fear :-( I have the impression that both of you are forgetting that there are 8 bits in a byte. 10 bits per element = 1.25MB per milion elements. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED - core functionality
On Tue, 21 Dec 2010 19:33:04 + Simon Riggs si...@2ndquadrant.com wrote: 1. The docs don't actually say what a foreign table is. Is it a local representation of foreign data? Or a local copy of foreign data? Or is it a table created on a remote node? Foreign table is an database object which represents the format of existing external data as PG-table, and it can be used as data source of SELECT statement. It is like a VIEW rather than a TABLE because FOREIGN TABLE doesn't have any data locally. 2. Will CREATE FOREIGN TABLE require a transactionid? It seems a good replacement for temp tables on Hot Standby to be able to run a CREATE FOREIGN TABLE using the file_fdw, then reuse the file again later. AFAIK Yes. CREATE FOREIGN TABLE make records in catalogs (pg_class, pg_attribute, etc.). 3. Do we support CREATE TEMP FOREIGN TABLE? It seems desirable to be able to move data around temporarily, as we do with normal tables. When we support write access to foreign tables, it would be useful. 4. In Hot Standby, we are creating many copies of the data tables on different servers. That seems to break the concept that data is in only one place, when we assume that a foreign table is on only one foreign server. How will we represent the concept that data is potentially available identically from more than one place? Any other comments about how this will work with Hot Standby? IIUC, If you create FOREIGN TABLE on primary node, it will be propagated to standby nodes with same generic options. Then, users connected to standby nodes can use the foreign tables to retrieve foreign data. If you have multiple standby nodes, all foreign tables on all nodes including primary node would refer one data source. For example, file_fdw would read data from the file pointed by filename option, but you can't change the path for each standby server. You may copy the file to each standby servers, or share one disk which contains the file by all servers. OTOH, RDBMS wrappers would refer same server if the SERVER, USER MAPPING and FOREIGN TABLE have same generic options (host, port, dbname, etc.), so you would need just one data instance for all of FOREIGN TABLEs on standby nodes, and data consistency might have to be checked on the remote side when the data is being changed. # Um, I might have missed your point... 5. In PL/Proxy, we have the concept that a table is sharded across multiple nodes. Is that possible here? Again, we seem to have the concept that a table is only ever in a single place. You would able to point one data source from multiple foreign tables on different PG-nodes. 6. Can we do CREATE FOREIGN TABLE AS SELECT ... I guess the answer depends on (1) We might be able to support that syntax, but IMHO it doesn't seem too useful. 7. Why does ANALYZE skip foreign tables? Surely its really important we know things about a foreign table, otherwise we are going to optimize things very badly. I think ANALYZE is good timing to get statistics of remote data. In current design, planner calls PlanRelScan() to get costs (startup/total) of the scan, but it seems difficult to estimate rows/width by each FDW. I think acquire_sample_rows() would be the hook point for that purpose. Then, how to get random sample rows would be FDW's matter, but I have not found smart way to acquire samples without sequential scan on the remote side... 8. Is the WHERE clause passed down into a ForeignScan? Parsed WHERE clause is passed to PlanRelScan() via baserestrictinfo of RelOptInfo. Wrappers would be able to push it (or part of it) down to the remote side. Maybe RDBMS wrappers need to implement deparsing routine similar to deparse_expression() or ri_GenerateQual() for themselves. 9. The docs for CHECK constraints imply that the CHECK is executed against any rows returned from FDW. Are we really going to execute that as an additional filter on each row retrieved? In current implementation, CHECK/NOT NULL constraints are not executed, and I'm not sure that they should be. NOT NULL and CHECK are supported for table inheritance mainly. 10. Can a foreign table be referenced by a FK? Currently no. FK requires PK on the referenced table, but foreign table can't have PK constraint. 11. Can you create a DO INSTEAD trigger on a FOREIGN TABLE? Currently no, but it would be useful. 12. I think it would be useful for both review and afterwards to write the documentation section now, so we can begin to understand this. Will there be a documentation section on writing a FDW also? There are enough open questions here that I think we need docs and a review guide, otherwise we'll end up with some weird missing feature, which would be a great shame. Agreed. ISTM that V. Server Programming section is suitable. 13. How does this relate to dblink? Is that going to be replaced by this feature? They would be independent each other in first version, and dblink would have to be maintained
Re: [HACKERS] SQL/MED - file_fdw
On Fri, 24 Dec 2010 11:09:16 +0900 Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Tue, Dec 21, 2010 at 21:32, Itagaki Takahiro itagaki.takah...@gmail.com wrote: On Tue, Dec 21, 2010 at 20:14, Shigeru HANADA han...@metrosystems.co.jp wrote: Attached is the revised version of file_fdw patch. This patch is based on Itagaki-san's copy_export-20101220.diff patch. #1. Don't you have per-tuple memory leak? I added GetCopyExecutorState() because the caller needs to reset the per-tuple context periodically. Sorry, I found there are no memory leak here. The related comment is: [execnodes.h] *CurrentMemoryContext should be set to ecxt_per_tuple_memory before *calling ExecEvalExpr() --- see ExecEvalExprSwitchContext(). I guess CurrentMemoryContext in Iterate callback a per-tuple context. So, we don't have to xport cstate-estate via GetCopyExecutorState(). Iterate is called in query context, so GetCopyExecutorState() need to be exported to avoid memory leak happens in NextCopyFrom(). Or, enclosing context switching into NextCopyFrom() is better? Then, CopyFrom() would need to create tuples in Portal context and set shouldFree of ExecStoreTuple() true to free stored tuple at next call. Please try attached patch. Or, if you eventually make a HeapTuple from values and nulls arrays, ExecStoreVirtualTuple() seems to be better than the combination of heap_form_tuple() and ExecStoreTuple() for the purpose. Could you try to use slot-tts_values and slot-tts_isnull for NextCopyFrom() directly? Virtual tuple would be enough to carry column data, but virtual tuple doesn't have system attributes including tableoid... Regards, -- Shigeru Hanada 20101224-switch_in_next.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] Streaming replication as a separate permissions
On Dec24, 2010, at 05:00 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: The problem here is that you suggest NOLOGIN should mean Not allowed to issue SQL commands, which really isn't what the name NOLOGIN conveys. No, it means not allowed to connect. Exactly. Which proves my point, unless you're ready to argue that replication connections somehow don't count as connections. It's possible now to issue commands as a NOLOGIN user, you just have to use SET ROLE to become the user. I think you're arguing about a design choice that was already made some time ago. You've lost me, how is that an argument in your favour? I *wasn't* arguing that NOLOGIN ought to mean No allowed to issue SQL commands. It was what *your* proposal of letting a role connect for replication purposes despite a NOLOGIN flag would *make* NOLOGIN mean. best regards, Florian Pflug -- Sent 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
Thank you for those replies, I understand things much better now. I have two remaining concerns... On Fri, 2010-12-24 at 19:51 +0900, Shigeru HANADA wrote: 15. In terms of planning queries, do we have a concept of additional cost per row on a foreign server? How does the planner decide how costly retrieving data is from the FDW? Costs for a scan on a foreign table is estimated in FDW routine PlanRelScan(). So FDW can use arbitrary algorithm to estimate costs. pgsql_fdw might execute EXPLAIN SELECT ... FROM xxx on remote side to get remote costs. OK, so there is an API call to allow the FDW to determine the size of the table, before we attempt to materialize it. That is good, and will allow us to make some reasonable optimisations. Am I right in thinking that if the materialized result is larger than some_limit_parameter, that a ForeignScan will end with an ERROR? I think we're much more at risk from this with SQL/MED than we are with direct access. Keeping data remote usually means it is very large. work_space? 16. If we cancel a query, is there an API call to send query cancel to the FDW and so on to the foreign server? Does that still work if we hot other kinds of ERROR, or FATAL? There is no handler for query cancel. If FDW wants cleanup on the interrupts, resourceowner mechanism would help. Please give this some thought. We need to be able to make a clean cancel for a remote query. If my comments seem in any way negative, it is because I have had previous experience with poorly designed SQL gateway products and have no wish to repeat those experiences in PostgreSQL. I understand it will take many years for whole feature set to arrive, though the ones mentioned above I regard as essential for the first release. Specifically, I've seen people do SELECT * FROM BigForeignTable and then be unable to cancel it until it/everyone explodes. That is especially annoying, since some SQL tools issue SELECTs as a means of doing DESCRIBE. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal : cross-column stats
2010/12/24 Florian Pflug f...@phlo.org: On Dec23, 2010, at 20:39 , Tomas Vondra wrote: I guess we could use the highest possible value (equal to the number of tuples) - according to wiki you need about 10 bits per element with 1% error, i.e. about 10MB of memory for each million of elements. Drat. I had expected these number to come out quite a bit lower than that, at least for a higher error target. But even with 10% false positive rate, it's still 4.5MB per 1e6 elements. Still too much to assume the filter will always fit into memory, I fear :-( I have the impression that both of you are forgetting that there are 8 bits in a byte. 10 bits per element = 1.25MB per milion elements. We are aware of that, but we really needed to do some very rough estimates and it's much easier to do the calculations with 10. Actually according to wikipedia it's not 10bits per element but 9.6, etc. But it really does not matter if there is 10MB or 20MB of data, it's still a lot of data ... 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] proposal : cross-column stats
On Dec24, 2010, at 11:23 , Nicolas Barbier wrote: 2010/12/24 Florian Pflug f...@phlo.org: On Dec23, 2010, at 20:39 , Tomas Vondra wrote: I guess we could use the highest possible value (equal to the number of tuples) - according to wiki you need about 10 bits per element with 1% error, i.e. about 10MB of memory for each million of elements. Drat. I had expected these number to come out quite a bit lower than that, at least for a higher error target. But even with 10% false positive rate, it's still 4.5MB per 1e6 elements. Still too much to assume the filter will always fit into memory, I fear :-( I have the impression that both of you are forgetting that there are 8 bits in a byte. 10 bits per element = 1.25MB per milion elements. Uh, of course. So in the real universe, the numbers are ~1.2MB per 1e6 elements for a false positive rate of 1% ~0.5MB per 1e6 elements for a false positive rate of 10% Hm. So for a table with a billion distinct elements, we'd need half a gigabyte per column for the filter. A tuple with two int columns takes at least 24+2*4 = 32bytes to store I think, making such a table at least 32GB in size. The filter size would thus be 1/64 of the table size in the worst case. best regards, Florian Pflug -- Sent 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 Fri, 24 Dec 2010 11:34:59 + Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2010-12-24 at 19:51 +0900, Shigeru HANADA wrote: 15. In terms of planning queries, do we have a concept of additional cost per row on a foreign server? How does the planner decide how costly retrieving data is from the FDW? Costs for a scan on a foreign table is estimated in FDW routine PlanRelScan(). So FDW can use arbitrary algorithm to estimate costs. pgsql_fdw might execute EXPLAIN SELECT ... FROM xxx on remote side to get remote costs. OK, so there is an API call to allow the FDW to determine the size of the table, before we attempt to materialize it. That is good, and will allow us to make some reasonable optimisations. Am I right in thinking that if the materialized result is larger than some_limit_parameter, that a ForeignScan will end with an ERROR? I think we're much more at risk from this with SQL/MED than we are with direct access. Keeping data remote usually means it is very large. work_space? Materialize node uses Tuplestorestate to keep the result, so huge result would use temporary files. If FDW need to store result locally, it can use Tuplestorestate. 16. If we cancel a query, is there an API call to send query cancel to the FDW and so on to the foreign server? Does that still work if we hot other kinds of ERROR, or FATAL? There is no handler for query cancel. If FDW wants cleanup on the interrupts, resourceowner mechanism would help. Please give this some thought. We need to be able to make a clean cancel for a remote query. Sure. If my comments seem in any way negative, it is because I have had previous experience with poorly designed SQL gateway products and have no wish to repeat those experiences in PostgreSQL. I understand it will take many years for whole feature set to arrive, though the ones mentioned above I regard as essential for the first release. Specifically, I've seen people do SELECT * FROM BigForeignTable and then be unable to cancel it until it/everyone explodes. That is especially annoying, since some SQL tools issue SELECTs as a means of doing DESCRIBE. First of all, I think that it depends on the implementation of FDW and capability of remote server whether user can cancel remote query. For example, current pgsql_fdw uses PQexec(SELECT * FROM table) to execute remote query, and set cleanup callback with RegisterResourceReleaseCallback() after establishment of connection. In cleanup function, pgsql_fdw issues PQfinish() to cancel the whole query. With this implementation, pgsql_fdw can stop both of local and remote query with user interrupt and other errors. I'll research whether the registration of cleanup handler can be moved into core. If we don't provide FdwRoutine handler for query cancel and other errors, it would be better to document usage of resourceower mechanism in How to write FDW section or somewhere. Regards, -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Move the documentation of --no-security-label to a more sensible
On Fri, Dec 24, 2010 at 7:13 AM, Peter Eisentraut pete...@gmx.net wrote: Move the documentation of --no-security-label to a more sensible place The order on the pg_dump/pg_dumpall man pages is not very strict, but surely putting it under connection options was wrong. I can't understand why this new location is either better or worse than the old one. As far as I can tell, the order is alphabetical for the options that have single-character forms and essentially random after that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal : cross-column stats
Dne 24.12.2010 13:15, t...@fuzzy.cz napsal(a): 2010/12/24 Florian Pflug f...@phlo.org: On Dec23, 2010, at 20:39 , Tomas Vondra wrote: I guess we could use the highest possible value (equal to the number of tuples) - according to wiki you need about 10 bits per element with 1% error, i.e. about 10MB of memory for each million of elements. Drat. I had expected these number to come out quite a bit lower than that, at least for a higher error target. But even with 10% false positive rate, it's still 4.5MB per 1e6 elements. Still too much to assume the filter will always fit into memory, I fear :-( I have the impression that both of you are forgetting that there are 8 bits in a byte. 10 bits per element = 1.25MB per milion elements. We are aware of that, but we really needed to do some very rough estimates and it's much easier to do the calculations with 10. Actually according to wikipedia it's not 10bits per element but 9.6, etc. But it really does not matter if there is 10MB or 20MB of data, it's still a lot of data ... Oooops, now I see what's the problem. I thought you were pointing out something out, but I've actually used 1B = 1b (which is obviously wrong). But Florian already noticed that and fixed the estimates. 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] proposal : cross-column stats
Dne 24.12.2010 13:37, Florian Pflug napsal(a): On Dec24, 2010, at 11:23 , Nicolas Barbier wrote: 2010/12/24 Florian Pflug f...@phlo.org: On Dec23, 2010, at 20:39 , Tomas Vondra wrote: I guess we could use the highest possible value (equal to the number of tuples) - according to wiki you need about 10 bits per element with 1% error, i.e. about 10MB of memory for each million of elements. Drat. I had expected these number to come out quite a bit lower than that, at least for a higher error target. But even with 10% false positive rate, it's still 4.5MB per 1e6 elements. Still too much to assume the filter will always fit into memory, I fear :-( I have the impression that both of you are forgetting that there are 8 bits in a byte. 10 bits per element = 1.25MB per milion elements. Uh, of course. So in the real universe, the numbers are ~1.2MB per 1e6 elements for a false positive rate of 1% ~0.5MB per 1e6 elements for a false positive rate of 10% Hm. So for a table with a billion distinct elements, we'd need half a gigabyte per column for the filter. A tuple with two int columns takes at least 24+2*4 = 32bytes to store I think, making such a table at least 32GB in size. The filter size would thus be 1/64 of the table size in the worst case. Yes, but in reality you need three such filters - one for each column, one for the combination. So that is 1.5GB (with 10% error rate) or 3.6GB (with 1% error rate). But this is severely excessive compared to the real needs, as there are usually much less distinct (not equal to the number of tuples as we assume in these computations). I was thinking about a simple heuristics to scale the filter properly, something like this: 1) sample a small portion of the table and count distinct of values 2) compute number of dist. values / number of sampled tuples 3) scale this to the whole table and scale the filter Say there are really 50 distinct values, 1.000 rows will be sampled but 20 distinct values are missing in the sample. This gives 5% in step (2) and if the table has 1.000.000 tuples you'll get 50.000 in (3). So the filter needs just 60kB. Which is a huge improvement compared to the previous approach (1.2MB). Obviously this will still lead to overestimates in most cases, and there are probably some other fail cases, but I think it's a reasonable solution. I don't think this can result in an underestimate (which is the case where you loose precision). And in case we want to build this incrementally (from a VACUUM) we really need to use a bit larger filter, because rescaling the filter is not possible AFAIK (without rebuilding it from scratch). regards 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] SQL/MED - file_fdw
On Fri, Dec 24, 2010 at 20:04, Shigeru HANADA han...@metrosystems.co.jp wrote: Iterate is called in query context, Is it an unavoidable requirement? If possible, I'd like to use per-tuple memory context as the default. We use per-tuple context in FunctionScan for SETOF functions. I hope we could have little difference between SRF and FDW APIs. Virtual tuple would be enough to carry column data, but virtual tuple doesn't have system attributes including tableoid... We could add tts_tableOid into TupleTableSlot. We'd better avoid materializing slot only for the tableoid support in foreign tables. Almost all of the foreign tables should have different data format from HeapTuple, including pgsql_fdw. -- 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] proposal : cross-column stats
Dne 24.12.2010 04:41, Florian Pflug napsal(a): The filter size could be derived from the table's statistics target, or be otherwise user-definable. We could also auto-resize once it gets too full. But still, that all seems awfully complex :-( Using a statistics target is a good idea I think. I think we could use it to determine error rate of the filter. Something like error rate = 10 - 0.9 * (statistics_target - 100) which gives 1% for statistics target 1000 10% for statistics target 100 or maybe something like this (where the error rate grows faster for smaller statistic target values) error rate = 11 - 91000 / (statistics_target^2) which gives about 1% for statistics target 1000 10% for statistics targer 100 36% for statistics target 50 But I guess 10% error rate is the minimum we need so it does not make much sense to use lower values. Another possibility is to collect the data from just a small portion of a table and then use the result to estimate the number of distinct values for the whole table. But I'm not sure we can do this reliably, I see many traps in this. This is how it works currently. The problem with this approach is that it gives you very little guarantees about how precise the result will be. Extrapolating works very well for things like MKVs and histograms, because there you're by definition interested mostly in values which occur often - and thus with a high probability in the relative few rows you sample. For the number of distinct values, however, this isn't true - if ndistinct is an order of magnitude smaller than the number of rows, relatively few rows can account for a large percentage of the distinct values... That basically means we need to sample a large portion of the table :-( Another idea would be to obtain the ndistinct values from an index somehow. Postgres cannot currently scan an index in physical order, only in logical order, due to locking considerations. But since we'd only be interested in an estimate, maybe a scan in physical block order would work for ndistinc estimates? Just a wild idea, mind you, I haven't checked at all if that'd be even remotely feasible. I was thinking about that too, and I think we could do this using pageinspect contrib module. Sure, there might be a problem with bloated indexes. And relying on this actually means it's required to have a multi-column index on all the columns. Individual indexes are not enough as we need to get the number of distinct combinations too. regards 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] Re: [COMMITTERS] pgsql: Move the documentation of --no-security-label to a more sensible
On fre, 2010-12-24 at 08:02 -0500, Robert Haas wrote: On Fri, Dec 24, 2010 at 7:13 AM, Peter Eisentraut pete...@gmx.net wrote: Move the documentation of --no-security-label to a more sensible place The order on the pg_dump/pg_dumpall man pages is not very strict, but surely putting it under connection options was wrong. I can't understand why this new location is either better or worse than the old one. As far as I can tell, the order is alphabetical for the options that have single-character forms and essentially random after that. Except that there is a The following command-line options control the database connection parameters. in between. -- Sent 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 -X
Robert Haas wrote: The existing comment says that -X is deprecated, but that doesn't make it entirely 100% clear that the code isn't intended to be further updated Yeah, Dan recently implemented the DEFERRABLE transaction behavior which was discussed on the list, so I added a --serializable-deferrable long option to pg_dump in my repo, and I had to look at that comment and the the current -X usage for several minutes before I decided I shouldn't also add a corresponding -X option -- and even then I wasn't feeling entirely sure about it. My first recommendation would be to rip it out, but a more explicit comment would have saved me that time. -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] Re: [COMMITTERS] pgsql: Move the documentation of --no-security-label to a more sensible
On Fri, Dec 24, 2010 at 9:01 AM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2010-12-24 at 08:02 -0500, Robert Haas wrote: On Fri, Dec 24, 2010 at 7:13 AM, Peter Eisentraut pete...@gmx.net wrote: Move the documentation of --no-security-label to a more sensible place The order on the pg_dump/pg_dumpall man pages is not very strict, but surely putting it under connection options was wrong. I can't understand why this new location is either better or worse than the old one. As far as I can tell, the order is alphabetical for the options that have single-character forms and essentially random after that. Except that there is a The following command-line options control the database connection parameters. in between. Ah, OK. I missed that. Thanks. -- 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_ctl and port number detection
Bruce Momjian wrote: Tom Lane wrote: Actually, if we're going to do this at all, we should do pid datadir port socketdir ... here be dragons ... so that pg_ctl doesn't have to assume the server is running with a default value of unix_socket_dir. Not sure what to put in the fourth line on Windows though ... maybe just leave it empty? OK, here is a patch that adds the port number and optionally socket directory location to postmaster.pid, and modifies pg_ctl to use that information. I throw an error on using Win32 with pre-9.1 servers because we can't get the port number from that file. This removes some crufty code from pg_ctl and removes dependency on serveral user-configurable settings that we added as a work-around. This will allow pg_ctl -w to work more reliabily than it did in the past. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO item for pg_ctl and server detection
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. -- 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] WIP patch for parallel pg_dump
Robert Haas wrote: I actually think that the phrase this has been discussed before and rejected should be permanently removed from our list of excuses for rejecting a patch. Or if we must use that excuse, then I think a link to the relevant discussion is a must, and the relevant discussion had better reflect the fact that $TOPIC was in fact rejected. It seems to me that in at least 50% of cases, someone comes back and says one of the following things: 1. I searched the archives and could find no discussion along those lines. 2. I read that discussion and it doesn't appear to me that it reflects a rejection of this idea. Instead what people seemed to be saying was X. 3. At the time that might have been true, but what has changed in the meanwhile is X. Agreed. Perhaps we need an anti-TODO that lists things we don't want in more detail. The TODO has that for a few items, but scaling things up there will be cumbersome. I agree that having the person saying it was rejected find the email discussion is ideal --- if they can't find it, odds are the patch person will not be able to find it either. -- 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] disk caching for writing log
flyusa2010 fly wrote: Thanks for your reply. Yes, i mean disk may lie to os. Our documentation covers this extensively: http://www.postgresql.org/docs/9.0/static/wal-reliability.html --- On Fri, Dec 3, 2010 at 12:14 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: On 12/03/2010 06:43 PM, Heikki Linnakangas wrote: On 03.12.2010 13:49, flyusa2010 fly wrote: When writing log, dbms should synchronously flush log to disk. I'm wondering, if it is possible that the logs are in disk cache, while the control is returned to dbms again, so dbms thinks logs are persistent on disk. In this case, if the disk fails, then there's incorrectness for dbms log writing, because the log is not persistent, but dbms considers it is persistent! I have no idea what you mean. The method we use to flush the WAL to disk should not be fallible to such failures, we wait for fsync() or fdatasync() to return before we assume the logs are safely on disk. If you can elaborate what you mean by control is returned to dbms, maybe someone can explain why in more detail. I think he is refering to the plain old the disk/os is lying about whether the data really made it to stable storage issue(especially with the huge local caches on modern disks) - if you have such a disk and/or an OS with broken barrier support you are doomed. Stefan -- 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] WIP patch for parallel pg_dump
anwhile is X. Agreed. Perhaps we need an anti-TODO that lists things we don't want in more detail. The TODO has that for a few items, but scaling things up there will be cumbersome. Well there is a problem with this too. A good example is hints. A lot of the community wants hints. A lot of the community doesn't. The community changes as we get more mature and more hackers. It isn't hard to point to dozens of items we have now that would have been on that list 5 years ago. I agree that having the person saying it was rejected find the email discussion is ideal --- if they can't find it, odds are the patch person will not be able to find it either. I would have to agree here. The idea that we have to search email is bad enough (issue/bug/feature tracker anyone?) but to have someone say, search the archives? That is just plain rude and anti-community. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/python improvements
On Dec 23, 2010, at 3:38 AM, Jan Urbański wrote: Oh, didn't know that. I see that it does some more fancy things, like defining a inheritance hierarchy for these exceptions and adding some more into the mix. Right, there were some cases that appeared to benefit from larger buckets than what the existing code classes provided. Also, some of the exceptions in there are strictly for py-postgresql/client-side things. The names I used are not really invented, they're just plpgsql condition names from http://www.postgresql.org/docs/current/static/errcodes-appendix.html with underscores changed to camel case. Also, since they're autogenerated from utils/errcodes.h they don't have any hierarchy, they just all inherit from SPIError. For the backend setting, I think this is quite appropriate. However, for pg-python, I had mixed feelings about this as I wanted to be able to leverage py-postgresql's hierarchy, but still have the projects independent. I ended up punting on this one by using a single error class, and forcing the user to compare the codes. =( Sticking Error to every one of them will result in things like SubstringErrorError, so I'm not really sold on that. There was some creativity applied to the names in postgresql.exceptions to accommodate for things like that. (Like no redundant Error) Basically I think more PL/Python users will be familiar with condition names as you use them in pl/pgsql than with the names from py-postgresql. I think that's fair assumption. In fact, I think that might make a good TODO for py-postgresql/pg-python. Provide a plpgsql-code-name to exception class mapping. cheers, jwp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for parallel pg_dump
On Fri, Dec 24, 2010 at 2:48 PM, Joshua D. Drake j...@commandprompt.com wrote: I would have to agree here. The idea that we have to search email is bad enough (issue/bug/feature tracker anyone?) but to have someone say, search the archives? That is just plain rude and anti-community. Saying search the bugtracker is no less rude than search the archives... And most of the bugtrackers I've had to search have way *less* ease-of-use for searching than a good mailing list archive (I tend to keep going back to gmane's search) 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] WIP patch for parallel pg_dump
On 12/24/2010 06:26 PM, Aidan Van Dyk wrote: On Fri, Dec 24, 2010 at 2:48 PM, Joshua D. Drakej...@commandprompt.com wrote: I would have to agree here. The idea that we have to search email is bad enough (issue/bug/feature tracker anyone?) but to have someone say, search the archives? That is just plain rude and anti-community. Saying search the bugtracker is no less rude than search the archives... And most of the bugtrackers I've had to search have way *less* ease-of-use for searching than a good mailing list archive (I tend to keep going back to gmane's search) It's deja vu all over again. See mailing list archives for details. 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] WIP patch for parallel pg_dump
On Fri, 2010-12-24 at 18:26 -0500, Aidan Van Dyk wrote: On Fri, Dec 24, 2010 at 2:48 PM, Joshua D. Drake j...@commandprompt.com wrote: I would have to agree here. The idea that we have to search email is bad enough (issue/bug/feature tracker anyone?) but to have someone say, search the archives? That is just plain rude and anti-community. Saying search the bugtracker is no less rude than search the archives... And most of the bugtrackers I've had to search have way *less* ease-of-use for searching than a good mailing list archive (I tend to keep going back to gmane's search) I think you kind of missed my point. JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for parallel pg_dump
On Dec 24, 2010, at 10:52 AM, Bruce Momjian br...@momjian.us wrote: Agreed. Perhaps we need an anti-TODO that lists things we don't want in more detail. The TODO has that for a few items, but scaling things up there will be cumbersome. I don't really think that'd be much better. What might be of some value is summaries of previous discussions, *with citations*. Foo seems like it would be useful [1,2,3] but there are concerns about bar [4,5] and baz[6]. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
