Re: [GENERAL] cloning postgres-xc
This may not be the best place to ask these questions and you could have considered using postgres-xc-general mailing list from the Postgres-XC project site. Anyways, see my comments below. On Tue, Feb 12, 2013 at 11:06 AM, Zenaan Harkness z...@freedbms.net wrote: Does somone know the object overlap likely between pg and pgxc repositories? There is quite a lot overlap. Even though Postgres-XC has changed many files and added many other, there is still plenty of common code. I ask because I could just git clone pgxc, or I could add a remote for pgxc to my pg git clone, and make sure the branches are added, and fetch that remote. And in this way, common files/ objects are properly shared in one repo, rather than duplicated. Thoughts? ISTM that's the right way, especially if you're interested in keeping PG code as well. This way, you will avoid a lot of duplicates and can also quickly do a git diff between files of these two projects. I find that very convenient at times. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Order of granting with many waiting on one lock
On Mon, Feb 11, 2013 at 12:26 PM, Chris Angelico ros...@gmail.com wrote: Is there any sort of guarantee that all the processes will eventually get a turn, or could two processes handball the lock to each other and play keepings-off against the other eighteen? That should not happen. There are instances when a lock requester will be promoted ahead of others to avoid deadlock, but in the scenario you described, no single process will starve forever. See following comment in ProcSleep() function under src/backend/storage/lmgr/proc.c which is self-explanatory. /* * Determine where to add myself in the wait queue. * * Normally I should go at the end of the queue. However, if I already * hold locks that conflict with the request of any previous waiter, put * myself in the queue just in front of the first such waiter. This is not * a necessary step, since deadlock detection would move me to before that * waiter anyway; but it's relatively cheap to detect such a conflict * immediately, and avoid delaying till deadlock timeout. * * Special case: if I find I should go in front of some waiter, check to * see if I conflict with already-held locks or the requests before that * waiter. If not, then just grant myself the requested lock immediately. * This is the same as the test for immediate grant in LockAcquire, except * we are only considering the part of the wait queue before my insertion * point. */ Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] REINDEX deadlock - Postgresql -9.1
On Thu, Feb 7, 2013 at 2:08 PM, Anoop K anoo...@gmail.com wrote: I have the setup in problem state. But I am not able to make psql connections to view the lock details. psql connections are hanging. Is there any other info which can be collected in this state ? Try attaching each process involved in the deadlock to gdb and print the call stack. That may or may not be useful, but given your situation I wonder if you have a deadlock at LWLock level. Do you have any external module installed ? Or any triggers written in C ? Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] REINDEX deadlock - Postgresql -9.1
On Thu, Feb 7, 2013 at 8:19 PM, Anoop K anoo...@gmail.com wrote: In an attempt to get access, I ended up killing a postgres process and the whole thing recovered from hang state. Now don't have more data points to debug. Sorry, I was going to ask what REINDEX was really indexing ? System tables ? ISTM that the idle in transaction connection was holding some kind of a heavy weight lock on one of the catalog tables and that may be causing all other transactions to just wait. For example, I can reproduce this by doing the following: Session 1: BEGIN; REINDEX TABLE pg_class; stay idle in transaction Session 2: REINDEX TABLE pg_attribute; will hang Try starting a new Session 3: will hung The stack traces of these processes will look similar to what you posted. And as soon as you end the transaction on the first session, everything will proceed. You may want to look at your application code and see if you're causing this kind of deadlock (or livelock, not sure what is a better term to describe this situation) Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Unusually high IO for autovacuum worker
On Fri, Feb 1, 2013 at 1:44 PM, Vlad Bailescu v...@mojitosoftware.com wrote: Hi everyone, We are experiencing a strange situation after upgrading our Postgres from 8.4 to 9.1 and our OS from Debian 6 to Ubuntu 12.04 LTS. We have an application where we are collecting timestamp-based data. Since a month of data goes over 30M rows (and growing each month), we decided to partition the data on an year-and-month basis. We basically have one master table and partition tables like master_y2013m01 and so on. Because of our application stack (Hibernate ORM) we use a before_insert trigger to insert rows into the right partition and in the master table and then an after_insert trigger to delete them from master table (we need the original insert to return the inserted row info). This means the master table becomes quickly populated with dead tuples and triggers the autovacuum frequently (we are using default settings for autovacuum). After upgrading our DB from 8.4 to 9.1 (and moving from Debian 6 to Ubuntu 12.04) we noticed a large increase in IO and traced it back to auto vacuum: iotop and collectd report bursts of 4-6M/s taking 90 seconds followed by a 30 seconds pause. That's a lot of data to chew on especially since the master table is only a couple of megabytes (indexes included). Can anyone please explain why this is happening and how we can get around it? Do you know for sure that its the master table that generating all the vacuum traffic ? What about the partition tables ? Do they get any updates/deletes ? It might be useful if you could turn autovacuum logging ON and see which tables are getting vacuumed and correlate that with the increased IO activity that you're seeing. Did you change any other parameters like checkpoint timeout/segments etc ? It might be worthwhile to log checkpoint activities as well to be doubly sure. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Unusually high IO for autovacuum worker
On Fri, Feb 1, 2013 at 3:24 PM, Vlad Bailescu v...@mojitosoftware.comwrote: Stats show only 3 tables get frequently autovacuumed Table Name Tuples inserted Tuples updated Tuples deleted Tuples HOT updated Live Tuples Dead Tuples Last vacuum Last autovacuum Last analyze Last autoanalyze Vacuum counter Autovacuum counter Analyze counter Autoanalyze counter Size vehicle_position 4689127 0 4689127 0 0 1985 2013-02-01 11:46:46.441227+02 2013-01-31 14:48:00.775864+02 2013-02-01 11:46:46.494234+02 0 3761 1 3754 4016 kB vehicle 2124 934640 0 924003 2124 390 2013-02-01 11:47:46.067695+02 2013-02-01 11:47:46.325444+02 0 1293 0 2038 2448 kB input_current 10032 1649206 0 1635902 10032 1728 2013-02-01 11:45:46.0681+02 2013-02-01 11:45:46.215048+02 0 442 0 1294 2336 kB So sure there are more than just one table involved here. Also, the last column in the output above shows that these are fairly large tables. In fact, if the last column is to be trusted, they are as large 37GB, 20GB and 12GB respectively. Note the kB at the end of those values. Also given the large number of inserts/deletes, I would doubt if the table can really remain that small. May be auto-vacuum is truncating most of it and thats why you see only a few MBs before it swells again by the time next vacuum cycles runs. Whats your rate of insertion/deletion in the first table ? You also seem to be updating the last two tables quite heavily, but most of them are HOT updates, so your tables should not bloat too much. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [GENERAL] Unusually high IO for autovacuum worker
On Fri, Feb 1, 2013 at 5:19 PM, Vlad Bailescu v...@mojitosoftware.comwrote: Pavan, it seems there's a small issue with copy-paste and column text-align. Table sizes are: 4136 kB 2448 kB 2336 kB Ah OK. I see. 2012-12-05 00:44:23 EET LOG: automatic analyze of table fleet.fleet.vehicle_position system usage: CPU 4.46s/0.61u sec elapsed 465.09 sec This is the interesting piece of information. So its the auto analyze thats causing all the IO activity. That explains why it was a read only IO that we noticed earlier. Whats happening here, and something that changed from 8.4 to 9.1, is that whenever the parent table is analyzed, the child tables are also automatically analyzed. I don't remember the rational for doing this change, but in your case the analyze on the parent table itself is quite useless because even though you inserting a large number of new tuples, you are also immediately deleting them. I don't want to comment on the design aspect of that, but you should be able to fix this problem by disabling auto-analyze on the parent table. Having said that, I don't see an easy way to just disable auto-analyze on a table. You can run ALTER TABLE foo SET (autovacuum_enabled = false), but that would also disable auto-vacuum, which you certainly don't want to do because the parent table would just keep growing. You can set autovacuum_analyze_threshold to an artificially high value to mitigate the problem and reduce the frequency of auto-analyze on the table or see if you can completely avoid insert/delete on the parent table. ALTER TABLE vehicle_position SET (autovacuum_analyze_threshold = 20); Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [GENERAL] Revoke drop database even for superusers?
On Sun, Dec 2, 2012 at 7:53 AM, Edson Richter edsonrich...@hotmail.comwrote: Em 01/12/2012 22:22, Chris Angelico escreveu: On Sun, Dec 2, 2012 at 10:20 AM, Edson Richter edsonrich...@hotmail.com wrote: I've put both files in ~/deny_drop folder, and executed make: # LANG=C make Makefile:13: ../../src/Makefile.global: No such file or directory Makefile:14: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. The most common thing to do before running 'make' is to look for a configure script: $ ./configure $ make ChrisA Yes, that was my first tought. But there is no configure script in the folder. Must be something else... While I haven't looked at the code, the error message looks very similar to what you will see while building contrib modules. So try this: $ export USE_PGXS=1 $ export PATH=$path_to_your_pg_config$:$PATH Set the PATH so that the correct pg_config command is used. It must come from the same installation that your server is running. $ make clean $ make $ make install Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: [GENERAL] ERROR: volatile EquivalenceClass has no sortref
On Thu, Nov 22, 2012 at 11:08 AM, Ranjeet Dhumal jeetu.dhu...@gmail.comwrote: Hi Tom , Sorry but i didn't understand that If this is a bug from postgres version then how the same query will be worked if i recreated the tables and with same version of postgres. This could be related to the plans that are chosen by the optimizer and this specific error might be coming only when a certain plan is chosen. When you recreated the table, the planner may have chosen a different plan because the stats were different, and hence you did not see the issue again. If you can reproduce the problem, it will be a good idea to run EXPLAIN to see the query plan. Thanks, Pavan
Re: [GENERAL] Where is the char and varchar length in pg_catalog for function input variables
On Wed, Sep 5, 2012 at 9:10 PM, jam3 jamort...@gmail.com wrote:
I have searched and searched and just cannot find the maximum lengths for
input variables in a function
i.e.
CREATE FUNCTION test(input1 char(5), input2 varchar(50))
RETURNS void AS
$$RAISE NOTICE('%,%'), $1, $2;$$
LANGUAGE plpgsql;
Where do I find the 5 and the 50 it has to be somewhere I have searched
through
pg_proc
pg_type
pg_attribute (whose attlen only relates to tables)
pg_type
and all possible manner of joining these tables.
Hmm. I only looked at the code and hence don't have a definite answer. But
it seems that information is not stored anywhere. That might explain why
the function you mentioned accepts parameters with any character length.
Thanks,
Pavan
Re: [GENERAL] values from txid_current()
On Wed, Sep 5, 2012 at 10:53 PM, Sahagian, David david.sahag...@emc.comwrote: Why are the Messages displayed by my pgAdmin sql window like this . . . INFO: 7902 INFO: 7903 INFO: 7904 instead of what I expected . . . INFO: 7902 INFO: 7904 INFO: 7906 ??? Are you sure those ALTER TABLE commands are executing without an error ? The only way I can see you get those messages is when ALTER TABLE for tables MyCoolTable_2 and MyCoolTable_4 (and possibly others too) are failing for some reason. Thanks, Pavan
Re: [GENERAL] Databas has no Object Identifier Types Functions
On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke r.clark...@gmail.com wrote: Hi there, I'm running postgresql 8.4 on a debian system. I have a database that has no object identifier types and functions in the pg_catalog, e.g. regclass, regclassout. Are you sure you don't have them ? I thought regclass is quite old, but I may be wrong. What does the following query returns ? SELECT * FROM pg_type WHERE typname = 'regclass'; Thanks, Pavan
[GENERAL] Re: [GENERAL] UPDATE RULE to be invoked when UPDATE .. WHERE fails the WHERE predicate ?
On Thu, Aug 30, 2012 at 6:31 PM, John Lumby johnlu...@hotmail.com wrote: I would like to use an UPDATE RULE to modify the action performed when any UPDATE is attempted on a certain table, *including* an UPDATE which would fail because of no rows matching the WHERE. Is this at all possible?I have tried with variations of ALSO|INSTEAD etc but the RULE is never invoked in the failing case. And my reading of chapter 38.3.1. How Update Rules Work is that the original query's quallification is always present. You did not mention why you need such a facility, but AFAICS RULEs will only be applied on the qualifying rows. So as you rightly figured out, you won't see them firing unless there are any qualifying rows. Is this not something you can achieve via statement-level triggers though ? Thanks, Pavan
Re: [GENERAL] Vacuum as easily obtained locks
On Wed, Aug 3, 2011 at 10:57 AM, Michael Graham mgra...@bloxx.com wrote: On Wed, 2011-08-03 at 10:17 -0400, Tom Lane wrote: Michael Graham mgra...@bloxx.com writes: Would my applications constant polling of the queue mean that the lock could not be easily obtained? Very possible, depending on what duty cycle is involved there. Hmm. The clients aren't that aggressive, especially when they failed to find data on a previous select, there are 4 clients, they each poll every 10 seconds and the select runs in 1ms. It might be worth noting that they don't ever disconnect from the server, but I assume that is not an issue for getting the AccessExclusiveLock on the table? I don't think so, unless they keep the transaction open. My worry at the moment is that because the table is so large the vacuum takes a very long time to run (one has been running for 5hrs) and I assume it will continue to run until it is able to get the AccessExclusiveLock is so desperately wants. You can run a CLUSTER command on the table which would guarantee that an exclusive lock is taken and the table is compacted, but remember that it will block out your select queries until the command completes. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] RCA for MemoryContextAlloc: invalid request size(Known Issue)
On Fri, Jan 9, 2009 at 2:53 PM, Yogvinder Singh yogvin...@newgen.co.in wrote: That I'll do but that still won't answer the question : What is the reason for the data corruption leading to MemoryContextAlloc: invalid request size That's because you may read the corrupt data and do further operations which leads to failure. For example, the tuple length may be corrupted and you try to copy that tuple to memory. That's when the palloc can fail with the error. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] RCA for MemoryContextAlloc: invalid request size(Known Issue)
On Fri, Jan 9, 2009 at 4:17 PM, Yogvinder Singh yogvin...@newgen.co.in wrote: Pavan, What is the possible reason for the data corruption? It could be anything from buggy hardware to bug in the code. As many have pointed out earlier, you are running a very old release and missing several bug fixes. If you are willing, you could read the release notes of those missing releases to check if there was any fix related to data corruption. I think the best thing to upgrade to the latest point release as soon as possible. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] tune postgres for UPDATE
On Tue, Dec 9, 2008 at 9:13 PM, Scott Marlowe [EMAIL PROTECTED]wrote: I'm pretty sure you'd have to vacuum still in between runs or the extra fill factor space would only get used the first time. I.e.: create table fill factor 50% load data into table update whole table -- 50% free space gets used. (should vacuum here but didn't) update whole table -- boom, new tuples are added onto the end of the table. Not really with 8.3. Assuming there are no long running transactions covering both the updates, HOT will free up the space used by the old (now DEAD) tuples and the second update will reuse that, at least for for the most common cases. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL]transaction isolation level in plpgsql function
On Fri, Nov 21, 2008 at 1:19 PM, Sergey Moroz [EMAIL PROTECTED] wrote: Is there any way to set transaction isolation level inside plpgsql function? In my case I have no control of transaction before function is started. I don't think there can be any. You are already inside a transaction when plpgsql function is called and there is no way to commit and start new transaction inside plpgsql. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Default fill factor for tables?
On Sat, Jul 12, 2008 at 2:25 AM, Roberts, Jon [EMAIL PROTECTED] wrote: -- I can find very little information on hot updates but I found this: http://archives.postgresql.org/pgsql-patches/2006-11/msg00059.php It states, This design optimizies the updates when none of the index columns are modified and length of the tuple remains the same after update. How can a row's length change? I think it must mean the size (in bytes) of the row remains the same. I bet you are looking at an old design. That has undergone many changes and the current implementation does not have any restriction about the row length changes. But a necessary condition is to have enough free space in the block (and of course not changing any index columns). You can find the latest README in the source code under src/backend/access/heap/README.HOT Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Problem with transaction isolation level
On Tue, May 13, 2008 at 1:56 PM, Michal Szymanski [EMAIL PROTECTED] wrote: I think problem is because we use default Read Commited isolation level. In presented example value of credit should be changed only if call_statusFINS and first transaction after modification of credit value set call_status to FINS. This should prevent from second modification of credit (bacause call_status=FINS), but in our systems sometimes such protection does not work. I think that between check of call_status and update of credit is small window that cause that second transaction cannot see results of first transaction (=second transaction cannot 'see' that call_status=FINS) I don't think Read Committed isolation level is at fault here, unless we are looking at some bug. The way it works is the second UPDATE would wait for the first transaction to either commit or abort. In this case, when the first transaction commits, the second UPDATE will re-fetch the latest committed-good copy of the row and re-apply the WHERE clauses before proceeding with the UPDATE operation. Since the latest committed-good copy has call_status set to FINS, the second UPDATE won't update anything. IMHO more information is needed, especially regarding how and when do you change the call_status value in other parts of your code. For example, if some other transaction is setting call_status to something other than FINS and that transaction commits before the second UPDATE comes out of wait, then the second UPDATE would execute successfully. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Need for help!
On Tue, May 13, 2008 at 2:43 PM, Semi Noob [EMAIL PROTECTED] wrote: But after upgrade the max clients is also 64 (?!?) Is this the maximum clients support by program pgbench (my server on Linux ver8.2.5, pgbench on Windows - version postgresql is 8.3.1)? And the number 57 tps is fast? You did not give CPU and disk info. But still 57 seems a small number. What I guess is you're running pgbench with scale factor 1 (since you haven't mentioned scale factor) and that causes extreme contention for smaller tables with large number of clients. Regarding maximum number of clients, check your max_connections setting. Another questions, i heard that PostgreSQL does not support HT Technology, is it right? I'm not sure what do you mean by HT, but if it's hyper threading, then IMO that statement is not completely true. Postgres is not multi-threaded, so a single process (or connection) may not be able to use all the CPUs, but as long as there are multiple connections (each connection corresponds to one backend process), as many CPUs will be used. Last question, i don't understand so much the shmmax, shared_buffers, after upgrading my server from 4 GB RAM to 8 GB RAM, first i configure shmmax to 2GB, share_buffers to 1GB and start server, it runs, after that i set shmmax to 4GB and restart, it fails (?!?). The error logs said that not enough share memory! and final i set shmmax to 3GB and share buffer to 2GB, it runs. Don't know why, can you explain? That doesn't make sense. I am guessing that you are running a 32 bit OS. 4GB of shmmax won't work on a 32 bit OS. Thanks, Pavan Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Non completing Vacuumdb
On Mon, May 12, 2008 at 8:42 PM, Willie Smith [EMAIL PROTECTED] wrote: Is there a log file for the vacuum utility, aside from using stdout? I ran the vacuumdb utility against my database and it only vacuumed one table after the PG tables and did not issue and error before ending. Is there a debugging or diagnostic facility available? Did you try vacuumdb -v ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Need for help!
On Thu, May 15, 2008 at 3:48 PM, Semi Noob [EMAIL PROTECTED] wrote: I set max_connections is 200. What error message you get when you try with more than 64 clients ? 57 seems a small number, according to you, how much tps is normal or fast? Its difficult to say how much is good. On my laptop for s = 10, c = 40, t = 1000, I get 51 tps. But on a larger 2 CPU, 2 GB, 3 RAID 0 disks for data and a separate disk for xlog, I get 232 tps. and what is the different of shared_buffers and effective_cache_size. shared_buffers is the size of the buffer pool which Postgres uses to cache the data blocks. effective_cache_size is usually size of the shared buffer plus estimate of whatever data OS can cache. Planner uses this approximation to choose right plan for execution. http://www.postgresql.org/docs/8.3/interactive/runtime-config-query.html Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Need to update all my 60 million rows at once without transactional integrity
On Wed, Apr 23, 2008 at 1:52 PM, A. Kretschmer [EMAIL PROTECTED] wrote: am Mon, dem 21.04.2008, um 0:19:34 +0200 mailte [EMAIL PROTECTED] folgendes: If I do a batched loop like this: UPDATE table SET flag=0 where id=0 and id 200; UPDATE table SET flag=0 where id=200 and id 400; UPDATE table SET flag=0 where id=400 and id 600; Don't forget to VACUUM after every Update... VACUUMing a large table so often could a problem. But if disk space is the only limitation and you don't care much about IO and CPU usage, its not a bad idea. Is there any other way to go? Update to 8.3 and profit from the new HOT feature (wild guess: there is no index on this flag-column) HOT may not help a lot in this case. HOT needs free space in the same block to put the new version. It can recycle the previously updated rows and thus free up space, but only if the rows were updated in an older (now committed) transaction. Now, if you are doing batch updates, then there is a chance that HOT may be able recycle rows updated in one of the previous batches. But if the updates are happening sequential, then the blocks which were updated previously would never be touched again and hence no space will be freed. If you are updating one row at a time (in a separate transaction) or if the batch updates are kind of scattered, then HOT can reuse the dead tuples and limit the bloat. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Need to update all my 60 million rows at once without transactional integrity
On Mon, Apr 21, 2008 at 3:49 AM, [EMAIL PROTECTED] wrote:
Could I use that to hack my way around transactions?
Since you are asking for trouble, may there is something you can do
with Before UPDATE Triggers and heap_inplace_update(). Before you try
this out: I must say, *I have no idea if this would work in all
scenario and I don't take any guarantee of data consistency*. So do it
on your own risk :-) Obviously, transaction integrity and MVCC is
compromised. But I think crash recovery should work fine because
heap_inplace_update() takes care of WAL logging.
Write a BEFORE UPDATE trigger in C, something like this:
PG_FUNCTION_INFO_V1(inplace_update_trigger);
extern Datum inplace_update_trigger(PG_FUNCTION_ARGS);
Datum
inplace_update_trigger(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *)fcinfo-context;
trigdata-tg_newtuple-t_self = trigdata-tg_trigtuple-t_self;
heap_inplace_update(trigdata-tg_relation, trigdata-tg_newtuple);
return NULL;
}
CREATE OR REPLACE FUNCTION inplace_update_trigger()
RETURNS TRIGGER
AS 'trigger.so', 'inplace_update_trigger'
LANGUAGE C STRICT;
CREATE TRIGGER inplace_update_trigger BEFORE UPDATE ON tblname
FOR EACH ROW EXECUTE PROCEDURE inplace_update_trigger();
Now whenever you update a row in the table, the before update trigger
would update the old tuple in-place and return NULL. That would ensure
that the actual UPDATE operation is not performed, but the changes are
permanently recorded on the old tuple. In case of crash or transaction
abort, the updates can not be rolled back. Also, you may want to take
an exclusive lock on the relation before you start the update.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
--
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] How is statement level read consistency implemented?
On Tue, Apr 22, 2008 at 4:11 PM, Thomas Kellerer [EMAIL PROTECTED] wrote: I am interested in the technical details on how PG determines that a block needs to be read from from some other place than the data block because another transaction has updated the data block. Postgres uses MVCC for transaction consistency. When a row is updated, a new copy of the row is created and the old version is marked for deletion (though its not removed immediately). The old readers continue to read from the old copy whereas new transactions see the new copy. This is all managed by XMIN and XMAX which are transaction ids of the inserting and deleting transactions respectively and control the visibility of the different versions. If you want to understand MVCC, please see this excellent documentation by Tom Lane: http://www.postgresql.org/files/developer/transactions.pdf Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Postgres on shared network drive
On Sat, Apr 12, 2008 at 11:00 PM, Dawid Kuroczko [EMAIL PROTECTED] wrote: Not quite workable. Remember that table data is not always available on the block device -- there are pages modified in the buffer cache (shared memory), and other machines have no access to the other's shared memory (and it would be a lot of work to do it efficiently). Remember also about the MVCC -- if your read only copy machine starts a complicated query on some big_table, and in the meanwhile read-write machine decides the big_table's pages can be reused... well your read-only machine doesn't even have a way of knowing its returning garbage data. ;-) I am not suggesting one read-write and many read-only architecture. I am rather suggesting all read-only systems. I would be interested in this setup if I run large read-only queries on historical data and need easy scalability. With read-only setup, you can easily add another machine to increase computing power. Also, we may come up with cache-sharing systems so that if a buffer is cached on some other node, that can be transfered on a high speed interconnect, rather than reading from a relatively slower disk. Noow, if you really really want a read-only copy of the read write data available over the network, many NAS/SAN devices will allow you to make a snapshot of the database -- and you can use that snapshot as a read-only copy of the database. But then again, if you want a read-only copy of a days/weeks old database, there are chaper and better ways of doing it. Yes. I was mostly assuming read-only scalability. What are the other better ways to do so ? A known implementation of such a set up would be Oracle RAC, where you have a shared storage and N machines using it. Oracle RAC is a multi-master kind of architecture where each node has access to the shared storage and can directly read/write data. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Postgres on shared network drive
On Fri, Apr 11, 2008 at 1:04 PM, A. Kretschmer [EMAIL PROTECTED] wrote: You idea is complete ill. PostgreSQL is a Server-Client-database, with one Server and multiple Clients. You can't access to the same database-files with multiple database-servers. I wonder if it would make sense to add support to mount database in *read-only* mode from multiple servers though. I am thinking about data warehouse kind of operations where multiple servers can be used answer read-only queries. Is there a use case for such applications in real world ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] begin transaction locks out other connections
On Thu, Apr 10, 2008 at 11:29 PM, Dennis Brakhane [EMAIL PROTECTED] wrote: I believe it does. See http://www.postgresql.org/docs/8.3/interactive/sql-set-constraints.html and the DEFERRABLE keyword in CREATE TABLE. Or am I missing something here? Only foreign key contrains checks (and triggers) can be deferred, not the primary or unique key checks. See the following statement in the same doc page: Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable. Triggers that are declared as constraint triggers are also affected. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] begin transaction locks out other connections
On Thu, Apr 10, 2008 at 7:18 PM, Ivano Luberti [EMAIL PROTECTED] wrote: Anyway I am a little surprised by this thing cause I thought that in a case like this the habgs should happen only at commit/rollback time. I think that's because Postgres does not have deferred constraint checks. They are checked at the execution time, instead of commit time. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Problem after VACUUM ANALYZE
On Wed, Apr 9, 2008 at 1:41 PM, David Wilson [EMAIL PROTECTED] wrote: I've run into a very similar problem. I have some long-running processes that generate a large amount of data and then query that data that must periodically drop their connections and rebuild to ensure that query plans get re-planned according to updated statistics. This is especially true when a new DB is first being brought up with an initial set of data (~1 week of compute time, ~30gb of data and ~120m rows) with processes that live through the entire process. I believe plan-invalidation in 8.3 should address this. Isn't it ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Thu, Apr 3, 2008 at 10:02 PM, Tom Lane [EMAIL PROTECTED] wrote: I've applied a modified/extended form of this patch for 8.3.2. Thanks. I had another concern about VACUUM not reporting DEAD line pointers (please see up thread). Any comments on that ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Thu, Apr 3, 2008 at 10:39 PM, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: Thanks. I had another concern about VACUUM not reporting DEAD line pointers (please see up thread). Any comments on that ? If you want to work on that, go ahead Ok. I would do that. but I wanted it separate because I didn't think it merited back-patching. It's strictly cosmetic in terms of being about what VACUUM VERBOSE prints, no? Umm.. Whatever we decide on the fix, I think we should backpatch it to 8.3 because I am worried that someone way get completely confused with the current vacuum report, especially if the autovac is triggered just because of heap full of DEAD line pointers. The num of dead rows reported may awfully be low in that case. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Fri, Apr 4, 2008 at 11:10 AM, Tom Lane [EMAIL PROTECTED] wrote: The policy of this project is that we only put nontrivial bug fixes into back branches, and I don't think this item qualifies ... Got it. I will submit a patch for HEAD. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Tue, Apr 1, 2008 at 1:22 AM, Tom Lane [EMAIL PROTECTED] wrote: Please do --- I have a lot of other stuff on my plate. Please see the attached patch. One change I made is to hold the SHARE lock on the page while ANALYZE is reading tuples from it. I thought it would be a right thing to do instead of repeatedly acquiring/releasing the lock. Another thing I noticed while working on this is VACUUM probably reports the number of dead tuples incorrectly. We don't count the DEAD line pointers as tups_vacuumed which is fine if the line pointer was marked DEAD in the immediately preceding heap_page_prune(). In that case the DEAD line pointer is counted in ndeleted count returned by heap_page_prune(). But it fails to count already DEAD line pointers. For example postgres=# CREATE TABLE test (a int, b char(500)); CREATE TABLE postgres=# INSERT INTO test VALUES (generate_series(1,15),'foo'); INSERT 0 15 postgres=# DELETE FROM test; DELETE 15 postgres=# select count(*) from test; count --- 0 (1 row) postgres=# VACUUM VERBOSE test; INFO: vacuuming public.test INFO: test: removed 0 row versions in 1 pages INFO: test: found 0 removable, 0 nonremovable row versions in 1 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 1 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: test: truncated 1 to 0 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM So VACUUM reports zero dead row versions which may seem counter-intuitive especially in the autovac log message (as someone may wonder why autovac got triggered on the table) I am thinking we can make heap_page_prune() to only return number of HOT tuples pruned and then explicitly count the DEAD line pointers in tups_vacuumed. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com Analyze-fix.patch.gz Description: GNU Zip compressed data -- 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] ANALYZE getting dead tuple count hopelessly wrong
On Mon, Mar 31, 2008 at 1:33 PM, Stuart Brooks [EMAIL PROTECTED] wrote: I have a table with about 15 million rows which is constantly having tuples added to the head and deleted in blocks from the tail to maintain the size. The dead tuple count in pg_stat_user_tables tracks the deleted rows fairly accurately until an auto-ANALYZE is done in the background at which point the value it calculates is wrong by a factor of 2-3 times (calculated value is 30-50% of the correct value) (copying -hackers) Seems like the redirected-dead line pointers are playing spoil-sport here. In this particular example, the deleted tuples may get truncated to redirected-dead line pointers. Analyze would report them as empty slots and not as dead tuples. So in the worst case, if all the deleted tuples are already truncated to redirected-dead line pointers, analyze may report zero dead tuple count. This is a slightly tricky situation because in normal case we might want to delay autovacuum to let subsequent UPDATEs in the page to reuse the space released by the deleted tuples. But in this particular example, delaying autovacuum is not a good thing because the relation would just keep growing. I think we should check for redirected-dead line pointers in analyze.c and report them as dead tuples. The other longer term alternative could be to track redirected-dead line pointers and give them some weightage while deciding on autovacuum. We can also update the FSM information of a page when its pruned/defragged so that the page can also be used for subsequent INSERTs or non-HOT UPDATEs in other pages. This might be easier said than done. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
On Mon, Mar 31, 2008 at 9:02 PM, Tom Lane [EMAIL PROTECTED] wrote: [ Please see if you can stop using the redirected dead terminology ] Apologies, will keep that in mind. Seems like a hang-over from the past :-) Yeah, I think I agree. The page pruning code is set up so that changing a line pointer to DEAD state doesn't change the count of dead tuples in the table, so we are counting unreclaimed DEAD pointers as still being dead tuples requiring VACUUM. ANALYZE should surely not affect that. It looks like there's no trivial way to get ANALYZE to do things that way, though. heap_release_fetch() doesn't distinguish a DEAD line pointer from an unused or redirected one. But in the current implementation of ANALYZE there's really no benefit to using heap_release_fetch anyway --- it always examines all line pointers on each selected page, so we might as well rewrite it to use a simple loop more like vacuum uses. I agree. I would write a patch on these lines, unless you are already on to it. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Table size
On Fri, Mar 21, 2008 at 3:03 PM, lak [EMAIL PROTECTED] wrote:
I have two questions.
How can I enter comments into a table? Where the comments are stored?
What do you mean by comments in a table ?
In psql How can I know the size of a single table?
Select pg_relation_size('mytable');
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
--
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] Table size
On Fri, Mar 21, 2008 at 10:12 PM, Andreas Kretschmer [EMAIL PROTECTED] wrote: Comments on objects can set by: comment on ... is 'comment'; Oh cool.. I did not such facility exists. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Table size
On Fri, Mar 21, 2008 at 10:25 PM, Pavan Deolasee [EMAIL PROTECTED] wrote: Oh cool.. I did not such facility exists. I meant, I did not know such facility exists Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Row size overhead
2008/3/19 Zubkovsky, Sergey [EMAIL PROTECTED]: Simple calculations show that each row occupies 76 bytes approximately. But anticipated row size would be 41 or near. You haven't mentioned PG version. For 8.2 onwards, the tuple header is 23 bytes. Add another 4 bytes for one line pointer for each row. If you have null values, another 5 bytes for null bitmap and alignment. Plus add few bytes for page header and any unusable space in a page (because a row can not fit in the remaining space). Also ISTM that you might be loosing some space because of alignment in the tuple itself. Try moving booleans and char(3) at the end. There is not much you can do with other overheads. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] 8.3.0 upgrade
On Mon, Mar 17, 2008 at 12:43 PM, Adam Rich [EMAIL PROTECTED] wrote: Finally, regarding the new HOT feature. The release notes say that benefits are realized if no changes are made to indexed columns. If my updates include *all columns* (the SQL is generated dynamically) but the new value matches the old value for all *indexed* columns, do I still reap the benefits of HOT? Yes. At the execution time, a binary comparison of old and new index column values is performed and if the old and new value is same for all index columns, HOT update is feasible. So even if the UPDATE statement sets value to one of the index columns, HOT update is possible as long as the old and the new value is same. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] Reindex does not finish 8.2.6
On Fri, Mar 14, 2008 at 5:19 AM, Clodoaldo [EMAIL PROTECTED] wrote: Try vacuuming pg_class, pg_index, pg_attribute manually and see if that makes the problem go away. It does not go away. Can it be a case where some other open transaction is holding a lock on the table ? Note that REINDEX would block even some other transaction is inserting/deleting/updating the table. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] pgbench not setting scale size correctly?
On Fri, Mar 14, 2008 at 2:34 PM, Enrico Sirola [EMAIL PROTECTED] wrote: as you see, the reported scaling factor is 1, but I specified -s 1000, which seems strange... I'm going to recompile it from the sources now. Didn't I get anything or there is a bug somewhere? You must have initialized pgbench with scale 1. While running the tests, it will pick up the scale factor with which it was initialized Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- 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] autovacuum not freeing up unused space on 8.3.0
On Tue, Feb 26, 2008 at 3:11 PM, Stuart Brooks [EMAIL PROTECTED] wrote: ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table metadb.test.transactions Are these happening regularly? They indicate that something is happening on the table that collides with what autovacuum needs to do, and autovacuum defers its task. For this to happen you need to be doing ALTER TABLE or similar however; normal UPDATE/INSERT/DELETE should not cause autovacuum to cancel itself. I am not using an ALTER table command but I am doing periodic ANALYZEs to evaluate the table size. Could this be causing the problem? I notice that stopping the ANALYZE calls appears to eliminate the canceled autovacuum. I am trying to reproduce the case here, but could not. Can you post the table schema and the operations you are carrying out ? Is it just INSERT new rows and DELETE old rows or are there any UPDATEs too ? Are there any long running transactions open ? What concerns me is that once the size has grown, even a VACUUM FULL doesn't recover the space. Regular external VACUUMs keep the table at around 10MB but if I use autovacuum and it grows to 40MB, a VACUUM FULL will only get it down to 35MB. Is it possible that a canceled autovacuum could result in permanently lost space? AFAIK it should not. Can you also post VACUUM FULL VERBOSE output ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres 8.3 HOT and non-persistent xids
On Dec 20, 2007 3:44 AM, Mike C [EMAIL PROTECTED] wrote: Hi, Can someone clarify HOT updates for me (and perhaps put more detail into the docs?). Is this statement correct: the HOT technique is used on *any* table so long as no indexed column is affected. Its partially correct. HOT is used on system and user tables. No index column change is a necessary but not sufficient condition for HOT update. There must be enough free space in the same block where the old tuple exists. Though we hope that the system will stabilize in terms of availability of free space in the blocks, it might be worthy to leave free space of at least one tuple size by using appropriate fill factor at the table creation time. create table T (A int, B int); create index TB on T (B); insert into T (A,B) Values (1,2); So if I do an update that is identical to the existing row, nothing changes? update T set A=1, B=2 where A=1; HOT update *is not* update-in-place. So every update, HOT or COLD, would generate a new version of the row. The power of HOT comes when the index column is not changed. This allows us to skip index inserts for the new version (thus preventing index bloats). Its also far easier to vacuum the dead HOT tuples without running VACUUM or VACUUM FULL. This gives us the ability to prevent heap bloats. If I change the non-indexed field, A, then HOT applies and no new tuple needed? update T set A=2, B=2 where A=1; HOT applies, but new tuple is needed as described above. If I change the indexed field, B, then HOT doesn't apply and a new tuple is needed? update T set A=2,B=3 where A=2; Right. Actually, what actually happens when you get an update with redundant information, e.g. update T set A=2,B=4 where A=2; The value of A hasn't changed, does postgres still write the value? Yes. Every update generates a new version of the row. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem with autovacuum and pg_autovacuum
On 7/5/07, Andreas 'ads' Scherbaum [EMAIL PROTECTED] wrote: Hello, On Wed, 4 Jul 2007 18:04:35 -0400 Alvaro Herrera wrote: Most likely it is worried about XID wraparound, and those are precisely the tables that need urgent vacuumed because they haven't been vacuumed in a long time. No, autovacuum is doing this with every run. Beside this, the database has only some 10k changes per day. The wraparound was my first idea, but i don't see a reason, why this should be happen with every autovacuum run. Did you check freeze_max_age values in the pg_autovacuum table ? A very small value can trigger XID wraparound related VACUUMs in every run. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Vacuuming
On 5/8/07, Tom Lane [EMAIL PROTECTED] wrote: I forgot to mention that any other operation that examines every table row will fix all the hint bits as well. In particular a CREATE INDEX would do that --- I might be missing something, but I think CREATE INDEX work on SnapshotAny and hence may not actually examine any table row ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Vacuuming
On 5/8/07, Tom Lane [EMAIL PROTECTED] wrote: SnapshotAny is a no-op, but HeapTupleSatisfiesVacuum isn't. Oh yes. My apologies for forgetting IndexBuildHeapScan() Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Strange result using transactions
On 3/27/07, Tom Lane [EMAIL PROTECTED] wrote: Matthijs Melissen [EMAIL PROTECTED] writes: I am executing the following queries (id has a unique key): 1) begin; 1) delete from forum where id = 20; 1) insert into forum (id, name) values (20, 'test'); 2) delete from forum where id = 20; 1) commit; The problem is that process 2 gets the message 'DELETE 0'. I would expect him to get the message 'DELETE 1'. Why do you find that strange? Process 1 hasn't committed its insert yet. I think what he is saying that *after* txn 1 commits, txn 2 does not see the record inserted by txn1. Isn't that a fair point ? I mean txn 2 can see the DELETE operation of txn 1, but can not see the INSERT operation of the same transaction. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] [HACKERS] 5 Weeks till feature freeze or (do you know where your patch is?)
On 2/24/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Pavan Deolasee: HOT ( never met him ) I am working on it with the target of 8.3. I am posting WIP patches since couple of weeks. One of the objectives of publishing WIP patches, even though they are not well tested (for correctness as well as performance) is to get early feedback on the design and code. It would really help if one or more of the community members look at the code so that we don't have last minute gotchas. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Dumb question - how to tell if autovacuum is doing its job in 8.2.x
On 2/13/07, Walter Vaughan [EMAIL PROTECTED] wrote: select last_autovacuum, last_autoanalyze from pg_stat_all_tables; last_autovacuum | last_autoanalyze -+-- | ...snip lots of identically blank lines... | | (939 rows) Does that mean it's working or not configured right? It means auto vacuum/analyze did not trigger on any of the tables. You may want to try: SELECT name, setting from pg_settings where name like '%autovacuum%'; to get the settings of autovacuum and check if autovacuum is turned on or not. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [GENERAL] Autovacuum Improvements
Simon Riggs wrote: On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote: Christopher Browne wrote: 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 should keep any small tables from getting vacuum-starved. This is exectly what I am trying, two process autovacuum and a GUC to seperate small tables. In this case, one process takes up vacuuming of the small tables and other process vacuuming of the remaining tables as well as Xid avoidance related vacuuming. The goal is to avoid starvation of small tables when a large table is being vacuumed (which may take several hours) without adding too much complexity to the code. Some feedback from initial testing is that 2 queues probably isn't enough. If you have tables with 100s of blocks and tables with millions of blocks, the tables in the mid-range still lose out. So I'm thinking that a design with 3 queues based upon size ranges, plus the idea that when a queue is empty it will scan for tables slightly above/below its normal range. That way we wouldn't need to specify the cut-offs with a difficult to understand new set of GUC parameters, define them exactly and then have them be wrong when databases grow. The largest queue would be the one reserved for Xid wraparound avoidance. No table would be eligible for more than one queue at a time, though it might change between queues as it grows. Alvaro, have you completed your design? Pavan, what are your thoughts? IMO 2-queue is a good step forward, but in long term we may need to go for a multiprocess autovacuum where the number and tasks of processes are either demand based and/or user configurable. Another idea is to vacuum the tables in round-robin fashion where the quantum could be either time or number of block. The autovacuum process would vacuum 'x' blocks of one table and then schedule next table in the queue. This would avoid starvation of small tables, though cost of index cleanup might go up because of increased IO. Any thoughts of this approach ? Thanks, Pavan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Improvements
Simon Riggs wrote: On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote: Christopher Browne wrote: 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 should keep any small tables from getting vacuum-starved. This is exectly what I am trying, two process autovacuum and a GUC to seperate small tables. In this case, one process takes up vacuuming of the small tables and other process vacuuming of the remaining tables as well as Xid avoidance related vacuuming. The goal is to avoid starvation of small tables when a large table is being vacuumed (which may take several hours) without adding too much complexity to the code. Some feedback from initial testing is that 2 queues probably isn't enough. If you have tables with 100s of blocks and tables with millions of blocks, the tables in the mid-range still lose out. So I'm thinking that a design with 3 queues based upon size ranges, plus the idea that when a queue is empty it will scan for tables slightly above/below its normal range. That way we wouldn't need to specify the cut-offs with a difficult to understand new set of GUC parameters, define them exactly and then have them be wrong when databases grow. The largest queue would be the one reserved for Xid wraparound avoidance. No table would be eligible for more than one queue at a time, though it might change between queues as it grows. Alvaro, have you completed your design? Pavan, what are your thoughts? IMO 2-queue is a good step forward, but in long term we may need to go for a multiprocess autovacuum where the number and tasks of processes are either demand based and/or user configurable. Another idea is to vacuum the tables in round-robin fashion where the quantum could be either time or number of block. The autovacuum process would vacuum 'x' blocks of one table and then schedule next table in the queue. This would avoid starvation of small tables, though cost of index cleanup might go up because of increased IO. Any thoughts of this approach ? Thanks, Pavan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
