Re: [HACKERS] pg_archivecleanup should remove WAL files also in pg_xlog?
On 13.12.2010 09:50, Fujii Masao wrote: On Mon, Dec 13, 2010 at 4:28 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 13.12.2010 08:44, Fujii Masao wrote: pg_archivecleanup removes unnecessary WAL files from the archive, but not from pg_xlog directory. So, after failover, those WAL files might exist in pg_xlog and be archived again later. A file that has already been archived successfully should not be archived again. The server keeps track of which files it has already archived with the .ready/.done files. This seems to require * archiver to save the last archived WAL file name in the shmem * walsender to send it to walreceiver * walreceiver to create .done file when it's arrived * bgwriter not to remove WAL files which don't have .done file in standby Right? One good side effect of this is that we can prevent WAL files from being removed from the standby before the master archives them. Oh, you said after failover, I missed that. So the problem is that the standby might try to re-archive files that the master already archived. If the only consequence is that you get some extra WAL files in the archive, until pg_archivecleanup runs again, I think we can just live with it. But don't you have bigger problems when standby tries to archive a file that already exists in the archive, because master already archived it? We advise to write archive_command so that it fails if the file exists already. -- 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] hstores in pl/python
Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Regards Pavel Stehule 2010/12/13 Jan Urbański wulc...@wulczer.org: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan -- 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] Instrument checkpoint sync calls
Robert Haas wrote: I'm wondering why md.c is converting the results from an exact value to a floating point, only to have xlog.c turn around and convert back to an integer. I think it could just return milliseconds directly, or if you're worried about a checkpoint that takes more than 24 days to complete, seconds and microseconds. md.c is printing the value as a float, so I converted early to a double and then percolated it upward from there. More an artifact of how the code grew from its initial form than an intentional decision. I see your point that making elapsed, total_elapsed, ckpt_agg_sync_time, and ckpt_longest_sync all the same type of integer that INSTR_TIME_GET_MICROSEC returns would reduce the potential for rounding abberations. I could do another rev of the patch tomorrow with that change if you'd prefer it that way. I don't have a strong opinion about that implementation detail; given that xlog.c is printing a less fine-grained time anyway (seconds with 3 digits vs. msec with 3 digits) it seems unlikely to run into a real problem here. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- 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_archivecleanup should remove WAL files also in pg_xlog?
On Mon, Dec 13, 2010 at 5:01 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: If the only consequence is that you get some extra WAL files in the archive, until pg_archivecleanup runs again, I think we can just live with it. We might get some extra WAL files also in pg_xlog. Because the WAL files which don't have .ready/.done file survive two checkpoints after failover. IOW, though first checkpoint right after failover should remove those WAL files, they cannot be removed since they don't have .done file. Then the first checkpoint creates .ready files for them, the archiver performs bulk-archiving (this would be harm in performance if there are many such WAL files), and the subsequent checkpoint removes them. But don't you have bigger problems when standby tries to archive a file that already exists in the archive, because master already archived it? We advise to write archive_command so that it fails if the file exists already. Yep, that's recommended in the document, but I don't want to use that. Because that might make new master fail to archive WAL file because of existence of half-baked file in the archive, after failover. This can happen if the master crashes while its archiver is copying WAL file. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] hstores in pl/python
Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Regards Pavel Stehule 2010/12/13 Jan Urbański wulc...@wulczer.org: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan -- 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 -- // Dmitriy.
Re: [HACKERS] proposal : cross-column stats
On 2010-12-13 03:28, Robert Haas wrote: Well, I'm not real familiar with contingency tables, but it seems like you could end up needing to store a huge amount of data to get any benefit out of it, in some cases. For example, in the United States, there are over 40,000 postal codes, and some even larger number of city names, and doesn't the number of entries go as O(m*n)? Now maybe this is useful enough anyway that we should Just Do It, but it'd be a lot cooler if we could find a way to give the planner a meaningful clue out of some more compact representation. A sparse matrix that holds only 'implicative' (P(A|B) P(A*B)?) combinations? Also, some information might be deduced from others. For Heikki's city/region example, for each city it would be known that it is 100% in one region. In that case it suffices to store only that information, since 0% in all other regions ca be deduced. I wouldn't be surprized if storing implicatures like this would reduce the size to O(n). regards, Yeb Havinga -- 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Tom Lane t...@sss.pgh.pa.us writes: The incremental FK checks are designed on the assumption that the constraint condition held before; they aren't likely to behave very sanely if the data is bad. I'd want to see a whole lot more analysis of the resulting behavior before even considering an idea like this. ALTER TABLE foo DISABLE TRIGGER ALL; I must have missed the point when PostgreSQL stoped providing this foot gun already, so that it's arguable less a surprise to spell the misfeature NOT ENFORCED rather than DISABLE TRIGGER. Seriously, real-world use cases such as Kevin's one seems to warrant that we are able to create a table withouth enforcing the FK. That's horrid, yes, that's needed, too. Maybe some operations would have to be instructed that the constraint ain't trustworthy but just declared to be so by the user? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal : cross-column stats
On 2010-12-13 03:28, Robert Haas wrote: Well, I'm not real familiar with contingency tables, but it seems like you could end up needing to store a huge amount of data to get any benefit out of it, in some cases. For example, in the United States, there are over 40,000 postal codes, and some even larger number of city names, and doesn't the number of entries go as O(m*n)? Now maybe this is useful enough anyway that we should Just Do It, but it'd be a lot cooler if we could find a way to give the planner a meaningful clue out of some more compact representation. A sparse matrix that holds only 'implicative' (P(A|B) P(A*B)?) combinations? Also, some information might be deduced from others. For Heikki's city/region example, for each city it would be known that it is 100% in one region. In that case it suffices to store only that information, since 0% in all other regions ca be deduced. I wouldn't be surprized if storing implicatures like this would reduce the size to O(n). OK, but I'll leave this for the future. My plan is to build a small PoC, just to see whether the contingency-table + probability-estimates approach works for the failure case mentioned by Heikki. I'l like to do this till the end of this week, if possible. I'll read the articles/mentioned by Nathan Boley (thanks for those links, if you have more of them just let me know). Once we have a solution that solves (or significantly improves) these failure cases, we can do further plans (how to do that ascually in the code etc.). BTW thanks for all the comments! 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
2010/12/13 Tom Lane t...@sss.pgh.pa.us: But allow me to harbor doubts that they really intend to allow someone to force a constraint to be considered valid without any verification. Table constraints are either enforced or not enforced. Domain constraints and assertions are always enforced., 4.17.2 I don't read that as meaning that such unenforced constraints are considered valid. It sounds as if unenforced constraints are the same as non-existing constraints (think: constraint templates), possibly as a means to remember that they should be re-enabled at some point. I.e., marking a constraint as unenforced and then as enforced again would be a shortcut for removing and re-adding the constraint, while having the advantage that one doesn't have to keep a list of constraint definitions that must be re-added. (In particular, can a constraint go from not-enforced to enforced state without getting checked at that time?) I assume not. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WaitLatch
Hi All, drainSelfPipe() function will read the data and remove it from the descriptor? Coz the signal (SIGUSR1) is writing sata to selfPipe descriptor but unable to wake up latch on select system call. Data is not avaialable at select in WaitLatch fun. Coz it is read by drailSelfPipe(). So How will the SIGUSR1 can wake up the latch(select in WaitLatch Fun)?? Plz Reply.. -- Thanks Regards, Aaliya Zarrin (+91)-9160665888
Re: [HACKERS] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On 13 December 2010 10:30, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Seriously, real-world use cases such as Kevin's one seems to warrant that we are able to create a table withouth enforcing the FK. That's horrid, yes, that's needed, too. Maybe some operations would have to be instructed that the constraint ain't trustworthy but just declared to be so by the user? Might I suggest that we call them aspirational foreign keys, while sticking with Simon's syntax? Reasons: 1. It's suggestive of the lack of certainty about the referential integrity of the underlying data - They're like a foreign key, but not quite as good. 2. It's indicative that they may one day become actual foreign keys through the use of something like the CHECK DATA utility. I'd favour doing this with a separate DDL command. 3. It's suggestive that they aren't just syntactic sugar or an expression of intent, as DB2's NOT ENFORCED FKs are, but rather that they behave like Oracle's NOT ENFORCED FKs. 4. It's memorable, I think. By the way, the DISABLE TRIGGER ALL method isn't equivalent to this. Apart from hackishly depending on an implementation detail, it isn't possible to prevent the big, up-front enforcement in the first place when the FK is declared, because DISABLE TRIGGER ALL only disables existing triggers. Perhaps, if and when this feature is implemented, it will also be possible to use some explicit mechanism to disable and re-enable an FK. However, that's secondary I think. -- 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] rest of works for security providers in v9.1
2010/12/12 KaiGai Kohei kai...@ak.jp.nec.com: I'd like to see opinions what facilities should be developed to the current v9.1 development cycle. It seems to me that the next commit after the label-switcher-function patch ought to be a contrib module that implements a basic form of SE-Linux driven permissions checking. I'm pretty unexcited about continuing to add additional facilities that could be used by a hypothetical module without actually seeing that module, and I think that the label-switcher-function patch is the last piece of core infrastructure that is a hard requirement rather than nice to have. I'd rather have a complete feature with limited capabilities than half a feature with really awesome capabilities. I suspect that getting fine-grained DDL permissions into PostgreSQL 9.1 is not going to happen. There is a significant amount of complexity there and we are getting short on time. It took us three CommitFests to work through the plan we discussed at PGCon, and this isn't so much simpler that I expect to be able to do it in one. Of course, how you want to spend your time is up to you, but count me as a strong vote for postponing this work to 9.2, when there will be ample time to give it the care and attention it needs. -- 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] Instrument checkpoint sync calls
On Mon, Dec 13, 2010 at 3:19 AM, Greg Smith g...@2ndquadrant.com wrote: Robert Haas wrote: I'm wondering why md.c is converting the results from an exact value to a floating point, only to have xlog.c turn around and convert back to an integer. I think it could just return milliseconds directly, or if you're worried about a checkpoint that takes more than 24 days to complete, seconds and microseconds. md.c is printing the value as a float, so I converted early to a double and then percolated it upward from there. More an artifact of how the code grew from its initial form than an intentional decision. I see your point that making elapsed, total_elapsed, ckpt_agg_sync_time, and ckpt_longest_sync all the same type of integer that INSTR_TIME_GET_MICROSEC returns would reduce the potential for rounding abberations. I could do another rev of the patch tomorrow with that change if you'd prefer it that way. I don't have a strong opinion about that implementation detail; given that xlog.c is printing a less fine-grained time anyway (seconds with 3 digits vs. msec with 3 digits) it seems unlikely to run into a real problem here. Yeah, it'd probably be OK anyway, but I think it doesn't really cost us anything to avoid the unnecessary conversion steps, so count me as a vote for doing it that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan and...@dunslane.net wrote: In fact it's possible now to disable FK enforcement, by disabling the triggers. It's definitely a footgun though. Just the other day I was asked how data violating the constraint could have got into the table, and caused some surprise by demonstrating how easy this was to produce. Ugh. I have read the entire pg manual and I did not recall that footgun. At least in MySQL disabling fk's is explicit. There is something to be said for being able to tell the database: Hey, hold my beer and watch this, it might be stupid but it is what we are going to do. I couldn't agree more, and that's a great way to put it. The user is in charge. Our job is to prevent the user from *accidentally* shooting themselves in the foot. But if a crocodile is biting their foot off and they want to fire their gun in that direction and take their chances, it's not our job to say oh, no, you might injure your foot. DBAs hate getting eaten by crocodiles. -- 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] GiST insert algorithm rewrite
On Mon, Dec 13, 2010 at 7:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Attached is an updated patch, but that issue with limited number of backup blocks needs to be resolved. The straightforward way would be to change the WAL format to increase the limit. Eh, is that going to bloat WAL? -- 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] GiST insert algorithm rewrite
On 13.12.2010 15:04, Robert Haas wrote: On Mon, Dec 13, 2010 at 7:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Attached is an updated patch, but that issue with limited number of backup blocks needs to be resolved. The straightforward way would be to change the WAL format to increase the limit. Eh, is that going to bloat WAL? Nah. The way split now works is that: 1. Split the page. Write a WAL record with the contents of the page halves. 2. Insert the downlink pointers in the parent, and set the NSN and clear F_FOLLOW_RIGHT flags on the child pages. A 2nd WAL record is written for this. In this new patch version, at step 2, the 2nd WAL record updates the LSNs and takes full-page-images of the child pages if necessary. Previous patches overlooked that. Usually a full page image won't be necessary, because we just wrote the page-split WAL record at step 1 for those pages. It's only if a checkpoint started between in the small window between steps 1 and 2. So this should have no effect on performance, but it is necessary for correctness. -- 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
Increasing max # of backup blocks (was Re: [HACKERS] GiST insert algorithm rewrite)
On 13.12.2010 15:20, Heikki Linnakangas wrote: On 13.12.2010 15:04, Robert Haas wrote: On Mon, Dec 13, 2010 at 7:09 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Attached is an updated patch, but that issue with limited number of backup blocks needs to be resolved. The straightforward way would be to change the WAL format to increase the limit. Eh, is that going to bloat WAL? Nah. Or did you mean, if changing the WAL format to raise the limit would bloat WAL? Depends on how it's done, of course. Assuming MAXALIGN=4, there's 2 wasted bytes in XLogRecord at the moment that we could take into use without making the header bigger. -- 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] rest of works for security providers in v9.1
(2010/12/13 21:53), Robert Haas wrote: 2010/12/12 KaiGai Koheikai...@ak.jp.nec.com: I'd like to see opinions what facilities should be developed to the current v9.1 development cycle. It seems to me that the next commit after the label-switcher-function patch ought to be a contrib module that implements a basic form of SE-Linux driven permissions checking. I'm pretty unexcited about continuing to add additional facilities that could be used by a hypothetical module without actually seeing that module, and I think that the label-switcher-function patch is the last piece of core infrastructure that is a hard requirement rather than nice to have. I'd rather have a complete feature with limited capabilities than half a feature with really awesome capabilities. It is a good news for me also, because I didn't imagine SE-PostgreSQL module getting upstreamed, even if contrib module. OK, I'll focus on the works to merge the starter-version of SE-PostgreSQL as a contrib module in the last commit fest. Probably, I need to provide its test cases and minimum documentations in addition to the code itself. Anything else? I suspect that getting fine-grained DDL permissions into PostgreSQL 9.1 is not going to happen. There is a significant amount of complexity there and we are getting short on time. It took us three CommitFests to work through the plan we discussed at PGCon, and this isn't so much simpler that I expect to be able to do it in one. Of course, how you want to spend your time is up to you, but count me as a strong vote for postponing this work to 9.2, when there will be ample time to give it the care and attention it needs. Yep, the label-switcher-function might be a good delimiter. I don't find out any disadvantages to postpone getting DDL permissions. I agree with these enhancements being pushed to v9.2 development. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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_execute_from_file, patch v10
On Sun, Dec 12, 2010 at 06:08, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: The other infrastructure patch that has been mark ready for commit then commented further upon by Tom is $subject, even if the function provided as been renamed to pg_execute_sql_file(). Please find attached the newer version that fixes Tom concerns, removing the VARIADIC forms of the functions (those placeholders idea). I think the version is almost OK, but I have a couple of comments: - Why do you need directory_fctx in genfile.h ? - It might be reasonable to have 3 and 1 arguments version of pg_read_file. i.e, (path, offset, size) and (path). Two args version (path, offset) doesn't seem to be so useful. In addition, CREATE EXTENSION will always call it with offset=0, no? - We don't need some of added #include utils/array.h anymore. -- 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] hstores in pl/python
2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. Pavel Regards Pavel Stehule 2010/12/13 Jan Urbański wulc...@wulczer.org: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan -- 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 -- // Dmitriy. -- 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_execute_from_file, patch v10
Itagaki Takahiro itagaki.takah...@gmail.com writes:
I think the version is almost OK, but I have a couple of comments:
- Why do you need directory_fctx in genfile.h ?
I then use it in extension.c, this way:
typedef struct extension_fctx
{
directory_fctx dir;
ExtensionList *installed;
} extension_fctx;
- It might be reasonable to have 3 and 1 arguments version of pg_read_file.
i.e, (path, offset, size) and (path). Two args version (path, offset)
doesn't seem to be so useful. In addition, CREATE EXTENSION will always
call it with offset=0, no?
Depending on the 'relocatable' property, we now do either of those calls:
execute_sql_file(get_extension_absolute_path(control-script),
pg_encoding_to_char(encoding));
read_text_file_with_endoding(filename,
pg_encoding_to_char(encoding));
So we're using the internal forms only here, and we can propose whatever
API we find best. Reading through the end of the file seems common
enough, but I agree I would prefer reading the whole file here if I had
to pick only one.
- We don't need some of added #include utils/array.h anymore.
Ah yes, true.
Do you want another patch version from me?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER EXTENSION ... UPGRADE;
On Sat, Dec 11, 2010 at 4:35 PM, David E. Wheeler da...@kineticode.com wrote: What about having the following keys supported in the control file: upgrade_version = 'script.version.sql' upgrade_all = 'script.sql' Why not just use an upgrade script naming convention? Think: Convention over configuration. Mainly, because of the situation where I have may versions that can all be upgraded from the same script. I'ld much rather distribution just 3 scripts (install + 2 upgrades), and a control file with something like this (pretend I'm on version 2.6) upgragde-1.0 = $EXT-upgrade-1.sql upgragde-1.1 = $EXT-upgrade-1.sql upgragde-1.1.1 = $EXT-upgrade-1.sql upgragde-1.1.2 = $EXT-upgrade-1.sql upgragde-1.2 = $EXT-upgrade-1.sql upgragde-1.3 = $EXT-upgrade-1.sql upgragde-1.4 = $EXT-upgrade-1.sql upgragde-1.4.1 = $EXT-upgrade-1.sql upgrade-2.0 = $EXT-upgrade-2.sql upgrade-2.1 = $EXT-upgrade-2.sql upgrade-2.2 = $EXT-upgrade-2.sql upgrade-2.2.1 = $EXT-upgrade-2.sql upgrade-2.3 = $EXT-upgrade-2.sql upgrade-2.4 = $EXT-upgrade-2.sql upgrade-2.5 = $EXT-upgrade-2.sql Forcing convention on me to maitain/install an upgrade script for every single version is way more than asking me to just specify an upgrade script for versions. Again, I'ld love for the version to support some sort of prefix or wildcard matching, so I could do: upgrade-1.* = $EXT-upgrade-1.sql upgrade-2.* = $EXT-upgrade-2.sql 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] Optimize PL/Perl function argument passing [PATCH]
On Dec 9, 2010, at 7:32 PM, Tim Bunce wrote:
On Wed, Dec 08, 2010 at 09:21:05AM -0800, David E. Wheeler wrote:
On Dec 8, 2010, at 9:14 AM, Tim Bunce wrote:
Do you have any more improvements in the pipeline?
I'd like to add $arrayref = decode_array_literal('{2,3}') and
maybe $hashref = decode_hstore_literal('x=1, y=2').
I don't know how much works would be involved in those though.
Those would be handy, but for arrays, at least, I'd rather have a GUC
to turn on so that arrays are passed to PL/perl functions as array
references.
Understood. At this stage I don't know what the issues are so I'm
nervous of over promising (plus I don't know how much time I'll have).
It's possible a blessed ref with string overloading would avoid
backwards compatibility issues.
I used to work on a patch that converts postgres arrays into perl array
references:
http://archives.postgresql.org/pgsql-hackers/2009-11/msg01552.php
I have a newer patch, which is, however, limited to one-dimensional resulting
arrays. If there's an interest in that approach I can update it for the
current code base, add support multi-dimensional arrays (I used to implement
that, but lost the changes accidentally) and post it for review.
/A
--
Alexey Klyukin http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc
--
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] hstores in pl/python
On Monday 13 December 2010 15:27:48 Pavel Stehule wrote: 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. So youre disturbed by the name? Andres -- 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] ALTER EXTENSION ... UPGRADE;
Aidan Van Dyk ai...@highrise.ca writes: Mainly, because of the situation where I have may versions that can all be upgraded from the same script. I'ld much rather distribution just 3 scripts (install + 2 upgrades), and a control file with something like this (pretend I'm on version 2.6) upgragde-1.0 = $EXT-upgrade-1.sql [...] upgrade-2.5 = $EXT-upgrade-2.sql Thanks for the example. Again, I'ld love for the version to support some sort of prefix or wildcard matching, so I could do: upgrade-1.* = $EXT-upgrade-1.sql upgrade-2.* = $EXT-upgrade-2.sql Problem is: what to do if a single upgrade matches more than one line? The only safe answer is to error out and refuse to upgrade but that ain't nice to the user. How much is that a problem here? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimize PL/Perl function argument passing [PATCH]
On 12/13/2010 09:42 AM, Alexey Klyukin wrote: I used to work on a patch that converts postgres arrays into perl array references: http://archives.postgresql.org/pgsql-hackers/2009-11/msg01552.php I have a newer patch, which is, however, limited to one-dimensional resulting arrays. If there's an interest in that approach I can update it for the current code base, add support multi-dimensional arrays (I used to implement that, but lost the changes accidentally) and post it for review. Yes please. I had forgotten that you'd done that, so I duplicated some of your work yesterday, but it sounds like you have (or had) the guts of what I am still missing. 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] hstores in pl/python
Interesting argument. I can ask, how many people knows what is tsvector or tsquery ? Or how many people knows what is polygon or path ? The answer is: everyone who need or using it. Hstore is a proven and well designed solution. And in fact I am surprising why it does not in core yet?! 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. Pavel Regards Pavel Stehule 2010/12/13 Jan Urbański wulc...@wulczer.org: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan -- 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 -- // Dmitriy. -- // Dmitriy.
Re: [HACKERS] hstores in pl/python
2010/12/13 Andres Freund and...@anarazel.de: On Monday 13 December 2010 15:27:48 Pavel Stehule wrote: 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. So youre disturbed by the name? name and interface - hstore is designed as external module - a internal class can be designed different. Pavel Andres -- 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_archivecleanup should remove WAL files also in pg_xlog?
On Mon, Dec 13, 2010 at 6:02 PM, Fujii Masao masao.fu...@gmail.com wrote: Yep, that's recommended in the document, but I don't want to use that. Because that might make new master fail to archive WAL file because of existence of half-baked file in the archive, after failover. This can happen if the master crashes while its archiver is copying WAL file. This occurred to me that archive_command should check the size of the existing archived WAL file, and overwrite it with new file when the size is not equal to 16MB. Of course, if backup history file or timeline history file is given, it should not take account of the file size. I implemented pg_archivecopy module to do the above. git://git.postgresql.org/git/users/fujii/postgres.git branch: pg_archivecopy If it's worth, I'll release it in pgFoundry or elsewhere. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] hstores in pl/python
On Monday 13 December 2010 16:01:35 Pavel Stehule wrote: 2010/12/13 Andres Freund and...@anarazel.de: On Monday 13 December 2010 15:27:48 Pavel Stehule wrote: 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. So youre disturbed by the name? name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. Andres -- 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] hstores in pl/python
2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Interesting argument. I can ask, how many people knows what is tsvector or tsquery ? Or how many people knows what is polygon or path ? TSearch isn't good example. There are not a common interface for fulltext. The answer is: everyone who need or using it. Hstore is a proven and well designed solution. And in fact I am surprising why it does not in core yet?! Hstore is designed as external module. I am think, so when we can modify parser when some functionality is internal, then a implementation can be more effective and without some surprising for user. Pavel 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: Hey Pavel, 2010/12/13 Pavel Stehule pavel.steh...@gmail.com Hello this is little bit offtopic, sorry. I am thinking, so we need a standard associative array support in core - like Perl, Python or Javascript. So, I don't think, so migration of hstore to core is good idea. Could you tell why in-core associative array support would be better than in-core hstore support ? We enjoying to use hstore and thinking that it is implemented great. Because hstore is PostgreSQL specific type. More well known syntax is better. More - who know, what is hstore? But everybody know, what is associative array or hash. Pavel Regards Pavel Stehule 2010/12/13 Jan Urbański wulc...@wulczer.org: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan -- 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 -- // Dmitriy. -- // Dmitriy. -- 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] GiST insert algorithm rewrite
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: But that creates a new problem: There's a maximum of three backup blocks per WAL record, but a GiST page can be split into any number of child pages as one operation. You might run out of backup block slots. Attached is an updated patch, but that issue with limited number of backup blocks needs to be resolved. The straightforward way would be to change the WAL format to increase the limit. I don't think you can fix it that way. If a page can be split into any number of child pages, then no fixed number of pages in a WAL record will be enough. Even if you were willing to suppose that ~16 would be enough, it would be a bad idea because of the extra overhead added into XLogInsert, which'd be paid by *every* WAL insert operation. I think you need to refactor the operation so that there's one WAL record per child page, or something along that line. I concede this might be diffcult :-( 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] ALTER EXTENSION ... UPGRADE;
On Mon, Dec 13, 2010 at 9:55 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Again, I'ld love for the version to support some sort of prefix or wildcard matching, so I could do: upgrade-1.* = $EXT-upgrade-1.sql upgrade-2.* = $EXT-upgrade-2.sql Problem is: what to do if a single upgrade matches more than one line? The only safe answer is to error out and refuse to upgrade but that ain't nice to the user. How much is that a problem here? To get a wildcard match (or a prefix match) for version upgrades, I'ld be willing to have that error if I give a bad set of version matches. If only have those 2 lines to manage, it's a lot more likely I won't mess them up than if I have to manage 30 almost identical lines and not miss/duplicate a version. ;-) -- 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] hstores in pl/python
name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal : cross-column stats
Tomas Vondra t...@fuzzy.cz writes: Well, until this point we've discussed failure cases involving 'AND' conditions. What about 'OR' conditions? I think the current optimizer computes the selectivity as 's1+s2 - s1*s2' (at least that's what I found in backend/optimizer/path/clausesel.c:630). If you can solve the AND case, the OR case falls out of that. Just replace s1*s2 with a more accurate AND calculation. 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] initdb failure with Postgres 8.4.4
On Fri, Dec 10, 2010 at 10:54 AM, Andrew Dunstan and...@dunslane.net wrote: Here's my understanding. It's not initdb that's really complaining. The timezone files are not inputs to initdb. It's the postgres that initdb invokes that's complaining. Postges will look for the share file in two places: the configured install directory or a share directory whose path is calculated relative to its own location .initdb's -L flag doesn't override that, it only overrides where initdb itself looks for files (such as the BKI file). The bottom line I think is that if you intend to use a non-standard layout you need to specify the paths for everything and then not move them after installation. If you want the installation to be movable, just specify --prefix, but then if you move it you have to move the whole thing together. You can't just relocate one directory and expect it to work. It won't. I'm not sure if our current approach would work with v8.4. This is what we do in the nutshell: - build Postgres - do not run install - collect all generated libraries, executables and input files and pack them along with other app - distribute the tar-ball to the customer - untar and install the app the installation script at some point calls initdb, create database, createlang, create user, it creates config files... done. I guess this eliminates option 1 - using the configured install directory I tried to figure out the second option share directory whose path is calculated relative to its own location and ran initdb with strace. Chances are I overlooked something, but I only saw Postgres trying to open the timezone files in the original build path. Do you see some way to support remote deployment with the approach I described? Thanks, Michael. This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- 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
Robert Haas robertmh...@gmail.com writes: On Sun, Dec 12, 2010 at 9:16 PM, Tomas Vondra t...@fuzzy.cz wrote: The proposed solution is based on contingency tables, built for selected groups of columns (not for each possible group). And the contingency table gives you the ability to estimate the probabilities needed to compute the selectivity. Or am I missing something? Well, I'm not real familiar with contingency tables, but it seems like you could end up needing to store a huge amount of data to get any benefit out of it, in some cases. The reason that this wasn't done years ago is precisely that nobody's figured out how to do it with a tolerable amount of stats data and a tolerable amount of processing time (both at ANALYZE time and during query planning). It's not hard to see what we'd ideally like to do; it's getting from there to something useful in production that's hard. 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Florian Pflug f...@phlo.org writes: On Dec13, 2010, at 00:16 , Robert Haas wrote: And in fact it strikes me that we might not have much choice about how to fix this. I think we are not going to retroactively change the behavior of ALTER DATABASE .. SET ROLE in a released version, but yet we do, I think, want to make pg_upgrade work. A simple fix is to teach pg_upgrade to issue RESET SESSION AUTHORIZATION immediately after connecting to a database. How is that a fix? RESET is defined to reset to the state you get immediately after connection. Including anything established by those ALTER commands. 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Mon, 2010-12-13 at 11:54 +, Peter Geoghegan wrote:
On 13 December 2010 10:30, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
Seriously, real-world use cases such as Kevin's one seems to warrant
that we are able to create a table withouth enforcing the FK. That's
horrid, yes, that's needed, too. Maybe some operations would have to be
instructed that the constraint ain't trustworthy but just declared to be
so by the user?
Might I suggest that we call them aspirational foreign keys, while
sticking with Simon's syntax?
Just checking what we are saying:
1.
(a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
will add a FK but NOT run the check - we mark it as check pending.
Lock held: ShareRowExclusiveLock
(b) Every new change to the table has the FK enforced - the triggers are
fully enabled and active. (That could mean we update a row and have the
update fail because of a FK violation.)
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
Note that 1 2 together are the equivalent of ADD FK CONCURRENTLY,
except that step 2 more usefully tells you which rows fail.
--
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] ALTER EXTENSION ... UPGRADE;
Aidan Van Dyk ai...@highrise.ca writes: On Sat, Dec 11, 2010 at 4:35 PM, David E. Wheeler da...@kineticode.com wrote: Why not just use an upgrade script naming convention? Mainly, because of the situation where I have may versions that can all be upgraded from the same script. I'ld much rather distribution just 3 scripts (install + 2 upgrades), and a control file with something like this (pretend I'm on version 2.6) upgragde-1.0 = $EXT-upgrade-1.sql upgragde-1.1 = $EXT-upgrade-1.sql upgragde-1.1.1 = $EXT-upgrade-1.sql upgragde-1.1.2 = $EXT-upgrade-1.sql upgragde-1.2 = $EXT-upgrade-1.sql upgragde-1.3 = $EXT-upgrade-1.sql upgragde-1.4 = $EXT-upgrade-1.sql upgragde-1.4.1 = $EXT-upgrade-1.sql upgrade-2.0 = $EXT-upgrade-2.sql upgrade-2.1 = $EXT-upgrade-2.sql upgrade-2.2 = $EXT-upgrade-2.sql upgrade-2.2.1 = $EXT-upgrade-2.sql upgrade-2.3 = $EXT-upgrade-2.sql upgrade-2.4 = $EXT-upgrade-2.sql upgrade-2.5 = $EXT-upgrade-2.sql I see no advantage of this over a script per version combination, so long as you allow scripts to \include each other. 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] initdb failure with Postgres 8.4.4
Excerpts from BRUSSER Michael's message of lun dic 13 12:34:49 -0300 2010: I'm not sure if our current approach would work with v8.4. This is what we do in the nutshell: - build Postgres - do not run install - collect all generated libraries, executables and input files and pack them along with other app - distribute the tar-ball to the customer - untar and install the app the installation script at some point calls initdb, create database, createlang, create user, it creates config files... done. I think this procedure needs to be fixed. It should be: - build Postgres - install - collect the files, pack - distribute - unpack to the destination directory Since our packaging is (supposed to be) relocatable, you can install in the customer's machine at a different destination directory, and it should work. If it doesn't, report it as a bug. -- Á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] hstores in pl/python
Pavel Stehule pavel.steh...@gmail.com writes: Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. We don't normally invent specialized syntax for a specific datatype. Not even if it's in core. 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] initdb failure with Postgres 8.4.4
BRUSSER Michael michael.brus...@3ds.com writes: I'm not sure if our current approach would work with v8.4. This is what we do in the nutshell: - build Postgres - do not run install - collect all generated libraries, executables and input files and pack them along with other app - distribute the tar-ball to the customer - untar and install the app the installation script at some point calls initdb, create database, createlang, create user, it creates config files... done. If that ever worked, with any version of Postgres, I'd be astonished. Here is what you should do: 1. Build Postgres with --prefix set to some empty directory. 2. Run install. 3. Bundle up the resulting install tree as part of your tarball. Do not editorialize upon the relative locations of its contents. 4. Drop the install tree wherever you want on the target machine. 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] SQL/MED - file_fdw
On 12/13/2010 01:31 AM, Itagaki Takahiro wrote: On Sat, Dec 11, 2010 at 05:30, Andrew Dunstanand...@dunslane.net wrote: On 12/04/2010 11:11 PM, Itagaki Takahiro wrote: One exports the copy functions from the core, and another implements file_fdw using the infrastructure. Who is actually going to do this split? I'm working for it :-) I extract those functions from copy.c: - CopyState BeginCopyFrom(Relation rel, const char *filename, List *attnamelist, List *options); - void EndCopyFrom(CopyState cstate); - bool NextCopyFrom(CopyState cstate, Datum *values, bool *nulls, Oid *oid); There was Reset() in file_fdw, but it is not contained in the patch. It will be added again if required, but I wonder we might need not only reset but also mark/restore a position in a file. Hmm. I don't think that's going to expose enough for what I want to be able to do. I actually had in mind exposing lower level routines like CopyReadAttibutesCSV/CopyReadAttributesText and allowing the Foreign Data Wrapper to manipulate the raw values read (for example from an irregularly shaped CSV file). 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] rest of works for security providers in v9.1
On Mon, Dec 13, 2010 at 8:32 AM, KaiGai Kohei kai...@kaigai.gr.jp wrote: (2010/12/13 21:53), Robert Haas wrote: 2010/12/12 KaiGai Koheikai...@ak.jp.nec.com: I'd like to see opinions what facilities should be developed to the current v9.1 development cycle. It seems to me that the next commit after the label-switcher-function patch ought to be a contrib module that implements a basic form of SE-Linux driven permissions checking. I'm pretty unexcited about continuing to add additional facilities that could be used by a hypothetical module without actually seeing that module, and I think that the label-switcher-function patch is the last piece of core infrastructure that is a hard requirement rather than nice to have. I'd rather have a complete feature with limited capabilities than half a feature with really awesome capabilities. It is a good news for me also, because I didn't imagine SE-PostgreSQL module getting upstreamed, even if contrib module. OK, I'll focus on the works to merge the starter-version of SE-PostgreSQL as a contrib module in the last commit fest. Probably, I need to provide its test cases and minimum documentations in addition to the code itself. Anything else? Extremely detailed instructions on how to test 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] Complier warnings on mingw gcc 4.5.0
On 11/01/2010 10:10 PM, Itagaki Takahiro wrote: On Tue, Nov 2, 2010 at 8:43 AM, Hiroshi Inouein...@tpf.co.jp wrote: The problem which was fixed by your old patch is at runtime not at compilation time. Is it fixed with gcc 4.5? Now it works as far as simple test, including core functions and dynamic modules. So, I think the fix for dllexport is safe enough for mingw gcc 4.5. BTW, with out without the above fix, regression test failed with weird error if the server is built with gcc 4.5. However, server can run if I started it manually with PGPORT or -o --port=X. We might need another fix for the issue. LOG: database system was shut down at 2010-11-02 10:32:13 JST LOG: database system is ready to accept connections LOG: autovacuum launcher started FATAL: parameter port cannot be changed without restarting the server (repeated) I have just run into this when trying to set up a new buildfarm member running Mingw. Is there a fix? Is anyone working on it? Or do I just need to downgrade the compiler? 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] hstores in pl/python
My most serious pro about hstore in core is a better dump/restore support. Also, since we have so much better hstore and people started to use it in their projects, it'd be great to have built-in feature in PostgreSQL, which mimic key-value or document-oriented database. Oleg On Mon, 13 Dec 2010, Jan Urbaski wrote: It would be cool to be able to transparently use hstores as Python dictionaries and vice versa. It would be easy enough with hstore as a core type, but with hstore as an addon it's not that easy. There was talk about including hstore in core, is there still chance for that to happen in 9.1? I'd like to include hstore-dict handling, but with hstore out-of-core the only half-sane way I see is: * hack PL/Python's makefile to add -Icontrib/hstore (yuck!) * create an extension module for Python that knows how to handle hstores that would live next to plpython.so * install it in $libdir on make install * when PL/Python receives or is asked to create an hstore, load the extension module and use it to parse the value (ugly, probably slow) * the module would also have to make sure hstore.so is loaded in the database, which in itself is not pretty, as it would refer to hstore_in/out symbols I wrote a module that can be used with current PL/Python to simplify hstore handling (https://github.com/wulczer/pyhstore), but it suffers from most of the aforementioned problems, and on top of that you get hstore-text-dict instead of just hstore-dict, which sucks. Cheers, Jan Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] ALTER EXTENSION ... UPGRADE;
Excerpts from Tom Lane's message of lun dic 13 12:50:43 -0300 2010: I see no advantage of this over a script per version combination, so long as you allow scripts to \include each other. Hmm, are the upgrade scripts going to be run via SPI? -- Á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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs si...@2ndquadrant.com wrote:
(a) ALTER TABLE ... ADD FOREIGN KEY ... NOT VALIDATED INITIALLY;
will add a FK but NOT run the check - we mark it as check pending.
Lock held: ShareRowExclusiveLock
Seems about right. Not sure whether the lock strength is correct.
(b) Every new change to the table has the FK enforced - the triggers are
fully enabled and active. (That could mean we update a row and have the
update fail because of a FK violation.)
Also seems about right.
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
I'm less sure about this part. I think there should be a DDL
statement to validate the foreign key. The return the problem rows
behavior could be done some other way, or just left to the user to
write their own query.
--
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] hstores in pl/python
There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. But, Pavel, I feel you idea. You want to make the syntax clear in particular... or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- // Dmitriy.
Re: [HACKERS] SQL/MED - file_fdw
Andrew Dunstan and...@dunslane.net writes: Hmm. I don't think that's going to expose enough for what I want to be able to do. I actually had in mind exposing lower level routines like CopyReadAttibutesCSV/CopyReadAttributesText and allowing the Foreign Data Wrapper to manipulate the raw values read (for example from an irregularly shaped CSV file). I think that exposing the guts of COPY to the open air is a bad idea. We refactor that code for performance or other reasons every release or two. I don't want to find us tied down to the current implementation because we're afraid of breaking third-party FDWs. 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] ALTER EXTENSION ... UPGRADE;
Tom Lane t...@sss.pgh.pa.us writes:
I see no advantage of this over a script per version combination, so
long as you allow scripts to \include each other.
I guess the following should do:
SELECT pg_execute_sql_file('upgrade-1.sql');
But I rather prefer the 2-liner control file, myself:
upgrade-1.* = 'upgrade-1.sql'
upgrade-2.* = 'upgrade-2.sql'
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Dec13, 2010, at 16:40 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Dec13, 2010, at 00:16 , Robert Haas wrote: And in fact it strikes me that we might not have much choice about how to fix this. I think we are not going to retroactively change the behavior of ALTER DATABASE .. SET ROLE in a released version, but yet we do, I think, want to make pg_upgrade work. A simple fix is to teach pg_upgrade to issue RESET SESSION AUTHORIZATION immediately after connecting to a database. How is that a fix? RESET is defined to reset to the state you get immediately after connection. Including anything established by those ALTER commands. I thought so too until yesterday when I tried it (the database db has ROLE set to db). f...@master:~$ psql db psql (9.0.1) db= select session_user, current_user; session_user | current_user --+ fgp | db (1 row) db= reset session authorization; RESET db= select session_user, current_user; session_user | current_user --+-- fgp | fgp (1 row) The manual agrees with this behaviour, it states The session user identifier is initially set to be the (possibly authenticated) user name provided by the client. and then goes on to explain The current user identifier is normally equal to the session user identifier, but might change temporarily in the context of SECURITY DEFINER functions and similar mechanisms; it can also be changed by SET ROLE. 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 - file_fdw
On 12/13/2010 11:12 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: Hmm. I don't think that's going to expose enough for what I want to be able to do. I actually had in mind exposing lower level routines like CopyReadAttibutesCSV/CopyReadAttributesText and allowing the Foreign Data Wrapper to manipulate the raw values read (for example from an irregularly shaped CSV file). I think that exposing the guts of COPY to the open air is a bad idea. We refactor that code for performance or other reasons every release or two. I don't want to find us tied down to the current implementation because we're afraid of breaking third-party FDWs. In that case I guess I'll need to do what Shigeru-san has done, and copy large parts of copy.c. 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] SQL/MED - core functionality
2010/12/14 Shigeru HANADA han...@metrosystems.co.jp: On Thu, 25 Nov 2010 15:03:29 +0200 Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I propose the attached API instead. This has a clear separation between plan and execution. I'm sure we'll have to extend the API in the future FDWs want tighter integration, but I think this is a good start. It makes it quite straightforward to write simple FDW like the file FDW, without having to know anything about the executor or planner internals, but provides enough flexibility to cover the functionality in your PostgreSQL FDW. Thanks for the comments and the proposal. I've revised fdw_core patch along your proposal and subsequent discussion, and tried to fix FDW patches pgsql_fdw and file_fdw according to fdw_core. Reviewing the patch a little, it occurred to me that it might be a good idea to split the patch into two pieces again. One is for adding CREATE FOREIGN TABLE syntax and actual creation and the other is for FDW APIs. ISTM syntax and and utility processing of FOREIGN TABLE has been stable in the latest patches, and the discussion should be concentrated on APIs. APIs change don't harm SQL interfaces. Of course CREATE FOREIGN TABLE is not useful as alone, but it would be helpful to be reviewed easily. Regards, -- Hitoshi Harada -- 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
2010/12/13 Shigeru HANADA han...@metrosystems.co.jp: On Sun, 12 Dec 2010 23:47:53 +0200 Peter Eisentraut pete...@gmx.net wrote: On ons, 2010-12-01 at 12:30 +0900, Hitoshi Harada wrote: From a user's view, this is very long way to see a simplest foreign table. I know it is based on the standard, but I really want a shortcut. Especially, I don't understand why CREATE USER MAPPING FOR current_user SERVER server is needed for default use case. If you forget CREATE USER MAPPING and do CREATE FOREIGN TABLE, it raises an error. User mapping is useful if the local user and remote user should be mapped but I imagine in most cases they are the same. postgresql_fdw can tell the remote user by conninfo string, in addition. I reviewed the standard about this, and a lot of things are implementation-defined. I think user mappings could be made optional. Simple FDWs such as File FDW might not have concept of user on remote side. In such case, it would be enough to control access privilege per local user with GRANT/REVOKE SELECT statement. Yeah, with file_fdw users won't understand why CREATE USER MAPPING is needed. This is another topic, but it would be useful if CREATE FOREIGN TABLE can omit column definitions since fdw usually knows what should be there in the definitions. I some times mistyped the column names between remote and local and resulted in fail on execution. Also, according to the standard, the column list in CREATE FOREIGN TABLE is optional (if you can get it in some automatic way, of course). To allow omitting column definitions for that purpose, a way to create ero-column tables would have to be provided. New syntax which allows FDWs to determine column definition would be necessary. ex) -- Create foo from the remote table foo on the server bar CREATE FOREIGN TABLE foo SERVER bar; -- Create zero-column table foo CREATE FOREIGN TABLE foo () SERVER bar; To support this feature, another hook function need to be added to FDW API. ISTM that this feature should be considered with IMPORT SCHEMA statement. Hmm, the benefit from it does not seem so much as the paid cost. Definition of minimum APIs sound like the first step to get it in the next release. We could add it later. Regards, -- Hitoshi Harada -- 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: Use symbolic names not octal constants for file permission flags
On 12/13/2010 12:54 AM, Glen Knowles wrote: On Sun, Dec 12, 2010 at 8:16 AM, Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.us wrote: What are the values of _S_IREAD and _S_IWRITE, anyway? I'm still wondering how come the previous coding with hardwired constants behaved correctly. Still curious about this. FWIW, _S_IREAD and _S_IWRITE are defined by Visual Studio C++ 2008 in sys/stat.h as 0x0100 and 0x0080 respectively. Mingw values are the same. 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] Complier warnings on mingw gcc 4.5.0
Andrew Dunstan and...@dunslane.net writes: On 11/01/2010 10:10 PM, Itagaki Takahiro wrote: BTW, with out without the above fix, regression test failed with weird error if the server is built with gcc 4.5. However, server can run if I started it manually with PGPORT or -o --port=X. We might need another fix for the issue. LOG: database system was shut down at 2010-11-02 10:32:13 JST LOG: database system is ready to accept connections LOG: autovacuum launcher started FATAL: parameter port cannot be changed without restarting the server (repeated) I have just run into this when trying to set up a new buildfarm member running Mingw. Is there a fix? Is anyone working on it? Or do I just need to downgrade the compiler? It smells a little bit like an optimization bug. Does dialing down to -O0 make it go away? 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] hstores in pl/python
2010/12/13 Dmitriy Igrishin dmit...@gmail.com: There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? yes 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. It's question. Minimally it can be a any known (defined) type - composite type too. It would be nice if we can store data in native format with constraints. Now Hstore can store only text - (note: It's terrible hard to write this as external module, so Hstore does maximum what is possible). But, Pavel, I feel you idea. You want to make the syntax clear in particular... I like a possibility to define own types in pg. But sometimes, and associative arrays is it, created interface is too specific - like Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore respects it in design. So when we could to move hstore functionality to core, we can extend a parser, and we can create some general usable API. It can be big plus for stored procedures programming. This is just my opinion - when Hstore will be in core, then we will not have a native associative array ever, so from my perspective is better Hstore as contrib module. Regards Pavel Stehule or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- // Dmitriy. -- 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On 13 December 2010 16:08, Robert Haas robertmh...@gmail.com wrote:
On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs si...@2ndquadrant.com wrote:
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
I'm less sure about this part. I think there should be a DDL
statement to validate the foreign key. The return the problem rows
behavior could be done some other way, or just left to the user to
write their own query.
+1. I think that a DDL statement is more appropriate, because it makes
the process sort of symmetrical.
Perhaps we could error on the first FK violation found, and give a
value 'foo' not present in table bar. It ought to not matter that
there could be a lot of violations, because they will be exceptional
if you're using the feature as intended - presumably, you're going to
want to comb through the data to find out exactly what has gone wrong
for each violation. On the off chance that it actually is a problem,
the user can go ahead and write their own query, like Robert
suggested.
--
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] initdb failure with Postgres 8.4.4
Here is what you should do: 1. Build Postgres with --prefix set to some empty directory. 2. Run install. 3. Bundle up the resulting install tree as part of your tarball. Do not editorialize upon the relative locations of its contents. 4. Drop the install tree wherever you want on the target machine. - Would it be a completely crazy idea if I try to modify Postgres to look at some env. var (similar to PGDATA) and if exists and path is valid look there for the timezone files? Michael This email and any attachments are intended solely for the use of the individual or entity to whom it is addressed and may be confidential and/or privileged. If you are not one of the named recipients or have received this email in error, (i) you should not read, disclose, or copy it, (ii) please notify sender of your receipt by reply email and delete this email and all attachments, (iii) Dassault Systemes does not accept or assume any liability or responsibility for any use of or reliance on this email.For other languages, go to http://www.3ds.com/terms/email-disclaimer. -- 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] GiST insert algorithm rewrite
On Mon, Dec 13, 2010 at 3:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think you need to refactor the operation so that there's one WAL record per child page, or something along that line. I concede this might be diffcult :-( If it's only the backup blocks that matter couldn't you generate noop WAL records with just the full page image in them. Once all those are generated then generate the actual split operation and since all the pages have been written to wal since the last checkpoint they won't need any backup block slots. This would require surpressing any checkpoints between writing the first backup block and the final operation record. That might be pretty hard to do cleanly. -- greg -- 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] initdb failure with Postgres 8.4.4
On 12/13/2010 12:16 PM, BRUSSER Michael wrote: Here is what you should do: 1. Build Postgres with --prefix set to some empty directory. 2. Run install. 3. Bundle up the resulting install tree as part of your tarball. Do not editorialize upon the relative locations of its contents. 4. Drop the install tree wherever you want on the target machine. - Would it be a completely crazy idea if I try to modify Postgres to look at some env. var (similar to PGDATA) and if exists and path is valid look there for the timezone files? Yes, that's only the first problem you have encountered. I predict there will be others if you go down this path. You are making life much harder for yourself than you need to. Just keep everything together and you'll be fine. If that's really impossible, use symlinks (or junction points on Windows) to achieve the same effect. 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Mon, Dec 13, 2010 at 05:15:29PM +, Peter Geoghegan wrote:
On 13 December 2010 16:08, Robert Haas robertmh...@gmail.com wrote:
On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs si...@2ndquadrant.com wrote:
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
I'm less sure about this part. I think there should be a DDL
statement to validate the foreign key. The return the problem rows
behavior could be done some other way, or just left to the user to
write their own query.
+1. I think that a DDL statement is more appropriate, because it makes
the process sort of symmetrical.
Perhaps we could error on the first FK violation found, and give a
value 'foo' not present in table bar. It ought to not matter that
there could be a lot of violations, because they will be exceptional
if you're using the feature as intended - presumably, you're going to
want to comb through the data to find out exactly what has gone wrong
for each violation. On the off chance that it actually is a problem,
the user can go ahead and write their own query, like Robert
suggested.
Perhaps the errhint could suggest a query. All the information needed
for it would be available :)
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] hstores in pl/python
2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? yes 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. It's question. Minimally it can be a any known (defined) type - composite type too. It would be nice if we can store data in native format with constraints. Now Hstore can store only text - (note: It's terrible hard to write this as external module, so Hstore does maximum what is possible). But, Pavel, I feel you idea. You want to make the syntax clear in particular... I like a possibility to define own types in pg. But sometimes, and associative arrays is it, created interface is too specific - like Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore respects it in design. So when we could to move hstore functionality to core, we can extend a parser, and we can create some general usable API. It can be big plus for stored procedures programming. This is just my opinion - when Hstore will be in core, then we will not have a native associative array ever, so from my perspective is better Hstore as contrib module. In my opinion, hstore is defined and implemented well. Its complete in most cases. Therefore hstore is mature enough to be in core. On the other hand associative arrays should be implemented from scratch. Very well. Let it be. But how integration hstore in core today can interfere with implementation of support for associative arrays in future ? Is it will a big problem ? Regards Pavel Stehule or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- // Dmitriy. -- // Dmitriy.
Re: [HACKERS] unlogged tables
On Fri, Dec 10, 2010 at 11:16 PM, Robert Haas robertmh...@gmail.com wrote: I think the first patch (relpersistence-v4.patch) is ready to commit, So I've now committed it. and the third patch to allow synchronous commits to become asynchronous when it doesn't matter (relax-sync-commit-v1.patch) Jeff Janes reviewed this, which was good, but he missed a key bit on which I've now set him straight. So an updated review of this would be much appreciated. doesn't seem to be changing much either, although I would appreciate it if someone with more expertise than I have with our write-ahead logging system would give it a quick once-over. The main patch (unlogged-tables-v4.patch) needs more thought. Right now, unlogged buffers are checkpointed, which I want to get rid of. Andres Freund suggested we could get by with this and still survive a clean shutdown if we fsync() every unlogged relation in the cluster before shutting down, but I'm concerned about the case where one of the fsync() calls fails. That's presumably already a problem with checkpoints generally, and I haven't traced through the logic to see exactly what happens, but I guess this would need similar treatment. In a non-shutdown checkpoint, the checkpoint can just fail. In a shutdown checkpoint, we presumably can't just refuse to exit, but it shouldn't look like a clean shutdown... Any input on this point? -- 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] CommitFest wrap-up
We're now just a day or two from the end of this CommitFest and there are still a LOT of open patches - to be specific, 23.Here's a brief synopsis of where we are with the others, all IMO of course. - fix for seg picksplit function - I don't have confidence this change is for the best and can't take responsibility for it. It needs review by a committer who understands this stuff better than me and can determine whether or not the change is really an improvement. - unlogged tables - This is not going to get fully resolved in the next two days. I'll move it to the next CF and keep plugging away at it. - instrument checkpoint sync calls - I plan to commit this in the next 48 hours. (Hopefully Greg Smith will do the cleanup I suggested, if not I'll do it.) - explain analyze getrusage tracking - It seems clear to mark this as Returned with Feedback. - synchronous replication - and... - synchronous replication, transaction-controlled - If we want to get this feature into 9.1, we had better get a move on. But I don't currently have it in my time budget to deal with this. - serializable lock consistency - I am fairly certain this needs rebasing. I don't have time to deal with it right away. That sucks, because I think this is a really important change. - MERGE command - Returned with Feedback? Not sure where we stand with this. - Add primary key using an existing index - Returned with Feedback unless a committer is available immediately to pick this up and finish it off. - SQL/MED - core functionality - Seems clear to move this to the next CF and keep working on it. - Idle in transaction cancellation V3 - I think this is waiting on further review. Can anyone work on this one RSN? - Writeable CTEs - I think we need Tom to pick this one up. - Per-column collation - Bump to next CF, unless Peter is planning to commit imminently. - Tab completion in psql for triggers on views - Added to CF late, suggest we bump it to the next CF where it will have a leg up by virtue of already being marked Ready for Committer. - SQL/MED - file_fdw - Discussion is ongoing, but I see no harm in marking this Returned with Feedback for now in anticipation of a new version before CF 2011-01. - SQL/MED - postgresql_fdw - Hasn't received as much review, I think, so should probably be moved to next CF as-is. - Label switcher function (trusted procedure) - I plan to commit this with whatever changes are needed within the next 48 hours. - Extensions - Still under active discussion, suggested we move to next CF. - Fix snapshot taking inconsistencies - Ready for committer. Can any committer pick this up? - Crash dump handler for Windows. Magnus? - Directory archive format for pg_dump. Heikki? - WIP patch for parallel dump. Returned with Feedback? All in all it's disappointing to have so many major patches outstanding at this point in the CommitFest, but I think we're just going to have to make the best of 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] GiST insert algorithm rewrite
On 13.12.2010 19:19, Greg Stark wrote: On Mon, Dec 13, 2010 at 3:14 PM, Tom Lanet...@sss.pgh.pa.us wrote: I think you need to refactor the operation so that there's one WAL record per child page, or something along that line. I concede this might be diffcult :-( If it's only the backup blocks that matter couldn't you generate noop WAL records with just the full page image in them. Once all those are generated then generate the actual split operation and since all the pages have been written to wal since the last checkpoint they won't need any backup block slots. This would require surpressing any checkpoints between writing the first backup block and the final operation record. That might be pretty hard to do cleanly. That would work, but it brings us back to square one (http://archives.postgresql.org/message-id/4ccfee61.2090...@enterprisedb.com). It's not necessarily a bad idea, A capability to hold off checkpoints might be the easiest way to do this, and other things in the future. -- 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] GiST insert algorithm rewrite
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 13.12.2010 19:19, Greg Stark wrote: If it's only the backup blocks that matter couldn't you generate noop WAL records with just the full page image in them. Once all those are generated then generate the actual split operation and since all the pages have been written to wal since the last checkpoint they won't need any backup block slots. This would require surpressing any checkpoints between writing the first backup block and the final operation record. That might be pretty hard to do cleanly. That would work, but it brings us back to square one Yeah. Wouldn't the original page-split record have been carrying full page images already? (And if so, why didn't we have this problem in the previous implementation?) 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] CommitFest wrap-up
On 12/13/2010 12:37 PM, Robert Haas wrote: - SQL/MED - core functionality - Seems clear to move this to the next CF and keep working on it. [...] - SQL/MED - file_fdw - Discussion is ongoing, but I see no harm in marking this Returned with Feedback for now in anticipation of a new version before CF 2011-01. - SQL/MED - postgresql_fdw - Hasn't received as much review, I think, so should probably be moved to next CF as-is. Don't we need the core patch before the FDW patches? I hope that the core patch is completed and committed ASAP so we have a chance to get the FDW patches in. 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] Complier warnings on mingw gcc 4.5.0
On 12/13/2010 12:01 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 11/01/2010 10:10 PM, Itagaki Takahiro wrote: BTW, with out without the above fix, regression test failed with weird error if the server is built with gcc 4.5. However, server can run if I started it manually with PGPORT or -o --port=X. We might need another fix for the issue. LOG: database system was shut down at 2010-11-02 10:32:13 JST LOG: database system is ready to accept connections LOG: autovacuum launcher started FATAL: parameter port cannot be changed without restarting the server (repeated) I have just run into this when trying to set up a new buildfarm member running Mingw. Is there a fix? Is anyone working on it? Or do I just need to downgrade the compiler? It smells a little bit like an optimization bug. Does dialing down to -O0 make it go away? Sadly, no. I'm testing downgrading the compiler now. 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] proposal : cross-column stats
On Sun, Dec 12, 2010 at 07:10:44PM -0800, Nathan Boley wrote: Another quick note: I think that storing the full contingency table is wasteful since the marginals are already stored in the single column statistics. Look at copulas [2] ( FWIW I think that Josh Tolley was looking at this a couple years back ). Josh Tolley still looks at it occasionally, though time hasn't permitted any sort of significant work for quite some time. The multicolstat branch on my git.postgresql.org repository will create an empirical copula each multi-column index, and stick it in pg_statistic. It doesn't yet do anything useful with that information, nor am I convinced it's remotely bug-free. In a brief PGCon discussion with Tom a while back, it was suggested a good place for the planner to use these stats would be clausesel.c, which is responsible for handling code such as ...WHERE foo 4 AND foo 5. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] initdb failure with Postgres 8.4.4
Andrew Dunstan and...@dunslane.net writes: On 12/13/2010 12:16 PM, BRUSSER Michael wrote: Would it be a completely crazy idea if I try to modify Postgres to look at some env. var (similar to PGDATA) and if exists and path is valid look there for the timezone files? Yes, that's only the first problem you have encountered. I predict there will be others if you go down this path. As far as the timezone data files go, you might have use for the --with-system-tzdata configure option. Note that that is not an environment variable; it requires a hard coded path to the system copy of the Olsen timezone database. The expectation is that any system-maintained copy will have a very well-defined location for those files on any given platform, so there is no need for runtime tweaking. You would use this only on platforms where you expect that normal system maintenance procedures will keep the TZ files more up-to-date than the ones included with Postgres. I agree with Andrew's point in general. Trying to customize the relative layout of the various Postgres-installed files is an exercise in pointless pain. 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] CommitFest wrap-up
On Mon, Dec 13, 2010 at 12:55 PM, Andrew Dunstan and...@dunslane.net wrote: On 12/13/2010 12:37 PM, Robert Haas wrote: - SQL/MED - core functionality - Seems clear to move this to the next CF and keep working on it. [...] - SQL/MED - file_fdw - Discussion is ongoing, but I see no harm in marking this Returned with Feedback for now in anticipation of a new version before CF 2011-01. - SQL/MED - postgresql_fdw - Hasn't received as much review, I think, so should probably be moved to next CF as-is. Don't we need the core patch before the FDW patches? I hope that the core patch is completed and committed ASAP so we have a chance to get the FDW patches in. The core patch will certainly need to be committed first. But I doubt that's going to happen in the next few days. If we get it in before the next CF starts, I think we'll be doing very well. -- 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] hstores in pl/python
2010/12/13 Dmitriy Igrishin dmit...@gmail.com: 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? yes 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. It's question. Minimally it can be a any known (defined) type - composite type too. It would be nice if we can store data in native format with constraints. Now Hstore can store only text - (note: It's terrible hard to write this as external module, so Hstore does maximum what is possible). But, Pavel, I feel you idea. You want to make the syntax clear in particular... I like a possibility to define own types in pg. But sometimes, and associative arrays is it, created interface is too specific - like Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore respects it in design. So when we could to move hstore functionality to core, we can extend a parser, and we can create some general usable API. It can be big plus for stored procedures programming. This is just my opinion - when Hstore will be in core, then we will not have a native associative array ever, so from my perspective is better Hstore as contrib module. In my opinion, hstore is defined and implemented well. Its complete in most cases. Therefore hstore is mature enough to be in core. On the other hand associative arrays should be implemented from scratch. Very well. Let it be. But how integration hstore in core today can interfere with implementation of support for associative arrays in future ? Is it will a big problem ? I think so it can be a problem. Any second implemented feature will have a handicap, because there will be a similar and realised feature. Maybe I am too pessimist, but there are very minimal probability to common existence two similar features in core like hstore or associative arrays. And because associative arrays are more general than hstore, I prefer a associative arrays. Hstore works well and a moving it to core doesn't carry a new value. It's not comparable with TSearch2. What I know, contrib modules are not problem for DBA now and Hstore hasn't a complex installation like TSearch2 had. More - there are not a security issues that had to be solved with TSearch2. Why we need a Hstore in core? Why Hstore need be in core? Back to plpython. There is possibility to call a external library without linking. So Hstore must not be in core - and PL/Python can call it. Regards Pavel Stehule Regards Pavel Stehule or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- // Dmitriy. -- // Dmitriy. -- 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] Complier warnings on mingw gcc 4.5.0
Andrew Dunstan and...@dunslane.net writes: On 12/13/2010 12:01 PM, Tom Lane wrote: It smells a little bit like an optimization bug. Does dialing down to -O0 make it go away? Sadly, no. I'm testing downgrading the compiler now. Mph. FWIW, I see that my last build of Postgres for Fedora 14 would have been with gcc 4.5.1, because that's what F14 is shipping. And that passed its regression tests on at least x86 and x86_64. Maybe you should pester the mingw folk for a compiler update. 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] hstores in pl/python
On Dec 13, 2010, at 8:06 AM, Oleg Bartunov wrote: My most serious pro about hstore in core is a better dump/restore support. Also, since we have so much better hstore and people started to use it in their projects, it'd be great to have built-in feature in PostgreSQL, which mimic key-value or document-oriented database. I thought the JSON data type was supposed to cover this. hstore would remain in contrib. Davd -- 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] GiST insert algorithm rewrite
On 13.12.2010 19:48, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: On 13.12.2010 19:19, Greg Stark wrote: If it's only the backup blocks that matter couldn't you generate noop WAL records with just the full page image in them. Once all those are generated then generate the actual split operation and since all the pages have been written to wal since the last checkpoint they won't need any backup block slots. This would require surpressing any checkpoints between writing the first backup block and the final operation record. That might be pretty hard to do cleanly. That would work, but it brings us back to square one Yeah. Wouldn't the original page-split record have been carrying full page images already? Yes. BTW, the original split record doesn't run into the limit because it doesn't use the backup-block mechanism, it contains all the tuples for all the pages in the main payload. (And if so, why didn't we have this problem in the previous implementation?) In the previous implementation, the NSN was updated immediately in the page split record, and there was no follow-right flag to clear. So the child pages didn't need to be updated when the downlinks are inserted. -- 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] GiST insert algorithm rewrite
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 13.12.2010 19:48, Tom Lane wrote: Yeah. Wouldn't the original page-split record have been carrying full page images already? Yes. BTW, the original split record doesn't run into the limit because it doesn't use the backup-block mechanism, it contains all the tuples for all the pages in the main payload. I see. (And if so, why didn't we have this problem in the previous implementation?) In the previous implementation, the NSN was updated immediately in the page split record, and there was no follow-right flag to clear. So the child pages didn't need to be updated when the downlinks are inserted. Can we fix it so that each child page is updated, and its downlink inserted, as a separate atomic action? That'd require each intermediate state to be consistent and crash-safe, but I think you really need the intermediate states to be consistent anyway because of concurrent scans. 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] GiST insert algorithm rewrite
On 13.12.2010 20:30, Tom Lane wrote: Can we fix it so that each child page is updated, and its downlink inserted, as a separate atomic action? That'd require each intermediate state to be consistent and crash-safe, but I think you really need the intermediate states to be consistent anyway because of concurrent scans. Yes, all the intermediate states are consistent. I'm looking at that approach as we speak. The logic to track what we've done and what needs to be done as the changes are propagated gets quite hairy, but in principle it should work. -- 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
On Mon, 2010-12-13 at 17:15 +, Peter Geoghegan wrote:
On 13 December 2010 16:08, Robert Haas robertmh...@gmail.com wrote:
On Mon, Dec 13, 2010 at 10:49 AM, Simon Riggs si...@2ndquadrant.com wrote:
2. pg_validate_foreign_key('constraint name');
Returns immediately if FK is valid
Returns SETOF rows that violate the constraint, or if no rows are
returned it updates constraint to show it is now valid.
Lock held: AccessShareLock
I'm less sure about this part. I think there should be a DDL
statement to validate the foreign key. The return the problem rows
behavior could be done some other way, or just left to the user to
write their own query.
+1. I think that a DDL statement is more appropriate, because it makes
the process sort of symmetrical.
OK, sold.
Perhaps we could error on the first FK violation found, and give a
value 'foo' not present in table bar. It ought to not matter that
there could be a lot of violations, because they will be exceptional
if you're using the feature as intended - presumably, you're going to
want to comb through the data to find out exactly what has gone wrong
for each violation. On the off chance that it actually is a problem,
the user can go ahead and write their own query, like Robert
suggested.
I think a function would help here, so I'll do that also.
--
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
Dne 13.12.2010 16:34, Tom Lane napsal(a): Tomas Vondra t...@fuzzy.cz writes: Well, until this point we've discussed failure cases involving 'AND' conditions. What about 'OR' conditions? I think the current optimizer computes the selectivity as 's1+s2 - s1*s2' (at least that's what I found in backend/optimizer/path/clausesel.c:630). If you can solve the AND case, the OR case falls out of that. Just replace s1*s2 with a more accurate AND calculation. Oh yeah, now I see - it's just the usual equation P(A or B) = P(A) + P(B) - P(A and B) and we're estimating P(A and B) as P(A)*P(B). 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] proposal : cross-column stats
Dne 13.12.2010 16:38, Tom Lane napsal(a): The reason that this wasn't done years ago is precisely that nobody's figured out how to do it with a tolerable amount of stats data and a tolerable amount of processing time (both at ANALYZE time and during query planning). It's not hard to see what we'd ideally like to do; it's getting from there to something useful in production that's hard. OK, I fully realize that. My plan is to simply (a) find out what statistics do we need to collect and how to use it (b) implement a really stupid inefficient solution (c) optimize in iterations, i.e. making it faster, consuming less space etc. It will take time, it won't be perfect for a long time, but every journey starts somewhere. 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] CommitFest wrap-up
On Mon, Dec 13, 2010 at 12:37:52PM -0500, Robert Haas wrote: We're now just a day or two from the end of this CommitFest and there are still a LOT of open patches - to be specific, 23.Here's a brief synopsis of where we are with the others, all IMO of course. [snip] - Writeable CTEs - I think we need Tom to pick this one up. What is it about this that's so complex? It's not like it could impinge on previous functionality, at least at the SQL level. - Tab completion in psql for triggers on views - Added to CF late, suggest we bump it to the next CF where it will have a leg up by virtue of already being marked Ready for Committer. People are, by the way, allowed to commit patches outside of CFs. I had submitted it imagining that its triviality would allow this, which is why it got into the CF late in the first place. [etc., etc., etc.] All in all it's disappointing to have so many major patches outstanding at this point in the CommitFest, but I think we're just going to have to make the best of it. I'm thinking that given all these givens, we should make the best of it with more CF in March. We don't want a repeat of the last-minute giant change anti-pattern, and even if we're releasing 9.1 in July, three months plus is plenty of time to shake things out. 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] proposal : cross-column stats
Dne 13.12.2010 18:59, Joshua Tolley napsal(a): On Sun, Dec 12, 2010 at 07:10:44PM -0800, Nathan Boley wrote: Another quick note: I think that storing the full contingency table is wasteful since the marginals are already stored in the single column statistics. Look at copulas [2] ( FWIW I think that Josh Tolley was looking at this a couple years back ). Josh Tolley still looks at it occasionally, though time hasn't permitted any sort of significant work for quite some time. The multicolstat branch on my git.postgresql.org repository will create an empirical copula each multi-column index, and stick it in pg_statistic. It doesn't yet do anything useful with that information, nor am I convinced it's remotely bug-free. In a brief PGCon discussion with Tom a while back, it was suggested a good place for the planner to use these stats would be clausesel.c, which is responsible for handling code such as ...WHERE foo 4 AND foo 5. Well, that's good news ;-) I've done a bit of research today, and I've found some quite interesting papers on this topic (probably, I did not have time to read them, in most cases I've read just the title and abstract). [1] Selectivity estimators for multidimensional range queries over real attributes http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.122.914 This seems like a very good starting point. AFAIK it precisely describes what data need to be collected, how to do the math etc. [2] Selectivity Estimation Without the Attribute Value Independence Assumption http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.105.8126 This obviously deals with the independence problem. Haven't investigated it further, but it seems worth to read. [3] On Analyzing the Cost of Queries with Multi-Attribute Restrictions and Sort Operations (A Cost Function for Uniformly Partitioned UB-Trees) http://mistral.in.tum.de/results/publications/MB00_ideas.pdf Describes something called UB-Tree, and shows how it may be used to do estimates. Might be interesting as an alternative to the traditional histograms. There are more details about UB-Trees at http://mistral.in.tum.de/results/publications/ [4] http://www.dbnet.ece.ntua.gr/~nikos/edith/qopt_bibl/ A rather nice collection of papers related to estimation (including some of the papers listed above). Hm, I planned to finally read the Understanding MySQL Internals over the Xmas ... that obviously won't happen. 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] CommitFest wrap-up
On Mon, Dec 13, 2010 at 2:19 PM, David Fetter da...@fetter.org wrote: On Mon, Dec 13, 2010 at 12:37:52PM -0500, Robert Haas wrote: We're now just a day or two from the end of this CommitFest and there are still a LOT of open patches - to be specific, 23.Here's a brief synopsis of where we are with the others, all IMO of course. [snip] - Writeable CTEs - I think we need Tom to pick this one up. What is it about this that's so complex? It's not like it could impinge on previous functionality, at least at the SQL level. I didn't say it was complex, although it is. I said I think we need Tom to pick it up. That's partly because he's likely to have overpoweringly strong opinions on how it should work, which may make it unproductive for someone else to spend time on it. Also, it is complex, and regardless of what effects it has on anything else, it does need to work. Tom is good at that. - Tab completion in psql for triggers on views - Added to CF late, suggest we bump it to the next CF where it will have a leg up by virtue of already being marked Ready for Committer. People are, by the way, allowed to commit patches outside of CFs. I had submitted it imagining that its triviality would allow this, which is why it got into the CF late in the first place. You can insist all you like that your favorite patches are trivial, but that doesn't make it so. I am well aware that patches can be committed between CommitFests. For example: git log --author=Haas --since=2010-10-16 --before=2010-11-14 All in all it's disappointing to have so many major patches outstanding at this point in the CommitFest, but I think we're just going to have to make the best of it. I'm thinking that given all these givens, we should make the best of it with more CF in March. We don't want a repeat of the last-minute giant change anti-pattern, and even if we're releasing 9.1 in July, three months plus is plenty of time to shake things out. -1. That's as likely to make the back-up of big patches worse as it is to make it better. Maybe more likely. -- 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] CommitFest wrap-up
Robert Haas robertmh...@gmail.com writes: We're now just a day or two from the end of this CommitFest and there are still a LOT of open patches - to be specific, 23.Here's a brief synopsis of where we are with the others, all IMO of course. Thanks for doing this! - Extensions - Still under active discussion, suggested we move to next CF. Well, it might be confusing to follow those threads at about any distance but in fact, the only active one left is about some details concerning the ALTER EXTENSION UPGRADE command, which is *not* to be in this commit fest but (hopefully) the next. I think the main extension patch is about as ready as it can be now, I've only been fixing nitpicks and interfacing for awhile (all along this commit fest) and the underlying code has been very stable. As we want to avoid pushing big patches into the last commit fest, I'd very like it if we could have a last round of review-then-commit on this patch. Of course it's still possible to work on it in between two commit fests, but that's not a good idea: this is a very restrained time when the more involved people here can work on their own ideas. So, who's in to finish up and commit this patch in this round? :) I certainly am ready to support last minute changes, given some are required. And if they are too big for the schedule, better shake the patch out now rather than let it bitrot another month. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WaitLatch
On Mon, Dec 13, 2010 at 5:48 AM, aaliya zarrin aaliya.zar...@gmail.com wrote: drainSelfPipe() function will read the data and remove it from the descriptor? Yes... Coz the signal (SIGUSR1) is writing sata to selfPipe descriptor but unable to wake up latch on select system call. The signal won't cause the select() to return, but the byte that the signal handler writes to the self-pipe ought to make it return. Data is not avaialable at select in WaitLatch fun. Coz it is read by drailSelfPipe(). I'm not sure what you're talking about here. drainSelfPipe() is called before calling SELECT. As soon as a self-pipe byte is written, the SELECT will return. -- 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_is_in_recovery=1
On Mon, Dec 13, 2010 at 12:48 AM, aaliya zarrin aaliya.zar...@gmail.com wrote: When pg_is_in_recovery in the table changes to zero(status change)?? At the time when recovery stops? Ah... yep. If switch over has to be done then, after receivibg the signal and telling the postgres to run the startup process (got_SIGHUP = true), shall we have to stop replication or wait for the recovery to get stop? Can I change this pg_is_in_recovery = 0? Stopping replication and ending recovery are the same operation in this case. In the case of a crash, we do recovery just to get back to a consistent state. In the case of replication, we recover continuously until failover is performed. -- 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] hstores in pl/python
2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? yes 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. It's question. Minimally it can be a any known (defined) type - composite type too. It would be nice if we can store data in native format with constraints. Now Hstore can store only text - (note: It's terrible hard to write this as external module, so Hstore does maximum what is possible). But, Pavel, I feel you idea. You want to make the syntax clear in particular... I like a possibility to define own types in pg. But sometimes, and associative arrays is it, created interface is too specific - like Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore respects it in design. So when we could to move hstore functionality to core, we can extend a parser, and we can create some general usable API. It can be big plus for stored procedures programming. This is just my opinion - when Hstore will be in core, then we will not have a native associative array ever, so from my perspective is better Hstore as contrib module. In my opinion, hstore is defined and implemented well. Its complete in most cases. Therefore hstore is mature enough to be in core. On the other hand associative arrays should be implemented from scratch. Very well. Let it be. But how integration hstore in core today can interfere with implementation of support for associative arrays in future ? Is it will a big problem ? I think so it can be a problem. Any second implemented feature will have a handicap, because there will be a similar and realised feature. Maybe I am too pessimist, but there are very minimal probability to common existence two similar features in core like hstore or associative arrays. And because associative arrays are more general than hstore, I prefer a associative arrays. Okay. According to http://www.postgresql.org/docs/9.0/static/arrays.html PostreSQL array - collection of values of the same type -- built-in or user-defined. Assoc. arrays (maps) are generalized arrays by definition. So, maps in PostgreSQL should become a generalizations of an currently existing arrays. Furthermore, if we speak about generalization, map keys must be arbitrary typed. And yes, ordering operator must exists for a key type and so on... Otherwise it will be specialized type just for fancy operator[] with text argument user friendly, rather than map. Hstore works well and a moving it to core doesn't carry a new value. It's not comparable with TSearch2. What I know, contrib modules are not problem for DBA now and Hstore hasn't a complex installation like TSearch2 had. More - there are not a security issues that had to be solved with TSearch2. Why we need a Hstore in core? Why Hstore need be in core? Well, okay. Could you explain by what formal criterion types become built-in ? Back to plpython. There is possibility to call a external library without linking. So Hstore must not be in core - and PL/Python can call it. BTW. Keys of maps in Python can be differently typed. Regards Pavel Stehule Regards Pavel Stehule or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or some similar. Regards Pavel Stehule -- // Dmitriy. -- // Dmitriy. -- // Dmitriy.
Re: [HACKERS] CommitFest wrap-up
On Dec 13, 2010, at 12:04 PM, Dimitri Fontaine wrote: So, who's in to finish up and commit this patch in this round? :) I certainly am ready to support last minute changes, given some are required. And if they are too big for the schedule, better shake the patch out now rather than let it bitrot another month. I'll try to do another review this week. David -- 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] hstores in pl/python
2010/12/13 Dmitriy Igrishin dmit...@gmail.com: 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: 2010/12/13 Pavel Stehule pavel.steh...@gmail.com 2010/12/13 Dmitriy Igrishin dmit...@gmail.com: There are a lot of operators and functions to work with hstore. Does it worth it to implement similar things only to make it possible using operator [] ? yes 2010/12/13 Pavel Stehule pavel.steh...@gmail.com name and interface - hstore is designed as external module - a internal class can be designed different. Could you actually name such a difference rather than pointing to some airily hint of one? That would make it way much easier to see where you want to go. My idea is: somevar['key'] = value value = somevar['key']; What type of value is? Can it be assoc. array ? Is it possible to indexing assoc. array by position ? Any many many other questions can be there. So, I don't think that assoc. arrays has common interface. Its still specialized type. It's question. Minimally it can be a any known (defined) type - composite type too. It would be nice if we can store data in native format with constraints. Now Hstore can store only text - (note: It's terrible hard to write this as external module, so Hstore does maximum what is possible). But, Pavel, I feel you idea. You want to make the syntax clear in particular... I like a possibility to define own types in pg. But sometimes, and associative arrays is it, created interface is too specific - like Hstore is it. PostgreSQL doesn't allow to extend a parser - and Hstore respects it in design. So when we could to move hstore functionality to core, we can extend a parser, and we can create some general usable API. It can be big plus for stored procedures programming. This is just my opinion - when Hstore will be in core, then we will not have a native associative array ever, so from my perspective is better Hstore as contrib module. In my opinion, hstore is defined and implemented well. Its complete in most cases. Therefore hstore is mature enough to be in core. On the other hand associative arrays should be implemented from scratch. Very well. Let it be. But how integration hstore in core today can interfere with implementation of support for associative arrays in future ? Is it will a big problem ? I think so it can be a problem. Any second implemented feature will have a handicap, because there will be a similar and realised feature. Maybe I am too pessimist, but there are very minimal probability to common existence two similar features in core like hstore or associative arrays. And because associative arrays are more general than hstore, I prefer a associative arrays. Okay. According to http://www.postgresql.org/docs/9.0/static/arrays.html PostreSQL array - collection of values of the same type -- built-in or user-defined. Assoc. arrays (maps) are generalized arrays by definition. So, maps in PostgreSQL should become a generalizations of an currently existing arrays. Furthermore, if we speak about generalization, map keys must be arbitrary typed. And yes, ordering operator must exists for a key type and so on... Otherwise it will be specialized type just for fancy operator[] with text argument user friendly, rather than map. Hstore works well and a moving it to core doesn't carry a new value. It's not comparable with TSearch2. What I know, contrib modules are not problem for DBA now and Hstore hasn't a complex installation like TSearch2 had. More - there are not a security issues that had to be solved with TSearch2. Why we need a Hstore in core? Why Hstore need be in core? Well, okay. Could you explain by what formal criterion types become built-in ? No I can't. Please, don't understand to me wrong. Usually I am not against to enhancing a core features. Just I see a significant risk, so PostgreSQL will not have a associative arrays ever, so I am talking about it. If I remember well, then in core are very old types from academic era and types that are necessary for ansi sql conformance. All others are controversial - there was a big war about XML, there is still very unsure JSON. TSearch2 is very specific. Very handy type like citext isn't in core. Significant argument for implementation a type in core is request on parser support. This is analogy to intarray contrib module. It's same. I am sure, so you don't want to use a arrays as was implemented in intarray module. Back to plpython. There is possibility to call a external library without linking. So Hstore must not be in core - and PL/Python can call it. BTW. Keys of maps in Python can be differently typed. Regards Pavel Stehule Regards Pavel Stehule or with constructor somevar = ARRAY[key1 = value1, key2 = value2, .. ] or
Re: [HACKERS] Why percent_rank is so slower than rank?
Tom Lane wrote: argue that there was a regression. It's certainly a performance bug though: nobody would expect that giving a query *more* work_mem would cause it to run many times slower. I wouldn't be that surprised - otherwise it'd just be hard-coded to something large. Especially since earlier in the thread: The example is *not* particularly slow if you leave work_mem at default. which makes me think it's arguably not quite a bug. -- 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] CommitFest wrap-up
David E. Wheeler da...@kineticode.com writes: On Dec 13, 2010, at 12:04 PM, Dimitri Fontaine wrote: So, who's in to finish up and commit this patch in this round? :) I'll try to do another review this week. Thanks! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BufFreelistLock
On Dec 12, 2010, at 8:48 PM, Jim Nasby wrote: There might be some advantage in having it move buffers to a freelist that's just protected by a simple spinlock (or at least, a lock different from the one that protects the clock sweep). The idea would be that most of the time, backends just need to lock the freelist for long enough to take a buffer off it, and don't run clock sweep at all. Yeah, the clock sweep code is very intensive compared to pulling a buffer from the freelist, yet AFAICT nothing will run the clock sweep except backends. Unless I'm missing something, the free list is practically useless because buffers are only put there by InvalidateBuffer, which is only called by DropRelFileNodeBuffers and DropDatabaseBuffers. So we make backends queue up behind the freelist lock with very little odds of getting a buffer, then we make them queue up for the clock sweep lock and make them actually run the clock sweep. Looking at the code, it seems to be pretty trivial to have SyncOneBuffer decrement the usage count of every buffer it's handed. The challenge is that the code that estimates how many buffers we need to sync looks at where the clock hand is at, and I think it uses that information as part of it's calculation. So the real challenge here is coming up with a good model for how many buffers we need to sync on each pass *and* how far the clock needs to be swept. There is also (currently) an interdependency here: the LRU scan will not sync buffers that have a usage_count 0. So unless the clock sweep is being run well enough, the LRU scan becomes completely useless. My thought is that the clock sweep should be scheduled the same way that OS VMs handle their free list: they attempt to keep X number of pages on the free list at all times. We already track the rate of buffer allocations, so that can be used to estimate how many pages are being consumed per cycle. Plus we'd want some number of extra pages as a buffer. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] Complier warnings on mingw gcc 4.5.0
On 12/13/2010 01:12 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 12/13/2010 12:01 PM, Tom Lane wrote: It smells a little bit like an optimization bug. Does dialing down to -O0 make it go away? Sadly, no. I'm testing downgrading the compiler now. Mph. FWIW, I see that my last build of Postgres for Fedora 14 would have been with gcc 4.5.1, because that's what F14 is shipping. And that passed its regression tests on at least x86 and x86_64. Maybe you should pester the mingw folk for a compiler update. Further digging shows some weirdness. This doesn't appear to be compiler-related. I've rolled back all the way to gcc 3.5. It is triggered by the following line in pg_regress.c, commenting out of which causes the problem to go away (although of course it causes the regression tests to fail): putenv(new_pgoptions); 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] Complier warnings on mingw gcc 4.5.0
On Mon, Dec 13, 2010 at 22:29, Andrew Dunstan and...@dunslane.net wrote: On 12/13/2010 01:12 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 12/13/2010 12:01 PM, Tom Lane wrote: It smells a little bit like an optimization bug. Does dialing down to -O0 make it go away? Sadly, no. I'm testing downgrading the compiler now. Mph. FWIW, I see that my last build of Postgres for Fedora 14 would have been with gcc 4.5.1, because that's what F14 is shipping. And that passed its regression tests on at least x86 and x86_64. Maybe you should pester the mingw folk for a compiler update. Further digging shows some weirdness. This doesn't appear to be compiler-related. I've rolled back all the way to gcc 3.5. It is triggered by the following line in pg_regress.c, commenting out of which causes the problem to go away (although of course it causes the regression tests to fail): putenv(new_pgoptions); Take a look at 741e4ad7de9e0069533d90efdd5b1fc9f3a64c81. If you enable that codepath to run on mingw, does it fix it? (it's msvc only now) -- 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] Complier warnings on mingw gcc 4.5.0
Andrew Dunstan and...@dunslane.net writes: Further digging shows some weirdness. This doesn't appear to be compiler-related. I've rolled back all the way to gcc 3.5. It is triggered by the following line in pg_regress.c, commenting out of which causes the problem to go away (although of course it causes the regression tests to fail): putenv(new_pgoptions); Oh really ... are we using src/port/unsetenv.c on that platform? I wonder if that little hack is incompatible with latest mingw libraries ... 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] proposal : cross-column stats
Tomas, (a) find out what statistics do we need to collect and how to use it (b) implement a really stupid inefficient solution (c) optimize in iterations, i.e. making it faster, consuming less space etc. I'll suggest again how to decide *which* columns to cross: whichever columns are combined in composite indexes. In version 2, allow the DBA to specify combinations. In the unlikely event that correlation could be reduced to a single float number, it would be conceivable for each column to have an array of correlation stats for every other column where correlation was non-random; on most tables (i.e. ones with less than 100 columns) we're not talking about that much storage space. The main cost would be the time spent collecting that info ... -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest wrap-up
On 12/13/10 9:37 AM, Robert Haas wrote: - synchronous replication - and... - synchronous replication, transaction-controlled - If we want to get this feature into 9.1, we had better get a move on. But I don't currently have it in my time budget to deal with this. I thought we'd covered most of the major issues here. What's holding these up? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
