Re: [GENERAL] Advisory transaction lock for 128-bit space
Indeed, if there is not some sort of implementation limitation, it would be cool to be able to lock two big integers like so: pg_try_advisory_xact_lock(key1 bigint, key2 bigint) That would solve your problem with locking UUIDs (although you still couldn't lock UUIDs simultaneously across different tables without risking lock interference.) It would also enable the use of advisory locks on multiple tables that have bigserial (bigint) as the primary key, eg: pg_try_advisory_xact_lock(t.id, t.tableoid::bigint) Obviously you don't need a bigint for tableoid but being able to lock two bigints allows you to go to 16-bytes if need be. This came up when I was thinking about how to implement processing queues. It's doable if you assign an int4 id for each queue row (each queue is limited to not grow beyond 2B rows, which seems reasonably generous), then you can do: pg_try_advisory_xact_lock(t.qid, t.tableoid::int4) This is supported by the current postgresql version. Kiriakos On Mar 7, 2012, at 12:52 PM, Andrey Chursin wrote: Hello. My application need to set advisory lock on UUID key, almost like it does pg_advisory_xact_lock function. The problem is argument type of this function - it consumes 8-byte value, not 16-byte. I can not lock on any(hi, low or middle) 8-byte part of UUID, as far as it can produce unexpected deadlock issues, because locking on some ID in this way will imply locking on more wide set of ID then I requested. Now I am doing the 'trick' using indexing insert/delete, e.g.: INSERT INTO table_with_uuid_pk(locking_value); DELETE FROM table_with_uuid_pk WHERE inserted_row_above; It works, but I did not found any description of such 'feature' of indexes. Can u, please, help to solve this synchronization issue, and comment the way I am dealing with it now(with index locking) P.S. The most significant fear I know have, is that currently used method suffers with same problem as locking for part of UUID - doest insert/delete really locks only on the value i passed to it? -- Regards, Andrey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Automatic shadow-table management script
I have released an experimental shadow table management script at:
https://github.com/akaariai/pgsql_shadow_tables
The idea is simple: there are some plpgsql functions which create shadow
tables and insert/update/delete triggers by introspecting pg_catalog and
information_schema. There is very limited support for ALTER TABLE, too.
The usage is simple. For each schema you want to track:
select shadow_meta.update_shadow_schema('public');
After alter table/create table do again the above line and the shadow
tables/triggers should be updated, too.
The above will create a new schema 'shadow_public', and in that schema
shadow tables prefixed with __shadow_.
Now, there is a little trick which allows you to timetravel your
database. The shadow_public contains views which use a session variable
to select the correct snapshot from the shadow table. In short, you
should be able to timetravel by using:
set search_path = 'shadow_public, public';
set test_session_variable.view_time = 'wanted view timestamp';
-- of course, you need test_session_variable in
custom_variable_classes in postgresql.conf
And then you have the snapshot views available. The snapshot views are
named like the original tables, so you might be able to use your
original queries when timetraveling without any rewriting.
Now, for the warnings part: the script is _very_ experimental at the
moment. This is not intended for production use.
I wanted to tell you about the script for two reasons at this early
stage. First, if there is interest in the script, that gives me reason
to actually polish the project into better shape. Second, if there is
already something similar available, then there is not much point in
continuing development of the scripts.
I hope you find the scripts at least interesting if not directly useful,
- Anssi Kääriäinen
--
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 and transaction id wraparound
On Wednesday 07 March 2012 21:13:26 pawel_kukawski wrote: Hi, Do you know any real reason why the autovacuum may fail to clear old XIDs? If it's unable to keep up. Or may be, if there're very long running idle in transactions. Is this highly probable ? postmaster will shutdown to prevent wraparound, if there are fewer than1 million transactions left until wraparound. Rgds, Jens -- 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] rounding a timestamp to nearest x seconds
On 03/07/2012 08:11 PM, Daniele Varrazzo wrote:
On Wed, Mar 7, 2012 at 3:09 PM, Andy Colsona...@squeakycode.net wrote:
Took me a while to figure this out, thought I'd paste it here for others to
use:
create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
select date_trunc('minute', $1) + cast(round(date_part('seconds',
$1)/$2)*$2 || ' seconds' as interval);
$$ language sql immutable;
If you pass 10 to the second argument, it'll round the timestamp to the
nearest 10 seconds. Pass 5 to round to nearest 5 seconds, etc..
Your function can only round the seconds: it cannot round on intervals
longer than one minute and always rounds down to the minute, creating
irregular intervals, e.g.:
= select round_timestamp('2012-03-12 01:42:58', 13);
2012-03-12 01:42:52
= select round_timestamp('2012-03-12 01:42:59', 13);
2012-03-12 01:43:05
= select round_timestamp('2012-03-12 01:43:00', 13);
2012-03-12 01:43:00
You don't get discontinuities if you map the timestamp on the real
axis by extracting the epoch, play there and then go back into the
time domain:
create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
select 'epoch'::timestamp + '1 second'::interval * ($2 *
round(date_part('epoch', $1) / $2));
$$ language sql immutable;
This version can round on any interval specified in seconds (but it
would be easy to specify the step as interval: date_part('epoch',
interval) returns the interval length in seconds).
-- Daniele
Oh, that's very nice, thank you. Never even thought of using epoch.
-Andy
--
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] Advisory transaction lock for 128-bit space
On Thu, Mar 8, 2012 at 2:05 AM, Kiriakos Georgiou
kg.postgre...@olympiakos.com wrote:
Indeed, if there is not some sort of implementation limitation, it would be
cool to be able to lock two big integers like so:
pg_try_advisory_xact_lock(key1 bigint, key2 bigint)
Well, this would require expanding the structure that holds the
in-memory lock. This is not free, since it's also used by the
database for internal lock tables. I would advise trying to work
under the constraints of the current system.
If you want a database-wide advisory lock for rows, probably the best
bet is to make a sequence that is shared by all tables that want to
participate in advisory locking. This is simple and works very well
regardless on how your keys are defined (uuid, natural, etc). It's a
good use for a domain:
create sequence lockid_seq;
create domain lockid_t bigint default nextval('lockid_seq');
alter table foo add column lockid lockid_t;
etc. You'll never exhaust a 64 bit sequence. In fact, you can
reserve a few bits off the top in case you want to do some other
advisory locking for different reasons. A bit hacky maybe, but it
works quite well.
merlin
--
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] Synchronous replication + Fusion-io = waste of money OR significant performance boost? (compared to normal SATA-based SSD-disks)?
On Wed, Mar 7, 2012 at 2:54 AM, Joel Jacobson j...@trustly.com wrote: My company is in the process of migrating to a new pair of servers, running 9.1. The database performance monetary transactions, we require synchronous_commit on for all transactions. Fusion-io is being considered, but will it give any significant performance gain compared to normal SATA-based SSD-disks, due to the fact we must replicate synchronously? To make it more complicated, what about SLC vs MLC (for synchronous replication)? Assume optimal conditions, both servers have less than a meter between each other, with the best possible network link between them providing the lowest latency possible, maxed out RAM, maxed out CPUs, etc. I've already asked this question to one of the core members, but the answer was basically you will have to test, I was therefore hoping someone in the community already had some test results to avoid wasting money. Thank you for any advice! flash, just like hard drives, has some odd physical characteristics that impose some performance constraints, especially when writing, and double especially when MLC flash is used. modern flash drives employ non volatile buffers to work around these constraints that work pretty well *most* of the time. since MLC is much cheaper improvements in flash controller technology are basically pushing SLC out of the market except in high end applications. if you need zero latency storage all the time and are willing to spend the extra bucks, then pci-e based SLC is definitely worth looking at (you'll have another product to evaluate soon when the intel 720 ramsdale hits the market). a decent MLC drive might work for you though, i'd suggest testing there first and upgrading to the expensive proprietary stuff if and only if you really need it. my experience with flash and postgres is that even with low-mid range drives like the intel 320 it's quite a challenge to make postgres be i/o bound. merlin -- 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] FDWs, foreign servers and user mappings
On 03/08/2012 12:06 AM, Shigeru Hanada wrote: I think that makes, and will make sense. Because SQL/MED standard mentions about schema for only foreign table in 4.12 SQL-schemas section. FYI, pgAdmin III shows them as a tree like: Database FDW Server User Mapping Schema Foreign Table Thanks. Incidentally, what version of pgAdmin shows that? I didn't see it in 1.14.0. Joe -- 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] Synchronous replication + Fusion-io = waste of money OR significant performance boost? (compared to normal SATA-based SSD-disks)?
I've also looked at the Fusion-IO products. They are not standard flash drives. They don't appear as SATA devices. They contains an FPGA that maps the flash directly to the PCI bus. The kernel-mode drivers blits data to/from them via DMA, not a SATA or SAS drive (that would limit transfer rates to 6Gb/s). But, I don't have any in-hand to test with yet... :( But the kool-aide looks tasty :) On Thu, Mar 8, 2012 at 8:52 AM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Mar 7, 2012 at 2:54 AM, Joel Jacobson j...@trustly.com wrote: My company is in the process of migrating to a new pair of servers, running 9.1. The database performance monetary transactions, we require synchronous_commit on for all transactions. Fusion-io is being considered, but will it give any significant performance gain compared to normal SATA-based SSD-disks, due to the fact we must replicate synchronously? To make it more complicated, what about SLC vs MLC (for synchronous replication)? Assume optimal conditions, both servers have less than a meter between each other, with the best possible network link between them providing the lowest latency possible, maxed out RAM, maxed out CPUs, etc. I've already asked this question to one of the core members, but the answer was basically you will have to test, I was therefore hoping someone in the community already had some test results to avoid wasting money. Thank you for any advice! flash, just like hard drives, has some odd physical characteristics that impose some performance constraints, especially when writing, and double especially when MLC flash is used. modern flash drives employ non volatile buffers to work around these constraints that work pretty well *most* of the time. since MLC is much cheaper improvements in flash controller technology are basically pushing SLC out of the market except in high end applications. if you need zero latency storage all the time and are willing to spend the extra bucks, then pci-e based SLC is definitely worth looking at (you'll have another product to evaluate soon when the intel 720 ramsdale hits the market). a decent MLC drive might work for you though, i'd suggest testing there first and upgrading to the expensive proprietary stuff if and only if you really need it. my experience with flash and postgres is that even with low-mid range drives like the intel 320 it's quite a challenge to make postgres be i/o bound. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to erase transaction logs on PostgreSQL
How do you purge the postgresql transaction log? I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? /voipfc
Re: [GENERAL] FDWs, foreign servers and user mappings
On Thu, 2012-03-08 at 10:04 -0500, Joe Abbate wrote: On 03/08/2012 12:06 AM, Shigeru Hanada wrote: I think that makes, and will make sense. Because SQL/MED standard mentions about schema for only foreign table in 4.12 SQL-schemas section. FYI, pgAdmin III shows them as a tree like: Database FDW Server User Mapping Schema Foreign Table Thanks. Incidentally, what version of pgAdmin shows that? I didn't see it in 1.14.0. It is in 1.14, but you probably don't have enabled them to be displayed. See menu File/Options, and then in the Display tab. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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 to erase transaction logs on PostgreSQL
On Thu, 2012-03-08 at 10:18 +, Frank Church wrote: How do you purge the postgresql transaction log? You don't. PostgreSQL does it for you. I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? The only security detail AFAIK would be passwords (if you set a password and log queries, the password will be in the logs... the only way to prevent that is to send it crypted). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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: [BUGS] [GENERAL] Altering a table with a rowtype column
On Wed, Mar 7, 2012 at 2:49 PM, Merlin Moncure mmonc...@gmail.com wrote: On a practical level, the error blocks nothing -- you can bypass it trivially. It's just an annoyance that prevents things that users would like to be able to do with table row types. So I'd argue to remove the check, although I can kinda see the argument that it's not a bug unless the check was recently introduced so that it broke older code. The behavior hasn't changed since at least as far back as 8.1, so you're correct (once again) -- not a bug. I'm really surprised I haven't already bumped into this. I usually don't mix tables-as-storage with tables-as-composites though. Mike, on 9.1, you'll probably get more mileage out of using the hstore type for row storage if you want to do auditing in that style. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] why no create variable
There seems to be CREATE everything in Postgres but it would be really nice to have a CREATE VARIABLE which would allow us to create global variables. I know there are other techniques but this would be the easiest when doing a init routine when a user logs in to the application. Best Regards Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to return the last inserted identity column value
In some languges you can use set l_localid = @@identity which returns the value of the identity column defined in the table. How can I do this in Postgres 9.1 Michael Gould Intermodal Software Solutions, LLC 904-226-0978 -- 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 to return the last inserted identity column value
On Thu, Mar 8, 2012 at 11:16 AM, mgo...@isstrucksoftware.net wrote:
In some languges you can use set l_localid = @@identity which returns
the value of the identity column defined in the table. How can I do
this in Postgres 9.1
Assuming you created a table like so:
smarlowe=# create table test (id serial,info text);
NOTICE: CREATE TABLE will create implicit sequence test_id_seq for
serial column test.id
CREATE TABLE
Then use returning:
smarlowe=# insert into test (info) values ('this is a test') returning id;
id
1
(1 row)
--
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 to erase transaction logs on PostgreSQL
one ultra dummy way would be to dump, back up, destroy the data dirs, and any human /var/log files and then re-initdb and restore. On Πεμ 08 Μαρ 2012 12:18:17 Frank Church wrote: How do you purge the postgresql transaction log? I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? /voipfc -- Achilleas Mantzios IT DEPT -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [BUGS] [GENERAL] Altering a table with a rowtype column
Not a bad idea. I'd need to convert existing data, but it'd be an excuse to try out hstore. ^_^ Mike * mike.blackw...@rrd.com* On Thu, Mar 8, 2012 at 11:08, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Mar 7, 2012 at 2:49 PM, Merlin Moncure mmonc...@gmail.com wrote: On a practical level, the error blocks nothing -- you can bypass it trivially. It's just an annoyance that prevents things that users would like to be able to do with table row types. So I'd argue to remove the check, although I can kinda see the argument that it's not a bug unless the check was recently introduced so that it broke older code. The behavior hasn't changed since at least as far back as 8.1, so you're correct (once again) -- not a bug. I'm really surprised I haven't already bumped into this. I usually don't mix tables-as-storage with tables-as-composites though. Mike, on 9.1, you'll probably get more mileage out of using the hstore type for row storage if you want to do auditing in that style. merlin
Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x My biggest table measures 154 GB on the origin, and 533 GB on the slave. Why is my slave bigger than my master? How can I compact it, please? On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop stu...@stuartbishop.net wrote back: Do you have a long running transaction on the slave? vacuum will not reuse space that was freed after the longest running transaction. You need to use the CLUSTER command to compact it, or VACUUM FULL followed by a REINDEX if you don't have enough disk space to run CLUSTER. And neither of these will do anything if the space is still live because some old transaction might still need to access the old tuples. Dear Stuart, We do not run any transactions on the slave besides we pg_dump the entire database every 3 hours. I don't have enough disk space to CLUSTER the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX TABLE. I'd love to get some insight into how much logical data I have versus how much physical space it is taking up. Is there some admin tool or command or query that will report that? For each table (and index), I'd like to know how much data is in that object (logical data size) and how much space it is taking up on disk (physical data size). Yours, Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?
Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan -- 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 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x My biggest table measures 154 GB on the origin, and 533 GB on the slave. Why is my slave bigger than my master? How can I compact it, please? On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop stu...@stuartbishop.net wrote back: Do you have a long running transaction on the slave? vacuum will not reuse space that was freed after the longest running transaction. You need to use the CLUSTER command to compact it, or VACUUM FULL followed by a REINDEX if you don't have enough disk space to run CLUSTER. And neither of these will do anything if the space is still live because some old transaction might still need to access the old tuples. Dear Stuart, We do not run any transactions on the slave besides we pg_dump the entire database every 3 hours. I don't have enough disk space to CLUSTER the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX TABLE. I'd love to get some insight into how much logical data I have versus how much physical space it is taking up. Is there some admin tool or command or query that will report that? For each table (and index), I'd like to know how much data is in that object (logical data size) and how much space it is taking up on disk (physical data size). Do you do things like truncate on the master? Cause truncates don't get replicated in slony. -- 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] Call for Google Summer of Code (GSoC) 2012: Project ideas?
On 03/08/2012 01:40 PM, Stefan Keller wrote: Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan How about one of: 1) on disk page level compression (maybe with LZF or snappy) (maybe not page level, any level really) I know toast compresses, but I believe its only one row. page level would compress better because there is more data, and it would also decrease the amount of IO, so it might speed up disk access. 2) better partitioning support. Something much more automatic. 3) take a nice big table, have it inserted/updated a few times a second. Then make select * from bigtable where indexed_field = 'somevalue'; work 10 times faster than it does today. I think there is also a wish list on the wiki somewhere. -Andy -- 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 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x My biggest table measures 154 GB on the origin, and 533 GB on the slave. Why is my slave bigger than my master? How can I compact it, please? On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop stu...@stuartbishop.net wrote back: Do you have a long running transaction on the slave? vacuum will not reuse space that was freed after the longest running transaction. You need to use the CLUSTER command to compact it, or VACUUM FULL followed by a REINDEX if you don't have enough disk space to run CLUSTER. And neither of these will do anything if the space is still live because some old transaction might still need to access the old tuples. Dear Stuart, We do not run any transactions on the slave besides we pg_dump the entire database every 3 hours. I don't have enough disk space to CLUSTER the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX TABLE. I'd love to get some insight into how much logical data I have versus how much physical space it is taking up. Is there some admin tool or command or query that will report that? For each table (and index), I'd like to know how much data is in that object (logical data size) and how much space it is taking up on disk (physical data size). On Thu, Mar 8, 2012 at 11:58 AM, Scott Marlowe scott.marl...@gmail.com wrote: Do you do things like truncate on the master? Cause truncates don't get replicated in slony. Dear Scott, No, we do not truncate this table on the master. We only add to it. The REINDEX FULL completed and the table is still swollen. Yours, Aleksey -- 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] Call for Google Summer of Code (GSoC) 2012: Project ideas?
Now I have the burden to look for a cool project... Any ideas? -Stefan How about one of: 1) on disk page level compression (maybe with LZF or snappy) (maybe not page level, any level really) I know toast compresses, but I believe its only one row. page level would compress better because there is more data, and it would also decrease the amount of IO, so it might speed up disk access. 2) better partitioning support. Something much more automatic. 3) take a nice big table, have it inserted/updated a few times a second. Then make select * from bigtable where indexed_field = 'somevalue'; work 10 times faster than it does today. I think there is also a wish list on the wiki somewhere. -Andy Ability to dynamically resize the shared-memory segment without taking postgresql down :) -- 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 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Thu, Mar 8, 2012 at 1:10 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Thu, Mar 8, 2012 at 12:10 PM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin atsaloli.t...@gmail.com wrote: We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x My biggest table measures 154 GB on the origin, and 533 GB on the slave. Why is my slave bigger than my master? How can I compact it, please? On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop stu...@stuartbishop.net wrote back: Do you have a long running transaction on the slave? vacuum will not reuse space that was freed after the longest running transaction. You need to use the CLUSTER command to compact it, or VACUUM FULL followed by a REINDEX if you don't have enough disk space to run CLUSTER. And neither of these will do anything if the space is still live because some old transaction might still need to access the old tuples. Dear Stuart, We do not run any transactions on the slave besides we pg_dump the entire database every 3 hours. I don't have enough disk space to CLUSTER the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX TABLE. I'd love to get some insight into how much logical data I have versus how much physical space it is taking up. Is there some admin tool or command or query that will report that? For each table (and index), I'd like to know how much data is in that object (logical data size) and how much space it is taking up on disk (physical data size). On Thu, Mar 8, 2012 at 11:58 AM, Scott Marlowe scott.marl...@gmail.com wrote: Do you do things like truncate on the master? Cause truncates don't get replicated in slony. Dear Scott, No, we do not truncate this table on the master. We only add to it. The REINDEX FULL completed and the table is still swollen. If you pg_dump -t tablename from each machine, are the backups about the same size? -- 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] Call for Google Summer of Code (GSoC) 2012: Project ideas?
On Thu, Mar 8, 2012 at 8:01 PM, Andy Colson a...@squeakycode.net wrote: On 03/08/2012 01:40 PM, Stefan Keller wrote: Hi I do have a student who is interested in participating at the Google Summer of Code (GSoC) 2012 Now I have the burden to look for a cool project... Any ideas? -Stefan How about one of: 1) on disk page level compression (maybe with LZF or snappy) (maybe not page level, any level really) I know toast compresses, but I believe its only one row. page level would compress better because there is more data, and it would also decrease the amount of IO, so it might speed up disk access. 2) better partitioning support. Something much more automatic. 3) take a nice big table, have it inserted/updated a few times a second. Then make select * from bigtable where indexed_field = 'somevalue'; work 10 times faster than it does today. I think there is also a wish list on the wiki somewhere. Nice ideas Those aren't projects we should be giving to summer students. I don't suppose many people could do those things in two months, let alone people with the least experience in both their career and our codebase. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Synchronous replication + Fusion-io = waste of money OR significant performance boost? (compared to normal SATA-based SSD-disks)?
Hi, On 9 March 2012 02:23, dennis jenkins dennis.jenkins...@gmail.com wrote: I've also looked at the Fusion-IO products. They are not standard flash drives. They don't appear as SATA devices. They contains an FPGA that maps the flash directly to the PCI bus. The kernel-mode drivers blits data to/from them via DMA, not a SATA or SAS drive (that would limit transfer rates to 6Gb/s). But, I don't have any in-hand to test with yet... :( But the kool-aide looks tasty :) I think they are good investment but we wasn't able to use them because: - iodrive was small (1.2TB only) and not very scalable in long term -- not enough PCIE slots - iodrive duo/octal needs more power and cooling than we had You can workaround both by upgrading server (two of them, HA) but you just delay the situation when you can't insert new card (no slots or power available). Performance wise, we were able to reduce query time - from few seconds to instant (500ms and better) - any query exceeding 300sec (apache timeout) finished under minute -- Ondrej Ivanic (ondrej.iva...@gmail.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 to return the last inserted identity column value
Hi,
On 9 March 2012 05:20, Scott Marlowe scott.marl...@gmail.com wrote:
On Thu, Mar 8, 2012 at 11:16 AM, mgo...@isstrucksoftware.net wrote:
In some languges you can use set l_localid = @@identity which returns
the value of the identity column defined in the table. How can I do
this in Postgres 9.1
Assuming you created a table like so:
smarlowe=# create table test (id serial,info text);
NOTICE: CREATE TABLE will create implicit sequence test_id_seq for
serial column test.id
CREATE TABLE
Then use returning:
smarlowe=# insert into test (info) values ('this is a test') returning id;
You can use lastval() or currval() functions:
http://www.postgresql.org/docs/9.1/static/functions-sequence.html
--
Ondrej Ivanic
(ondrej.iva...@gmail.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 to erase transaction logs on PostgreSQL
On Fri, 2012-03-09 at 00:09 +, Frank Church wrote: On 8 March 2012 16:23, Guillaume Lelarge guilla...@lelarge.info wrote: On Thu, 2012-03-08 at 10:18 +, Frank Church wrote: How do you purge the postgresql transaction log? You don't. PostgreSQL does it for you. I am creating a virtual machine image and I want to erase any transaction logs that got built up during development. What is the way to do that? I am currently using 8.3 and 8.4. Is there the possibility that the logs saved in /var/log also contain security details? The only security detail AFAIK would be passwords (if you set a password and log queries, the password will be in the logs... the only way to prevent that is to send it crypted). What are the commands to accomplish that, ie getting PostgreSQL to erase the logs? PostgreSQL doesn't erase logs. You need to do it yourself, either manually or with a script (executed by cron for example). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
