Re: [GENERAL] Foreign Keys and Deadlocks
Hi David, On Wed, 2011-11-09 at 09:52 -0800, David Kerr wrote: So, aside from removing the PKs do i have any other options? Sure you have: order the inserts by primary key inside each transaction. Then you will not get deadlocks, but inserting the same key again will fail of course (but that's the purpose of the primary key, right ?) Ordering inserts/updates by the columns which cause locks is the first thing to do to avoid dead-locks... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Foreign Keys and Deadlocks
Hi David, On Thu, 2011-11-03 at 15:30 -0700, David Kerr wrote: I suspect that it has to be a transaction, and that further up in the TX is an update to one of the reference tables in each TX. This is your cause - updating the referenced table in the same transaction. That will want an exclusive lock on the row, but the shared lock taken by the foreign key check (in another process) is conflicting, and will deadlock when the other process will also want to update some row in the referenced table which is locked by a foreign key check in this process. While the lock on the referenced row was changed to be a shared lock instead of an exclusive lock as in older postgres versions (see http://archives.postgresql.org/pgsql-general/2002-11/msg00397.php for the original problem, which is relaxed now), the lock is still too strong and the deadlock problem remains. A solution is not trivial at all, and involves only locking the row for changes of the referenced columns (which postgres can't do currently). While getting rid of the foreign key will solve your problem, I think it's not the best solution - you can perhaps design a way to not update the referenced tables in the same transaction. Here we adopted a different solution - we run a patched postgres which skips that lock altogether, which means a partially broken foreign key code which mostly works but can leave orphans. I will not recommend to do that though - the reasons we did it that way is that it was the path of least resistance as the application was also running on other DBs (which were the primary DB at that time) and there was no way to make extensive changes to the application code. If I were to change the code, I would have separated the updated fields from the parent table to yet another child table, and have the parent table never updated. That will still have some potential for deadlock (if you don't order the inserts/updates properly) but much less. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL driver for Joomla review
Hi Thom, Sorry for the delay, I got sick in the meantime. I see that others already did some review, I will do a quick one too, later maybe I'll actually try it out... so after a quick review: * on line 218, the ENCODING '$DBname') part feels wrong, you probably want hardcoded UTF8 encoding there ? * as Merlin already commented, transactions are always safe in postgres, this is no mysql ;-) * again, as Merlin commented, getTableList is getting the data bases, which doesn't make sense, but maybe you actually wanted to get the tables - in this case you don't have a typo but you need to change the query ;-) If I'll get some time I'll test it too, but likely not this week... Cheers, Csaba. On Tue, 2009-10-20 at 15:28 +0200, Thom Brown wrote: 2009/10/20 Reid Thompson reid.thomp...@ateb.com: your attachment contains this... ?xml version=1.0 encoding=iso-8859-1? !DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0 Transitional//EN http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd; html xmlns=http://www.w3.org/1999/xhtml; xml:lang=en lang=en head title403 - Forbidden/title /head body h1403 - Forbidden/h1 /body /html Erk.. that's weird. I got that too even after being logged in. I'm not sure how anyone can review it if no-one has access to it. I've attached my working version which differs only slightly to conform with coding-styles required by Joomla. Apologies Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL driver for Joomla review
Hi Thom, I would like to review it, but I get 403 - Forbidden when clicking: http://downloads.joomlacode.org/trackeritem/4/5/0/45041/postgresql.php Not sure what that means, probably I need some kind of login to the joomla tracker system, and I don't have one, and I would prefer not to create one... is it possible to access that somehow without full access to the joomla tracker ? Cheers, Csaba. On Tue, 2009-10-20 at 14:02 +0200, Thom Brown wrote: If anyone has a moment, could they review the PostgreSQL driver I wrote for Joomla's next major release? The developers at Joomla have listened to the persistent noise created about only having MySQL as an option and are now accepting submissions for alternative database systems. (see http://groups.google.com/group/joomla-dev-cms/browse_thread/thread/1382dc6f4af56278#msg_9b95648941ef6fa7 for this development) My submission can be found at: http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdittracker_item_id=18384 Things to watch out for a version-dependent features and clumsy implementations. Thanks Thom Brown -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update Query doesn't affect all records
Hi Andor, On Wed, 2009-08-05 at 14:15 +0200, Schindler Andor wrote: Can anyone tell me, how this is possible? If we insert 12 on the end, then it decreases, but 11 remains the same. The problem only occurs, when the where condition contains sorrend 9 or less. I bet the sorrend column is of some text type, and the sorrend 9 comparison is a text comparison. Try sorrend::integer 9 and it should work ;-) Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
Hi all, On Thu, 2009-07-30 at 11:02 +0200, Greg Stark wrote: On Thu, Jul 30, 2009 at 7:43 AM, Craig Ringercr...@postnewspapers.com.au wrote: On Wed, 2009-07-29 at 14:56 +0100, Greg Stark wrote: What does work well is occasionally poking the socket with recv(..., MSG_DONTWAIT) while doing other work. Program attached. TCP keepalives seem to work very well at least on my Linux test system, and it's easy to test for a dud connection using recv(...) with the MSG_DONTWAIT and (if desired) MSG_PEEK flags. If the connection has exited cleanly it'll return a zero-size read; if the connection has dropped due to keepalive failure it'll return ETIMEDOUT. The problem with this is that it introduces spurious failures for transient network failures. Also it requires the server to periodically take time out from processing the query to do this. I think we want a zero-cost method which will interrupt processing if the client actively disconnects. If there's a network failure we'll find out about it in the normal course of events. Sorry, I have to disagree here. If there's a spurious network error, you have usually bigger problems. I prefer to have the connection killed even if the network recovers than risk an idle in transaction connection to live forever when the client/network crashes for any reason. In case of network failure the connection will probably be cleaned eventually, but it did happen to me that a client machine crashed in the middle of a transaction while not executing any SQL, and that connection stayed until I killed it manually. A simple ping to the client would have cleared the fact that the client is not there anymore. I would also be happy to pay the cost of pinging the clients let's say once per a minute (or configurable interval). Considering that the connections are one to one with a client, it's enough to have a single timer which periodically signals each backend to ping it's client, but this is implementation details for which I have no clue how it would be best, the main thing is: I would love to have this functionality. It's extremely hard to secure all clients against crash, and a crash of one of the clients in the middle of a transaction can have very bad consequences (think indefinitely stucked open transaction). Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
On Thu, 2009-07-30 at 11:41 +0200, Greg Stark wrote: I know this is a popular feeling. But you're throwing away decades of work in making TCP reliable. You would change feelings quickly if you ever faced this scenario too. All it takes is some bad memory or a bad wire and you would be turning a performance drain into random connection drops. But if I get bad memory or bad wire I'll get much worse problems already, and don't tell me it will work more reliably if you don't kill the connection. It's a lot better to find out sooner that you have those problems and fix them than having spurious errors which you'll get even if you don't kill the connection in case of such problems. Well it ought to have eventually died. Your patience may have ran out before the keep-alive timeouts fired though. Well it lived for at least one hour (could be more, I don't remember for sure) keeping vacuum from doing it's job on a heavily updated DB. It was not so much about my patience as about starting to have abysmal performance, AFTER we fixed the initial cause of the crash, and without any warning, except of course I did find out immediately that bloat happens and found the idle transactions and killed them, but I imagine the hair-pulling for a less experienced postgres DBA. I would have also preferred that postgres solves this issue on it's own - the network stack is clearly not fast enough in resolving it. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
On Thu, 2009-07-30 at 13:22 +0200, Craig Ringer wrote: So, barring network breaks (wifi down / out of range, ethernet cable fell out, etc etc) how is the OP managing to leave backends running queries? Hard-resetting the machine? It happened to us when a client box went out of memory and started swapping up to the point it was unaccessible even for console login. The connections of that machine were still live but unusable, as the client box will never get out of that state until hard resetting... which we would promptly do, but the connections on postgres side would still live on. I will probably have to check out now the network connection parameters in the postgres configuration, never had a look at them before... in any case 2 hours mentioned in an earlier post seems a bad default to me. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
On Thu, 2009-07-30 at 13:40 +0200, Craig Ringer wrote: A simple ping to the client would have cleared the fact that the client is not there anymore. Yep. It'd also stop PostgreSQL working for clients with software firewalls, since most of them drop ICMP ECHO (ping). I wasn't meaning TCP 'ping', but a higher level one... TCP keepalives are designed to do the same thing, but do it reliably and properly. Why not configure your tcp keepalive intervals instead? Will do, normally we have good networking, never had to touch it before (and have no experience in network problems anyway)... the main thing is: I would love to have this functionality. It's extremely hard to secure all clients against crash, and a crash of one of the clients in the middle of a transaction can have very bad consequences (think indefinitely stucked open transaction). Nope. Just tune your keepalives if you have hopelessly flakey clients. On the contrary, we do have very stable networking here, the problem was never a networking one... Even if the client _program_ crashes, though, you shouldn't have anything left lying around. It's only if the client _OS_ crashes or the machine is hard-reset that you should be left with a backend lying around until tcp keepalives notice. As explained in earlier email, the client box's OS went down in SWAP hell. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
[just to make things clear, I'm not the one who brought up this discussion, only that I was also bitten once by zombie connections] On Thu, 2009-07-30 at 13:29 +0200, Craig Ringer wrote: Idle? I thought your issue was _active_ queries running, servicing requests from clients that'd since ceased to care? No, the problem was zombie connections idle in transaction. How did you manage to kill the client in such a way as that the OS on the client didn't send a FIN to the server anyway? Hard-reset the client machine(s)? It was a runaway client box in swapping hell. It had plenty of open connections, mostly of which idle in transaction (because it started to take veeery long to do it's processing between the queries). I thought your issue was the backend not terminating a query when the client died while the backend was in the middle of a long-running query. Keepalives alone won't solve that one. No, that is not so dangerous for us, usually we chunk our queries coming from applications that they can't run too long at all. But it seems that if I lower the network timeout then even the crashed client scenario could be OK, I will have to test that... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clients disconnect but query still runs
[this is getting off topic] On Thu, 2009-07-30 at 13:44 +0200, Craig Ringer wrote: A host with a runaway process hogging memory shouldn't be dying. It should really be killing off the problem process, or the problem process should be dying its self after failing to allocate requested memory. If this isn't the case, YOU HAVE TOO MUCH SWAP. After all, swap is useless if there's so much that using it brings the system to a halt. In theory you're right, in practice I can't control any of this - it's the client boxes, I control the DB. The most I can do about it is to friendly ask the colleagues in charge with that to make sure it won't happen again, and then still there will be cases like a virtual machine just crashing. I will probably have to check out now the network connection parameters in the postgres configuration, never had a look at them before... in any case 2 hours mentioned in an earlier post seems a bad default to me. It's the OS's default. PostgreSQL just doesn't change it. Well, then looks like I will have to learn a bit about TCP keep-alive and how linux handles it... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] a strange error
Hi Ray, On Wed, 2009-05-27 at 14:24 +0200, Iv Ray wrote: When the db is refreshed, the first click inside the web application that uses it (php 5.x, Apache 2.x), and some of the next clicks (i. e. the 3rd, 5th, 8th, 12th) result in the following error - PGSQL ERROR: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. After two dozen of clicks and several such messages, the application operates normally until the next refresh. Although I don't know the exact mechanics of how this happens, I guess it has to do with permanent connections used by PHP (a kind of connection caching), and the reused connections discovering that the data base backend they were connected to is gone. Given that an idle connection will not notice that the backend is gone until the next query, it can happen well after the data base refresh if the cached connection stays idle for that time. So probably you also want to clear the connection cache of php, but for that I have no idea how can be done. BTW, I think it would be less disruptive if you truncate your tables (supposing you don't need the content)... that can also be done using a plsql script which iterates through all tables in the public schema and truncates them, if you're concerned with changing schema... of course you would need to lock all tables exclusively for a short time, but that would be for sure less disruptive than deleting the DB. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DELETE running at snail-speed
On Thu, 2008-12-18 at 05:29 -0800, gerhard wrote: I suspect the foreign key constraint of downtime_detail to slow down the delete process. Is this a bug, probably fixed in latest version (8.1.x) or should I drop the constraint and recreate after deletion - which I only see as workaround ? The foreign key is the cause indeed, but you should put an index on downtime_detail(downtimeid) and it would work fine. What happens is that for each row you delete from 'downtime' table, the following is done by the foreign key triggers (cascade the deletion to the child tables): delete from downtime_detail where downtimeid = $1 You can try to see what kind of plan you get for that by: prepare test_001(integer) as delete from downtime_detail where downtimeid = $1; explain execute test_001(0); Now multiply whatever you get there by the count of rows deleted from 'downtime' and you'll get the reason why it is slow... then try it again with the above mentioned index in place. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using postgres.log file for replication
On Thu, 2008-11-27 at 09:20 -0800, Ioana Danes wrote: I've been wondering if anybody tried to use the postgresql csv log file to replicate sql statements. I've been looking into it in the past days and after a brief testing it doesn't look bad at all... Try to execute something like: UPDATE some_table SET some_timestamp_field = now(); The replica is now different than the master :-) Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] return MAX and when it happened
Hi Scara, This should work just fine: select num, min(mydate) from mytab group by num order by num desc limit 1; If you have an index on 'num' it will also be fast. Cheers, Csaba. On Wed, 2008-11-19 at 08:47 -0600, Scara Maccai wrote: Hi all, suppose I have a table like: CREATE TABLE mytab ( num integer, mydate timestamp ); and I want to find MAX(num) and the mydate where it first happened. I guess I could use select * from mytab where num = (select MAX(num) from mytab) order by mydate limit 1; but that would scan the data twice (I guess...) Do I have to write my own MAX function, something like: select MYMAX(num, timestamp) from mytab which would return a custom type? Or is there a better way? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: Copying Blobs between two tables using Insert stmt
On Fri, 2008-10-17 at 13:40 -0700, John Skillings wrote: Hello Csaba, Back in the month of April, I noticed that you posted a similar request on copying blobs between two tables, having separate OID. Can you let me know your final solution please. The final version I'm using is this one: CREATE OR REPLACE FUNCTION copy_blob(p_blobId oid) RETURNS oid AS ' DECLARE v_NewOID OID; v_LODesc INTEGER; BEGIN SELECT lo_create(0) INTO v_NewOID; SELECT lo_open(v_NewOID, -1) INTO v_LODesc; PERFORM lowrite(v_LODesc, data) FROM pg_largeobject WHERE loid = p_blobId ORDER BY pageno; PERFORM lo_close(v_LODesc); RETURN v_NewOID; END; ' LANGUAGE 'plpgsql'; The only drawback is that you must enable reading of pg_largeobject for the application user, as by default only the postgres super user can access it. You would do that with something like (as postgres super user): GRANT SELECT ON pg_largeobject TO my_app_user; Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] interesting trigger behaviour in 8.3
On Tue, 2008-07-29 at 19:25 +0400, Ivan Zolotukhin wrote: Any clues? Can anybody suggest how to debug this? Is it possible to get an explain of the query within the trigger? I bet it's the difference between prepared/not prepared plans. The trigger prepares the plan without considering the actual parameter values, on the psql prompt you give the parameter values explicitly in the sql. Try to use the PREPARE command to prepare the plan on the psql prompt, and EXPLAIN EXECUTE it to see how it works in the trigger... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature: FOR UPDATE SKIP LOCKED
On Wed, 2008-07-09 at 00:48 -0400, Tom Lane wrote: Jonathan Bond-Caron [EMAIL PROTECTED] writes: It would be quite useful to implement a database queue. Although FOR UPDATE NOWAIT and trying again can work as well as other techniques, just skipping over the locks has its advantages (simplicity and zero wait) And disadvantages, such as complete lack of predictability or failure detection. Well, it's not like SQL is completely predictable in general... think about ordering of results. Such a feature would definitely help queue like table processing, and the fact that it is predictably unpredictable should not be a surprise for anybody using such a feature... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature: FOR UPDATE SKIP LOCKED
On Wed, 2008-07-09 at 16:23 +0800, Craig Ringer wrote: Especially if it returned an updated row count or supported the RETURNING clause, so you could find out after the fact what was or wasn't done. Well, it is supposed to be used as SELECT ... FOR UPDATE SKIP LOCKED, so you can in fact put the locked row ids in the target list. With a LIMIT 1 appended would be the perfect way to check out the next queue item to process... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Switching between terminals
On Thu, 2008-07-03 at 19:56 +0530, cinu wrote: Could anyone please tell me where I am going wrong and if there is a way I can get the same behaviour that I am getting while I am executing the through psql prompt. You're mistake is that you think a transaction is related to your terminal, but it is in fact tied to the psql session you are running... Your first example is running one psql instance per terminal, hence one transaction per terminal, while in your second example the transaction is terminated each time psql finishes to run. Basically what you're asking for is to keep a transaction opened by one session (the first psql execution) and connect to it with the second session (the second psql call) and continue the transaction which was opened by the first one... which I'm pretty sure is wrong to want. It is likely possible to do (using PREPARE TRANSACTION), but even likelier that it is a wrong thing to do in normal circumstances. If you'll say what you really want to do, I bet you'll get a lot more useful advices... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching between terminals
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote: If you'll say what you really want to do, I bet you'll get a lot more useful advices... Oh, and you should use the general list only for these kind of questions, hackers is for discussion about hacking on the postgres code itself. And cross-posting will also not help too much, the subscribers on hackers which are likely to answer you are subscribed to the general list too. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching between terminals
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote: [snip] It is likely possible to do (using PREPARE TRANSACTION) [snip] I was wrong, you can't do it with that either, see: http://www.postgresql.org/docs/8.2/static/sql-prepare-transaction.html Maybe there is some feature to attach/deattach to/from a session, but I might be just confused... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unreferenced temp tables disables vacuum to update xid
Hi all, I just want to report that we had here almost exactly the same problem as reported here: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00134.php The whole scenario repeated the same: production DB refused to work, restarted in single user mode, run vacuum (few hours), postgres still complaining and refuse to start, more web searching, found leaked temporary tables (this time they were a few proper temporary tables created by our application, no toast table), dropped them, problem solved. Net effect: ~5 hours downtime affecting a few hundreds of our customers... Can this scenario be included on the doc page regarding routine vacuuming: http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html If it would have been there, it would have saved us all this down time, I could have just simply drop the leaked temp tables in the first place... In general, I found very little information in the docs about the ways temporary tables work in postgres. There are a few gotchas about temporary tables, a special page discussing temp tables would be nice to have, and linked from all other places which currently discuss different aspects of this topic. One thing which I still don't know what is it exactly doing is vacuuming a temporary table from a different session: it worked for me in the sense it did not throw any error, but it did nothing to the temp table as far as I can tell... is there a way to vacuum/analyze temporary tables from another session ? The docs definitely don't say anything about this topic... I would think it can't work if the table lives in fact in private memory of it's session, but it would be nice if the docs would state these things clearly... In fact I could attempt to write that page but need guidance. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multithreaded queue in PgSQL
We also have such a queue here, and our solution is an algorithm like this: 1. get the next processor_count * 2 queue ids which are not marked as taken; 2. choose randomly one of these ids; 3. lock for update with nowait; 4. if locking succeeds: 4.1. check again the item, as it could have been processed in the meantime - if not available, go to 5.; 4.2. update the DB row to mark the id as taken, and process the item; 5. there are more ids to try: loop to 2. 6. sleep a small random interval, and loop to 1. This algorithm should have small enough collision rate on a busy queue due to the random chosen ids and random sleep (it will have high collision rate on an almost empty queue, but than you don't really care), while still allowing all processors to access all entries. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In the belly of the beast (MySQLCon)
On Sun, 2008-04-20 at 11:32 -0600, Scott Marlowe wrote: On Sun, Apr 20, 2008 at 11:12 AM, Scott Ribe [EMAIL PROTECTED] wrote: I am going to play with this and see where it breaks, but it's going to be an enormous time investment to babysit it. One more suggestion: if you happen to use the alt_perl tools, be sure to uncomment/change the line: $SYNC_CHECK_INTERVAL = 1000; In my experience it made a big difference to set that to 6 (meaning sync events created once per minute instead of once per second) for the synchronizing after copy phase. The number of events generated while the copy over is running can be so big that it will never get in sync again otherwise... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying large object in a stored procedure
Hi all, Is there an easy way to copy a large object to a new one, having a new OID and it's content independent from the original ? The large object API functions would only allow to stream the original to the client and stream it back to the server for the copy, which is not really optimal... I would like to do it all on the server side. The server side functions would work to export the original to a file and then import it again, but that needs superuser privileges which rules that out. I could simply copy the contents of the pg_largeobject table, but then I need a new OID for that. I can use the server side large object API to create an empty large object, delete it, then use the obtained OID for the copy... but that also sounds somewhat suboptimal. Is there any simple way to copy a large object ? Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying large object in a stored procedure
Is there an easy way to copy a large object to a new one, having a new OID and it's content independent from the original ? So my current solution would be: CREATE OR REPLACE FUNCTION copy_blob(p_blobId OID) RETURNS OID AS ' DECLARE v_NewOID BIGINT; BEGIN SELECT lo_create(0) INTO v_NewOID; DELETE FROM pg_largeobject WHERE loid = v_NewOID; INSERT INTO pg_largeobject (loid, pageno, data) SELECT v_NewOID, pageno, data FROM pg_largeobject WHERE loid = p_blobId; RETURN v_NewOID; END; ' LANGUAGE plpgsql; I would still be grateful if anybody knows a better solution using plain SQL/plpgsql... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying large object in a stored procedure
[snip] DECLARE v_NewOID BIGINT; ^^ small correction: v_NewOID should be of type OID: DECLARE v_NewOID OID; BIGINT would of course work too as long as there is implicit cast from it to/from OID, which seems to be the case on 8.2. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgre vs MySQL
On Fri, 2008-03-14 at 08:43 -0700, Steve Crawford wrote: Also, it is MVCC-safe only from 8.3 upwards; on older versions it (incorrectly) deletes dead tuples that are still visible to old transactions. More interesting. I may have a broken mental-model. I *thought* that CLUSTER acquired exclusive locks and that acquisition of the exclusive lock would imply that there couldn't be any transactions accessing that table. Where is my misunderstanding? Here's a scenario: - transaction A starts to read table A; - transaction B starts, deletes some records from table B, end ends; - transaction C starts and clusters table B; - transaction A finished reading table A, and now tries to read the records just deleted by transaction B; Question: under MVCC rules should transaction A see the deleted records or not ? Unfortunately I don't know for sure the answer, but if it is yes, then bad luck for transaction A, because cluster just ate them. And the locking will not help this... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Perceived weaknesses of postgres
http://www.theserverside.com/news/thread.tss?thread_id=48339 The interesting part is where somebody asks why NOT use postgres, and it's answers could give some additional hints to those interested on what people find missing from postgres to adopt it. Just to summarize some of the answers: * major PITA to upgrade between major versions; * executing a single query on multiple cpus/cores; * no direct table cache control; * accent-insensitive text comparisons; * fast select count(*); Wrong assumptions (but people seem to be sure it's like this): * no hotbackup except pg_dump (wrong: there are in fact a few different ways to do that); * pg_dump the only way to cleanly upgrade (wrong: slony is good for that); * missing 2 phase commit (wrong: it is now implemented); * inadequate performance with really large databases (wrong: there are known examples of really large postgres DBs); There are other claims like (quoting): RAC, enterprise backup capabilities, database on raw partition, compatibility with enterprise storage (SAN,...) which I don't know if there are adequate solutions for postgres or not. Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Perceived weaknesses of postgres
On Wed, 2008-02-13 at 13:29 +0100, Dawid Kuroczko wrote: * no direct table cache control; Could you elaborate more on this one? Well, I was just summarizing what other people wrote :-) But I guess they refer to table level control of how much cache memory to use. I think there are DBMSs where you can tune the cache usage based on tables/table groups, so you can make sure the important tables (where you want instant response even if the table was not accessed in the last hours) are always in cache. Currently that's not possible in postgres - it is true that the cache memory will be used in an optimal way regarding mean performance, but sometimes the mean performance is just good enough even if not optimal, and you would like to eliminate worst case scenarios for some queries. In that case more control (on the cache strategy too) is better... Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Perceived weaknesses of postgres
On Wed, 2008-02-13 at 13:39 +0100, Csaba Nagy wrote: On Wed, 2008-02-13 at 13:29 +0100, Dawid Kuroczko wrote: * no direct table cache control; Could you elaborate more on this one? OK, re-reading what I just wrote makes me think it was not clear enough: I think they mean you can _reserve_ some cache memory directly to specific tables/table groups so that reserved cache is only used by those tables, and thus will not be sensitive to other activities than the access to those tables. Particularly a sequential scan on another, big, table will not touch that reserved cache, or any other big sweep of data access on other tables. Not sure this time I got it actually clearer though :-) Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Perceived weaknesses of postgres
On Wed, 2008-02-13 at 13:56 +0100, Magnus Hagander wrote: I don't think these people are comparing to other opensource ones... They're comparing to the commercial ones (at least in this case) Yes, that's definitely the case. And that can actually be taken as a compliment to the already attained status of postgres... I would guess they're referring to the ability to pin a table into memory, so that it always stays in the cache regardless of what else the database is doing. There is a narrow use-case where this can be very useful, but it can also be a very dangerous tool (hint: if you pin a table that grows up to say 80-90% of your RAM size, your database will not be fast for anything else) If you can not only pin the complete table, but just allocate a fixed amount of memory for caching _only that table_, then you get a compromise which would be better than the 2 extremes: the table is cached in a mixed bag together with all the rest, resulting in effectively being cached 0-100% depending on all the activities on the DB, or it is always 100% cached independently of the any activities in the DB. The intermediate solution is: the table is always cached using x amount of memory independently of what happens on the DB. That makes sure the table is completely cached if the allocated memory is enough, and at least the most used records are always cached if the table grows bigger than the allocated cache, while there's no risk of overrunning the available memory. This could also be applied to groups of tables. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Renaming a constraint
On Tue, 2008-02-05 at 12:11 -0500, Tom Lane wrote: Csaba Nagy [EMAIL PROTECTED] writes: I found an old post regarding the subject, where modifying the pg_constraint entry was recommended: http://archives.postgresql.org/pgsql-admin/2003-04/msg00339.php Is this still safe to do ? What kind of constraint? The only ones left are foreign key constraints. I had a few not nulls too, but those are not actually there anyway (see my other post). Thanks, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Renaming a constraint
On Wed, 2008-02-06 at 11:03 -0500, Tom Lane wrote: IIRC, the name of an FK constraint also appears in the arguments for its triggers. If you didn't update those too, the behavior might be a bit inconsistent, depending on which PG version you're using. In particular I'm not sure which name pg_dump will believe. OK, I think I'll just give up on renaming them. How hard would be to implement renaming constraints as an alter table option ? It sounds to me like it should be a task requiring mostly legwork than programming skills, would it be good task for a beginner in C like me ? If it is at all regarded as a needed feature... Thanks, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Renaming a constraint
I found an old post regarding the subject, where modifying the pg_constraint entry was recommended: http://archives.postgresql.org/pgsql-admin/2003-04/msg00339.php Is this still safe to do ? The pertinent docs don't say anything pro or contra: http://www.postgresql.org/docs/8.2/static/catalog-pg-constraint.html I need to rename a few constraints to keep our schema logically consistent. It would be of course nice to have an ALTER TABLE option to do it, but if updating pg_constraint is safe it would do it for now. Thanks, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Constraint name for named NOT NULL constraints is ignored
While upgrading our schema between application versions, we also had a few constraint changes. Some of those changes were dropping NOT NULL constraints on some columns. Our schema had a few such NOT NULL constraints, which were created using the named variant of the column constraint clause (something like '... col_name col_type CONSTRAINT nn_col_name NOT NULL ...'). This syntax is happily accepted by postgres. So our schema change script was expecting that simply dropping those named constraints will drop the NOT NULL constraint on the relevant columns, but the constraint is just simply not there at all, so trying to drop it gives an error. From the description of the pg_constraint table http://www.postgresql.org/docs/8.2/static/catalog-pg-constraint.html is clear that it does not hold NOT NULL constraints, which go to the pg_attribute table, but then maybe an error should be raised if somebody tries to create a named NOT NULL constraint ? Ignoring integral parts of the SQL syntax feels somewhat mySQL-ish. Or at least mention this behavior on the CREATE TABLE page (I couldn't find it if it's there): http://www.postgresql.org/docs/8.2/static/sql-createtable.html Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] PostgreSQL Certification
On Mon, 2008-02-04 at 08:31 -0800, Joshua D. Drake wrote: On Mon, 04 Feb 2008 12:18:55 + Gregory Stark [EMAIL PROTECTED] wrote: Joshua D. Drake [EMAIL PROTECTED] writes: I was going to suggest pruning the mailing lists down to just 3-4 already. The last thing we need to be doing is creating new ones. I don't agree in the least, I was actually going to suggest we add a new one for relational design questions. I like many lists that are contextually specific. IMO, general should be removed for example. Why don't you go ahead and create those special lists and make general collect all of them ? Some sort of hierarchy of lists... if doable at all, that could make everybody happy... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL Certification
On Mon, 2008-02-04 at 12:18 +, Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Guys, with respect this thread does nothing for us unless it is on the certification list. Do we really need a separate mailing list for every thread? It's already kind of crazy with dozens of lists, many of them moribund, which most people aren't even aware exist. I was going to suggest pruning the mailing lists down to just 3-4 already. The last thing we need to be doing is creating new ones. +1 At least for me it's far easier to ignore threads I'm not interested in than subscribe to yet another list. This particular subject (certification) would be interesting for me as a potential end user, so I'm not really qualified for any comment on the organization side of it, but ultimately interested in the end result. I suspect many of the postgres general list subscribers are in the same situation, so why not let them know about how it evolves ? Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] varchar sort ordering ignore blanks
On Tue, 2008-01-15 at 16:32 +0100, Luca Arzeni wrote: In all cases I'm using locale LATIN9 during DB creation, but I tested also with ASCII, UTF8 and LATIN1 encoding. I guess this has nothing to do with the encoding, but with the collation rules used, which is governed by lc_collate parameter. See what you get on both DBs for: SHOW lc_collate ; Quoting from the docs: The nature of some locale categories is that their value has to be fixed for the lifetime of a database cluster. That is, once initdb has run, you cannot change them anymore. LC_COLLATE and LC_CTYPE are those categories. They affect the sort order of indexes, so they must be kept fixed, or indexes on text columns will become corrupt. PostgreSQL enforces this by recording the values of LC_COLLATE and LC_CTYPE that are seen by initdb. The server automatically adopts those two values when it is started. See: http://www.postgresql.org/docs/8.1/static/charset.html HTH, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Making a query from 2 tables at same time
This is the problem: ... t2.idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101')2; What is that t2.idr ? Based on the syntax postgres must look for a function (because of the parentheses), and it thinks t2 is the schema where it must look for it. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Primary Key
On Fri, 2007-11-23 at 11:37 +0100, Martijn van Oosterhout wrote: On Fri, Nov 23, 2007 at 09:33:13AM +, Peter Childs wrote: I tend to agree that primary keys should be single fields if they need to be referenced but should also be natural if at all possible. ie use car number plates rather than some serial int. Car number plates are unique over time? I didn't think so... I'm not sure how it is in the US, but here in Germany I just reused a car plate from the owner it had before me... so now the plate is uniquely associated at most with the car, not the owner... and I'm pretty sure that's not unique either. And what do you do when the things shift meaning in your natural key ? Cause that's a very common thing to happen to natural keys. And suddenly what was unique becomes not unique anymore... and the headaches begin... You're better off using synthetic keys for references between tables, and you can still keep your natural keys for lookup, just don't use them as unique join criteria, only search/filter criteria. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Chunk Delete
On Thu, 2007-11-15 at 17:13 +, Gregory Stark wrote: DELETE FROM atable AS x USING (SELECT ctid FROM atable LIMIT 5) AS y WHERE x.ctid = y.ctid; Have you tried to EXPLAIN this one ? Last time I tried to do something similar it was going for a sequential scan on atable with a filter on ctid. The other form using where ctid = any (array(select ctid from ... (see my previous post forwarding Tom's suggestion) was going for a ctid scan, which should be orders of magnitudes faster than the sequential scan for big tables and small chunks. Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Chunk Delete
[snip] With Oracle we do it with: delete ,tname where cond and rownum Y; Can we have the same goody on Postgres? The attached message is Tom's response to a similar question, in any case it would work fine in your case too (assuming you have postgres 8.2). HTH, Csaba. ---BeginMessage--- Stephan Szabo [EMAIL PROTECTED] writes: Unfortunately the stuff that makes a ctid=value nice doesn't seem to be used when you're doing an in. Yeah, see the header comments in tidpath.c: * There is currently no special support for joins involving CTID; in * particular nothing corresponding to best_inner_indexscan(). Since it's * not very useful to store TIDs of one table in another table, there * doesn't seem to be enough use-case to justify adding a lot of code * for that. Of course, that argument is wrong for a self-join, which is what this would essentially be. So maybe it would be worth doing sometime. Still, the issue doesn't come up very often. [ thinks for a bit ... ] Actually, you can do it as of 8.2 or so, by abusing the ScalarArrayOp stuff: turn the subquery into an array. An example in the regression database: regression=# explain update tenk1 set ten=ten+1 regression-# where ctid = any (array(select ctid from tenk1 limit 10)); QUERY PLAN - Tid Scan on tenk1 (cost=0.46..40.71 rows=10 width=250) TID Cond: (ctid = ANY ($0)) InitPlan - Limit (cost=0.00..0.46 rows=10 width=6) - Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=6) (5 rows) It even seems to get the cost estimate right... regards, tom lane ---End Message--- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reliability of WAL replication
Marc, On Tue, 2007-10-23 at 13:58 +0200, Marc Schablewski wrote: We had some corrupted data files in the past (missing clog, see http://archives.postgresql.org/pgsql-bugs/2007-07/msg00124.php) and are thinking about setting up a warm standby system using WAL replication. Would an error like the one we had appear in WAL and would it be replicated too? Or is there some kind of consistency check, that prevents broken WAL from being restored? Here we had WAL based replication in place some time ago, and the result are somewhat mixed: in one case the corruption was replicated, other times it was not... I guess it has to do with where the corruption occurred, and I have a feeling the first case (corruption replicated) was some postgres corner case reacting badly on kill -9 and alike, the second case (corruption not replicated) was file system corruption. I didn't run WAL based replication for a while, so I don't know what have changed in it lately... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Obtaining random rows from a result set
Dear Santa, I'd like my database to have functionality analogue to how LIMIT works, but for other - non-sequential - algorithms. There was some discussion before to possibly reuse the algorithm ANALYZE is using for sampling some given percentage of the table data and provide this for some kind of SELECT SAMPLE x% style of functionality. This would be the fastest you can get for a reasonably big sample so it can be statistically significant, but not repeatable. I'm not sure if this is the same what you were asking for though, I would like something like this for statistical stuff, not for randomly selecting rows. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete/update with limit
Andrew, Thanks for your input, comments below. On Thu, 2007-07-26 at 13:30, Andrew Kroeger wrote: After reading through this thread, I have an idea that should accomplish what I believe are your 3 main goals (avoid any negative performance impact on the user's inserts, do not lose any data from those inserts, and do not double process any data) and possibly improve performance (I'm not sure what the overhead is for triggers, so there may not be any benefit). The essential changes you're proposing are: 1) use a fixed table instead of temp table - this is a reasonable one, temp tables are not exactly free if they are created and dropped too often; 2) use an insert selected/delete scheme instead of the delete/insert via trigger. I doubt this would be faster... in fact I wonder if instead of the trigger it is not possible to use a rule to do it - I've never used the postgres rule system, but it seems to me it is possible to create a rule which inserts the row in another table when deleting it. I'm not sure how cheap that would be compared to the trigger version. In any case, on top of all these thoughts there is one more goal I have: the solution used for postgres should be as close to the other DBs as possible, for obvious maintenance reasons. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Delete/update with limit
On Tue, 2007-07-24 at 19:06, Stephan Szabo wrote: Unfortunately I don't think this will work. Multiple backends will happily pick up the same ctid in their selects and then try to delete the same records. I'm pretty sure he said that the batch processing (and the delete) would only be happening from one backend at a time, no concurrency on that portion, merely concurrency with the large volume of inserts. Yes it's exactly like that... only it also happened accidentally that 2 batch processes started at the same time, and they should not double process the data, nor loose some of it. The above scheme is OK with that too... but the array version from Tom is even better :-) Regarding the proposed mark/process/delete version, we've done it that way, and we always managed to get some corner case which lost us data... so even if it's possible to do it well, it's definitely not easy. The delete/copy/process private data version is much safer, and it actually can be done in one transaction to assure crash safety. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete/update with limit
First of all, thanks for all the suggestions. put a SERIAL primary key on the table Or: Maybe add OIDs to the table, and delete based on the OID number? No, this is not acceptable, it adds overhead to the insertions. Normally the overhead will be small enough, but on occasions it is noticeable. Loop Forever DELETE from incoming_table; VACUUM incoming_table; End Loop; Not workable either, it still won't assure the table never getting too big. Once the table is too big, it takes too much to process, and it gets even bigger for the next time. The whole thing is transient (i.e. the load will smooth out after a while), but then exactly when it should work it doesn't... and if you didn't guess, the users want the results immediately, not next day so we could do the processing at night when we have virtually no load. Use partitioning: don't delete, just drop the partition after a while. OK, this could work. It will still be completely different than the code for the other DBs, but it will work. Thanks again for all the suggestions, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Delete/update with limit
How about using the following? delete from table where ctid in (select ctid from table limit num); I actually checked this out before starting this thread, and the plan looked like: explain delete from my_table where ctid in (select ctid from my_table limit 10); QUERY PLAN - Merge IN Join (cost=101.68..108.03 rows=10 width=6) Merge Cond: (public.my_table.ctid = IN_subquery.ctid) - Sort (cost=101.11..104.21 rows=1240 width=6) Sort Key: public.my_table.ctid - Seq Scan on my_table (cost=0.00..37.40 rows=1240 width=6) - Sort (cost=0.57..0.59 rows=10 width=6) Sort Key: IN_subquery.ctid - Limit (cost=0.00..0.30 rows=10 width=6) - Seq Scan on my_table (cost=0.00..37.40 rows=1240 width=6) (9 rows) It looked strange to me, and I thought it won't work too well on a big table... but it's true that was a toy table, so let's try on a big one: explain delete from big_table where ctid in (select ctid from big_table limit 10); QUERY PLAN Merge IN Join (cost=11086906.66..11404636.41 rows=10 width=60) Merge Cond: (public.big_table.ctid = IN_subquery.ctid) - Sort (cost=11086906.26..11245771.06 rows=63545920 width=66) Sort Key: public.big_table.ctid - Seq Scan on big_table (cost=0.00..834103.20 rows=63545920 width=66) - Sort (cost=0.40..0.42 rows=10 width=6) Sort Key: IN_subquery.ctid - Limit (cost=0.00..0.13 rows=10 width=6) - Seq Scan on big_table (cost=0.00..834103.20 rows=63545920 width=6) (9 rows) So, while the batch table is not expected to have 60M rows, on occasions it got to a few 100Ks... and in that case the chunking would slow down things even more. I guess if the ctid in (...) thing would do a better job it would be the best solution. Regarding all the other questions, the other DB does the trick well too, without any hidden cost. And the whole complicated mechanism is in place not because of cost savings, but because I didn't find any better way to do it so that concurrent inserts are neither slowed down nor lost... the problem is that if you want to reliably delete only processed rows, you must mark them somehow, and that would mean an update + delete later - and I figured the delete + trigger + temp table approach will be still cheaper. And the processing code will have to scan the processed chunk multiple times, so for that purpose it is also better to have it in a temp table. And we had to make sure an accidental second run of the processor won't corrupt the data either (it happened before)... the trigger approach helps there too... We had here so many data losses on this processing with different approaches, that I start to be tired about it... and this delete + trigger + temp table looks to be the one which finally works correctly, but gets us performance problems on occasions. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Delete/update with limit
Unfortunately the stuff that makes a ctid=value nice doesn't seem to be used when you're doing an in. It's possible that a function that does something like for rec in select ctid from my_table limit 10 loop delete from my_table where ctid=rec.ctid; end loop might do okay, but I haven't tried it. OK, I think this will work. It would be nice though to have the 'ctid in' trick work just as well as 'ctid = ' ... Thanks, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Delete/update with limit
On Tue, 2007-07-24 at 18:19, Tom Lane wrote: [ thinks for a bit ... ] Actually, you can do it as of 8.2 or so, by abusing the ScalarArrayOp stuff: turn the subquery into an array. An example in the regression database: regression=# explain update tenk1 set ten=ten+1 regression-# where ctid = any (array(select ctid from tenk1 limit 10)); QUERY PLAN - Tid Scan on tenk1 (cost=0.46..40.71 rows=10 width=250) TID Cond: (ctid = ANY ($0)) InitPlan - Limit (cost=0.00..0.46 rows=10 width=6) - Seq Scan on tenk1 (cost=0.00..458.00 rows=1 width=6) (5 rows) It even seems to get the cost estimate right... Cool, I will use this then (we do have the relevant DB on 8.2). It would still be nice to have it work directly... Thanks, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Delete/update with limit
Hi all, This subject was touched a few times in the past, I looked into the archives... the result is invariably key developers saying such a feature is unsafe because the result is unpredictable, while the people requesting is saying it is OK that way, it is expected... but no compelling use case for it. OK, I have one... We have here quite a few processes which collect user input, and put them in batch tables, which in turn are processed regularly and update other tables with summary data, reject invalid records, etc. The insertions are unpredictable, they can happen any time and any of them in parallel, they are user input... and they must be very fast, it's our user experience at stake. The batch processing is done by a single periodical process. Now we had a few attempts of making this parallelism safe enough so we don't loose some of the concurrent input while we do the processing step, while still keeping minimal overhead in the table. The end result was a scheme where the batch processor deletes from the table and a delete trigger puts the deleted rows into a temporary table, and then the processor can do with that private data anything it pleases without interfering with the inserts (the processing is actually quite complex on occasions). This works fine in terms of correctness, however it turns out to be a problem with high bursts of incoming data, or when the processor is not running for a while and a lot of data is accumulating... then we have lots of data to process at once, which leads to long running transactions (the whole thing runs in one transaction) and worse, connection timeouts. On other databases, it is possible to limit the delete to a maximum number of rows to be deleted. This way we can limit the size of one batch with minimal overhead... In postgres we're currently not chunking, due to the fact that the code to do it is simply overly contorted and inefficient compared to the other DBs we use. At least all the solutions we could think of to do the chunking in a safe way while the inserts are running in parallel, without disturbing them, have invariably resulted in overly complicated code compared to the simple delete with limit + delete trigger solution we have for the other DBs. Now I don't put too much hope I can convince anybody that the limit on the delete/update commands has valid usage scenarios, but then can anybody help me find a good solution to chunk-wise process such a buffer table where insert speed is the highest priority (thus no indexes, the minimum of fields), and batch processing should still work fine with big table size, while not impacting at all the inserts, and finish in short time to avoid long running transactions ? Cause I can't really think of one... other than our scheme with the delete with limit + trigger + private temp table thing. Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Database corruption: finding the bad block
Hi all, I just had the following error on one of our data bases: ERROR: could not access status of transaction 1038286848 DETAIL: could not open file pg_clog/03DE: No such file or directory I researched on the mailing list and it looks like the usual suspect is disk page corruption. There are few previous discussions about how to dump the suspect disk page, e.g.: http://archives.postgresql.org/pgsql-general/2006-10/msg01372.php http://groups.google.com/group/comp.databases.postgresql.hackers/browse_frm/thread/4988752a6939f45a/fd9f12468e86dd3?hl=enlr=ie=UTF-8rnum=8prev=/groups%3Fq%3Dpg_filedump%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D20030922162322.E12708%2540quartz.newn.cam.ac.uk%26rnum%3D8#fd9f12468e86dd3 You can probably find more searching for ERROR: could not access status of transaction or pg_filedump. What I could not find was a simple and fast way to find the bad block. The error message itself is not useful in this regard, and the binary search method is anything but fast on a big table. So I wrote the following plpgsql function: create or replace function find_bad_block(p_TableName text) returns tid as $find_bad_block$ declare result tid; crtRow record; count bigint := 0; begin for crtRow in execute 'select ctid from ' || p_TableName loop result = crtRow.ctid; count := count + 1; if count % 50 = 0 then raise notice 'rows processed: %', count; end if; end loop; return result; exception when OTHERS then raise notice '%: %', SQLSTATE, SQLERRM; return result; end; $find_bad_block$ language plpgsql; This will spit out the error + the last valid block visited. If there's no bad block, you won't get the notice with the error, just the last block of the table... worked fine for me, resulting in: NOTICE: 58P01: could not access status of transaction 1038286848 find_bad_block (657780,157) (1 row) Now to finding the file I should dump: select oid from pg_database where datname = 'dbname'; oid --- 16389 (1 row) select relfilenode from pg_class where relname='table_name'; relfilenode - 20418 (1 row) The file is then 'db_path/base/16389/20418'. Or a collection of '20418.x' files if the table's data is more than 1 GB, each file being a 1GB chunk of the table... so which one to dump ? First calculate the block count in one chunk: 1024 * 1024 / block_size, where block_size is the block size in KB with which postgres was compiled. That's normally 8, but we have systems where it is set to 32. If you didn't change that yourself, it is likely 8. So in my case the block count per chunk was: 1024 * 1024 / 8 = 131072. So the chunk number will be: blockId / blocks_per_chunk = 657780 / 131072 = 5 So I should look in the file 'db_path/base/16389/20418.5'... but wait, the block id has to be made relative to the chunk file's start: chunk_block_id = block_id % blocks_per_chunk So the block id to use with pg_filedump is: 657780 % 131072 = 2420 So my final command line was: pg_filedump -if -R 2420 db_path/base/16389/20418.5 resulting in something like: [snip] Data -- Item 1 -- Length: 44 Offset: 8148 (0x1fd4) Flags: USED XMIN: 1048587 CMIN: 90130188 XMAX: 0 CMAX|XVAC: 1036845056 Block Id: 655376 linp Index: 18451 Attributes: 1375 Size: 0 infomask: 0x0200 (XMIN_INVALID) Error: Computed header length not equal to header size. Computed 28 Header: 0 1fd4: 0b001000 0c475f05 cd3d .G_= 1fe4: 0a001000 13485f05 0002 2b03 .H_.+... 1ff4: 2d03 0100 -... [snip] So I found my bad block, and the previous and following looked OK. Now I want to fix just that one block even if the rows on it are lost, as the table data is not detail-critical (massive amounts of lost data would be critical, small loss is tolerable). Now to the actual fixing: from my searches it looks like zeroing out the complete block + reindex the table seems to be the recommended solution if it is not possible to downright drop the table and restore from backup (in my case that is not possible - this error is there from last year's October, and all our backups failed from then to now - and nobody checked the logs till now when I tried to upgrade postgres via slony and failed to do it because of this error - rather telling for the importance of this DB). So, how to zero out the page ? The following message shows a way to do it: http://www.webservertalk.com/archive142-2004-7-319751.html Basically set the 'zero_damaged_pages' setting to 'on': http://www.postgresql.org/docs/8.1/interactive/runtime-config-developer.html ... and select that page again. Unfortunately this did not work for me... looks like if the page header is not corrupt but only individual tuples are a problem, the 'zero_damaged_pages' thing doesn't work. Next try: http://usr-share-man.org/man1/dd.html
Re: [GENERAL] Database corruption: finding the bad block
On Thu, 2007-07-12 at 16:18, Simon Riggs wrote: The corruption could only migrate if the WAL records themselves caused the damage, which is much less likely than corruption of the data blocks at hardware level. ISTM that both Slony and Log shipping replication protect fairly well against block corruption on the standby, but only log shipping allows you to recover the precise block, as you describe. Well, I could only speak of what I experienced, and that is that in the total of 2 former file system level corruptions the replica was corrupted too. This time it was not... Because of that I had the impression Slony will be more immune to such glitches, as it is not shuffling raw file data around... I mean you still can have data corruption replicated, but the replica will be functional. Our WAL standby did not start up at all when we had that former file corruption issue... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Excell
On Thu, 2007-06-21 at 16:45, Scott Marlowe wrote: Another option is to use your favorite scripting language and throw an excel header then the data in tab delimited format. Or even in excel xml format. Why would you need any scripting language ? COPY supports CSV output pretty well, it can even put you a header on the top. And 8.2 can COPY a SELECT too, so you can generate the CSV dump directly from joins too... on older systems I usually create a temporary table as the result of the join and then COPY that - plain SQL is all you need... I always did it this way when it comes to occasional data from/to excel. Now if it is about regularly exchanging data with excel, possibly using excel as a DB interface, probably ODBC is the only viable choice, but if the OP really needs a DB for the data, I would say using excel as the interface to it is likely a bad decision... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
Hi all, System: postgres 8.1.3p, not a released version but a CVS checkout somewhere after 8.1.3 was released. I have a table (quite big one, 2 columns, 2166381 DB relpages, 364447136 reltuples as reported by pg_class) which is mostly inserted into, very rarely deleted from. I also have a primary key on the combination of the 2 columns with the corresponding PK index. Initially I left this table out of the pg_autovacuum table, as like I said it is never updated, rarely deleted from, and I have a weekly complete DB vacuum scheduled which should theoretically be enough so autovacuum will never touch it except maybe for analyzing, which is OK. Recently however I discovered that my weekly DB wide vacuum started to crash due to an error on vacuuming this table: ERROR: failed to re-find parent key in pk_table This turned out to be a problem for autovacuum too, which spent lots of time vacuuming this table, only to (presumably) crash and start it all over next time (I can't explain otherwise the table bloating going on on this DB box, which translates in slow operation). I think I have seen some bug reports on this list with similar errors and I guess it is fixed in 8.2, and I have planned a move to 8.2 anyway next month, so I thought I just take this table out from autovacuum's list. So I did: insert into pg_autovacuum ( vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit ) values ( (select oid from pg_class where relname='table_name'), false, 5000, 0.2, 1, 0.2, -1, -1 ); Which indeed inserted one row in pg_autovacuum. Trouble: the autovacuum daemon is still taking that table and vacuums it... how do I know ? So: select l.pid,c.relname from pg_locks l, pg_class c where l.pid not in (select distinct procpid from pg_stat_activity) and l.relation=c.oid and c.relkind='r'; pid | relname ---+ 16317 | table_name (1 row) [EMAIL PROTECTED]:~$ ps auxww|grep auto postgres 16317 0.8 5.8 436008 240656 ? D13:26 0:55 postgres: autovacuum process dbname So, the autovacuum's process is locking the table, which I interpret as autvacuum vacuuming it. Question: is the autovacuum daemon somehow caching it's table list ? Can I reset somehow this ? I tried killing it's process, but it doesn't work, next time it took the table again. Thanks, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
If that table has a TOAST table you might need to mark the toast table as disabled too. Or maybe it's forcing a vacuum because it's worried about XID wraparound? OK, that might be the case, as I guess there are tables which were not successfully vacuumed in the last few months (DB wide vacuum never finished, and this system was stable enough before that I only noticed this problem recently when performance started to degrade considerably). Is there a way I can check the imminence of XID wraparound ? Thanks, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
On Fri, 2007-06-01 at 15:58, Alvaro Herrera wrote: Try reindexing the table; that should make the problem go away. Also, update to the latest of the 8.1 branch ASAP. Reindexing won't work, it would mean hours of downtime. I plan to move the DB to 8.2 via slony in ~2-3 weeks, that should take care of the bloating too. Regarding upgrade, I have to use the CVS version because I have some local patches to apply. Now what is more recommended, use the latest version on the REL8_1_STABLE branch or stick with REL8_1_9 ? I checked and there are quite a few changed files after REL8_1_9. Thanks, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
How would reindexing a table imply hours of downtime? Simply, all meaningful activities on this system will sooner or later insert into this table :-) So given that we use a connection pool we end up pretty soon with all connections waiting for an insert on this table, and at that point nothing else will work either. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
[snip] You don't need to reindex, just update. I was pretty sure I've seen the error reported and the fix too, the thing is relatively harmless except the vacuum troubles. And considering that the table itself does not need vacuuming, it would be even more harmless if it wouldn't block other stuff and if XID wraparound wouldn't be a problem. HEAD from the branch is certainly better as it contains some extra fixes (particularly one for a bug introduced in 8.1.9 which may bite you if you just pick that one ... see about bug #3116) OK, I'll go with head. Thanks, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
On Fri, 2007-06-01 at 17:40, Alvaro Herrera wrote: Huh, why do you say that it doesn't need any vacuuming? This table is only inserted and rarely deleted, so other than the XID wraparound vacuuming it is not critical. Of course it will need vacuum finally, but it can survive months without it - or till the XID wraparound occurs, which I can't estimate yet if in danger or not... how could I check that ? (I can probably find the answer if looking harder though). Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum keeps vacuuming a table disabled in pg_autovacuum
select age(datfrozenxid) from pg_database where datname = 'your database' 2 billions and you are screwed. Autovacuum starts panicking way before that, to have enough slack. dbname=# select age(datfrozenxid) from pg_database where datname = 'dbname'; age 1648762992 (1 row) Sooo... looks like time to quickly upgrade to 8.1 head. Thanks for the help, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dangers of fsync = off
[snip] Take the example of a query UPDATE tablename SET x = x + 1. When this query is erroneously issued twice, data corruption will occur. Huh ? I thought slony is replicating data, not queries... what on the master is UPDATE tablename SET x = x + 1 will translate to UPDATE tablename SET x = new_value on the slave, where new_value equals that x + 1. That's why slony is working well even if you do UPDATE tablename SET x = now(). Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Update violating constraint
If you are updating a large portion of your tree, you will probably want to throw in a vacuum in between the two updates. This should reduce the bloat caused by dead tuples in both your index and table. ... but that will only work if you can commit the first set of changes before you get to the end result, possibly having an inconsistent state for the duration of the vacuum... if you want all in one transaction, vacuum will not help. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Disadvantages on having too many page slots?
Other than hard disk space, are there any disadvantages on having a large number of page slots? It's not using hard disk space, it's using shared memory, so you might want to adjust it to make that memory available for other purposes... DETAIL: A total of 2233472 page slots are in use (including overhead). 2233472 page slots are required to track all free space. Current limits are: 1400 page slots, 1000 relations, using 82096 KB. I'm not sure how much a difference would make to have ~60M of memory more, but it could mean one more index in memory ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Import data from 8.2.3 into 8.0.8
JiÅ™Ã, I guess you should be able to do it via slony. Usually one wants to upgrade using slony, but it should work the other way around too :-) Cheers, Csaba. On Fri, 2007-04-13 at 15:09, Jiřà NÄ›mec wrote: Hello, I need to import PostgreSQL DB from 8.2.3 server 8.0.8 server. Is there some compatibility mode? When I try to import this dump PostgreSQL 8.0.8 reports errors - probably due version incompatibility. Thank you for your advices. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Import data from 8.2.3 into 8.0.8
Sorry JiÅ™Ã, that was a hasty answer from my part... I haven't used Slony by now. Do you have some advices or articles they may help? I have no ssh access at FreeBSD server box when PostgreSQL 8.0 server runs - so I hope this is no problem... Slony is quite a heavy beast to install, I'm pretty sure now that I think about it that you don't want to go that way. What you probably want is to: * make a separate schema dump + data dump; * apply the schema dump to the old box and manually fix errors; * possibly drop indexes/constraints so the data loading goes faster; * load the data to the 8.0 box - theoretically here you shouldn't have big problems, but I'm not sure about that; * recreate indexes/constraints if you dropped them; The index/constraint thing is a concern if you have big amount of data, otherwise I wouldn't touch it. HTH, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL][OT] cutting out the middleperl
I agree with everything you said except the point about the GWT. Using a framework like this you can have your query in the javascript, and pass it through directly the database and pass the data back using extremely simple (think 10 line) php or perl rpc that renders query result back in json to the browser. In fact, you can write, compile, and debug the app in java which is great advantage of gwt (imo). Of course, this is not an appropriate way of writing an application over untrusted network but otoh, isvery RAD. Untrusted is the key point here... in most of the real world cases you will be far away from such trust that you would run SQL coming from the end users browser... What you get is the limitation of working through the browser but you can kiss goodbye to deployment headaches that plague classic thick client apps because the runtime is 100% contained in the browser rendering engine and some mighty .js files. And this draws the next problem, in the moment your .js is too mighty, the users will come screaming after you once their browser starts to regularly crash, drive the client box out of memory, bog it down to a halt, etc. There's no way you can replace all the functionality of a middleware layer, but it's certainly true some cleverness can be placed at the client side. Google mail is a very good example, I love that kind of interface... Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] [Fwd: postgres dev question - MVCC slowdown]
(see below the original mail I forwarded to the list) Zsombor, As far as I know there's no way to disable the MVCC features in postgres, that's a fundamental design choice in it's workings. However, your problem can be solved by properly running vacuum on the tables you update frequently. A first good step would be to enable autovacuum in the postgres configuration file, that will take care of most of the vacuuming you need. As a long term fix would be for you to understand how postgres works and design your application so it uses it's strengths and avoids it weaknesses... your mail is not very clear if you tuned the postgres settings at all, so maybe you should read the docs if you did not do that already: Documentation contents: http://www.postgresql.org/docs/8.1/interactive/index.html Server configuration: http://www.postgresql.org/docs/8.1/interactive/runtime-config.html You should pay special attention to these: http://www.postgresql.org/docs/8.1/interactive/runtime-config-resource.html http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html HTH, Csaba. -Forwarded Message- From: Heffter Zsombor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: postgres dev question - MVCC slowdown Date: Fri, 23 Mar 2007 01:31:15 +0100 Hi Csaba, I'm new to Postgres and I'm asking you couse you are a pgdeveloper and may have a closer look to the system architecture, so maybe you will have a simple solution to my problem. I have a big table in Potgres 8.1 on Fedora5 named tblphones containing 3million records. My clients execute a sp_LockPhoneID function which queries the table via various filtering syntax and if match found, updates the table like this: declare _PhoneID integer; begin update tblsettings set lockid=lockid+1 where settingid=1; select into _PhoneID phoneid from tblphones where ... limit 1; if FOUND then update tblphones set ... where phoneid=_PhoneID; end if; update tblsettings set lockid=lockid-1 where settingid=1; The problem is, that after 3-4 hours the response time of the sp increases over 5-20 seconds. If I issue vacuum full, the response time's got normal again I think the cause is the MVCC on updates. Is there any way to disabe them I don't need concurrent running of the function, I would need to wait concurrent instances to finish the previous ones... I've tried : LOCK TABLE tblphones IN EXCLUSIVE MODE; but in this case if a function is in progress and another client executes the function, the previous instance will halt untill the next one finishes... Anyway does TABLE LOCK disable MVCC update overheads??? Thanks a lot, Zsombor ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] OT: Canadian Tax Database
And due at least in part to government (and other institutions operated by damned fools) opting for the least expensive provider rather than paying for someone who actually knows what they're doing. Just as buying cheap junk always comes back to get you, hiring incompetent fools that don't know their ass from a hole in the ground will come back to get you too. What you describe is a hundred times better than the reality... most of them actually get _expensive_ junk with some kick-back ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Importing *huge* mysql database into pgsql
I would like to convert a mysql database with 5 million records and growing, to a pgsql database. I wouldn't qualify 5 million as huge. We have here several 100 million sized tables, and I still think it's a medium sized DB... All the stuff I have come across on the net has things like mysqldump and psql -f, which sounds like I will be sitting forever getting this to work. Why do you think that would be running forever ? It should be fast enough. Or you mean it's forever measured in development effort ? I would say you should give it a go, it shouldn't be that bad... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to force disconnections from a Perl script?
On Tue, 2007-02-20 at 03:43, Tom Lane wrote: Nonsense. pg_stat_activity + kill -TERM should solve this problem reasonably well. Some of us don't trust kill -TERM 100%, which is why it's not currently exposed as a standard function, but if you're using a reasonably recent PG release it's probably safe. Oh, OK... so pg_stat_activity shows all connections now ? Or it was always like that ? For some reason I thought it will only show connections where a transaction is in progress. Idle can mean there is nothing executing at all... are those shown too ? Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to force disconnections from a Perl script?
Why irrecoverably? If the command fails, you just wait and try it again. You could use the pg_stat tables to work out who is connected and use pg_cancel_backend() to kill them. You could kill -INT them yourself. You could change the pg_hba.conf to forbid logging in and then bouncing the server. I was going to suggest the same things you did, but then I thought better... the OP is running the thing at night from a client box, not on the DB, so restart and process listing is probably off limits... There's 0 chance somebody will close at midnight it's open connection forgotten when he left office, so wait and retry would not do any good ;-) And pg_stat will only show you running queries, not the idle connections. If you only could list all the connection's pids in a client you could loop and kill them all. Of course the loop would kill itself too if not careful enough ;-) Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] too many trigger records found for relation item -
[snip] I believe this is fixed as of 8.2 --- can you duplicate it there? (No, backpatching the fix is not practical.) No, I was not able to duplicate it on 8.2, so I think it's fixed (given that on 8.1 the errors are triggered almost 100% of the runs). How sure are you about that uninterlocked getChildTableName() thing? It's possible to get a failure complaining about duplicate type name instead of duplicate relation name during CREATE TABLE, if the timing is just right. Not sure at all (I left it deliberately unsynchronized to go as fast as it can even if it errors sometimes on duplicate tables), so that might be an explanation. The error is a bit misleading though, or better inconsistent: if I would have to detect the duplicate table error condition in my code so that I can take corrective steps I would need to look for 2 error types instead of 1 - if I only knew that I have to. And BTW, I have seen something similar while creating temporary tables which should not conflict even with the same table name I think... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] too many trigger records found for relation item -
[Update: the post didn't make it to the list probably due to the attachment, so I resend it inlined... and I was not able to trigger the same behavior on 8.2, so it might have been already fixed.] [snip] Well, if you can show a reproducible test case, I'd like to look at it. OK, I have a test case which has ~ 90% success rate in triggering the issue on my box. It is written in Java, hope you can run it, in any case you'll get the idea how to reproduce the issue. The code is attached, and I list here some typical output run against an 8.1.3 postgres installation. The first exception is strange on it's own, it was produced after a few runs, might be caused by another issue with creating/dropping tables (I think I have seen this too some time ago). I'll go and run it against 8.2 and see if the issue is still there. My problems on the integration box turned out to be postgres logging set to too high level and running out of disk space due to log amount... Cheers, Csaba. Error executing sql: CREATE TABLE test_child_0 (a bigint primary key references test_parent(a)) org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint pg_type_typname_nsp_index at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329) at com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155) at com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:91) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76) Error executing sql: DROP TABLE test_child_0 com.domeus.trials.TestChildTableCreationIndependent$MissingTableException at com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:158) at com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76) Error executing sql: DROP TABLE test_child_251 org.postgresql.util.PSQLException: ERROR: 2 trigger record(s) not found for relation test_parent at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329) at com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155) at com.domeus.trials.TestChildTableCreationIndependent.access$200(TestChildTableCreationIndependent.java:12) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.doWork1(TestChildTableCreationIndependent.java:98) at com.domeus.trials.TestChildTableCreationIndependent$WorkerThread.run(TestChildTableCreationIndependent.java:76) Error executing sql: DROP TABLE test_child_258 org.postgresql.util.PSQLException: ERROR: too many trigger records found for relation test_parent at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329) at com.domeus.trials.TestChildTableCreationIndependent.executeQuery(TestChildTableCreationIndependent.java:155) at
Re: [GENERAL] too many trigger records found for relation item -
On Mon, 2007-01-22 at 20:56, Lenorovitz, Joel wrote: [snip] ERROR: too many trigger records found for relation item I've got this error on a development data base where we were continuously creating new child tables referencing the same parent table. The responsible code is in src/backend/commands/trigger.c, and I think it only happens if you manage to create/drop a new trigger (which also could be a FK trigger created by a new foreign key referencing that table, as in our case) exactly between that code gets the count of the triggers and processes them. In any case it should be a transient error, i.e. it should only happen when you heavily create/drop triggers... our integration test case was actually heavily creating new child tables, so that's how it happened for us. In a production scenario I won't be creating all the time new triggers in parallel with other heavy activities, so it doesn't bother me. Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] too many trigger records found for relation item -
On Tue, 2007-01-23 at 14:49, Brandon Aiken wrote: Nevertheless, the database should be able to handle any combination of syntactically correct SQL statements without throwing errors and maintaining the database in a consistent state. If what you're saying is right, the error thrown here is not a user configuration error, but an RDBMS implementation error. A development database is still obviously an important role for PostgreSQL to function in (as far as PostgreSQL's dev team is concerned, a development database *is* a production use since once of *their* end-users experiences the problem) and it needs to be able to handle cases such as this with no problems. And no matter how unlikely it is to be in a production environment, *someone* will try to modify their schema dynamically like this. I'm wondering if there is a race condition in CREATE or DROP with respect to triggers and foreign keys. If that's the case, it's going to affect someone eventually. When I said it doesn't bother me, I meant it literally me, not implying the Postgres community in any way :-) And I did report it that time (I can't find the mail, but I think Tom had a look at it and probably decided it is not top priority). Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] too many trigger records found for relation item -
On Tue, 2007-01-23 at 15:43, Tom Lane wrote: Csaba Nagy [EMAIL PROTECTED] writes: The responsible code is in src/backend/commands/trigger.c, and I think it only happens if you manage to create/drop a new trigger (which also could be a FK trigger created by a new foreign key referencing that table, as in our case) exactly between that code gets the count of the triggers and processes them. All such code takes exclusive lock on the table, so the above explanation is impossible. Well, in that case it must be some other bug as it is readily reproducible here. My nightly integration has this error each night. The reason I don't panic (although I thought I reported it, but I can't find the mail) is that rerunning the failed things succeeds, and the failed operation is a table creation which is never critical for us in the sense that it can be retried as many times as necessary. The test data base is an 8.1.3 installation. The queries failing for the last run were: - an insert into the parent table; - a create table which was creating a child table to the same parent table the other query was inserting; I'm not sure if the 2 failures were connected or not. I also can't confirm if it also happens on 8.2 as my integration is still not running through on 8.2... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Index bloat of 4x
On Fri, 2007-01-19 at 20:03, Jeremy Haile wrote: Is it feasible to add a reindex concurrently that doesn't lock the table for the rebuild, then locks the table when doing a second pass to pickup rows that were changed after the first pass? Or something like that IIRC, the objection was the deadlock potential of any lock upgrade, and the problems of impossible cleanup on failure if something changed the permissions of the executing user in the meantime. That's why I think it would make sense if it could be done by a privileged background thread like the autovacuum ones, so the lock upgrade can be tried without blocking, as it can take quite some time till it succeeds, and the cleanup is possible due to the privileged nature of the executor. If there would be such a facility it would also need some policies to control time windows and priorities just as for autovacuum, that's why I connect it in my usage-focused mind to autovacuum. Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Index bloat of 4x
[snip] I afraid I don't see how any of the answers I saw discussed fit a 24x7 operation. Reindex, drop index, vacuum full, ... they all block production queries of one sort or another for significant periods of time (minutes) on large (multi/tens of GB) tables, and thus are infeasible for true 24x7 operations.[snip] This is not completely true, as of 8.2 there is an online index build, and if that could be used in a background thread to rebuild the index and replace the bloated one once it's finished, that would be a non-blocking operation which could be done in 24x7 situations. There are some issues with using the online index build for replacing an existing index, but if those could be solved it would be a viable solution I think... Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Index bloat of 4x
[snip] Come to think of it, an auto-reindex option might be nice in core someday. TODO item? Marry it with autovacuum + online index build, and it will be cool ;-) BTW, having a privileged background thread doing the reindex could be a solution to most of the objections regarding online reindex, as the thread would be privileged enough already to be able to back out if something fails (part of the objections), and it could stay around long enough to only lock tentatively in a loop in order to avoid deadlocks (another part of the objections). If it would be also marked the same as the vacuum threads not to block other vacuums, then it would also not be a problem that it runs 2 days... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Autovacuum Improvements
On Mon, 2007-01-08 at 22:29, Chris Browne wrote: [snip] Based on the three policies I've seen, it could make sense to assign worker policies: 1. You have a worker that moves its way through the queue in some sort of sequential order, based on when the table is added to the queue, to guarantee that all tables get processed, eventually. 2. You have workers that always pull the cheapest tables in the queue, perhaps with some sort of upper threshold that they won't go past. 3. You have workers that alternate between eating from the two ends of the queue. Only one queue is needed, and there's only one size parameter involved. Having multiple workers of type #2 seems to me to solve the problem you're concerned about. This sounds better, but define cheapest in #2... I actually want to continuously vacuum tables which are small, heavily recycled (insert/update/delete), and which would bloat quickly. So how do you define the cost function for having these tables the cheapest ? And how will you define the worker thread count policy ? Always 1 worker per category, or you can define the number of threads in the 3 categories ? Or you still have in mind time window policies with allowed number of threads per worker category ? (those numbers could be 0 to disable a a worker category). Other thing, how will the vacuum queue be populated ? Or the queue here means nothing, all workers will always go through all tables to pick one based on their own criteria ? My concern here is that the current way of checking 1 DB per minute is not going to work with category #2 tables, they really have to be vacuumed continuously sometimes. Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] Advice needed on using postgres in commercial
On Mon, 2007-01-08 at 20:11, Ron Mayer wrote: [snip] That's unlikely to work anyway. Organizations protecting valuable data using technical approaches (DVDs, etc) find it gets out anyway. Since you'll ship a client that can decrypt the data anyway, anyone with a debugger could decrypt it (unless you only want it to run on Trusted computing platform / palladium computers). Hmm, I do hope those techniques will never be good enough to stop hackers cracking them. But this is a philosophical and off topic question... the point is, I don't believe there is any kind of software/hardware out there that can't be cracked once it gets in hostile hands. On to the off topic thing, I really think all data should be legally forced to be free... research would have to change and maybe stumble a bit in the beginning, but I'm completely sure all interested parties would be forced to better cooperate and that would boost the advancement of science in the long term. Hiding research results will not work these days, so companies would be forced to do it in cooperation with all other players... of course not convenient for todays big corporations, but maybe they should disappear anyway. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Autovacuum Improvements
On Tue, 2007-01-09 at 17:31, Matthew T. O'Connor wrote: Without getting into all the details, the autovacuum naptime is a GUC variable right now, so it can be much more frequent than the current default which is 60 seconds. Hmm, for some reason I thought the granularity is minutes, but it is indeed in seconds... one more thing learned. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Improvements
On Tue, 2007-01-09 at 17:36, Csaba Nagy wrote: On Tue, 2007-01-09 at 17:31, Matthew T. O'Connor wrote: Without getting into all the details, the autovacuum naptime is a GUC variable right now, so it can be much more frequent than the current default which is 60 seconds. Hmm, for some reason I thought the granularity is minutes, but it is indeed in seconds... one more thing learned. OK, so after checking my config, it is still not optimal because it refers to all the data bases in the cluster. I have setups where I have multiple data bases in the same cluster, with various degrees of activity... some of them should be checked continuously, some rarely... so now if I let the default 60 seconds, each data base will be checked in db_count * (60 + vacuum time) seconds. This is not optimal... some of the DBs have way much activity than others. Those I would like to be checked say each 10 seconds, the rest each 5 minutes... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Autovacuum Improvements
On Sun, 2006-12-24 at 03:03, Christopher Browne wrote: [snip] Seems to me that you could get ~80% of the way by having the simplest 2 queue implementation, where tables with size some threshold get thrown at the little table queue, and tables above that size go to the big table queue. That would most definitely not cut it for me, I have more than 2 categories of tables: - a few small but very often updated/inserted/deleted table: these must be continuously vacuumed, your little queue is not good enough for that, as even the round trip between the small tables could lead to bloat on them; - a few small and moderately updated, that could live with the little queue; - a few big and frequently updated, but which only have a small percentage of rows actively updated at any time: those could live with the big queue; - the rest which are rarely updated, I would put those in a separate queue so they won't affect the rest, cause vacuuming them is really mostly not critical; The point is that I'm not sure there couldn't be even more reasons to split the tables in even more queues based on the importance of vacuuming them combined with update rate and their size. If I can set up my own queues I can experiment with what works best for me... for the base setup you could set up some default queues. I wonder though how would you handle dynamics of tables, I mean when will a small table which grows start to be considered a big table for the purpose of putting it in one queue or the other ? I guess it would be done on analyzing the table, which is also handled by autovacuum, so tables with no vacuum queue settings could go to one of the 2 default queues you mention. That should keep any small tables from getting vacuum-starved. I'd think the next step would be to increase the number of queues, perhaps in a time-based fashion. There might be times when it's acceptable to vacuum 5 tables at once, so you burn thru little tables like the blazes, and handle larger ones fairly promptly. And other times when you don't want to do *any* big tables, and limit a single queue to just the itty bitty ones. This is all nice and it would be cool if you could set it up per vacuum queue. I mean how much more effort would be to allow vacuum queues with generic settings like time windows with max number of threads for each window, and let the user explicitly assign tables to those queues, instead of hard coding the queues and their settings and assign tables to them based on size or any other heuristics ? For the average application which needs simple settings, there could be a default setup with the 2 queues you mention. If it would be possible to set up some rules to assign tables to queues based on their properties on analyze time, instead of explicitly assigning to one queue or other, that would be nice too, and then you can completely cover the default setup with those settings, and allow for more complex setups for those who need it. This approach allows you to stay mostly heuristic-based, as opposed to having to describe policies in gratuitous detail. I agree that for simple setups that would be OK, but like I said, if it would be easy enough to code that heuristics, and provide some sane setup as default, and then let the user optimize it, that would be a cool solution. Now it's true I don't really know how would you code 'assign all tables which are smaller than x rows to vacuum queue little-tables' ... maybe by providing a function to the queue which matches on the table ? And you can change that function ? No idea, but it probably can be done... Having a mechanism that requires enormous DBA effort and where there is considerable risk of simple configuration errors that will be hard to notice may not be the best kind of feature :-). I think most people will not want to touch the default settings unless it will not work good enough for them. I definitely not like too much that I had to set up some cron jobs beside autovacuum, as they are most definitely not doing optimal job, but autovacuum was not doing that either, and I'm afraid a 2-queue system would also not do it at least for the queue-like tables I have, which must be vacuumed continuously, but only if they need it... that's what I expect from autovacuum, to vacuum all tables in the proper periodicity/time window for each of them, but only if they need it... and I can imagine way more such periodicity/time window settings than 2. Now if autovacuum could figure out on itself all those settings, that would be even cooler, but if I can set it up myself that would be good enough. Actually I think all vacuum patterns could be automatically figured out by looking at the statistics AND the dynamics of those statistics (i.e. it changes in bursts, or steadily increasing over time, etc.), and possibly also the read access statistics (there's no big reward in too frequently vacuuming a table which is only inserted and deleted and rarely read), and
Re: [GENERAL] Partitioning Vs. Split Databases - performance?
Ben, On Thu, 2006-12-21 at 20:10, Benjamin Smith wrote: I'm breaking up a database into several sets of data with similar layout. (we currently have multiple customers using a single database and tableset, we're splitting it out to give us more wiggle room) We have here a very similar situation. We started out with one schema containing all customers, based on a customerid. 1) Copy out the data specific to a customer and load into a separate database for that customer, or We went with 1, combined with the original solution we had... i.e. we keep the same schema for customers, and if the number of customers/size of them grows too much, we move them out to another machine. If they need to be isolated from the rest, we move them to another DB possibly on the same machine... but the original schema is still there to allow us having multiple small customers on the same DB. Separate DBs on the same box for each customer would be a maintenance nightmare because we have a (sizable) cluster of application boxes connecting, and all of them must be configured with all connection data to all data bases... so we must keep the nr. of data bases to the minimum. Not to mention that for each DB we have a connection pool, and the nr. of connections would grow too much if we would have one connection pool per customer. Grouping customers according to their business status allows us to upgrade the schema separately for each DB, so we have some choice in when to upgrade different customers to the next version of the application... although usually we make the DB changes in such a way that multiple versions of the software can work with the upgraded schema, and we can upgrade customers from the same DB independently, but this IS a concern when you have the same schema for all customers (we do have occasional pressure from some customers to upgrade sooner than we would like). This does not exclude the possibility of partitioning the biggest tables in your schema per customerid or whatever else you can use to partition them (our biggest table does not have customerid as a column, but it still could be partitioned by something else). Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Autovacuum Improvements
On Thu, 2006-12-21 at 18:41, Alvaro Herrera wrote: From all the discussion here I think the most benefit would result from a means to assign tables to different categories, and set up separate autovacuum rules per category (be it time window when vacuuming is allowed, autovacuum processes assigned, cost settings, etc). I doubt you can really define upfront all the vacuum strategies you would need in real life, so why not let the user define it ? Define the categories by assigning tables to them, and the rules per category. Then you can decide what rules to implement, and what should be the defaults... Hmm, yeah, I think this is more or less what I have in mind. Cool :-) Can I suggest to also consider the idea of some kind of autovacuum process group, with settings like: - number of processes running in parallel; - time windows when they are allowed to run; Then have the table categories with all the rest of the threshold/cost/delay settings. Then have the possibility to assign tables to categories, and to assign categories to processing groups. I think this would allow the most flexibility with the minimum of repetition in settings (from the user perspective). Cheers, Csaba. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Autovacuum Improvements
On Thu, 2006-12-21 at 18:03, Matthew T. O'Connor wrote: The maintenance window design as I understand it (Alvaro chime in if I get this wrong) is that we will be able to specify blocks of time that are assigned specific autovacuum settings. For example we might define a maintenance window of Sunday morning from 1AM - 8AM, during that time all autvacuum thresholds will be dropped to .01, that way everything will get vacuumed that needs it during that window. Outside of the window default autovacuum settings apply. Changing thresholds is not a viable solution for all the cases. If I have a huge table with many indexes, I still don't want to vacuum it unless there are a significant amount of dead pages so that the sequential scan of it and it's indexes pays off. In this case dropping the autovacuum threshold would be totally counterproductive even at night. This solution would only rule out really static tables, which don't change almost at all. In real life there are many more possible data access scenarios... From all the discussion here I think the most benefit would result from a means to assign tables to different categories, and set up separate autovacuum rules per category (be it time window when vacuuming is allowed, autovacuum processes assigned, cost settings, etc). I doubt you can really define upfront all the vacuum strategies you would need in real life, so why not let the user define it ? Define the categories by assigning tables to them, and the rules per category. Then you can decide what rules to implement, and what should be the defaults... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Second attempt, roll your own autovacuum
On Tue, 2006-12-19 at 07:28, Tom Lane wrote: Glen Parker [EMAIL PROTECTED] writes: I am still trying to roll my own auto vacuum thingy. Um, is this purely for hack value? What is it that you find inadequate about regular autovacuum? It is configurable through the pg_autovacuum catalog --- which I'd be the first to agree is a sucky user interface, but we're not going to set the user interface in concrete until we are pretty confident it's feature-complete. So: what do you see missing? I'm not sure what the OP had in mind, but the thing which is missing for us is a time window restriction sort of thing. What I mean is to make sure a vacuum will never kick in in the main business hours, but only at night at pre-specified hours, and only if the vacuum threshold was met for the delete/update counts. It would be nice if there could be a flexible time window specification, like specifying only some days, or only weekends, or each night some specific hours... but just one time window would be a big improvement already. Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Second attempt, roll your own autovacuum
From my POV, autovacuum is doing a very good job, with the exception of: - There might be time-based exclusions to the effect that large tables oughtn't be processed during certain periods (backup time?) Either (per table!) exception or permission based control of when a table can be vacuumed is needed to avoid vacuuming big tables during peek business periods. While this can be alleviated by setting lower vacuum cost settings, and it won't block anymore other vacuums, it will still need the multiple vacuum stuff to still process small tables: - One might have *two* consumers, one that will only process small tables, so that those little, frequently updated tables can get handled quickly, and another consumer that does larger tables. Or perhaps that knows that it's fine, between 04:00 and 09:00 UTC, to have 6 consumers, and blow through a lot of larger tables simultaneously. So one of the 2 might be enough. I guess time-based exclusion/permissions are not that easy to implement, and also not easy to set up properly... so what could work well is: - allow a priority setting per table in pg_autovacuum; - create a vacuum thread for each priority; - each thread checks it's own tables to be processed based on the priority setting from pg_autovacuum; - there have to be a default priority for tables not explicitly set up in pg_autovacuum; - possibly set a per priority default vacuum cost and delay; In 8.2 the different vacuum threads for the different priorities won't step on each other toes, and the default settings for the priorities can be used to create some easily manageable settings for vacuuming table categories with different update/delete patterns. There could be some preset priorities, but creating new ones would be useful so the user can create one per table update/delete pattern. Maybe priority is not the best word for this, but I can't think now on other better... Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Second attempt, roll your own autovacuum
Alternatively, perhaps a threshold so that a table is only considered for vacuum if: (table-size * overall-activity-in-last-hour) threshold Ideally you'd define your units appropriately so that you could just define threshold in postgresql.conf as 30% (of peak activity in last 100 hours say). No, this is definitely not enough. The problem scenario is when autovacuum starts vacuuming a huge table and that keeps it busy 10 hours and in the meantime the small but frequently updated tables get awfully bloated... The only solution to that is to have multiple vacuums running in parallel, and it would be really nice if those multiple vacuums would be coordinated by autovacuum too... Cheers, Csaba. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Online index builds
Yeah, we could add defenses one by one for the cases we could think of, but I'd never feel very secure that we'd covered them all. What you all forget in this discussion is that reindexing concurrently would have to be a highly administrative task, controlled by the DB admin... so whoever has a big index to be reindexed can schedule it so that no other schema changes occur to the table until the reindex is finished. So an implementation which optimistically builds the new index concurrently while holding no lock, and then hopes for the 3rd transaction to be able to get the exclusive lock and be able to swap the new index in the place of the old index, and error out if it can't - it is perfectly acceptable. The waisted effort when dropping the newly created index on error is easily avoidable by not doing anything which would cause an error in that phase... and it is easily controlled by the DBA. The only thing needed is documentation to point it out. I didn't understand completely the discussion here, and if there are some problems detecting the error conditions in the index swap phase, that's a problem... but if it is possible to reliably detect cases where the swap is not possible because something changed in between, erroring out will be acceptable for the purpose of this command... Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Online index builds
On Wed, 2006-12-13 at 17:12, Tom Lane wrote: Csaba Nagy [EMAIL PROTECTED] writes: So an implementation which optimistically builds the new index concurrently while holding no lock, and then hopes for the 3rd transaction to be able to get the exclusive lock and be able to swap the new index in the place of the old index, and error out if it can't - it is perfectly acceptable. It would maybe be acceptable if there were a way to clean up the mess after a failure, but there wouldn't be ... With the mess you refer to the new index, and the fact it is impossible to delete it if not possible to replace the old one ? I fail to see why... you WILL get an exclusive lock, so you should be able to delete the index. The deadlock is not an issue if you release first the shared locks you hold... If mess means that it's impossible to tell that you can or can't safely replace the index, then that's a problem, but I think the scenarios you thought out and would break things are detectable, right ? Then you: take the exclusive lock, check if you can still safely replace the index, do it if yes, delete the new index otherwise or on failure to swap (to cover unexpected cases). If you can't delete the new index cause somebody changed it in the meantime (that must be a really strange corner case), then bad luck, nobody is supposed to do that... While I'm not familiar enough with how postgres handles locking, wouldn't be also possible for DDLs to first also acquire a lock which would only lock other DDLs and not DMLs ? In that case you could get that lock first and hold it through the second phase, and make the second phase also swap the indexes after also acquiring the full exclusive lock. That could potentially still deadlock, but the chance to do so would be a lot smaller. I think the above is not clear enough... what I mean is to make all DDLs get 2 locks: - first an DDL exclusive lock which blocks other DDLs from getting the same; - second a full exclusive lock which blocks any other locks; Between the 2 there could go some operation which is not blocking normal operation but needs protection from other concurrent DDL. If only DDLs do this and always in this order, there's no deadlock potential. Disadvantage is the very need to place one more lock... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Re: Male/female
On Fri, 2006-12-08 at 17:39, Bernhard Weisshuhn wrote: On Fri, Dec 08, 2006 at 05:26:22PM +0100, Harald Armin Massa [EMAIL PROTECTED] wrote: Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! I want replication WITH that girl! Any chance for 8.3? Well, all of you who ask for this, don't forget that the main mantra of open source is scratch your own itch ;-) So go out and get it yourself... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql bug found?
FOR daycnt IN 1..31 LOOP How about months with less than 31 days ? What do you get for those if the day is 31 ? Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] FK locking still too strong
Hi all, While postgres 8.x improved a lot the locking issues related to foreign keys, the locking used is still stronger than needed. The following test case deadlocks on postgres but goes through on oracle: preparation of tables: create table test1(col_fk integer primary key, col_1 integer); create table test2(col_2 integer primary key, col_fk integer references test1(col_fk)); insert into test1 (col_fk, col_1) values (1, 1); insert into test1 (col_fk, col_1) values (2, 2); session_1: begin; update test1 set col_1 = 10 where col_fk = 1; session_2: begin; insert into test2 (col_2, col_fk) values (1, 2); session_1: -- this locks on postgres, does not on oracle update test1 set col_1 = 20 where col_fk = 2; session_2: -- deadlock on postgres, goes through on oracle insert into test2 (col_2, col_fk) values (2, 1); The problem is that this deadlock cannot be solved by predictable ordering of any of the 2 sets of statements, because the foreign key is not sure to keep the same ordering relationship between the primary keys of the tables (i.e. there can be a smaller col_fk associated to a bigger col_2 as well as the other way around). This kind of deadlock is causing us quite some trouble as we have some operations like this where circumventing it would cause unacceptable contention (practically serializing all our operation which we carefully distribute to multiple processing boxes), even if the deadlock wouldn't be per se a problem (but it is, there are complex updates/inserts on thousands of rows in those transactions). Our solution is to patch the postgres sources to omit the shared lock of the parent row altogether. This leaves the possibility that some orphan child records slip in if the parent row is deleted while the child row is updated/inserted, but this is causing us less trouble than the deadlock, as the delete rate is quite low in our application compared to the update rate, and the orphan rows will be ignored anyway (of course this would be unacceptable in many applications, it just happens to be OK for us). In fact, the postgres regression suite is happily going through with the patch applied (no locking on FK parent rows). And the patched version will also not lock/deadlock on the above test case... Now the real question: is it possible to further relax the lock needed by the foreign key relationship ? I guess this has something to do with some form of predicate locking, and I wonder if there is a simple way to achieve this for supporting the FK locking. The fact that Oracle doesn't lock/deadlock tells me that it must be possible to do it, although it is likely not easy... Cheers, Csaba. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] AutoVacuum on demand?
[snip] I think the idea is to edit the postgresql.conf file on the fly and send a SIGHUP to the postmaster. I haven't ever heard of anyone doing that, but I don't see any reason why it wouldn't work. It works, I did it for a while with the statement_timeout to change it globally over night when admin tasks were running. For the statement_timeout thing it wasn't optimal however, finally I had to ditch the thing... but it can be done. See below my shell script attached, it did work for me, YMMV. Cheers, Csaba. #!/bin/sh CONF_FILE=/var/lib/postgres/data-8_1_3p/postgresql.conf PG_CTL=/usr/local/postgres813p/bin/pg_ctl PG_DIR=/var/lib/postgres/data-8_1_3p TIMEOUT=0 if [ -n $1 ]; then TIMEOUT=$1 fi cat $CONF_FILE | sed s/^\(statement_timeout = \)[0123456789]\+/\1$TIMEOUT/ $CONF_FILE.new mv $CONF_FILE.new $CONF_FILE $PG_CTL -D $PG_DIR reload ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] AutoVacuum on demand?
I'm confused, what does statement timeout have to do with this? I was assuming you would edit autovacuum = off to autovacuum = on wouldn't that work? Sorry for the confusion, I was thinking about the procedure of changing programatically the config file and reload it, all this from a crontab. That is working fine... only I did it for statement_timeout not autovacuum, so you have to modify the script if you want to use it for that. Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
