gistfile1.txt

10:46 -!- Irssi: Join to #postgresql was synced in 1 secs
10:46 < EvanCarroll> if you do UPDATE foo SET bar = bar+1, on a table with a trillion rows, will you be able to insert into that table before that update txn completes?
10:46 < RhodiumToad> yes
10:46 < orion> RhodiumToad: I guess it might make sense to create a rule to prevent updates to the table... to force the application to delete and insert a new event using the inserter function.
10:46 < EvanCarroll> does an update on a large table, lock the table? or all rows in it?
10:46 < RhodiumToad> orion: trigger, not rule
10:47 < RhodiumToad> EvanCarroll: each updated row individually
10:47 < EvanCarroll> and if so, is there an overhead on locking all of the rows in a very large table -- and can you get around that overhead by locking the /whole/ table
10:47 < RhodiumToad> EvanCarroll: and no, there's no overhead, because the simple fact that the old row version now has xmax set is what makes it "locked"
10:47 < johto> as long as there are no BEFORE triggers
10:48 < EvanCarroll> but does setting xmax (no idea what that is) have any overhead?
10:49 < RhodiumToad> EvanCarroll: yes, but it's an essential part of the update
10:49 < G_SabinoMullane> pg_lizard: WAL has lower overhead, does DDL, automatically replicates all tables without being explicit and scales much better.
10:49 < EvanCarroll> so you can't get around it by locking the whole table?
10:49 < RhodiumToad> EvanCarroll: it marks the old version of the row as dead, and identifies which transaction killed it
10:49 < RhodiumToad> EvanCarroll: no.
10:49 < EvanCarroll> RhodiumToad++ # that clears it up
10:50 < RhodiumToad> EvanCarroll: there is a little hack you can do if you don't mind locking the whole table against queries too
10:50 < EvanCarroll> LOCK TABLE mytable IN EXCLUSIVE MODE;
10:50 < RhodiumToad> EvanCarroll: which is to do  alter table foo alter column bar type integer using bar+1;
10:50 < EvanCarroll> continue ;)
10:51 < RhodiumToad> that's all there is :-)
10:51 < EvanCarroll> and that is faster than UPDATE table FOO SET bar = bar+1?
10:52 < RhodiumToad> usually, yes (and it works if bar is unique, too)
10:53 < EvanCarroll> and the reason that it is faster is... ?
10:53 < EvanCarroll> just for clairity, xmax does need to be set in the ALTER table .. syntax?
10:54 < RhodiumToad> EvanCarroll: the alter table doesn't modify rows, it creates a whole new copy of the table with the new values
10:55 < croepha> is there a way that I can save space by telling pg that my timestamps and intervals dont need a resulution greater than 1 second?
10:55 < EvanCarroll> Are there any docs at all for this sort of stuff?
10:55 < EvanCarroll> Don't updates normally result in new rows?
10:56 < RhodiumToad> croepha: no; you can use timestamp(0) or interval(0) but that rounds to nearest, rather than truncating, and doesn't save any space
10:56 < RhodiumToad> EvanCarroll: updates work by writing a new version of the row, yes
10:56 < croepha> RhodiumToad, kk thx
10:57 < EvanCarroll> so when you say the "alter table doesn't modify rows", nothing really modifies them? What does alter table do differently?
10:57 < EvanCarroll> it just doesn't have to swap the tables row-pointers?
10:58 < EvanCarroll> because there is a totally new table?
10:58 < dannymc> RhodiumToad: Is there a better way to do an upsert? What did you mean by the col thing is nonsense?
10:58 < RhodiumToad> EvanCarroll: alter table makes a completely new table and swaps it with the old one.