Re: [HACKERS] SSL renegotiation
On Thu, Jul 11, 2013 at 4:20 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: I'm having a look at the SSL support code, because one of our customers reported it behaves unstably when the network is unreliable. I have yet to reproduce the exact problem they're having, but while reading the code I notice this in be-secure.c:secure_write() : The recap of my experiences you requested... I first saw SSL renegotiation failures on Ubuntu 10.04 LTS (Lucid) with openssl 0.9.8 (something). I think this was because SSL renegotiation had been disabled due to due to CVE 2009-3555 (affecting all versions before 0.9.8l). I think the version now in lucid is 0.9.8k with fixes for SSL renegotiation, but I haven't tested this. The failures I saw with no-renegotiation-SSL for streaming replication looked like this: On the master: 2012-06-25 16:16:26 PDT LOG: SSL renegotiation failure 2012-06-25 16:16:26 PDT LOG: SSL error: unexpected record 2012-06-25 16:16:26 PDT LOG: could not send data to client: Connection reset by peer On the hot standby: 2012-06-25 11:12:11 PDT FATAL: could not receive data from WAL stream: SSL error: sslv3 alert unexpected message 2012-06-25 11:12:11 PDT LOG: record with zero length at 1C5/95D2FE00 Now I'm running Ubuntu 12.04 LTS (Precise) with openssl 1.0.1, and I think all the known renegotiation issues have been dealt with. I still get failures, but they are less informative: postgres@[unknown]:19761 2013-03-15 03:55:12 UTC LOG: SSL renegotiation failure -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] [BUGS] BUG #6532: pg_upgrade fails on Python stored procedures
On Sat, Mar 17, 2012 at 12:54 AM, Bruce Momjian br...@momjian.us wrote: Well, it will because, by creating the symlink, you allowed this function to be restored into the new database, and it isn't properly hooked to the plpython language. I wonder if you should just delete it because I believe you already have the right plpython2 helper functions in place. Can you run this query for me in one of the problem databases in the new and/or old cluster and send me the output: SELECT proname,probin FROM pg_proc WHERE probin LIKE '%python%'; # SELECT nspname,proname,probin FROM pg_proc,pg_namespace WHERE probin LIKE '%python%' and pg_proc.pronamespace=pg_namespace.oid; nspname |proname| probin +---+-- pg_catalog | plpython_call_handler | $libdir/plpython public | plpython_call_handler | $libdir/plpython (2 rows) I have no idea how I managed to grow the duplicate in the public schema, but this does seem to be the source of the confusion. I might be able to dig out when I grew it from revision control, but I don't think that would help. What we need is for pg_dumpall to _not_ output those handlers. Or pick it up in the check stage and make the user resolve the problem. If I shot myself in the foot in some particularly obtuse way, it might not be sane to bend over backwards making pg_upgrade repair things. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] storing TZ along timestamps
On Mon, Jun 6, 2011 at 7:50 AM, Jim Nasby j...@nasby.net wrote: On Jun 4, 2011, at 3:56 AM, Greg Stark wrote: On Thu, Jun 2, 2011 at 8:58 PM, Jim Nasby j...@nasby.net wrote: I'm torn between whether the type should store the original time or the original time converted to GMT. This is the wrong way to think about it. We *never* store time converted to GMT. When we want to represent a point in time we represent it as seconds since the epoch. Right. Sorry, my bad. The question here is how to represent more complex concepts than simply points in time. I think the two concepts under discussion are a) a composite type representing a point in time and a timezone it should be interpreted in for operations and display and b) the original input provided which is a text string with the constraint that it's a valid input which can be interpreted as a point in time. My fear with A is that something could change that would make it impossible to actually get back to the time that was originally entered. For example, a new version of the timezone database could change something. Though, that problem also exists for timestamptz today, so presumably if it was much of an issue we'd have gotten complaints by now. The common problem is daylight savings time being declared or cancelled. This happens numerous times throughout the year, often with short notice. If you want to store '6pm July 3rd 2014 Pacific/Fiji', and want that to keep meaning 6pm Fiji time no matter what decisions the Fijian government makes over the next two years, you need to store the wallclock (local) time and the timezone. The wallclock time remains fixed, but the conversion to UTC may float. If you are storing an point in time that remains stable no matter future political decisions, you store UTC time and an offset. The conversion to wallclock time may float, and your 6pm Fiji time meeting might change to 5pm or 7pm depending on the policical edicts. If you are only storing past events, its not normally an issue but timezone information does occasionally get changed retroactively if errors are discovered. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] Indent authentication overloading
On Wed, Nov 17, 2010 at 10:35 PM, Magnus Hagander mag...@hagander.net wrote: Currently, we overload indent meaning both unix socket authentication and ident over tcp, depending on what type of connection it is. This is quite unfortunate - one of them being one of the most secure options we have, the other one being one of the most *insecure* ones (really? ident over tcp? does *anybody* use that intentionally today?) We use it. Do you have an alternative that doesn't lower security besides Kerberos? Anti-ident arguments are straw man arguments - If you setup identd badly or don't trust remote root or your network, ident sucks as an authentication mechanism. Ident is great as you don't have to lower security by dealing with keys on the client system (more management headaches == lower security), or worry about those keys being reused by accounts that shouldn't be reusing them. Please don't deprecate it unless there is an alternative. And if you are a pg_pool or pgbouncer maintainer, please consider adding support :) -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] Time-based Releases WAS: 8.5 release timetable, again
On Sat, Aug 29, 2009 at 12:19 AM, Josh Berkusj...@agliodbs.com wrote: I'd think the advantages for our commercial adopters (who pay the salaries for many of the people on this list) would be obvious; if they know with a small margin of error when the next version of PostgreSQL is coming out, they can plan testing and deployment of their new products. See Kevin's post; many companies need to schedule serious testing hardware months in advance, and every ISV needs to plan new product deployments up to a year in advance. We bitch a lot in the community about the super-old versions of PG which commercial software is using, but our variable release cycle is partly to blame. It also works on the other end - with time based releases you can also schedule obsolescence. It is just as critical knowing when the community will stop bug fixes and security fixes when you are trying to schedule major rollouts and planning product development. Canonical (my employer) certainly believe in time based releases, and that is one of the major reasons for the growth of Ubuntu and the Ubuntu Community. We now use time based releases for almost all our sponsored projects (some 6 monthly, some monthly), and are lobbying various projects and other OS distributions to get into some sort of cadence with releases so everyone benefits. It makes us happier (especially when we are choosing what we can commit to providing security updates for the 5 year releases), and our users happier, and I think you happier with less support issues. (In fact the one project I'm personally aware of that doesn't have time based releases also has the worst reputation for bug fixes and updates and caused us trauma because of it, so I'll be pushing to get that fixed too :-P) Certainly our project experiences with waiting for feature X have all been negative. The windows port never got into 7.4 despite holding it up 4 months. HOT held up 8.3 for three to five months, depending on how you count it, in what I think everyone feels was our most painful beta period ever. Most recently, we let HS/SR hold up 8.4 for 2 months ... and they still weren't ready. I would like to see us go to an annual release timeline in which we release in the same month every year. Any time we say variable release date what it really means is later release date. We've never yet released something *early*. Yes please. You may even want to seriously consider shorter release cycles. Tighter cycles can actually reduce stress, as people are less concerned with slippage. With our projects on one month cycles, it doesn't matter that much if a feature isn't good enough for a release - it just goes out with the next months release or the one after if you really underestimated the work. With longer cycles, the penalties of missing deadlines is much greater which can lead to cutting corners if people are not disciplined. Of course, PG already has its own historical cadence to start from where as we had the luxury of adopting time based releases at the start or relatively early in development. For PostgreSQL, with the regular commit fests you might end up to a similar process to GNU Bazaar except with yearly major releases and 2 month development releases, documented at http://doc.bazaar-vcs.org/latest/developers/cycle.html. This is a smaller project, but had to address a number of similar concerns that PostgreSQL would have to so may be a good basis for discussion. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] Time-based Releases WAS: 8.5 release timetable, again
On Tue, Sep 8, 2009 at 7:54 PM, Andrew Dunstanand...@dunslane.net wrote: The release cycle is quite independent of the release lifetime. If you have dates on releases, it is easier to set dates on release lifetime. If you know the releases come out once a year at about the same time, and you want to have a set number of versions in play, you can state at release time when the community will stop support. This gives everyone a clear picture to people what versions they should be targeting and when upgrades will be required. In any case, I don't accept this analogy. The mechanics of a Linux distribution are very different from the mechanics of a project like PostgreSQL. The prominent OSS project that seems to me most like ours is the Apache HTTP project. But they don't do timed releases AFAIK, and theirs is arguably the most successful OSS project ever. We find it works for stuff other than Ubuntu too. IIRC original concerns where you could do it for a small open source project, but it would be impossible to do when juggling as many moving parts as a Linux distribution. You might find the document I cited is for a project with similar issues to PostgreSQL and may address your concerns. It seems to work for other large projects too, such as Gnome, as well as smaller ones. People are discussing switching for reasons Joshua cited (maintaining momentum, planning, enterprise adoption etc.), because people find it a good idea on other projects they work with, or maybe because they read too many articles on agile and lean development practices. It seems to be working fine for me personally (I work on launchpad.net, which is an Open Source mostly-web application using generally Lean/Agile development methodologies, a one month release cycle and a team of about 30 spread over all timezones). I'm especially resistant to suggestions that we should in some way coordinate our releases with other projects' timings. Getting our own developers organized is sufficiently like herding cats that I have no confidence that anyone will successfully organize those of a plethora of projects. I tend to think it will evolve naturally as more people switch to time based releases. Its natural to sync in with the OS releases your developers care about because it makes their lives easier, and its natural for the distributions to get in sync too because it makes their developer's lives easier. But only hindsight will tell of course :-) With a yearly schedule, it probably doesn't matter much except for distributions with a 2 or 3 year cycle - you would still end up with latest PostgreSQL a maximum of I think 8 months after the official release. I am not saying timed releases are necessarily bad. But many of the arguments that have been put forward to support them don't seem to me to withstand critical analysis. I would argue that it would be an major setback for us if we made another release without having Hot Standby or whatever we are calling it now. I would much rather slip one month or three than ship without it. This is why you want your cycle as small as possible - if you have a 6 month cycle for instance, the feature would be available a maximum of 6 months after it is ready. With the feature based release cycle, what if it still isn't ready for prime time after three months of slippage? Having one feature slip hurts, but having all features slip hurts more. Josh cited several examples where he felt similar situations had hurt PostgreSQL development. Of course, if you think it is critical enough you can let it slip and if it is critical enough people will understand - we let one of the 10 Ubuntu releases slip once and people generally understood (you want to get a LTS release right since you have to live with your mistakes for 5 years). There was some flak but we are still here. I personally suspect PostgreSQL would want a 1 year cycle for major releases while a full dump/reload is required for upgrades. When this changes, 6 or even 4 months might actually be a good fit. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] WIP: plpython3
On Fri, Jul 24, 2009 at 5:23 AM, James Pyeli...@jwp.name wrote: That also means that maintaining a separate, parallel code base for a Python 3 variant can only be acceptable if it gives major advantages. I'm not particularly interested in Python 3.x support yet (we are still back on 2.4, soon to hop to 2.5 or 2.6. For us 3.1 is probably 2 years away at the earliest). I am interested in improved plpython though. * Reworked function structure (Python modules, not function fragments) I think it would be an improvement to move away from function fragments. One thing I would like to be able to do is have my Python test suite import my plpython and run tests on it. This would be much easier to do if instead of 'import Postgres' to pull in the api, an object was passed into the entry point which provides the interface to PostgreSQL. This way I can pass in a mock object. This is also useful outside of the test suite - the same module can be used as a stored procedure or by your Python application - your web application can use the same validators as your check constraints for instance. The second feature, function structure, is actually new to the PL. Originally PL/Py took a pl/python-like approach to triggers and functions. *Currently*, I want to change procedures to be Python modules with specific entry points used to handle an event. Mere invocation: main. Or, a trigger event: before_insert, after_insert, before_update, etc. So, a regular function might look like: CREATE OR REPLACE FUNCTION foo(int) RETURNS int LANGUAGE plpython3u AS $python$ import Postgres def main(i): return i $python$; Despite the signature repetition, this is an improvement for the user and the developer. The user now has an explicit initialization section that is common to Python(it's a module). The PL developer no longer needs to munge the source, and can work with common Python APIs to manage and introspect the procedure's module(...thinking: procedure settings..). I'd like a way to avoid initialization on module import if possible. Calling an initialization function after module import, if it exists, would do this. CREATE FUNCTION foo(int) RETURNS in LANGUAGE plpythonu AS $python$ [initialization on module import] def pg_init(pg): [initialization after module import] def pg_main(pg, i): return i $python$; Thoughts? [...it still has a *long* ways to go =] I tend to dislike magic function names, but perhaps it is the most usable solution. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Re: [GENERAL] pgstattuple triggered checkpoint failure and database outage?
On Tue, Mar 31, 2009 at 2:20 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: This is exactly what happened, and temporary tables belonging to other sessions where fed to pgstattuple. +1 for throwing an error. That's what we do for views, composite types, and GIN indexes as well. If you want to write a query to call pgstattuple for all tables in pg_class, you'll need to exclude all those cases anyway. To exclude temp tables of other sessions, you'll need to add AND pg_is_other_temp_schema(relnamespace). I would have expected an exception to be raised personally. I'm ok with returning NULLs as well, but returning zeroes doesn't feel right. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] pgstattuple triggered checkpoint failure and database outage?
On Tue, Mar 31, 2009 at 11:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: A quick look at contrib/pgstattuple shows that it makes no effort whatsoever to avoid reading temp tables belonging to other sessions. So even if that wasn't Stuart's problem (and I'll bet it was), this is quite broken. There is no way that pgstattuple can compute valid stats for temp tables of other sessions; it doesn't have access to pages in the other sessions' temp buffers. It seems that the alternatives we have are to make it throw error, or to silently return zeroes (or perhaps nulls?). Neither one is tremendously appetizing. The former would be especially unhelpful if someone tried to write a query to apply pgstattuple across all pg_class entries, which I kinda suspect is what Stuart did. This is exactly what happened, and temporary tables belonging to other sessions where fed to pgstattuple. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.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] Security and Data Protection Issues
Thank you, I'm also curious as to whether the data folder is already in some way encrypted and if so, what encryption/obfuscation is being used. There doesn't seem to be anything about this on the web.
[HACKERS] Security and Data Protection Issues
I am setting up a postgres database on a standalone system with a randomized text password. However, the db will contain very sensitive data and my boss is worried about the possibility of someone being able to crack the db data if they stole the machine. Can anyone point me to information about how securely the data is stored? Or is my only option to hash all my data? Thanks in advance.
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
Pavan Deolasee wrote:
On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane [EMAIL PROTECTED] wrote:
The
policy of this project is that we only put nontrivial bug fixes into
back branches, and I don't think this item qualifies ...
Got it. I will submit a patch for HEAD.
Thanks,
As I mentioned earlier, I patched 8.3.1 with Pavan's patch and have been
running tests. After a few days I have got postgres to lock up - not
sure if it is related. Below is a ps from my system (NetBSD 3).
TEST ps -ax | grep post
1952 ? IWs 13:52.24 postgres: writer process
2113 ? Ss0:03.04 postgres: logger process
2157 ? Ss0:03.12 postgres: autovacuum launcher process
2199 ? Is0:00.04 postgres: metauser metadb [local] SELECT
2472 ? DWs 814:23.50 postgres: metauser metadb localhost(65524)
COMMIT
2661 ? DWs 0:11.27 postgres: metauser metadb localhost(65525) idle
2680 ? Ss1:18.75 postgres: stats collector process
3156 ? Ss0:45.12 postgres: wal writer process
24362 ? IWs 0:00.00 postgres: autovacuum worker process
25024 ? IWs 0:00.00 postgres: autovacuum worker process
25134 ? IWs 0:00.00 postgres: autovacuum worker process
3289 ttyp5 I 0:01.96 /usr/local/pgsql/bin/postgres -D ../data/metadb
and I was disconnected in my client app with the following message:
[WARN] PSQL:exec - failed in command SELECT
relname,n_tup_ins,n_live_tup,n_dead_tup,pg_total_relation_size('s8_.'
|| relname)*10/(1024*1024),last_autovacuum FROM pg_stat_user_tables
WHERE schemaname='s8_' ORDER BY n_tup_ins DESC
[WARN] error = 'server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'
[WARN] ConnectionNB: PQconsumeInput failed with error 'server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.'
The server is still running but I can't access it. A top yields:
load averages: 0.23, 0.23, 0.2109:53:58
110 processes: 109 sleeping, 1 on processor
Memory: 513M Act, 256M Inact, 1336K Wired, 75M Exec, 557M File, 2776K Free
Swap: 600M Total, 600M Free
PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND
463 root 20 6132K 14M select 0:06 0.05% 0.05% kdeinit
2472 postgres -22 -2 4580K4K mclpl814:23 0.00% 0.00% postgres
2631 root -220 644K4K mclpl606:25 0.00% 0.00% test_writer
1622 root 20 8456K 14M select19:05 0.00% 0.00% kdeinit
1952 postgres 2 -2 3544K4K netlck13:52 0.00% 0.00% postgres
233 root 2024M 31M select 4:47 0.00% 0.00% XFree86
451 root 20 3544K 15M select 4:45 0.00% 0.00% kdeinit
16 root 180 0K 182M syncer 3:51 0.00% 0.00% [ioflush]
17 root -180 0K 182M aiodoned 1:46 0.00% 0.00% [aiodoned]
15 root -180 0K 182M pgdaemon 1:30 0.00% 0.00% [pagedaemon]
1301 root -220 4092K4K mclpl 1:23 0.00% 0.00% kdeinit
2680 postgres 2 -2 3560K 1588K poll 1:18 0.00% 0.00% postgres
1493 root 20 3488K 17M select 1:09 0.00% 0.00% korgac
461 root 20 3748K 16M select 0:57 0.00% 0.00% kdeinit
3156 postgres 2 -2 3448K 1792K select 0:45 0.00% 0.00% postgres
1174 root 20 2608K 2928K select 0:40 0.00% 0.00% profiler
1428 root 20 3376K 13M select 0:26 0.00% 0.00% kdeinit
2661 postgres -22 -2 4896K4K mclpl 0:11 0.00% 0.00% postgres
I'm not convinced this is a postgresql bug (state=mclpl concerns me),
but it's the first time I've seen it. I suppose it could be:
http://www.netbsd.org/cgi-bin/query-pr-single.pl?number=35224.
Anything I can do which might help isolating the problem?
Regards
Stuart
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
Please do --- I have a lot of other stuff on my plate. Please see the attached patch. One change I made is to hold the SHARE lock on the page while ANALYZE is reading tuples from it. I thought it would be a right thing to do instead of repeatedly acquiring/releasing the lock. I have applied the patch and have started my test again, it takes a little while to fill up so I should have the results sometime tomorrow. Thanks for the quick response. Stuart -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] ascii() for utf8
From: Alvaro Herrera Decibel! wrote: Moving to -hackers. On Jul 27, 2007, at 1:22 PM, Stuart wrote: Does Postgresql have a function like ascii() that will return the unicode codepoint value for a utf8 character? (And symmetrically same for question chr() of course). I suspect that this is just a matter of no one scratching the itch. I suspect a patch would be accepted, or you could possibly put something on pgFoundry. Nay; there were some discussions about this not long ago, and I think one conclusion you could draw from them is that many people want these functions in the backend. That would certainly be my preference. I will be distributing an application, the database part of which may (not sure yet) require this function, to multiple platforms including Windows and (though I have never done it) am anticipating it will be significantly harder if I have to worry about the recipient compiling an external function or making sure a dll goes in the right place, gets updated, etc. I'd set it up so that ascii() and chr() act according to the appropriate locale setting (I'm not sure which one would be appropriate). I don't see why any of them would react to the locale, but they surely must honor client encoding. Wouldn't this be the database encoding? (I have been using strictly utf-8 and admit I am pretty fuzzy on encoding issues.) If one had written an external function, how much more effort would it be to make it acceptable for inclusion in the backend? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] elog(FATAL) vs shared memory
Jim Nasby wrote: On Apr 11, 2007, at 6:23 PM, Jim Nasby wrote: FWIW, you might want to put some safeguards in there so that you don't try to inadvertently kill the backend that's running that function... unfortunately I don't think there's a built-in function to tell you the PID of the backend you're connected to; if you're connecting via TCP you could use inet_client_addr() and inet_client_port(), but that won't work if you're using the socket to connect. *wipes egg off face* There is a pg_backend_pid() function, even if it's not documented with the other functions (it's in the stats function stuff for some reason). eh. No worries - my safeguard is just a comment saying 'don't connect to the same database you are killing the connections of' :-) -- Stuart Bishop [EMAIL PROTECTED] http://www.canonical.com/ Canonical Ltd.http://www.ubuntu.com/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] elog(FATAL) vs shared memory
Tom Lane wrote: Stuart Bishop [EMAIL PROTECTED] writes: After a test is run, the test harness kills any outstanding connections so we can drop the test database. Without this, a failing test could leave open connections dangling causing the drop database to block. Just to make it perfectly clear: we don't consider SIGTERMing individual backends to be a supported operation (maybe someday, but not today). That's why you had to resort to plpythonu to do this. I hope you don't have anything analogous in your production databases ... No - just the test suite. It seems the only way to terminate any open connections, which is a requirement for hooking PostgreSQL up to a test suite or any other situation where you need to drop a database *now* rather than when your clients decide to disconnect (well... unless we refactor to start a dedicated postgres instance for each test, but our overheads are already pretty huge). -- Stuart Bishop [EMAIL PROTECTED] http://www.canonical.com/ Canonical Ltd.http://www.ubuntu.com/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] elog(FATAL) vs shared memory
Mark Shuttleworth wrote: Tom Lane wrote: (1) something (still not sure what --- Martin and Mark, I'd really like to know) was issuing random SIGTERMs to various postgres processes including autovacuum. This may be a misfeature in our test harness - I'll ask Stuart Bishop to comment. After a test is run, the test harness kills any outstanding connections so we can drop the test database. Without this, a failing test could leave open connections dangling causing the drop database to block. CREATE OR REPLACE FUNCTION _killall_backends(text) RETURNS Boolean AS $$ import os from signal import SIGTERM plan = plpy.prepare( SELECT procpid FROM pg_stat_activity WHERE datname=$1, ['text'] ) success = True for row in plpy.execute(plan, args): try: plpy.info(Killing %d % row['procpid']) os.kill(row['procpid'], SIGTERM) except OSError: success = False return success $$ LANGUAGE plpythonu; -- Stuart Bishop [EMAIL PROTECTED] http://www.canonical.com/ Canonical Ltd.http://www.ubuntu.com/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Is Patch Ok for deferred trigger disk queue?
Bruce Momjian wrote: I assume this will not be completed for 7.4. I will keep the emails for 7.5. One idea I had was to use the existing sort_mem parameter to control when to force the deferred trigger queue to disk --- it doesn't have anything to do with sorting, but it does have the same purpose, to force thing to disk when we consume enough RAM. --- Bruce Momjian wrote: Stuart, were are on this patch? Seems we need GUC additions, though I can do that for you, and changes to write the head to disk. Was that completed? --- Stuart wrote: Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: As a side question, it looks to me that the code stores the first trigger records in memory and then after some point starts storing all new records on disk. Is this correct? I'd wonder if that's really what you want in general, since I'd think that the earliest ones are the ones you're least likely to need until end of transaction (or set constraints in the fk case) whereas the most recent ones are possibly going to be immediate triggers which you're going to need as soon as the statement is done. Good point. It would be better to push out stuff from the head of the queue, hoping that stuff near the end might never need to be written at all. regards, tom lane Hmmm I see your point. I will change the patch to write the head to disk and reenter when the development branch splits off. Also I've noticed that there is an fd.h which has file routines which I should be using rather than the stdio routines. I will also clean up those errors. Thank you, - Stuart Sorry for the tardiness in replying, I've been away for the past week or so. I didn't intend for 7.4 partly because I knew I'd be away partly because I had seen there was a problem I hadn't realised with the previous patch and didn't want to submit something that may not be stable just before beta. Currently it compiles but there are some, er, issues - shouldn't take to long to fix but it might not be till wednesday as I've got a bit of a backlog to get through. I could use sortmem, but if this is to be the case maybe there should be a change the call it something like max_local_mem with a way to register that you are using it. Maybe the memory allocs could automatically add to it and remove as memory is assigned. Alternativly just make a global to record the memory currently used by interested parties (currently the trigger sortmem I'd guess). The only trouble with this that I can see is that the first one to claim the memory may claim it all, leaving nothing for the other. I'll carry on using the dedicated guc variable for the moment as I can't really see the correct way to solve this cleanly. regards, - Stuart ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Is Patch Ok for deferred trigger disk queue?
Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: As a side question, it looks to me that the code stores the first trigger records in memory and then after some point starts storing all new records on disk. Is this correct? I'd wonder if that's really what you want in general, since I'd think that the earliest ones are the ones you're least likely to need until end of transaction (or set constraints in the fk case) whereas the most recent ones are possibly going to be immediate triggers which you're going to need as soon as the statement is done. Good point. It would be better to push out stuff from the head of the queue, hoping that stuff near the end might never need to be written at all. regards, tom lane Hmmm I see your point. I will change the patch to write the head to disk and reenter when the development branch splits off. Also I've noticed that there is an fd.h which has file routines which I should be using rather than the stdio routines. I will also clean up those errors. Thank you, - Stuart ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [CYGWIN] command
Title: RE: [CYGWIN] command Florian Litot wrote: what is the command to launch a sql script not in psql thanks psql is used to execute sql scripts as follows: psql -f filename database hth, - Stuart
[HACKERS] tell Bugtraq about 7.2.2
Title: tell Bugtraq about 7.2.2 Does someone from core want to inform bugtraq about 7.2.2? Cheers, - Stuart Westcountry Design Print, Heron Road, Sowton, Exeter. EX2 7NF - also at - 17 Brest Road, Derriford, Plymouth. PL6 5AA England www.westcountry-design-print.co.uk
Re: [HACKERS] Abort state on duplicated PKey in transactions
I believe LOCK TABLE table IN EXCLUSIVE MODE should block everything but selects, but it locks for the entire transaction I think. Maybe in tcl you could create your own locking using global variables. If the spin lock code is available to user functions you might be able to use that. Alternativley, inside a plpgsql function, could you use something like this: INSERT INTO ex_tbl (a,b,pk) SELECT var1 AS a,var2 AS b,var3 AS pk WHERE NOT EXISTS (SELECT * FROM ex_tbl WHERE pk=var3) LIMIT 1; GET DIAGNOSTICS rc =ROW_COUNT; where pk is the primary key is the primary key of ex_tbl. if rc=0 then you'd know the primary key already existed and if rc=1 then it would have inserted succesfully - Stuart Haoldo Stenger wrote: Matthew T. O'Connor wrote: A solution, could be to query for the existance of the PK, just before the insertion. But there is a little span between the test and the insertion, where another insertion from another transaction could void the existance test. Any clever ideas on how to solve this? Using triggers maybe? Other solutions? All you need to do is use a sequence. If you set the sequence to be the primary key with a default value of nextval(seq_name) then you will never have a collision. Alternatly if you need to know that number before you start inserting you can select next_val(seq_name) before you inser and use that. By the way the datatype serial automates exactly what I described. Yes, but there are situations where a sequenced PK isn't what is needed. Imagine a DW app, where composed PKs such as (ClientNum, Year, Month, ArticleNum) in a table which has ArticleQty as a secondary field are used, in order to consolidate detail record from other tables. There, the processing cycle goes like checking for the existance of the PK, if it exists, add ArticleQtyDetail to ArticleQty, and update; and if it doesn't exist, insert the record with ArticleQtyDetail as the starting value of ArticleQty. See it? Then, if between the select from and the insert into, other process in the system (due to parallel processing for instance) inserts a record with the same key, then the first transaction would cancel, forcing redoing of all the processing. So, sort of atomicity of the check?update:insert operation is needed. How can that be easily implemented using locks and triggers for example? Regards, Haroldo. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Porting to Native WindowsNT/2000
Dwayne Miller [EMAIL PROTECTED] writes: Well, for one I have no idea what cygwin is, or what it does to your system, or what security vulnerabilities it might add to your system. It comes with alot of stuff that I may or may not need, but what components I need to run Postgres is not clear. Cygwin is a Unix environment for Windows. For information, see http://cygwin.com/ Cygwin comes with a lot of stuff which you don't need to run Postgres. Simply having that stuff on your computer will not introduce any security vulnerabilities if you don't run the programs. Cygwin is simply a DLL and a bunch of Unix programs. It has no server component. In order to build Postgres, you will need the compiler and associated tools. In order to run all the Postgres commands, you will need the shell and several of the tools. In fact, I believe that a cygwin distribution actually comes with Postgres prebuilt and ready to run. (To be honest, the idea of worrying about security vulnerabilities on Windows seems odd to me. If you are honestly worried about security on your database server, the first step is to stop running Windows.) Two could Postgres be made more efficient on Windows if it ran without cygwin? Yes. Cygwin adds measurable overhead to all I/O operations, and obviously a database does a lot of I/O. Postgres employs operations which are fast on Unix but are very slow on cygwin, such as fork. As mlw said, porting Postgres to run natively on Windows would be a significant effort. The forking mechanism it uses currently would have to be completely rearchitected. The buffer, file manager, and networking code would have to be rewritten. Off the top of my head, for a top programmer who is an expert in Unix, Windows, and Postgres, it might take a year. There would also be a heavy ongoing maintenance cost to keep up with new Postgres releases. Three can you start cygwin programs on startup of the system? Sure. cygwin programs are just Windows programs which use a particular DLL. Ian Cygrunsrv allows postgresql to be run as a service. There's a slight hiccup on shutdown meaning that the postmaster.pid file gets left. This is due to sighup being sent by windows shutdown. I think current cygwin snapshots might cure this, otherwise there is a patch some where that causes SIGHUP to be ignored. I *think* the pre-built binary already has this patch applied. - Stuart ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] RE: OID wraparound (was Re: pg_depend)
Would it be possible to offer an option for the OID column to get its value from an int4 primary key (settable on a per table basis maybe)? - Stuart -Original Message- From: Hiroshi Inoue [SMTP:[EMAIL PROTECTED]] Sent: Saturday, July 21, 2001 7:31 AM To: Zeugswetter Andreas SB Cc: PostgreSQL-development Subject: RE: OID wraparound (was Re: pg_depend) -Original Message- Zeugswetter Andreas SB As I mentioned already I'm implementing updatable cursors in ODBC and have half done it. If OIDs would be optional my trial loses its validity but I would never try another implementation. But how can you do that ? The oid index is only created by the dba for specific tables, thus your update would do an update with a where restriction, that is not indexed. This would be darn slow, no ? Please look at my another(previous ?) posting to pgsql-hackers. I would use both TIDs and OIDs, TIDs for fast access, OIDs for identification. How about instead selecting the primary key and one of the tid's (I never remember which, was it ctid ?) instead, so you can validate when a row changed between the select and the update ? Xmin is also available for row-versioning. But now I'm wondering if TID/xmin are guranteed to keep such characteriscs. Even Object IDentifier is about to lose the existence. Probably all-purpose application mustn't use system columns at all though I've never heard of it in other dbms-s. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] RE: Row Versioning, for jdbc updateable result sets
Don't know about JDBC, but couldn't you just use UPDATE xxx SET yyy=zzz WHERE xmin=stored/old xmin AND primarykey=stored/old pk and get the number of altered records? (if its zero then you know somethings wrong and can investigate further) - Stuart -Original Message- From: Dave Cramer [SMTP:[EMAIL PROTECTED]] Sent: Thursday, June 14, 2001 4:34 AM To: [EMAIL PROTECTED] Subject: Row Versioning, for jdbc updateable result sets In order to be able to implement updateable result sets there needs to be a mechanism for determining if the underlying data has changed since the resultset was fetched. Short of retrieving the current data and comparing the entire row, can anyone think of a way possibly using the row version to determine if the data has been concurrently changed? Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RE: Plans for solving the VACUUM problem
Apologises if I've missed something, but isn't that the same xmin that ODBC uses for row versioning? - Stuart Snip Currently, the XMIN/XMAX command counters are used only by the current transaction, and they are useless once the transaction finishes and take up 8 bytes on disk. Snip ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Split Distro
When I downlaod a full tarball I want it all, I'm greedy like that. ;) If it is to be split up as standard I believe problems will arise with different versions being used together (by me most likley...). Also IMHO it will not necessarily be relised the docs have not been down loaded which means refering to older docs if there was a previous installation, or not finding any if no previous install. Also to prevent confusion it might be usefull to have the split distro in its own sub directory (eg Postgresql-7.1-Split-Distro, or somesuch), as when I first looked in on the download directory it was not imediatly obvious there was one main tarball and the rest where a split version rather than a main one with optional stuff (which is not my favoured option). This is all just in my opinion of course. - Stuart ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
