Re: [HACKERS] Building under Visual Studio 2008 - pqcomm.c compile error
I'm getting this same error and wonder if Tom's fix is sufficient. IPPROTO_IPV6 is defined in wd2def.h IF _WIN32_WINNT = 0x0501, but in pg_config_os.h _WIN32_WINNT is DEFINED as 0x0500 so IPPROTO_IPV6 is left undefined. Regards, Dave --- Unfortunately I had to uninstall VS2005 to install VS2008 due to limited space, or I'd search the old libraries to see where the symbol used to be. Anyone care to search their VS2005 includes for IPPROTO_IPV6 so I can compare to the VS2008? It is defined unconditionally in PlatformSDK/Include/WinSock2.h in VS2005 Professional. Will a build run on Win2000 if we define 0x0501 ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the case for machine-readable error fields
Right now, I do this like this:
if ($err =~ /name_of_first_foreign_key/) {
$r-error_exit('First error message.')
}
elsif ($err =~ /name_of_second_foreign_key/) {
...
As an aside comment, a bit more regex foo with \b is indicated here :-)
if ($err =~ /\bname_of_first_foreign_key\b/) {
$r-error_exit('First error message.')
}
Andreas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Automating Partitions in PostgreSQL - Query on syntax
Which leads me to the same conclusion: anything as complicated as CASE is the wrong design. But perhaps for slightly different reasons. What I like about the sql CASE is, that it is expression based, and thus allows full flexibility in partitioning and is highly self documenting. Do we need to invent special syntax, or could we use common syntax and detect specific use cases and handle them specially ? e.g. when a = const1 and a const2 ...; when a = const2 and a const3 - check a btree opclass exists for datatype of a - prove the partitions don't overlap - prove the btree order of the partitions - ... Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Abwesend: [HACKERS] [GENERAL] string_to_array with empty input
Could the list admin please unsubscribe Andreas Zeugswetter from the lists until he can fix his vacation-responder-gone-nuts? I am very sorry, fixed. I forgot to set nomail before setting the out of office assistant which my company requires me to do in this braindead way. But I assume Exchange clearly marked it as automatic in the headers, so I wonder why the list software did not kill it, Marc ? Andreas -- 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: [HACKERS] Kerberos V5 required for PostgreSQL installation on Windows
We should delayload this dll since it is only needed for specific configuration. No need to install when it is not used. Andreas -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Dann Corbit Sent: Friday, February 27, 2009 2:31 AM To: pgsql-gene...@postgresql.org Cc: pgsql-hackers@postgresql.org Subject: [HACKERS] Kerberos V5 required for PostgreSQL installation on Windows [bayes][heur] Importance: Low If Kerberos V5 is not installed on a Windows platform, the following error dialog is returned upon attempted installation: Posgres.exe - Unable to Locate Component This application has failed to start because krb5_32.dll was not found. Re-installing the application may fix this problem. [OK] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Service not starting: Error 1053
I did re-install Tune-Up Utilities, Kaspersky, Holdem Manager and Daemon Tools, so those were probably not the problemcause since it's still working. I think unfortunately it may be the case, that only initdb has a problem. Andreas -- 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: [HACKERS] RE: [HACKERS] Kerberos V5 required for PostgreSQL installation on Windows
We should delayload this dll since it is only needed for specific configuration. No need to install when it is not used. That would require building knowledge of DLL names into the code, which isn't practical as some projects have a habit of changing them regularly (e.g. gettext). Yup, that is bad. Seems the krb5_32.dll name is quite stable though. Not sure if you can specify a list of anticipated names ? I'm not sure why Dann would see this problem - all our installers include the required DLLs, and they should also be in the Oh, didn't know that. I guess that shifts it to pilot error then. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuumdb --freeze
One more question I have though is: How do you make sure noone (e.g. autovacuum analyze) unfreezes tuples after the vacuum freeze ? I will start a new thread to answer this question, but the short answer is that the freeze only needs to happen in a fresh initdb database, and once clog is copied over, new transactions can be created normally. Yes. I am still answering here because my question was related to upgrade. I think you need to turn off autovacuum before freezing to avoid a later analyze that unfreezes pg_class (or the stats table). Also to really have all tables frozen I think the order of the freezes is crutial. Consider the order: freeze pg_class freeze pg_columns -- won't this unfreeze pg_class ? And what about shared catalogs ? Or does heap_update not change the xid ? Or can it use a frozen xid ? Or does it all work when done in one large transaction ? I think I am confused, sorry :-( Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuumdb --freeze
I would like to add a --freeze parameter to vacuumdb for use by the binary upgrade utility, and for symmetry with the existing VACUUM options; patch attached. Exactly what do you think the upgrade utility is going to do with it? Surely not a database-wide VACUUM FREEZE, if we are hoping that upgrade is going to be fast. As far as I can see this is a solution looking for a problem. I didn't go into the use-case. The way pg_migrator works is to copy the _schema_ from the old database and load it into the new database. We then need to run vacuum freeze on the schema-only databases because we then move pg_clog from the old database to the new one; so, it is needed, and it will not take long to run. My first impulse was the same as Tom's, thanks for the explanation. To the filled database case: Would it make sense to enhance --table to allow wildcards and remove the cannot vacuum a specific table in all databases check ? One more question I have though is: How do you make sure noone (e.g. autovacuum analyze) unfreezes tuples after the vacuum freeze ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
So if this is a compiler bug, it certainly isn't an obvious one. I'll dig deeper to see how I can convince configure to use -qnooptimize. Set CFLAGS in its environment. The default is set in src/template/aix CFLAGS=-O2 -qmaxmem=16384 -qsrcmsg -qlonglong When I've used the build farm scripts, configure gives xlC_r these flags: configure:7117: xlC_r -q64 -o conftest -O2 -qmaxmem=16384 -qsrcmsg -qlonglong -g -I/opt/freeware/include/libxml2 -L/opt/freeware/lib conftest.c -lm 5 Um, why are you using the C++ frontend ? Have you tried xlc_r or cc_r instead ? Using xlC_r or xlc_r, implicitly sets -qansialias. IIRC we had some issues with aliasing on other platforms too ? Would that switch be wrong ? Doc sais: Use type-based aliasing during optimization. Dunno where the -q64 came from ... Probably together with the choice of compiler command ? Andreas -- 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: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Yes, I've had CC exported as xlC_r -q64 to do 64 bit builds, and use vacpp C++ instead of C. Guess it didn't like that, and ended up with some horrible compiler optimization or something that killed it. Have you determined whether the problem is optimization or 64bit ? Are there any other tests I can run now that PostgreSQL is installed? Well, the next thing would be running the regression tests. Since the -qnooptimize build is not optimal, an interesting build would probably be with: CC=xlc_r -q64 -qnoansialias Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to get SE-PostgreSQL acceptable
I don't think partitioning is really the same thing as row-level security. Of course not, but it seems to me that it can be used to accomplish most of the same practical use-cases. The main gripe about doing it via partitioning is that the user's nose gets rubbed in the fact that there can't be an enormous number of different security classifications in the same table (since he has to explicitly make a partition for each one). Imho a useful partitioning feature that is worth extra syntax additions will have to include the ability to automatically create partitions on demand (and maybe remove empty ones during vacuum). (I have refrained from discussing partitioning until now, because I thought this is not the time. But the certainty with which manual creation is implied here makes me nervous.) I short it (imho) requires a partitioning clause (much like a group by clause in sql) and optionally an expression to produce a partition name (+ maybe for the nostalgic a tablespace name mapping expression). If partitioning for row level sec includes a sec column as proposed, I think the two could be combined as a means for performance optimization. But I am not sure partitioning alone can efficiently replace the sec column approach. (especially in the admittedly unlikely 100 sec label scenario). (When a constraint says the partition only contains visible security labels, the sec check can be done at the partition level (including CE for denied labels)) Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Automatic view update rules Bernd Helmle
There is another thing that's bothering me, though, which is that the present approach to dumping rules isn't adequate. Consider the following scenario: 1. You create a view that the system considers updatable, so it creates some automatic rules. 2. You don't want those rules, so you delete them, leaving you with the traditional behavior where attempted inserts etc on the view fail. Is that why other db's only make views updateable, that are created WITH CHECK OPTION ? Should we also follow that path ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving compressibility of WAL files
You don't want to just modify pg_standby to accept small files, because then you've made it harder to make absolutely sure when the file is ready to be processed if a non-atomic copy is being done. It is hard, but I think it is the right way forward. Anyway I think the size is not robust at all because some (most ? e.g. win32) non-atomic copy implementations will also show the final size right from the beginning. Could we use stricter file locking when opening WAL for recovery ? Or implement a wait loop when the crc check (+ a basic validity check) for the next record fails (and the next record is on a 512 byte boundary ?). I think standby and restore recovery can be treated differently to startup recovery because a copied wal file, even if the copy is not atomic, will not have trailing valid WAL records from a recycled WAL. (A solution that recycles WAL files for restore/standby would need to make sure it renames the files *after* restoring the content.) Btw how do we detect end of WAL when restoring a backup and WAL after PANIC ? 1) Provide the length as part of the archive command +1 Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Do we still need constraint_exclusion?
So, barring objections, I'll go make this happen. What do we want to call the intermediate constraint_exclusion value? The first thing that comes to mind is constraint_exclusion = 'child', but perhaps someone has a better idea. Not a huge fan of 'child' since it implies inheritance. 'union' doesn't work for a similar reason. What about 'partitioned'? Hm, how about just 'partition'? +1 Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lazy_truncate_heap()
Logically, xmin horizon conflicts could be flexible/soft. That is, if we implemented the idea to store a lastCleanedLSN for each buffer then xmin horizon conflicts would be able to continue executing until they see a buffer with buffer.lastCleanedLSN conflictLSN. I think the trouble is, that HOT can put extremely recent lastCleanedLSN's on pages. It would need some knobs to avoid this, that most likely reduce efficiency of HOT. What about using the page LSN after max_standby_delay ? Using the page LSN cancels queries earlier than the lastCleanedLSN, but probably in many cases later than an immediate cancel after max_standby_delay. Of course that only helps when reading static parts of tables :-( Instead of a cancel message, the replay would need to send (set in shmem) the first LSN applied after max_standby_delay to the relevant backend for it's LSN checks (if buffer.LSN = received_max_delay_lsn cancel). Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: default values for function parameters
it's look well, but I still prefer some combination with = name: = '' name: = ''' :name = '' $name = .. $name = .. I wonder about name := ''. := is used in Pascal/Ada to assign a value. Or would that again be an allowed operator in pg ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
If we use some type of integer, I suggest using this structure for pg_security: CREATE TABLE pg_security( relid oid, secid int2, secacl aclitem[], secext TEXT ); This allows the per-row value to be a simple int2. It also improves maintenance because rows are associated only with a specific table; unused values can then be removed more easily. And it allows both secacl and secext security to be specified. I do not expect that the number of unique combinations of rights strongly varies between the tables. Thus I think creating pg_security rows per table would vastly increase the size of pg_security. The expected size of pg_security is small in the current implementation. Example: security_context = top_secret_t With above schema you need one row in pg_security for each table that has top_secret_t rows. The current implementation only needs one row for this, which is imho better. CREATE TABLE pg_security( secid serial, secacl aclitem[], secext TEXT ); May be ok, but I am with KaiGai, that it is not obvious how to update the security context syntactically when using 2 subsystems simultaneously. But using, restricting and selecting is easy. Andreas -- 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: [HACKERS] Updates of SE-PostgreSQL 8. 4devel patches (r1268)
Ah, that is a good point, that if we have security column which is usually null then we are requiring the NULL bitmask. Yes, I think that would not be optimal, thus I think WITH SECURITY_CONTEXT is needed. I sure wish others were adding ideas to this discussion. One such idea would be, that the security info is already normalized. pg_security has one row for each security_context. It is my understanding, that such a context row may already be a combination of rights. Thus adding an extra column per subsystem to the user tables may not be required. You could have all info for each security subsystem in the pg_security table. This can eighter be done by having one row in pg_security per subsystem type and oid, or by having a separate column in pg_security per subsystem. The imho difficult part is, that currently selecting security_context defaults to mapping the oid to the text representation for selinux. Concern has already been voiced in this regard. Maybe this is another reason to not do automatic mapping, but require a specified conversion for text output. Or is the column name security_context and representation a standard ? This is just an idea, since I do not really think actually using more than one security subsystem in parallel will be common. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1268)
Ah, that is a good point, that if we have security column which is usually null then we are requiring the NULL bitmask. Yes, I think that would not be optimal, thus I think WITH SECURITY_CONTEXT is needed. I sure wish others were adding ideas to this discussion. One such idea would be, that the security info is already normalized. I formulated that sentence badly , sorry :-( Replace with: Since the security info is already normalized, one such idea would be: pg_security has one row for each security_context. It is my understanding, that such a context row may already be a combination of rights. Thus adding an extra column per subsystem to the user tables may not be required. You could have all info for each security subsystem in the pg_security table. This can eighter be done by having one row in pg_security per subsystem type and oid, or by having a separate column in pg_security per subsystem. The imho difficult part is, that currently selecting security_context defaults to mapping the oid to the text representation for selinux. Concern has already been voiced in this regard. Maybe this is another reason to not do automatic mapping, but require a specified conversion for text output. Or is the column name security_context and representation a standard ? This is just an idea, since I do not really think actually using more than one security subsystem in parallel will be common. We already have this. The idea is that the security columns will hold an OID and the OID will point to a row in a table that contains the security rights/ACL for the column, with multiple rows using the same rights OID. If you change the rights on the column the code has to check the existing entries and add a new one if it doesn't already exist. This does add the problem of how to remove security rows that are no longer referenced. Please reread with above correction, and I'll also try a little differently: Since a pg_security row already represents a combination of rights within selinux, I do not really see why that cannot be extended to a combination of rowacl and selinux rights or more general one oid represents a unique combination of rights within different subsystems ? A simplified example of pg_security: oid rights 1 selinux:secret_read rowacl:ra,rb 2 selinux:unlabeled_t rowacl:ra,rb 3 selinux:secret_read rowacl:ra Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby utility and administrator functions
* pg_last_recovered_xact_xid() Will throw an ERROR if *not* executed in recovery mode. returns bigint * pg_last_completed_xact_xid() Will throw an ERROR *if* executed in recovery mode. returns bigint Should these return xid? And shouldn't these two be folded together ? It seems most usages of this xid(/lsn?) will be agnostic to the recovery mode. Or if not, it seems more convenient to have a function that returns both recovery mode and xid, no ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.3 .4 + Vista + MingW + initdb = ACCESS_DENIED
The user running initdb (or the postmaster) needs SeCreateGlobalPrivilege - which is something we cannot really start Why not ? Doesn't the pg installer already tweak the permissions of the installation user. On XP you can connect to session 0, so that is an alternative on XP. telling people they must have. My view is that we revert the change (well, replace it with something that looks less like a broken attempt to use the global namespace) and leave it at that. iirc, the use of yea, removing the +18 is grotesque, since it moves the shmem into the private session but gives it a very misleading name (Global\ -- Global/). the global namespace is there to ensure things work as they should under a non-console terminal services session - which is pretty rare and can usually be avoided. Hm? non-console is the default for remote, and all you can get in Vista/2008 and up. The reason it should be in the Global namespace is that shmem is one part of detecting an existing postmaster. Especially in situations where the db is started by hand, the protection against duplicate startup is important. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches
We already have an optional OID system column that can be specified during table creation (WITH OIDS). We could have another optional oid column (WITH ROW SECURITY) called security_context which would store the oid of the role that can see the row; if the oid is zero (InvalidOid), A role alone is not sufficient. It needs to be the proposed mapping to pg_security. anyone can see it. SE-PostgreSQL would default to WITH ROW SECURITY and use the oid to look up strings in pg_security. The above explanation is not correct, as Tom mentioned. The security system column is declared as TEXT type, however, every tuple has a Oid value to indicate pg_security system catalog. It enables to prevent waste of storage. When user tries to read the system column, it is translated from Oid to text representation. Imho the important points Bruce wanted to make are: 1. there is only one extra oid storage column per row (regardless whether it is translated to text upon select) this is already the case in the patch. 2. the column(s) are system columns, so they do not show up in select * I think having access to the oid directly could be beneficial to performance. e.g. a smart client could cache pg_security and map the oid's to text locally instead of transferring the quite verbose text representation for every row. That may be mute, because showing the security_context definitely sounds more like an admin kind of functionality. Traditionally the column would probably be oid and sql would need to cast it for the text representation (e.g. security_context::regsecurity). Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction Snapshots and Hot Standby
Simon Riggs wrote: 2. Master ignores Standby's OldestXmin Effects: * Long running queries on standby... Have no effect on primary Can delay apply of WAL records on standby * Queries on standby give consistent answers in all cases. Just for clarification, if you set a max_slave_delay it means it is the maximum amount of time WAL replay can be delayed on the slave, _and_ it is the maximum amount of time a query/snapshot can be guaranteed to run without the possibility of being canceled. My point is that these two concepts are linked to the same setting. I wonder whether the cancel can be delayed until a tuple/page is actually accessed that shows a too new xid. The procedure would be like this: Instead of cancel, the backend gets a message with a lsn_horizon. From there on, whenever the backend reads a page/tuple with a LSN lsn_horizon it cancels. I think that should allow some more queries to complete. Especially such that target static tables, or static parts of large tables using appropriate index btree ranges that are also static. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction Snapshots and Hot Standby
I wonder whether the cancel can be delayed until a tuple/page is actually accessed that shows a too new xid. Yes, its feasible and is now part of the design. This is all about what happens *if* we need to remove rows that a query can still see. I was describing a procedure for exactly that case. If a slave backend has a snapshot that we cannot guarantee any more (because max_slave_delay has been exceeded): Instead of cancel, the backend gets a message with a lsn_horizon. From there on, whenever the backend reads a page/tuple with a LSN lsn_horizon it cancels. but not before (at the time max_slave_delay has been reached), as described earlier. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous Log Shipping Replication
Don't understand. I am referring to the logic at the top of AdvanceXLInsertBuffer(). We would need to wait for all people reading the contents of wal_buffers. Oh, I see. If a slave falls behind, how does it catch up? I guess you're saying that it can't fall behind, because the master will block before that happens. Also in asynchronous replication? And what about when the slave is first set up, and needs to catch up with the master? I think the WAL Sender needs the ability to read the WAL files directly. In cases where it falls behind, or just started, it needs to be able to catch up. So, it seems we eighter need to copy the WAL buffer into local memory before sending, or lock the WAL buffer until send finished. Useful network timeouts are in the = 5-10 sec range (even for GbE lan), so I don't think locking WAL buffers is feasible. Thus the WAL sender needs to copy (the needed portion of the current WAL buffer) before send (or use async send that immediately returns when the buffer is copied into the network stack). When the WAL sender is ready to continue it eighter still finds the next WAL buffer (or the rest of the current buffer) or it needs to fall back to Plan B and read the WAL files again. A sync client could still wait for the replicate, even if local WAL has already advanced massively. The checkpointer would need the LSN info from WAL senders to not reuse any still needed WAL files, although in that case it might be time to declare the replicate broken. Ideally the WAL sender also knows whether the client waits, so it can decide to send a part of a buffer. The WAL sender should wake and act whenever a network packet full of WAL buffer is ready, regardless of commits. Whatever size of send seems appropriate here (might be one WAL page). The WAL Sender should only need to expect a response, when it sent a commit record, ideally only if a client is waiting (and once in a while at least for every log switch). All in all a useful streamer seems like a lot of work. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SeqScan costs
Proposal: Make the first block of a seq scan cost random_page_cost, then after that every additional block costs seq_page_cost. +1 AFAICS the cost cross-over is much higher than the actual elapsed time cross-over for both narrow and wide tables. Which makes absolute sense, since readahead can only reduce cost when you read more than one page (and more than a few when lacking fadvise tech). I am wondering about your test though. It was all cached, so it seems we also underestimate the CPU cost of accessing and comparing all the rows during seq scan. Thats why using SET enable_seqscan=off helps performance in many cases, or why people reduce random_page_cost to force index selection. Sequential scans that cause more IO than an alternate index path are often counter productive in highly concurrent scenarios. In such scenarios it is really reasonable to lower random_page_cost. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: Add columns via CREATE OR REPLACE VIEW
If you accept the idea that column identity should be based on column name, then the only two operations that are really necessary are CREATE OR REPLACE VIEW and ALTER VIEW RENAME COLUMN, and it is 100% clear what the semantics of those operations should be. +1 I think this would be an easily useable and understandable concept. I also fully support Robert's reasoning in his next reply to Tom, detailing why his patch's provided functionality is acceptable. Andreas PS: alter view in O does not change the base definition, it only allows modifying view constraints. -- Sent 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: plan invalidation vs stored procedures
Changing statement result type is also currently prohibited in StorePreparedStatement. There maybe good reasons for this, How about the SQL spec says so? Prepare time is often also the time when you bind the result, or more generally set up the code to handle the result. Generally I argue, that a mode of operation must exist where a change in return type throws an error, so the client can readjust to the change. We are only allowed to silently replan when it is clear that the caller is agnostic to the change. e.g. because the caller only accesses explicit columns of the return type/result set, or does not supply a new parameter with a default, (or because he set some parameter that tells us he can cope). Certainly a new prepare must be able to cope with the change though, which currently does not seem to be the case when an SP calls another one that was dropped (and recreated)? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal - improve eqsel estimates by including histogram bucket numdistinct statistics
Obviously we run into problems when a) we have a poor estimate for ndistinct - but then we have worse problems b) our length measure doesn't correspond well with ndistinct in an interval One more problem with low ndistinct values is that the condition might very well hit no rows at all. But Idea 1 will largely overestimate the number of hits. e.g. char(2) field has a histogram bin for 'a1' - 'b1' ndistinct is 2 because actual values in the bin are 'a1' and 'a2'. A query for 'a3' now has a bogus estimate of nrowsperbin / 2. I think for low ndistinct values we will want to know the exact value + counts and not a bin. So I think we will want additional stats rows that represent value 'a1' stats. Andreas -- 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: Proposal - improve eqsel estimates by including histogram bucket numdistinct statistics
I think for low ndistinct values we will want to know the exact value + counts and not a bin. So I think we will want additional stats rows that represent value 'a1' stats. Isn't that what our most frequent values list does? Maybe ? Do we have the relevant stats for each ? But the trick is to then exclude those values from the histogram bins. Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax decisions for pl/pgsql RAISE extension
So right now I'm thinking I like my original proposal http://archives.postgresql.org/pgsql-hackers/2008-05/msg00357.php with the exception that we should go with SQLSTATE 'xyzzy' as the syntax in EXCEPTION lists. Also I'm willing to go with ERRCODE rather than CODE as the name of the USING option, since Other db's go with SQLCODE and SQLSTATE. Would SQLCODE be better than ERRCODE ? SQLCODE is usually an integer value, but the values correspond to the strings used in pg. (Think of the strings as typedefs for a number, like DEVIDE_BY_ZERO == -11028 SQLSTATE '22012') Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] statement timeout vs dump/restore
Do we want the following: 1. pg_dump issues set statement_timeout = 0; to the database prior to taking its copy of data (yes/no/default-but-switchable) 2. pg_dump/pg_restore issue set statement_timeout = 0; in text mode output (yes/no/default-but-switchable) 3. pg_restore issues set statement_timeout = 0; to the database in restore mode (yes/no/default-but-switchable) I think yes for all three. There was some handwaving about someone maybe not wanting it, but an utter lack of convincing use-cases; so I see no point in going to the effort of providing a switch. Note that 2 and 3 are actually the same thing (if you think they are not, then you are putting the behavior in the wrong place). I thought a proper fix for 3 would not depend on 2 ? Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
