Re: [GENERAL] share lock when only one user connected?
On 28 Oct 2010, at 19:28, Ivan Sergio Borgonovo wrote:
I'm running this query when I'm the only user and this should be the
only thing running.
update catalog_items
...
from (
select a.id, a.codice, a.codicealt,
...
from
import.Articoli a
left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
where a.action=8
) as s
where s.id=catalog_items.ItemID
;
And I get
DETAIL: Process 7188 waits for ShareLock on transaction 110562621;
blocked by process 7244. Process 7244 waits for ShareLock on
transaction 110562544; blocked by process 7188.
On that table 2 triggers are defined:
You left out the actual trigger definitions ;)
Could it be possible that you accidentally call the wrong trigger on update of
catalog_items?
Another possibility is that the trigger on catalog_items has a side-effect of
updating catalog_brands - which in turn updates catalog_items again, causing
your situation.
create or replace function FT1IDX_catalog_items_update() returns
trigger as
$$
declare
_Name varchar(64);
begin
select into _Name Name from catalog_brands
where BrandID=new.BrandID;
new.FT1IDX:=
GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt, new.ISBN,
new.Name, new.Authors, _Name);
return new;
end;
$$ language plpgsql volatile;
create or replace function FT1IDX_catalog_brands_update() returns
trigger as
$$
begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=
GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors, '')
where BrandID=old.BrandID;
return old;
else
if(coalesce(new.Name,'')coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=
GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name, Authors,
new.Name) where BrandID=new.BrandID;
end if;
end if;
return new;
end;
$$ language plpgsql volatile;
What could it be? how can I fix it?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4cca678310291669837610!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] create table as select VS create table; insert as select
I have inherited an application that populates a number of temp.y tables using create table ... as select ... This is taking roughly five to ten minutes to run As this process hammers the database, I can only run benchmarks at night so am asking here if anyone know if create table ...; then insert into ... as select... ; would be faster. or if anyone can suggest an alternative I may have missed. I am happy to move code server side if need be. TIA Jacqui I know these is not a lot of detail in the above - the system is under NDA and I need to check with my employer before I give out any system details. Before you ask it is not a big customer - just a very paranoid one :-) -- 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] Can Postgres Not Do This Safely ?!?
On 29 October 2010 03:04, Karl Pickett karl.pick...@gmail.com wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? If I understand your question correctly, you want a gapless PK: http://www.varlena.com/GeneralBits/130.php -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore -t table doesn't restore PKEY
Hello, I have dump of an entire database and I'm trying to restore only one named table: pg_restore --format=c -C -U myUser -d myDB /tmp/mydump -t my_table The command completes without errors/warnings but the resulting table in the database is missing the PKEY constraint ! Is this done on purpose ? What is the correct way to restore a named table ? I know I can use the -t switch during the dump and then restore without using -t but I would like to avoid this solution. The files I can work on are dumps of the entire DB. Thanks for your help, Jan
Re: [GENERAL] create table as select VS create table; insert as select
Hi, On 29 October 2010 11:46, Jacqui Caren-home jacqui.ca...@ntlworld.com wrote: I have inherited an application that populates a number of temp.y tables using create table ... as select ... What is the principle of creating this temp.y tables? May be table partitioning is better to implement here - http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html This is taking roughly five to ten minutes to run As this process hammers the database, I can only run benchmarks at night so am asking here if anyone know if create table ...; then insert into ... as select... ; would be faster. or if anyone can suggest an alternative I may have missed. I am happy to move code server side if need be. TIA Jacqui I know these is not a lot of detail in the above - the system is under NDA and I need to check with my employer before I give out any system details. Before you ask it is not a big customer - just a very paranoid one :-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp -- 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] share lock when only one user connected?
On Fri, 29 Oct 2010 08:19:27 +0200
Alban Hertroys dal...@solfertje.student.utwente.nl wrote:
I've to amend the one user connected assumption.
But I'm really sure there is no other process writing on
catalog_items.
There is a process that read catalog_items and write on another
table.
create catalog_items {
itemid bigint primary key
}
create catalog_related {
itemid bigint references catalog_items (itemid) on delete cascade,
itemid_related bigint references catalog_items (itemid) on delete
cascade
}
To add some more details the update is running inside a larger
transaction that update other tables.
There are no on update cascade and no other triggers.
I'm using 8.3.4
I admit I've a very naïve knowledge of locks and maybe I'm making
assumptions that aren't valid.
I've been able to find:
http://www.postgresql.org/docs/7.2/static/locking-tables.html
but no equivalent for 8.3
I assume the same is valid for 8.3 so since there are no explicit
LOCK TABLE on catalog_items what's left are the INDEX.
I've been experiencing the same problem even dropping the gin index
on the FT1IDX column but there are other btree index on that table.
How can I get more information in the logs to know which statement
were producing the lock?
One for sure was the update.
On 28 Oct 2010, at 19:28, Ivan Sergio Borgonovo wrote:
I'm running this query when I'm the only user and this should be
the only thing running.
update catalog_items
...
from (
select a.id, a.codice, a.codicealt,
...
from
import.Articoli a
left join import.ArticoliPropTipo_1 ip on a.id=ip.idArt
where a.action=8
) as s
where s.id=catalog_items.ItemID
;
And I get
DETAIL: Process 7188 waits for ShareLock on transaction
110562621; blocked by process 7244. Process 7244 waits for
ShareLock on transaction 110562544; blocked by process 7188.
On that table 2 triggers are defined:
You left out the actual trigger definitions ;)
create trigger FT1IDX_catalog_items_update_trigger
after insert or update on catalog_items for each
row execute procedure FT1IDX_catalog_items_update();
create trigger FT1IDX_catalog_brands_update_trigger
after update or delete on catalog_brands for each
row execute procedure FT1IDX_catalog_brands_update();
Could it be possible that you accidentally call the wrong trigger
on update of catalog_items?
Another possibility is that the trigger on catalog_items has a
side-effect of updating catalog_brands - which in turn updates
catalog_items again, causing your situation.
Is this the case?
My intention was that when catalog_brands.brandid get changed
catalog_items.FT1IDX get updated
create or replace function FT1IDX_catalog_items_update() returns
trigger as
$$
declare
_Name varchar(64);
begin
select into _Name Name from catalog_brands
where BrandID=new.BrandID;
new.FT1IDX:=
GetFTIDX('pg_catalog.english', new.Code, new.CodeAlt,
new.ISBN, new.Name, new.Authors, _Name);
return new;
end;
$$ language plpgsql volatile;
create or replace function FT1IDX_catalog_brands_update() returns
trigger as
$$
begin
if(TG_OP='DELETE') then
update catalog_items set
FT1IDX=
GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN, Name,
Authors, '') where BrandID=old.BrandID;
return old;
else
if(coalesce(new.Name,'')coalesce(old.Name,'')) then
update catalog_items set
FT1IDX=
GetFTIDX('pg_catalog.english', Code, CodeAlt, ISBN,
Name, Authors, new.Name) where BrandID=new.BrandID; end if;
end if;
return new;
end;
$$ language plpgsql volatile;
What could it be? how can I fix it?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] server gets slow at certain time
I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. I have different DBs in the system and the large one is 12 GB and it is a CRM DB and heavily used. Every day after 1 pm the system gets slow and after analyzing the nmon spreadsheet, I found that IO spike at that period. Users write data much at that period of time. DB parameters are as follows: max_connections= 500 shared_buffers=122144 effective_cache_size = 1835010 work_mem = 8192 max_fsm_pages= 356656 fsync=on commit_delay=0 commit_siblings= 5 random_page_cost = 0.01 default_statistics_target=100 wah_buffers = 500 Any idea please how to find the bottleneck and improve performance. Let me know if any more information is needed.
Re: [GENERAL] server gets slow at certain time
On 29 October 2010 13:24, AI Rumman rumman...@gmail.com wrote: I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. I have different DBs in the system and the large one is 12 GB and it is a CRM DB and heavily used. Every day after 1 pm the system gets slow and after analyzing the nmon spreadsheet, I found that IO spike at that period. Users write data much at that period of time. DB parameters are as follows: max_connections= 500 shared_buffers=122144 effective_cache_size = 1835010 work_mem = 8192 max_fsm_pages= 356656 fsync=on commit_delay=0 commit_siblings= 5 random_page_cost = 0.01 default_statistics_target=100 wah_buffers = 500 Any idea please how to find the bottleneck and improve performance. Let me know if any more information is needed. What's your checkpoint_segments setting? You may wish to up it if you're getting many inserts/updates. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Re: [GENERAL] server gets slow at certain time
On Fri, 2010-10-29 at 18:24 +0600, AI Rumman wrote: I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. snip In addition to what Thom wrote, please note that checkpoints in pre-8.3 negative effect on disk I/O -- you may see spikes during checkpoints. -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Failover on Windows
Hi all I'm testing a warm standby setup using PostgreSQL 9 x64 on Windows 2008 R2. The problem is that when I put the trigger file on the location specified in the parameter 'trigger_file' of the recovery.conf, nothing happens. No log entries, the recovery just continues as if nothing has happened. Any clues of what may be wrong? Thanks for the attention. Norberto -- 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] Replication Poll
On 2010-10-28 22.50, Joshua D. Drake wrote: Hey, Based on the discussion here: http://www.commandprompt.com/blogs/joshua_drake/2010/10/users_versus_customers_-_you_dont_need_no_stinking_replication/ http://thebuild.com/blog/2010/10/28/small-postgresql-installations-and-9-0-replication/ http://thebuild.com/blog/2010/10/27/users-want-functionality-not-features/ I have created a Replication Poll. Curious was the general populous thinks: https://www.postgresqlconference.org/content/replication-poll You don't have to log in, but that would obviously help with validity of results. Where (and when?) would one find the results? -- Regards, Robert roppert Gravsjö -- 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] server gets slow at certain time
At present, checkpoint_segment=3 checkpoint_timeout=300 On 10/29/10, Thom Brown t...@linux.com wrote: On 29 October 2010 13:24, AI Rumman rumman...@gmail.com wrote: I am using Postgresql 8.1 in 8-cpu cores with 32 gb ram with CestOS 5. I have different DBs in the system and the large one is 12 GB and it is a CRM DB and heavily used. Every day after 1 pm the system gets slow and after analyzing the nmon spreadsheet, I found that IO spike at that period. Users write data much at that period of time. DB parameters are as follows: max_connections= 500 shared_buffers=122144 effective_cache_size = 1835010 work_mem = 8192 max_fsm_pages= 356656 fsync=on commit_delay=0 commit_siblings= 5 random_page_cost = 0.01 default_statistics_target=100 wah_buffers = 500 Any idea please how to find the bottleneck and improve performance. Let me know if any more information is needed. What's your checkpoint_segments setting? You may wish to up it if you're getting many inserts/updates. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] server gets slow at certain time
On 29 October 2010 14:34, AI Rumman rumman...@gmail.com wrote: At present, checkpoint_segment=3 checkpoint_timeout=300 I'd recommend at least setting checkpoint_segments to 12 and you might want to increase checkpoint_timeout too, although not too high. Maybe try doubling it. And as Devrim pointed out, pre-8.3 versions suffer from these problems more, and 8.1 will cease to be supported in the near future, so if you're able to, upgrade. :) Thom
[GENERAL] 9.0.1-1 windows install VC++ 2008 redistributalbe warning
Hi! http://get.enterprisedb.com/postgresql/postgresql-9.0.1-1-windows.exe Upon starting the abov installer, I get a dialog titled Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148 It lists the window titles of some processes, like MS Outlook, Firefox, Eclipse and has 3 buttons: Cancel, Retry and Ignore 1.) It does not say what it wants or why did it appear. 2.) It looks like the these programs should be closed before I can continue dialogs, in which case: Why is for example Eclipse needed to close to install a VC++ library? Eclipse is a Java program. Regards, David PS: Can post a screenshot if needed. -- 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] Can Postgres Not Do This Safely ?!?
On 10/29/2010 10:04 AM, Karl Pickett wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. Essentially, in a table populated by concurrent inserts by many transactions which may commit out of order, you want a way to say get me all tuples inserted since I last asked. Or, really get me all tuples that became visible since I last looked. I've never found a good answer for this. If there is one, it'd be wonderful for trigger-free, efficient replication of individual tables using batches. The problem is that - because of commit ordering - there doesn't seem to be any way to match a *range* of transactions, you have to match a *list* of individual transaction IDs that committed since you last ran. And you need a way to generate and maintain that list, preferably only including transactions that touched the table of interest. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Oh, so you don't care if you get the same tuple multiple times if there's some old, long running transaction? You're just trying to avoid repeatedly grabbing the REALLY old stuff? In that case xmin is what you want. You may have to be aware of xid wraparound issues, but I don't know much more about dealing with them than the term. -- Craig Ringer -- 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] 9.0.1-1 windows install VC++ 2008 redistributalbe warning
On Fri, Oct 29, 2010 at 2:40 PM, David Balažic xerc...@gmail.com wrote: Hi! http://get.enterprisedb.com/postgresql/postgresql-9.0.1-1-windows.exe Upon starting the abov installer, I get a dialog titled Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148 It lists the window titles of some processes, like MS Outlook, Firefox, Eclipse and has 3 buttons: Cancel, Retry and Ignore 1.) It does not say what it wants or why did it appear. 2.) It looks like the these programs should be closed before I can continue dialogs, in which case: Why is for example Eclipse needed to close to install a VC++ library? Eclipse is a Java program. Thats the Microsoft runtime installer, which is included in the PostgreSQL installer. Unfortunately, as it's written and supplied by Microsoft, we don't have any idea how or why it works as it does. We just know we need the runtimes it installs. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Can Postgres Not Do This Safely ?!?
On Thursday 28 October 2010 7:04:48 pm Karl Pickett wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. http://www.postgresql.org/docs/8.4/interactive/functions-info.html#FUNCTIONS-TXID-SNAPSHOT-PARTS The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an epoch counter so it will not wrap around during the life of an installation. The data type used by these functions, txid_snapshot, stores information about transaction ID visibility at a particular moment in time. Its components are described in Table 9-53. So: Current snapshot: test= SELECT txid_current_snapshot(); txid_current_snapshot --- 5098:5098: xmin of snapshot: test= SELECT txid_snapshot_xmin(txid_current_snapshot()); txid_snapshot_xmin 5098 (1 row) All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? Thank you very much. -- Karl Pickett -- Adrian Klaver adrian.kla...@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] Can Postgres Not Do This Safely ?!?
n Fri, Oct 29, 2010 at 2:53 AM, Peter Geoghegan peter.geoghega...@gmail.com wrote: On 29 October 2010 03:04, Karl Pickett karl.pick...@gmail.com wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? If I understand your question correctly, you want a gapless PK: http://www.varlena.com/GeneralBits/130.php -- Regards, Peter Geoghegan That's interesting, but we're fine with having gaps in the range that never appear. We also don't want to add a performance penalty for concurrent writers. We just don't want any ids to appear (commit) after we got a later id. To clarify, we are using a plain serial primary key and we already have plenty of holes - that's fine. We just want to do an incremental 'tail -f' of this giant table (along with some joins) to feed into a reporting server every few minutes. So we're treating it like a queue, but not deleting anything and having absolute real-time data is not required. It appears that theoretical options are: 1. Start a serializable transaction and wait until all earlier transactions are gone (query pg_stat_activity or something?) 2. Ignore rows that were created later than any other in progress transactions Both of these options assume that serials can never go backward as they're handed out to connections / xids. I think that's safe to assume? Either would be fine, I just don't know if they're officially supported by postgres. -- Karl Pickett -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 replication -- multiple hot_standby servers
Two days ago I upgraded five DB boxes (for load balancing) from 8.3.0 to 9.0.1 in order to use replication. The replication configuration went reasonably well, and now all the four hot_standby servers are (streaming) replicating just fine from the primary DB server. If the primary fails and I touch the trigger file on one of the standby boxes, that goes into primary mode just as it should. Of course, I have to externally redirect updates to the new server. My question is, how do I configure the other three (still) hot_standby boxes to now use the new primary? Clearly I can change the recovery.conf file on each standby box, but that seems like an unnecessary nuisance. What I would like to do is put multiple primary_conninfo lines into the recovery.conf file, and have each standby server dynamically pick the one that is the primary. Further, when the primary goes down, the standby should re-choose the new primary using the multiple primary_conninfo lines. Such a feature (if it does not already exist) would really be useful !!! Question: While the documentation described how to switch a standby server from hot_standby to primary, I didn't see how to switch it back to hot_standby. Is the following the best (or only) method ??? 1. Optionally, bring the previous primary back up into hot_standby mode. 2. STOP the current primary server. 3. Using the trigger file on another hot-standby box, bring it into primary mode. 4. Externally, start redirecting updates to the new primary server. 5. On the stopped server, delete the trigger file, and rename the recovery.done file back to recovery.conf. 6. Restart the stopped server; it should come back up into hot_standby mode. Ideally, this could be done seamlessly without the (small) possibility of lost updates, but I don't see how to do that. Fortunately, it's not a big issue in my case. Here's what I think would be *ideal* from an update integrity standpoint: 1. If a hot_standby box gets a non-read-only SQL transaction, if checks to see if it is still talking to a primary server: * If it is, it does whatever is done now (I presume it reports an error). * If not, it touches the trigger file and switches into primary mode. If there are other hot_standby servers, ideally the new primary signals them that it is the new primary (in order to keep them from accessing the old primary, which may still be visible to them). 2. This way, an external application feeding SQL update-type transactions, upon finding that the old primary is down, just switches to any hot_standby server as its new target and retries the update; this will automatically cause the desired hot_standby server to switch to primary. 3. Since we don't know what the issue was with the the previous primary, it needs to be recovered manually (and isolated from other hot_standby servers in the meantime). Sincerely, Dean
Re: [GENERAL] exceptionally large UPDATE
On Thu, Oct 28, 2010 at 1:06 PM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: What I'm planning to do is: max_connections = 5 shared_buffers = 240M work_mem = 90MB maintenance_work_mem = 1GB max_fsm_pages = 437616 max_fsm_relations = 1200 checkpoint_segments = 70 default_statistics_target = 30 #log_min_duration_statement = 1000 default_statistics_target = 100 is the new default for newer postgres, and with good reason... try that. if you boost your checkpoint_segments, also twiddle the checkpoint_timeout (increase it) and checkpoint_completion_target (something like 0.8 would be good, depending on how fast your disks are) values to try to smooth out your I/O (ie, keep it from bursting at checkpoint timeout). Is 5 connections really enough for you? And like I said before, you can set the work_mem and/or maintenance_work_mem on a per-connection basis as needed, so for your big update you can increase those values just during that work without affecting the rest of the system. -- 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] create table as select VS create table; insert as select
Jacqui Caren-home jacqui.ca...@ntlworld.com writes: I have inherited an application that populates a number of temp.y tables using create table ... as select ... As this process hammers the database, I can only run benchmarks at night so am asking here if anyone know if create table ...; then insert into ... as select... ; would be faster. No, it'd likely be slower --- possibly quite a bit slower. When you say temp-y, do you mean these are in fact TEMP tables? If not, can you make them be so? That would help. regards, tom lane -- 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] pg_restore -t table doesn't restore PKEY
Jan C. chal...@gmail.com writes: I have dump of an entire database and I'm trying to restore only one named table: pg_restore --format=c -C -U myUser -d myDB /tmp/mydump -t my_table The command completes without errors/warnings but the resulting table in the database is missing the PKEY constraint ! Use pg_restore -l, then extract the lines relevant to your table, then pg_restore -L to restore just the named items. I think there's a TODO to make pg_restore's -t switch work more like pg_dump's, but at the moment they're really quite different animals. pg_restore doesn't have any logic about oh, if he asked for this item I bet he wants that one too. regards, tom lane -- 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] Can Postgres Not Do This Safely ?!?
On Fri, Oct 29, 2010 at 8:58 AM, Adrian Klaver adrian.kla...@gmail.com wrote: On Thursday 28 October 2010 7:04:48 pm Karl Pickett wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. http://www.postgresql.org/docs/8.4/interactive/functions-info.html#FUNCTIONS-TXID-SNAPSHOT-PARTS The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an epoch counter so it will not wrap around during the life of an installation. The data type used by these functions, txid_snapshot, stores information about transaction ID visibility at a particular moment in time. Its components are described in Table 9-53. So: Current snapshot: test= SELECT txid_current_snapshot(); txid_current_snapshot --- 5098:5098: xmin of snapshot: test= SELECT txid_snapshot_xmin(txid_current_snapshot()); txid_snapshot_xmin 5098 (1 row) So what happens when txid_snapshot_xmin() goes over 4 billion, and the table's xmin doesn't? You can't compare a 32 bit value that rolls over to a 64 bit that doesn't. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? Thank you very much. -- Karl Pickett -- Adrian Klaver adrian.kla...@gmail.com -- Karl Pickett -- 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] Can Postgres Not Do This Safely ?!?
On Thu, Oct 28, 2010 at 10:04 PM, Karl Pickett karl.pick...@gmail.com wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? You don't have a sequence problem so much as a wrong implementation problem. Sequences are always *grabbed* in order but they can hit the table out of order and there is a time lag between when the sequence value is generated and the transaction commits. If I issue 'begin', insert a log record, and hold the commit for 5 minutes you are going to skip the record because you are only looking at the last processed record. Your algorithm is going to fail if you use a sequence, timestamp, or gapless sequence to manage your queue position. You need to divide your log records into two logical sets, procesed and unprocessed, and look at the set as a whole. I would suggest staging your unprocessed records to a queue table and having your writer consume them and move them to a processed table. You can also look at already built queuing implementations like PGQ written by our spectacularly skilled friends at Skype (haven't used it myself, but I've heard it's good!). 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] pg_restore -t table doesn't restore PKEY
So I'd have to do: pg_restore -l /tmp/mydump | grep my_table /tmp/mytable_lines pg_restore --format=c -C -U myUser -d myDB /tmp/mydump -L /tmp/mytable_lines It looks like I have to use grep to look for my table and not -t my_table because the PKEY constraint is NOT listed when I do pg_restore -l /tmp/mydump -t my_table In any case, shouldn't the documentation describe more the pg_restore -t command ? It's really misleading and one can discover this only by running into problems. Thanks for your help, Jan On Fri, Oct 29, 2010 at 4:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jan C. chal...@gmail.com writes: I have dump of an entire database and I'm trying to restore only one named table: pg_restore --format=c -C -U myUser -d myDB /tmp/mydump -t my_table The command completes without errors/warnings but the resulting table in the database is missing the PKEY constraint ! Use pg_restore -l, then extract the lines relevant to your table, then pg_restore -L to restore just the named items. I think there's a TODO to make pg_restore's -t switch work more like pg_dump's, but at the moment they're really quite different animals. pg_restore doesn't have any logic about oh, if he asked for this item I bet he wants that one too. regards, tom lane -- 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] exceptionally large UPDATE
On Fri, 29 Oct 2010 10:21:14 -0400 Vick Khera vi...@khera.org wrote: On Thu, Oct 28, 2010 at 1:06 PM, Ivan Sergio Borgonovo m...@webthatworks.it wrote: What I'm planning to do is: max_connections = 5 shared_buffers = 240M work_mem = 90MB maintenance_work_mem = 1GB max_fsm_pages = 437616 max_fsm_relations = 1200 checkpoint_segments = 70 default_statistics_target = 30 #log_min_duration_statement = 1000 default_statistics_target = 100 is the new default for newer postgres, and with good reason... try that. if you boost your checkpoint_segments, also twiddle the checkpoint_timeout (increase it) and checkpoint_completion_target (something like 0.8 would be good, depending on how fast your disks are) values to try to smooth out your I/O (ie, keep it from bursting at checkpoint timeout). Is 5 connections really enough for you? No. 5 is too few. OK... this is what I end up with: max_connections = 100 shared_buffers = 240M work_mem = 90MB maintenance_work_mem = 1GB max_fsm_pages = 437616 max_fsm_relations = 1200 default_statistics_target = 100 checkpoint_segments = 70 checkpoint_timeout = 10min checkpoint_completion_target = 0.6 #(not very fast drives in raid5) #log_min_duration_statement = 1000 random_page_cost = 3.0 I tested this on a RAID10 SATA, 8Gb RAM and 2x4cores Xeons - updating 227985 records over roughly 1.4M took 197744.374 ms - recreating the gin index took 313962.162 ms - commit took 7699.595 ms - vacuum analyse 188261.481 ms The total update took around 13min. I've just heard that a similar update on a slower box (RAID1 SAS, 4Gb, 2x2Cores Xeon) running MS SQL took over 30min. Considering MUCH less pk/fk, constraint and actions where defined on the MS SQL DB, things now look much better for postgres. Furthermore postgresql full text search kicks ass to the MS SQL box even on the slowest box of all (RAID5 SATA, 4Gb, 2x1core HT Xeon, over 6 years old). I'll take note of performance even on the slower box as soon as I'll have large updates, still I'm looking how to make it faster. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xor(bytea,bytea)
hello, does anyone have a C implementation of xor(bytea,bytea)? i need to xor aggregate the results of a digest. an xor operator for bytea would be great too, but i'd be happy with just the function. i've searched and found past posts about xor'ing text, or bit - bytea casting. it seemed the general developer attitude was that xor operators for bytea might be appropriate, but i haven't found a materialization of that. thanks, tavin -- 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] 9.0 replication -- multiple hot_standby servers
On 10/28/10 11:25 PM, Dean Gibson AE7Q wrote: Two days ago I upgraded five DB boxes (for load balancing) from 8.3.0 to 9.0.1 in order to use replication. The replication configuration went reasonably well, and now all the four hot_standby servers are (streaming) replicating just fine from the primary DB server. If the primary fails and I touch the trigger file on one of the standby boxes, that goes into primary mode just as it should. Of course, I have to externally redirect updates to the new server. My question is, how do I configure the other three (still) hot_standby boxes to now use the new primary? Clearly I can change the recovery.conf file on each standby box, but that seems like an unnecessary nuisance. I've not worked with the 9.0.x replication yet, so my comments are of a more general nature... A) keep it super simple. complex schemes have a marvelous way of finding corner cases and biting you in the @%%. B) don't forget corner cases like a 'stoned' server that somehow isn't communicating with the others and decides ITS the master when in fact another node is running just fine. robust cluster management systems like Veritas Cluster insist on redundant inter-node heartbeat communications paths, and hardware fencing support so only one node can possibly be 'master' at any given time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.0 replication -- multiple hot_standby servers
Oops; previously sent from the wrong eMail address, so I don't know if this actually got sent: Two days ago I upgraded five DB boxes (for load balancing) from 8.3.0 to 9.0.1 in order to use replication. The replication configuration went reasonably well, and now all the four hot_standby servers are (streaming) replicating just fine from the primary DB server. If the primary fails and I touch the trigger file on one of the standby boxes, that goes into primary mode just as it should. Of course, I have to externally redirect updates to the new server. My question is, how do I configure the other three (still) hot_standby boxes to now use the new primary? Clearly I can change the recovery.conf file on each standby box, but that seems like an unnecessary nuisance. What I would like to do is put multiple primary_conninfo lines into the recovery.conf file, and have each standby server dynamically pick the one that is the primary. Further, when the primary goes down, the standby should re-choose the new primary using the multiple primary_conninfo lines. Such a feature (if it does not already exist) would really be useful !!! Question: While the documentation described how to switch a standby server from hot_standby to primary, I didn't see how to switch it back to hot_standby. Is the following the best (or only) method ??? 1. Optionally, bring the previous primary back up into hot_standby mode. 2. STOP the current primary server. 3. Using the trigger file on another hot-standby box, bring it into primary mode. 4. Externally, start redirecting updates to the new primary server. 5. On the stopped server, delete the trigger file, and rename the recovery.done file back to recovery.conf. 6. Restart the stopped server; it should come back up into hot_standby mode. Ideally, this could be done seamlessly without the (small) possibility of lost updates, but I don't see how to do that. Fortunately, it's not a big issue in my case. Here's what I think would be *ideal* from an update integrity standpoint: 1. If a hot_standby box gets a non-read-only SQL transaction, if checks to see if it is still talking to a primary server: * If it is, it does whatever is done now (I presume it reports an error). * If not, it touches the trigger file and switches into primary mode. If there are other hot_standby servers, ideally the new primary signals them that it is the new primary (in order to keep them from accessing the old primary, which may still be visible to them). 2. This way, an external application feeding SQL update-type transactions, upon finding that the old primary is down, just switches to any hot_standby server as its new target and retries the update; this will automatically cause the desired hot_standby server to switch to primary. 3. Since we don't know what the issue was with the the previous primary, it needs to be recovered manually (and isolated from other hot_standby servers in the meantime). Sincerely, Dean -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [GENERAL] 9.0 replication -- multiple hot_standby servers
On October 29, 2010, Dean Gibson (DB Administrator) postgre...@ultimeth.com wrote: My question is, how do I configure the other three (still) hot_standby boxes to now use the new primary? Clearly I can change the recovery.conf file on each standby box, but that seems like an unnecessary nuisance. I'm curious about this too. It seems that currently I'd have to rebuild any additional slaves basically from scratch to use the new master. -- A hybrid Escalade is missing the point much in the same way that having a diet soda with your extra large pepperoni pizza is missing the point. -- 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] 9.0 replication -- multiple hot_standby servers
On 2010-10-29 11:17, Alan Hodgson wrote: I'm curious about this too. It seems that currently I'd have to rebuild any additional slaves basically from scratch to use the new master. I think so long as you pointed (via primary_conninfo) the additional slaves to the new (pending) master, before you touched the pending master's trigger file, you should be OK, as all the DBs should be in sync at that point. When the primary DB server goes down, updates are no longer accepted. In such a situation, the human goal is to get the updates accepted again, and human beings in a hurry are apt to forget the exact sequence of steps to follow. If one forgets, and enables the new master without repointing the other slaves, then you have a situation where you have to backup/restore the new primary to each of the slaves, in order to recover the slaves. The failover shouldn't be that brittle. A similar situation exists when having to reboot all the DB servers (say, for maintenance), or just reboot one in a period where you can afford to suspend updates: As near as I can tell (and I will find out over the weekend), the correct procedure is to stop the primary FIRST, and then reboot whatever slaves you need, and then reboot (if needed) or restart the primary. I wonder if this thread should be in the ADMIN group (and if so, should I repost the original message there) ??? -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce.
Re: [GENERAL] 9.0 replication -- multiple hot_standby servers
On October 29, 2010, Dean Gibson (DB Administrator) postgre...@ultimeth.com wrote: On 2010-10-29 11:17, Alan Hodgson wrote: I'm curious about this too. It seems that currently I'd have to rebuild any additional slaves basically from scratch to use the new master. I think so long as you pointed (via primary_conninfo) the additional slaves to the new (pending) master, before you touched the pending master's trigger file, you should be OK, as all the DBs should be in sync at that point. Yeah they're in sync data-wise, but do they think they're the same WAL stream for continuity? Would be nice. -- A hybrid Escalade is missing the point much in the same way that having a diet soda with your extra large pepperoni pizza is missing the point. -- 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] Unhandled exception in PGAdmin when opening 16-million-record table
This one aught to be good! The tool is after all called pgAdmin rather that say pgBrowser. I think you have a teaching opportunity here. There is a feature for getting the first N rows that might help (a lot). There is query-by-example as well. I can't really imagine the value of being able to look at all 16M records in one list. Not saying this excuses the crash necessarily or more importantly the poor error message. One might find a stack trace in the system error log? On 10/29/2010 02:52 PM, Rob Richardson wrote: A customer was reviewing the database that supports the application we have provided. One of the tables is very simple, but has over 16 million records. Here is the table's definition: CREATE TABLE feedback ( charge integer, elapsed_time integer, -- number of elapsed minutes since data began recording tag_type character varying(24), -- Description of tag being recorded tag_value real, -- value of tag being recorded status smallint, -- PLC Status, recorded with Control PV only stack integer, -- Not used heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling cooling smallint DEFAULT 0 -- not used ) As you see, there is no primary key. There is a single index, as follows: CREATE INDEX feedback_charge_idx ON feedback USING btree (charge); In PGAdmin, the customer selected this table and clicked the grid on the toolbar, asking for all of the records in the table. After twenty minutes, a message box appeared saying that an unhandled exception had happened. There was no explanation of what the exception was. The database log does not contain any information about it. The PGAdmin display did show a number of records, leading me to believe that the error happened in PGAdmin rather than anywhere in PostGres. Can anyone explain what is happening? The customer is using PostgreSQL 8.4.5 (we just updated them within the last few days) and PGAdmin 1.10.5 on a Windows Server 2003 box. I see PGAdmin is now up to 1.12.1. I suppose the first thing I should do is update their PGAdmin. Thanks for your help! RobR -- 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] Unhandled exception in PGAdmin when opening 16-million-record table
On Fri, 2010-10-29 at 15:02 -0600, Rob Sargent wrote: Not saying this excuses the crash necessarily or more importantly the poor error message. One might find a stack trace in the system error log? Think probably ran out of memory. 16M records? Really? JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Max Tables in a union
What is the max number of tables allowed in a union (in 8.4 and 9.0)? -- 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] Unhandled exception in PGAdmin when opening 16-million-record table
Le 29/10/2010 13:52, Rob Richardson a écrit : A customer was reviewing the database that supports the application we have provided. One of the tables is very simple, but has over 16 million records. Here is the table's definition: CREATE TABLE feedback ( charge integer, elapsed_time integer, -- number of elapsed minutes since data began recording tag_type character varying(24), -- Description of tag being recorded tag_value real, -- value of tag being recorded status smallint, -- PLC Status, recorded with Control PV only stack integer, -- Not used heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling cooling smallint DEFAULT 0 -- not used ) As you see, there is no primary key. There is a single index, as follows: CREATE INDEX feedback_charge_idx ON feedback USING btree (charge); In PGAdmin, the customer selected this table and clicked the grid on the toolbar, asking for all of the records in the table. After twenty minutes, a message box appeared saying that an unhandled exception had happened. There was no explanation of what the exception was. The database log does not contain any information about it. The PGAdmin display did show a number of records, leading me to believe that the error happened in PGAdmin rather than anywhere in PostGres. Can anyone explain what is happening? Definitely not an error in PostgreSQL. More related to pgAdmin. The customer is using PostgreSQL 8.4.5 (we just updated them within the last few days) and PGAdmin 1.10.5 on a Windows Server 2003 box. I see PGAdmin is now up to 1.12.1. I suppose the first thing I should do is update their PGAdmin. Won't do anything if your customer still wants to look at 16 million rows. The only thing we could probably do on the coding side is looking at the estimated number of rows and displays a warning message telling: Hey dude, you're trying to look at around 16 million rows. That can't work. You would be very well advised to cancel., but still allows the user to bypass this check (if the estimated number of rows is wrong). -- Guillaume http://www.postgresql.fr http://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] Can Postgres Not Do This Safely ?!?
On 10/29/2010 9:49 AM, Merlin Moncure wrote: On Thu, Oct 28, 2010 at 10:04 PM, Karl Pickettkarl.pick...@gmail.com wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? You don't have a sequence problem so much as a wrong implementation problem. Sequences are always *grabbed* in order but they can hit the table out of order and there is a time lag between when the sequence value is generated and the transaction commits. If I issue 'begin', insert a log record, and hold the commit for 5 minutes you are going to skip the record because you are only looking at the last processed record. Your algorithm is going to fail if you use a sequence, timestamp, or gapless sequence to manage your queue position. You need to divide your log records into two logical sets, procesed and unprocessed, and look at the set as a whole. I would suggest staging your unprocessed records to a queue table and having your writer consume them and move them to a processed table. You can also look at already built queuing implementations like PGQ written by our spectacularly skilled friends at Skype (haven't used it myself, but I've heard it's good!). merlin Yep, you dont want a sequence. You want a flag. add a boolean processed flag, default it to false. then every 5 minutes run this: begin insert into logged select * from events where processed = false; update events set processed = true where processed = false; commit; or, if you want to select them and do something to them: begin select * from events where processed = false; ... do you processing on each, which would include inserting it... update events set processed = true where processed = false; commit; Just make sure you do it all in the same transaction, so the update sees the exact same set as the select. You could also create a function index on processed to keep track of just those that are false. -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] Max Tables in a union
Fred Miller f...@frederickmiller.com writes: What is the max number of tables allowed in a union (in 8.4 and 9.0)? There's no specific limit. I doubt you'd get good performance with thousands ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgAdmin on Mac connecting to Postgres 9 on Linux - SSL/timeout issue
I have a Postgres 9 server running on a server out on the Internet and I connect to it with pgAdmin on OS/X over an SSL connection. I notice if I keep the connection open and idle for maybe an hour or so, when I try to run a query it either times out or pgAdmin just kinda freezes up and I have to force quit.. Is there some sort of idle timeout setting on SSL connections, or maybe it has to re-negotiate the connection after a certain amount of idle time and it's not doing that right? Anyone run into this before? Thanks! Mike -- 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] Can Postgres Not Do This Safely ?!?
On 10/29/2010 07:32 AM, Karl Pickett wrote: On Fri, Oct 29, 2010 at 8:58 AM, Adrian Klaveradrian.kla...@gmail.com wrote: On Thursday 28 October 2010 7:04:48 pm Karl Pickett wrote: Hello Postgres Hackers, We have a simple 'event log' table that is insert only (by multiple concurrent clients). It has an integer primary key. We want to do incremental queries of this table every 5 minutes or so, i.e. select * from events where id LAST_ID_I_GOT to insert into a separate reporting database. The problem is, this simple approach has a race that will forever skip uncommitted events. I.e., if 5000 was committed sooner than 4999, and we get 5000, we will never go back and get 4999 when it finally commits. How can we solve this? Basically it's a phantom row problem but it spans transactions. I looked at checking the internal 'xmin' column but the docs say that is 32 bit, and something like 'txid_current_snapshot' returns a 64 bit value. I don't get it. http://www.postgresql.org/docs/8.4/interactive/functions-info.html#FUNCTIONS-TXID-SNAPSHOT-PARTS The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an epoch counter so it will not wrap around during the life of an installation. The data type used by these functions, txid_snapshot, stores information about transaction ID visibility at a particular moment in time. Its components are described in Table 9-53. So: Current snapshot: test= SELECT txid_current_snapshot(); txid_current_snapshot --- 5098:5098: xmin of snapshot: test= SELECT txid_snapshot_xmin(txid_current_snapshot()); txid_snapshot_xmin 5098 (1 row) So what happens when txid_snapshot_xmin() goes over 4 billion, and the table's xmin doesn't? You can't compare a 32 bit value that rolls over to a 64 bit that doesn't. The long explanation is here: http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND The short version as I understand it is that if everything is working correctly the XID(hence xmin) values exist in a continuous loop where 2 billion are in the past and 2 billion are in the future(assuming default settings). At some point the old values are frozen i.e. replaced with a special FrozenXID. This would mean that the *snapshot functions should only return currently valid xmins. Since I have never rolled over a database I can only speak to theory as I understand it. All I want to is make sure I skip over any rows that are newer than the oldest currently running transaction. Has nobody else run into this before? Thank you very much. -- Karl Pickett -- Adrian Klaver adrian.kla...@gmail.com -- Adrian Klaver adrian.kla...@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
[GENERAL] Call For Talks: PGDay LA @ SCALE 9X
PGDay is scheduled at this year's Southern California Linux Exposition (SCALE) held in the LAX Hilton Hotel in the city of Los Angeles, California, on Friday February 25th, 2010. We are looking for talks designed for a general audience of web developers, sysadmins, DBAs and open source users. Talks should have significant technical content. This includes: *Interesting PostgreSQL Case Studies *Solutions to common problems *Performance, scaling and optimization *Administration *Using PostgreSQL with popular languages or tools *Replication and clustering Talks should be 50 minutes in length. We are also accepting 5 minute lightning talks. Talks should be submitted by January 5, 2010. Final speakers will be chosen by January 15, 2010. Post your talk submission to pgday-submissi...@googlegroups.com. Submissions should include: *Speaker Full Name *E-mail *Cell Phone *Title of Talk *Description of Talk *Notes for Committee For more information visit: https://sites.google.com/site/pgdayla/ -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Paradox to postgresql interface
Has anyone had any experience with writing an interface between Paradox (199X's vintage) and postgresql. I have the need to be able to read a paradox database to see it's structure, replicate that structure in postgresql and finally transfer the data to postgresql. I have not done any searches of the various lists archives yet for a possible solution. Dale. -- 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] Can Postgres Not Do This Safely ?!?
On Fri, 2010-10-29 at 16:57 -0500, Andy Colson wrote: begin insert into logged select * from events where processed = false; update events set processed = true where processed = false; commit; There's a race condition there. The SELECT in the INSERT statement may read 5 tuples, then a concurrent transaction inserts a 6th tuple, then you do an update on all 6 tuples. begin select * from events where processed = false; ... do you processing on each, which would include inserting it... update events set processed = true where processed = false; commit; Same problem here. Just make sure you do it all in the same transaction, so the update sees the exact same set as the select. You need to use SERIALIZABLE isolation level for this to work. The default is READ COMMITTED. Or better yet, use Merlin's suggestion of PgQ. They've already worked this out in a safe, efficient way. It's the basis for Londiste, a replication system. Regards, Jeff Davis -- 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] Paradox to postgresql interface
On Fri, 2010-10-29 at 18:34 -0500, Dale Seaburg wrote: Has anyone had any experience with writing an interface between Paradox (199X's vintage) and postgresql. I have the need to be able to read a paradox database to see it's structure, replicate that structure in postgresql and finally transfer the data to postgresql. Sure. As I recall paradox just uses the dbf file format. So write an interface with a dbf driver (perl, python, etc...) and use your native postgresql interface. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Paradox to postgresql interface
On Friday 29 October 2010 5:16:14 pm Joshua D. Drake wrote: On Fri, 2010-10-29 at 18:34 -0500, Dale Seaburg wrote: Has anyone had any experience with writing an interface between Paradox (199X's vintage) and postgresql. I have the need to be able to read a paradox database to see it's structure, replicate that structure in postgresql and finally transfer the data to postgresql. Sure. As I recall paradox just uses the dbf file format. So write an interface with a dbf driver (perl, python, etc...) and use your native postgresql interface. Sincerely, Joshua D. Drake A quick Google search found this: http://pxlib.sourceforge.net/pxview/index.php On Windows if you have Access it should have a Paradox driver. I have also used OpenOffice Base to convert dBase files. -- Adrian Klaver adrian.kla...@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] Unhandled exception in PGAdmin when opening 16-million-record table
On 29 October 2010 21:52, Rob Richardson rob.richard...@rad-con.com wrote: A customer was reviewing the database that supports the application we have provided. One of the tables is very simple, but has over 16 million records. Here is the table's definition: CREATE TABLE feedback ( charge integer, elapsed_time integer, -- number of elapsed minutes since data began recording tag_type character varying(24), -- Description of tag being recorded tag_value real, -- value of tag being recorded status smallint, -- PLC Status, recorded with Control PV only stack integer, -- Not used heating smallint DEFAULT 0, -- 1 for heating, 0 for cooling cooling smallint DEFAULT 0 -- not used ) As you see, there is no primary key. There is a single index, as follows: CREATE INDEX feedback_charge_idx ON feedback USING btree (charge); In PGAdmin, the customer selected this table and clicked the grid on the toolbar, asking for all of the records in the table. After twenty minutes, a message box appeared saying that an unhandled exception had happened. There was no explanation of what the exception was. The database log does not contain any information about it. The PGAdmin display did show a number of records, leading me to believe that the error happened in PGAdmin rather than anywhere in PostGres. Can anyone explain what is happening? Does WxWidgets/PgAdmin provide an overload of global operator new() that follows the pre-standard C++ behaviour of returning a null ptr, ala malloc()? C++ application frameworks that eschew exceptions often do. This sounds like an unhandled std::bad_alloc exception. Why don't we have some hard limit on the number of rows viewable in a table? Would that really be so terrible? -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Max tables in a union
What is the maximum number of tables allowed in a union in 8.4 and 9.0? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
