Re: [GENERAL] ECPG problem with 8.3
On Sun, Jan 13, 2008 at 03:01:04PM +, Peter Wilson wrote: > that fixes that problem. My build now gets further, but I get an error > and a seg-fault later in the build. Whow, you're really stress testing it. Thanks a lot! This is what we need. > Apart from the seg-fault, is there any particular reason I can't use a The segfault is fixed in CVS. Reason was that on finding the variable it set an error message but not the normal return value and then tried to proceed anyway. > variable in the FETCH anymore? It's always worked in the past and would > seem to be an important capability. Yes. ECPG move to the latest backend protocol version to be able to prepare statements correctly. However, with this protocol my own addition to the standard, namely a variable as fetch count, is not supported anymore. But there is a simple workaround. Just sprintf the statement to a string and thereby replace the count variable with its content and then EXEC SQL EXECUTE the string variable should do the job. Hope this helps. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] ERROR: shared buffer hash table corrupted
Hello list members,I hav a table with 140M rows. While I am trying to select the count from the tableI am getting following errorERROR: shared buffer hash table corruptedCan anybody please suggest me wht had gone wrong and how to fix it?PostgreSQL 8.2.4OS:Suse 10.3With RegardsAshish... Save all your chat conversations. Find them online.
Re: [GENERAL] tcp_keepalives_idle ignored
On Sun, January 13, 2008 6:53 pm, henry wrote: > On Sun, January 13, 2008 7:25 pm, Tom Lane wrote: >> Martijn van Oosterhout <[EMAIL PROTECTED]> writes: >>> On Sun, Jan 13, 2008 at 08:40:34AM +0200, henry wrote: lsof doesn't tell me what's talking to PG through /tmp/.s.PGSQL.5432 either. Maybe I'm not understanding exactly how /tmp/.s.PGSQL.5432 is used - what would connect to PG via a domain socket? >> >>> Connecting to unix domain socket happens if you don't specify a host. >> >> Specifically, a local connection goes through the socket file by >> default, and via TCP only if you say "-h localhost" or equivalent. WRT the origional question: why is tcp_keepalives_idle being ignored (whether with SET or in postgresql.conf)? - ie, SHOW ALL says it's 0 no matter what I do. Regards Henry ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installation problem: failed to initialize lc_messages to ""
I'm wondering if some of those files are missing, or the symlinks are dangling, on yours. Hmmm. here is what I have. Looks pretty similar Running out of easy answers, aren't we :-( The next thing I can think of is to run initdb under "ktrace -i" and compare the results with a working system. The output of this is likely to be voluminous, so don't send it to the list --- but if you'll send it to me off-list I'll compare it to what I get here. Ufff. I completely reinstalled Leopard (cause was a bit messy, meanwhile). And thought: Hey, now it should be any problem anymore But oups it did it again. Gush! So, looked around again on the net and found this message: I found this referenced under bug #1344: locale problem The problem appears to be in going from Linux 2.6.9-5.0.5 to 2.6.9-11 the default LANG environment variable changed from LANG=en_US.UTF-8 to LANG=english_us.8859. Error could be identified by typing locale at the opsys prompt. I fixed by adding to posgres .bash_profile: LANG=en_US.UTF-8; export LANG (http://www.mail-archive.com/[EMAIL PROTECTED]/msg12073.html) Also mentioned Linux, it seems to be the same problem with Mac... Putting the LANG... into the .bash_profile settled the problem. Hope this helps! Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Forgot to dump old data before re-installing machine
Hi there, I re-installed my machine and "forgot" to dump my database(s). I naturally still have the whole database folders. For the moment I installed the "old" postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Can someone please give me a hint on how I should proceed?! Thanks a lot! Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ECPG problem with 8.3
Michael Meskes wrote: On Sun, Jan 13, 2008 at 03:01:04PM +, Peter Wilson wrote: that fixes that problem. My build now gets further, but I get an error and a seg-fault later in the build. Whow, you're really stress testing it. Thanks a lot! This is what we need. I have to say I didn't write the original code - so I'm not particularly an expert in this area. I just get to maintain it and keep it working with newer releases of Postgres! Apart from the seg-fault, is there any particular reason I can't use a The segfault is fixed in CVS. Reason was that on finding the variable it set an error message but not the normal return value and then tried to proceed anyway. variable in the FETCH anymore? It's always worked in the past and would seem to be an important capability. Yes. ECPG move to the latest backend protocol version to be able to prepare statements correctly. However, with this protocol my own addition to the standard, namely a variable as fetch count, is not supported anymore. But there is a simple workaround. Just sprintf the statement to a string and thereby replace the count variable with its content and then EXEC SQL EXECUTE the string variable should do the job. Fetch with a variable seems to be almost the only useful way of using FETCH ABSOLUTE (or any of the variants that have count parameter). For backwards compatibility wouldn't it be better to do the sprintf in the ECPG preprocessor if the count is a variable rather than generate an error? In that way none of the existing applications would break. I think it's always better to keep the application interface the compatible with existing applications, even if that means a little behind the scenes glue! Hope this helps. Michael Thanks again for your help :-) Pete ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ECPG problem with 8.3
- Original Message > From: Peter Wilson <[EMAIL PROTECTED]> > To: Michael Meskes <[EMAIL PROTECTED]>; pgsql-general@postgresql.org > Sent: Monday, January 14, 2008 8:41:12 AM > Subject: Re: [GENERAL] ECPG problem with 8.3 > > Fetch with a variable seems to be almost the only useful way of > using > FETCH > ABSOLUTE (or any of the variants that have count parameter). > > For backwards compatibility wouldn't it be better to do the sprintf > in > the ECPG > preprocessor if the count is a variable rather than generate an > error? I'd like to add to this discussion from an Oracle Pro*C (Oracle's name for embedded SQL) perspective. Most of the Pro*C code that I've worked with over the years uses a variable for the fetch count as well. It'd be nice if there was some way to support this convention directly in ECPG (assuming it doesn't create maintenance/security issues) for anyone porting applications from Oracle to Postgresql. Regards, Shelby Cain Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ERROR: shared buffer hash table corrupted
Ashish Karalkar <[EMAIL PROTECTED]> writes: > Hello list members,I hav a table with 140M rows. While I am > trying to select the count from the tableI am getting following > errorERROR: shared buffer hash table corruptedCan > anybody please suggest me wht had gone wrong and how to fix > it?PostgreSQL 8.2.4OS:Suse 10.3With > RegardsAshish... >Save all your chat conversations. href="http://in.rd.yahoo.com/tagline_webmessenger_3/*http://in.messenger.yahoo.com/webmessengerpromo.php";>Find > them online. Please avoid posting in HTML on these lists ... or at least not HTML-only. As to the problem, can you extract a reproducible test case? It certainly seems like a bug, but it's not one I can recall hearing of before. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] tcp_keepalives_idle ignored
"henry" <[EMAIL PROTECTED]> writes: > WRT the origional question: why is tcp_keepalives_idle being ignored > (whether with SET or in postgresql.conf)? - ie, SHOW ALL says it's 0 no > matter what I do. I think you're looking at it in a session that's connecting over a Unix socket. You need to be connected over TCP in order for the variable to be meaningful. It'll read as 0 in a socket connection. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tcp_keepalives_idle ignored
"henry" <[EMAIL PROTECTED]> writes: > The culprit in my case was a local trigger firing on INSERTs using > dblink_exec() without 'host=127.0.0.1'. Bad news though, even _with_ > 'host=127.0.0.1' the connections do not idle timeout. They just hang > around waiting for the rapture. > So,... this appears to be dblink related after all. I'll be trying > explicit open/exec/close. Weird that dblink_exec in a trigger doesn't > release resources. Hmm, this is the transient-connection form of dblink? If so, that would be a bug. Can you put together a test case? Also, which PG version are you using, exactly? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ECPG problem with 8.3
Peter Wilson <[EMAIL PROTECTED]> writes: > Michael Meskes wrote: >> Yes. ECPG move to the latest backend protocol version to be able to >> prepare statements correctly. However, with this protocol my own >> addition to the standard, namely a variable as fetch count, is not >> supported anymore. But there is a simple workaround. Just sprintf the >> statement to a string and thereby replace the count variable with its >> content and then EXEC SQL EXECUTE the string variable should do the job. > Fetch with a variable seems to be almost the only useful way of using FETCH > ABSOLUTE (or any of the variants that have count parameter). > For backwards compatibility wouldn't it be better to do the sprintf in > the ECPG preprocessor if the count is a variable rather than generate > an error? I'm concerned about this too. We'll at least have to call this out as an incompatibility in 8.3, and it seems like a rather unnecessary step backwards. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Forgot to dump old data before re-installing machine
Stefan Schwarzer <[EMAIL PROTECTED]> writes: > I re-installed my machine and "forgot" to dump my database(s). I > naturally still have the whole database folders. For the moment I > installed the "old" postgres version (8.1) to be able to read my data. > But how can I read them? It seems that it doesn't work that I just > overwrite the new database folder with the old one... Would be too > simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Online Oracle to Postgresql data migration
Thanks On Jan 12, 2008 9:19 AM, David Fetter <[EMAIL PROTECTED]> wrote: > On Fri, Jan 11, 2008 at 01:02:01PM -0500, Josh Harrison wrote: > > Hi > > We have an Oracle production database with some terbytes of data. We > > wanted to migrate that to Postgresql (rigt now...a test database and > > not production) database. What are the good options to do that? > > I have written some Free software, DBI-Link, for just this use case. > The software is under the BSD license, so you can use it freely. I > also offer consulting on such migrations. > I downloaded DBI-Link. When I tried to compile postgres8.3 with-perl option it gives me this error. ld: fatal: relocations remain against allocatable but non-writable sections collect2: ld returned 1 exit status gmake[3]: *** [libplperl.so.0.0] Error 1 My OS is SunOS 5.10 (The same compiles fine in FreeBSD but gives an error in Solaris). Is this kind 0f solaris-specific error? Do you know what caues this error? Thanks josh
Re: [GENERAL] tcp_keepalives_idle ignored
On Mon, January 14, 2008 5:46 pm, Tom Lane wrote: >> So,... this appears to be dblink related after all. I'll be trying >> explicit open/exec/close. Weird that dblink_exec in a trigger doesn't >> release resources. > > Hmm, this is the transient-connection form of dblink? If so, that would > be a bug. Can you put together a test case? Also, which PG version are > you using, exactly? Version 8.2.4. I *would* normally be using the latest, but I recently made a switch to CentOS (from slackware) and am trying real hard to break old habits (which using a clean package system requires) and not roll my own. I'll get on the test case. Regards Henry ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Members-choice award at LinuxQuestions.org
Hi Guys, Apologies for the cross-post, I already posted this in advocacy a few days ago, and am hoping for a slightly better exposure here. Over at http://www.linuxquestions.org a members-choice award for 2007 is currently going on. Every year I see a great influx of people signing up solely for the purpose of voting for firebird ... to counter- balance that I thought we could maybe get some people to vote for the best OpenSource RDBMS instead? :} Not that firebird wins (most of the time MySQL does :/), but I'd sure like to see more votes for Postgres in that poll. http://www.linuxquestions.org/questions/2007-linuxquestions.org-members-choice-awards-79/database-of-the-year-610185/ Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tcp_keepalives_idle ignored
On Mon, January 14, 2008 12:49 pm, henry wrote: > WRT the origional question: why is tcp_keepalives_idle being ignored > (whether with SET or in postgresql.conf)? - ie, SHOW ALL says it's 0 no > matter what I do. A quick follow-on with more info to my own post. The culprit in my case was a local trigger firing on INSERTs using dblink_exec() without 'host=127.0.0.1'. Bad news though, even _with_ 'host=127.0.0.1' the connections do not idle timeout. They just hang around waiting for the rapture. So,... this appears to be dblink related after all. I'll be trying explicit open/exec/close. Weird that dblink_exec in a trigger doesn't release resources. Regards Henry ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] oddly slow query
Hello. Thanks for the help. Tom Lane wrote: Jessi Berkelhammer <[EMAIL PROTECTED]> writes: Here are the 3 EXPLAIN ANALYZE commands followed by the output: Well, here's the problem: Join Filter: (clinical_reg_current.client_id = client.client_id) -> Subquery Scan clinical_reg_current (cost=754.36..758.23 rows=1 width=4) (actual time=57.359..146.717 rows=684 loops=1) Filter: (tier_program(benefit_type_code) = 'SAGE'::text) -> Unique (cost=754.36..756.47 rows=117 width=211) (actual time=56.427..67.998 rows=1000 loops=1) For some reason it's estimating only one row out of the clinical_reg_current view will satisfy the tier_program(benefit_type_code) = 'SAGE' constraint. This causes it to think a nestloop join to the client view would be a good idea. The same estimation error is present in your example with the function and no join, but it doesn't hurt anything because there are no planning decisions that depend on the estimate in that case. The estimate of the view's rowcount without the filter isn't that great either (117 vs 1000 actual) but it's not wrong enough to prompt selection of a bad plan choice. There's something funny going on with the estimation of the function's selectivity --- does the expression "tier_program(benefit_type_code)" match an index, perhaps? If so, have you updated stats for that table lately? Our database is analyzed & vacuumed nightly. I'm also wondering why the function call isn't getting pushed down further into the plan --- what's the definition of that view look like? Here is the definition of the function: Schema | Name | Result data type | Argument data types | Owner | Language | Source code| Description +--+--+-+-+--+---+- public | tier_program | text | character varying | Chasers | sql | | : : SELECT COALESCE(clinical_project_code,description) FROM l_benefit_type WHERE benefit_type_code = $1; : : Here is the definition of clinical_reg_current view: View "public.clinical_reg_current" Column| Type | Modifiers | Description --++---+- clinical_reg_id | integer| | client_id| integer| | clinical_reg_date| date | | benefit_type_code| character varying(10) | | funding_source_code | character varying(10) | | clinical_reg_date_end| date | | clinical_exit_reason_code| character varying(10) | | kc_authorization_id | integer| | kc_authorization_status_code | character varying(10) | | current_case_rate| numeric(8,2) | | case_rate_reason_code| character varying(10) | | kc_exit_type_code| character varying(10) | | added_by | integer| | added_at | timestamp(0) without time zone | | changed_by | integer| | changed_at | timestamp(0) without time zone | | is_deleted | boolean| | deleted_at | timestamp(0) without time zone | | deleted_by | integer| | deleted_comment | text | | sys_log | text | | View definition: SELECT DISTINCT ON (clinical_reg.client_id) clinical_reg.clinical_reg_id, clinical_reg.client_id, clinical_reg.clinical_reg_date, clinical_reg.benefit_type_code, clinical_reg.funding_source_code, clinical_reg.clinical_reg_date_end, clinical_reg.clinical_exit_reason_code, clinical_reg.kc_authorization_id, clinical_reg.kc_authorization_status_code, clinical_reg.current_case_rate, clinical_reg.case_rate_reason_code, clinical_reg.kc_exit_type_code, clinical_reg.added_by, clinical_reg.added_at, clinical_reg.changed_by, clinical_reg.changed_at, clinical_reg.is_deleted, clinical_reg.deleted_at, clinical_reg.deleted_by, clinical_reg.deleted_comment, clinical_reg.sys_log FROM clinical_reg WHERE clinical_reg.clinical_reg_date <= 'now'::text::date AND (clinical_reg.clinical_reg_date_end >= 'now'::text::date
Re: [GENERAL] oddly slow query
Jessi Berkelhammer <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> For some reason it's estimating only one row out of the >> clinical_reg_current view will satisfy the >> tier_program(benefit_type_code) = 'SAGE' constraint. My math was off the other day --- actually, that's exactly what you'd expect for the default estimate on an equality condition it has no stats for, when the underlying scan is estimated to have only 117 rows. So either you should do something about getting that underlying estimate up closer to reality (perhaps increasing default_statistics_target would improve matters?), or you need to fix things so that the planner can apply its statistics to estimating what is happening with the tier_program constraint. Expressed as a function this way, it's just a black box to the planner so you get a default estimate. Given that the function is just extracting from a table, I think you could remove the function call and express the condition with a join instead, and that might result in a better estimate. >> I'm also wondering why the function call isn't getting pushed down >> further into the plan --- what's the definition of that view look like? > View definition: > SELECT DISTINCT ON (clinical_reg.client_id) Ah, it's the DISTINCT ON that's preventing any better optimization. Not much to be done about that, unless you can recast things to not need DISTINCT ON, which looks a bit hard. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Locking & concurrency - best practices
I have a "parent_tbl" and dozens of data tables, with foreign keys referencing the PK of "parent_tbl" (one-to-many). There are 100+ users accessing the application, usually (but not always) each user is working on a different record in parent_tbl. (this would seem like a pretty standard scenario for a lot of apps) Each user performs multiple queries in a transaction, reading and modifying the data in parent_tbl and multipe data tables before commiting. I need the data to be consistent during and after the transaction. (I basically need a way to lock a row in parent_tbl, and all rows in the data tables referencing that row, and prevent new rows from being inserted that reference that row). To guard against this, I added "FOR UPDATE" to queries against the parent_tbl and "LOCK TABLE IN EXCLUSIVE MODE" before queries against all of the data tables. This works, except it slows down the entire application because all transactions are serialized. Even users who are working on seperate records in parent_tbl are not allowed to proceed simultaneously. This is not ideal, the vast majority of access to this database is users working on separate records. Should I drop the "LOCK TABLE" statements completely? As long as *every* part of the application that modifies data obtains a "FOR UPDATE" lock on the parent table's record first, there shouldn't be any concurrency issues. But, I realize I'm really only implementing advisory locking, and there's nothing preventing data corruption from any application that forgets or leaves out the "FOR UPDATE". Is this the best practice for dealing with this situation? Should I be using real advisory locks instead of "FOR UPDATE" ? What are the pros & cons of each? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
And also remember to use the same version of Postgres as the previous installation... It might be helpful to post the tail of your server's log ahen it fails. Best Regards, On Jan 14, 2008 7:58 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > Stefan Schwarzer <[EMAIL PROTECTED]> writes: > > I re-installed my machine and "forgot" to dump my database(s). I > > naturally still have the whole database folders. For the moment I > > installed the "old" postgres version (8.1) to be able to read my data. > > But how can I read them? It seems that it doesn't work that I just > > overwrite the new database folder with the old one... Would be too > > simple, I guess... > > Should work, if you've got the whole $PGDATA directory tree. Maybe > you forgot to stop the postmaster while copying the backup into place? > >regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008 2:43 PM, Adam Rich <[EMAIL PROTECTED]> wrote: > > I have a "parent_tbl" and dozens of data tables, with foreign keys > referencing the PK of "parent_tbl" (one-to-many). There are 100+ > users accessing the application, usually (but not always) each user > is working on a different record in parent_tbl. (this would seem like > a pretty standard scenario for a lot of apps) You should be able to do "select for update" on both parent and child records and get the effect you desire. Think up your own worst case scenario for concurrent updates, then sit down at two or more psql terminals, and try to simulate such a thing and see what happens. Experimentation is a great tool. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008 3:31 PM, Adam Rich <[EMAIL PROTECTED]> wrote: > > You should be able to do "select for update" on both parent and child > > records and get the effect you desire. > > > > I don't think that will work. Let me demonstrate: > (this is simplified, but sufficient to make my point) > > -- Connection 1 -- > begin trans; > > select * from parent_tbl > where id=1 for update; > > select count(*) into myvar > from data_tbl where fk=1; You're right. with count(*) involved, you won't be able to get an accurate view. Generally speaking, when you've got to count rows like that, locking the table is the only thing that works. That or changing how you process the data. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Locking & concurrency - best practices
Adam Rich wrote: I have a "parent_tbl" and dozens of data tables, with foreign keys referencing the PK of "parent_tbl" (one-to-many). There are 100+ users accessing the application, usually (but not always) each user is working on a different record in parent_tbl. (this would seem like a pretty standard scenario for a lot of apps) Each user performs multiple queries in a transaction, reading and modifying the data in parent_tbl and multipe data tables before commiting. I need the data to be consistent during and after the transaction. (I basically need a way to lock a row in parent_tbl, and all rows in the data tables referencing that row, and prevent new rows from being inserted that reference that row). To guard against this, I added "FOR UPDATE" to queries against the parent_tbl and "LOCK TABLE IN EXCLUSIVE MODE" before queries against all of the data tables. This works, except it slows down the entire application because all transactions are serialized. Even users who are working on seperate records in parent_tbl are not allowed to proceed simultaneously. This is not ideal, the vast majority of access to this database is users working on separate records. Should I drop the "LOCK TABLE" statements completely? As long as *every* part of the application that modifies data obtains a "FOR UPDATE" lock on the parent table's record first, there shouldn't be any concurrency issues. But, I realize I'm really only implementing advisory locking, and there's nothing preventing data corruption from any application that forgets or leaves out the "FOR UPDATE". Is this the best practice for dealing with this situation? Should I be using real advisory locks instead of "FOR UPDATE" ? What are the pros & cons of each? In our program we wrote the locking into the program, and created a modulelock table like: create table moduelock( userid int, module int, primary key (userid, module) ) The program then locks things before it uses them... but we also have pretty low contention for modules. A lock is: begin insert into modulelock... commit; if commit ok, then go ahead. When we are done, delete from modulelock where ... -Andy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Locking & concurrency - best practices
> You should be able to do "select for update" on both parent and child > records and get the effect you desire. > I don't think that will work. Let me demonstrate: (this is simplified, but sufficient to make my point) -- Connection 1 -- begin trans; select * from parent_tbl where id=1 for update; select count(*) into myvar from data_tbl where fk=1; -- connection 2 runs here (see below) -- if (myvar < 3) then update parent_tbl set status=1 where id=1; else update parent_tbl set status=2 where id=1; end if; commit; -- Connection 2 -- begin trans; insert into data_tbl (fk, data) values (1, 'foo'); insert into data_tbl (fk, data) values (1, 'bar'); insert into data_tbl (fk, data) values (1, 'baz'); commit; -- End example -- In what way would you use "FOR UPDATE" on data_tbl to ensure parent_tbl doesn't end up with the wrong status ? AFAIK, "FOR UPDATE" locks only the rows returned, and does nothing to prevent new inserts. using a "serialized" isolation doesn't seem appropriate either. As far as I can tell, the only options are locking the entire data_tbl at the start of both connections (which unfortunately also blocks all other transactions with id/fk != 1), or using advisory locks. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008, at 3:54 PM, andy wrote: In our program we wrote the locking into the program, and created a modulelock table like: create table moduelock( userid int, module int, primary key (userid, module) ) The program then locks things before it uses them... but we also have pretty low contention for modules. A lock is: begin insert into modulelock... commit; if commit ok, then go ahead. When we are done, delete from modulelock where ... From what I can tell, this kind of roll-your-own application level locking system is exactly what advisory locks are for. Search the archives for the last couple of weeks as I remember someone posting some really helpful functions to assist in using advisory locks. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008, at 4:57 PM, Adam Rich wrote: From what I can tell, this kind of roll-your-own application level locking system is exactly what advisory locks are for. Search the archives for the last couple of weeks as I remember someone posting some really helpful functions to assist in using advisory locks. Erik Jones Yes & No... it depends on the lifetime of the locks you need. The new advisory locks in postgres only live for the duration of your session. The ones Andy describes will live past session end, connection end, even through database restarts. And if you're using replication or log shipping, the locks will be propagated to partner databases as well. If you need your locks to live past session end, the advisory locks won't help you. Good point. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Locking & concurrency - best practices
> > From what I can tell, this kind of roll-your-own application level > locking system is exactly what advisory locks are for. Search the > archives for the last couple of weeks as I remember someone posting > some really helpful functions to assist in using advisory locks. > > Erik Jones Yes & No... it depends on the lifetime of the locks you need. The new advisory locks in postgres only live for the duration of your session. The ones Andy describes will live past session end, connection end, even through database restarts. And if you're using replication or log shipping, the locks will be propagated to partner databases as well. If you need your locks to live past session end, the advisory locks won't help you. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Registration for PostgreSQL Conference East now open
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, Registration for PostgreSQL Conference: East is now open. For those who haven't seen the emails, the conference is taking place at University of Maryland in College Park. This location is about 10 minutes from Washington D.C. The dates of the conference are March 29th and 30th, 2008. If you enjoyed West, this conference is going to be bang up. Instead of one room and one day of talks we have 3 (yes three) rooms and 2 (yes two) days worth of content to satisfy your PostgreSQL knowledge feasting needs! Register early, we had to shut down registration for West last October, we are expecting to have to do the same for East! http://www.postgresqlconference.org/ And thank you to those who have signed up for Sponsorhsip Early: Command Prompt: http://www.commandprompt.com/ Continuent: http://www.continuent.com/ Open Technology Group: http://www.otg-inc.com Sun: http://www.sun.com/ Remember all registrations are direct donations to the PostgreSQL Community! Sincerely, Joshua D. Drake PostgreSQL Conference Organizer PostgreSQL SPI Liaison - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHi/pNATb/zqfZUUQRAs2nAJ4vaZUKLwVM4HJLsSZXzu4pRTHEUACeMqXe TulSo0GTjJRMktp/+3hDE5s= =7W67 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What pg_restore does to a non-empty target database
Based on Tom Lane's response, here is version 2 of my attempt to document what pg_restore does to a target database that already contains objects. Version 2 has been limited to the case where pg_dump was run with the --column-inserts option and pg_restore is run with the --clean option. Also, when there is a possible difference between what pg_restore "tries" to do (i.e. the SQL actions it invokes) and what actually happens, Version 2 indicates the latter on a line labeled "Exception:". Version 2 depends on the answer to the following question: Is it true that the sequence of SQL commands that is extracted into a file by: pg_restore --file=foo.sql [other arguments] foo.bak is exactly the sequence of SQL commands that will be executed in database foo by the command: pg_restore --dbname=foo [other arguments] foo.bak (assuming the same [other arguments] in both cases)? If so, having looked at the extracted SQL from several examples, I'm thinking it's a safe bet that no pg_restore DROP, DELETE, or CREATE action will ever fail (except when a DROP's target object does not exist), because pg_restore sequences the "cleaning" activities so they get rid of dependent objects first. Right? If so, that would seem to guarantee that every action that pg_restore tries to do in the narrative below is guaranteed to succeed, except INSERTs, which can fail only as described below. If not, I have another version of the story below that is a lot more qualified and complicated. As before, I solicit your confirmations, corrections, and additions of this document, hoping to get it to the point where my project team (and anyone else who wants it) can use it with confidence. ~ Thanks ~ Ken --- WHAT PG_RESTORE DOES Version 2 Given a pg_restore command, where: * A is the source archive file (as filtered and reordered by a ToC file, if any) produced by pg_dump with "--format=t" and "--column-inserts" options. * T is the target database. * O is a database object (table, function, etc) that exists in A and/or in C. * The pg_restore command has the --clean option, and it does not have the --table= or --trigger= or "--exit-on-error or --disable-triggers options. The following are the changes that the pg_restore command will produce in database T. EFFECTS ON SCHEMA OBJECTS If object O exists in both A and T, pg_restore: * Drops T's version of O. * Creates A's version of O. If object O exists in T but not in A, pg_restore: * Leaves T's version of O unchanged. If object O exists in A but not in T, pg_restore: * Creates A's version of O. EFFECTS ON TABLES AND THEIR DATA Suppose in addition that: * Database object O is a base table. * O contains data in both archive A and target database T. If the command says "--data-only", pg_restore: * Leaves T's schema definition of O unchanged. * Tries to delete all of T's O data. If this causes FK violations, the result depends row-by-row on the ON DELETE action of the FK constraint. * Tries to insert all of A's O data. The INSERT of any row that causes constraint violations or other fatal errors (see below) fails. If the command says "--schema-only", pg_restore: * Drops T's version of O, which deletes T's O data as a side-effect. * Create A's version of O. * Does not try to insert any of A's O data, so O ends up empty. If the command says "--data-only" and "--schema-only", pg_restore: * Leaves O and its data unchanged. If the command says neither "--data-only" nor "--schema-only", pg_restore: * Drops T's version of O. * Assumes that T's O data were deleted (as a side-product of the DROP) * Creates A's version of O. * Inserts all of A's O data. EFFECTS OF DIFFERENCES BETWEEN A AND T TABLE SCHEMAS Suppose in addition that: * The pg_restore command says "--data-only". * T's schema definition of table O is different from A's. If column O.C exists in T's schema but not in A's: * O.C is set to Null in all rows that pg_restore inserts. If column O.C exists in A's schema but not in T's: * The O.C values are lost from all rows that pg_restore inserts. If column O.C exists in both schemas with incompatible types: * All inserts of rows from A fail. If constraint K exists in T's schema but not in A's: * Inserts of rows from A that would violate K fail. If constraint K exists in A's schema but not in T's: * K has no effect on the insertion of rows from A. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Satisfactory Query Time
On Jan 13, 2008 4:55 PM, x asasaxax <[EMAIL PROTECTED]> wrote: > Hi, > >I have a query that takes 0.450 ms. Its a xml query. Is that a good time > for a query? If a have multiple connections on the database, will this time > makes my db slow? How much time is good for a xml query? You have provided zero relevant information. In fact, your question is probably one of the vaguest I've ever read! Anways, 0.450 ms is extremely fast (a disk seek on a good drive will cost 2-5 ms). That computes to over 2000 txn/sec (over 7 million txn/hour). I wouldn't worry to much. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008 5:57 PM, Adam Rich <[EMAIL PROTECTED]> wrote: > > > > From what I can tell, this kind of roll-your-own application level > > locking system is exactly what advisory locks are for. Search the > > archives for the last couple of weeks as I remember someone posting > > some really helpful functions to assist in using advisory locks. > > > > Erik Jones > > Yes & No... it depends on the lifetime of the locks you need. The new > advisory locks in postgres only live for the duration of your session. > The ones Andy describes will live past session end, connection end, > even through database restarts. And if you're using replication or > log shipping, the locks will be propagated to partner databases > as well. > > If you need your locks to live past session end, the advisory locks > won't help you. That's not really a lock (although it behaves like one). That's simply a field in a table that says 'If i'm this do that otherwise do that'. I don't know if there's a formal definition of locks, so I'm loosely going to define them as things that protect access to the data that are not in the data. merlin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Locking & concurrency - best practices
On Jan 14, 2008 4:31 PM, Adam Rich <[EMAIL PROTECTED]> wrote: > > You should be able to do "select for update" on both parent and child > > records and get the effect you desire. > > > > I don't think that will work. Let me demonstrate: > (this is simplified, but sufficient to make my point) > > -- Connection 1 -- > begin trans; > > select * from parent_tbl > where id=1 for update; > > select count(*) into myvar > from data_tbl where fk=1; > > -- connection 2 runs here (see below) -- > > if (myvar < 3) then >update parent_tbl >set status=1 where id=1; > else >update parent_tbl >set status=2 where id=1; > end if; > > commit; > > -- Connection 2 -- > > begin trans; > insert into data_tbl (fk, data) values (1, 'foo'); > insert into data_tbl (fk, data) values (1, 'bar'); > insert into data_tbl (fk, data) values (1, 'baz'); > commit; > > -- End example -- > > In what way would you use "FOR UPDATE" on data_tbl > to ensure parent_tbl doesn't end up with the wrong > status ? AFAIK, "FOR UPDATE" locks only the rows > returned, and does nothing to prevent new inserts. > using a "serialized" isolation doesn't seem appropriate > either. As far as I can tell, the only options are > locking the entire data_tbl at the start of both > connections (which unfortunately also blocks all > other transactions with id/fk != 1), or using > advisory locks. Advisory locks would work here (better that than table lock), but I don't think that's the right approach. Transaction 2 should simply do a select * from parent_tbl where id=1 for update; at the start of the transaction. The idea here is that a property of 'parent_tbl' is the count of _all_ it's data elements. Therefore, locking should be consistently applied at the parent level, so you serialize access to a particular parent. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Locking & concurrency - best practices
> Advisory locks would work here (better that than table lock), but I > don't think that's the right approach. Transaction 2 should simply do > a > select * from parent_tbl > where id=1 for update; > > at the start of the transaction. That's actually what I'm doing (just forgot to include it in the simplified example). What I'm struggling with is that since these locks aren't enforced in one central place, so I have to run the "for update" query in every far corner of my code that touches data, whether or not it reads or writes to parent_tbl. If any of the developers forget to add it, the data can become corrupted. And since I'm essentially using row-level locks as advisory locks, I wondered if just using advisory locks directly would benefit us somehow, in quicker transactions, CPU/memory overhead, WAL, etc. In my real application, there are lots of "parent_tbl" and when I try to "for update" the appropriate ones, I get deadlocks. I know in theory, I only need to lock things in the same order, everywhere. But in practice, it seems hard to achieve. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query to get column-names in table via PG tables?
I am looking for expertise on how to program the equivalent to this query, but using the pg_catalog tables, which I understand have fewer security restrictions than information_schema in some cases: SELECT column_name FROM information_schema.columns WHERE table_catalog=? AND table_schema=? AND table_name=? ORDER BY ordinal_position Do what psql does...launch it with psql -E, and it will echo any internal queries it makes back to you. Do \d on a couple of tables and you should see what is going on. The output of this is very verbose and broken into multiple queries making joins difficult for me to understand, I'm afraid; my current experience level likely will not reliably produce a single-query equivalent to the above. I have to again ask for designer expertise on this one. Also a factor is that since the query will be hard coded into a driver, knowledge of how to make it most durable across server versions would be a benefit (assuming the underlying tables change?). Thank you, Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
