Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs
It is embarassing for me, but I could not reproduce the bug. :( Maybe I just ended up with a corrupted database (or I was just too tired). Behaviour seems to be the same for both SQL and pl/pgsql functions on a new database (and I got rid of the old one). Sorry. --strk; On Thu, Mar 17, 2005 at 06:46:04PM -0500, Tom Lane wrote: > [EMAIL PROTECTED] writes: > > I've tried with 7.4.3 - *good* results with both SQL and PL/PGSQL > > (actually even less that best 8.0.1: 12Mb) > > > I think this makes it a bug... > > You haven't actually provided a test case that would let someone else > reproduce the problem ... > > regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] what to do with backend flowchart
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Robert Treat > Sent: 21 March 2005 19:05 > To: Tom Lane > Cc: pgsql-hackers@postgresql.org; [EMAIL PROTECTED] > Subject: Re: [HACKERS] what to do with backend flowchart > > My thoughts were that it is certainly easier for us to > implement web code that > comes from the web cvs rather than the core cvs, and also > that it seems wierd > to have this type of html/graphics living tucked away inside > the application > code. You seem to be opposed to moving it though (or at > least unconvinced) > so I guess we will work things out on the web side. It wouldn't be the first thing we suck from the core CVS for the web. If it's just a case of keeping the correct set of files up to date on the webside, let me know what they are and I'll set it up for you Robert. /D ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Failed to re-find parent key
What does the error message failed to re-find parent key in "tablename_pkey" mean? This happens reproducibly during VACUUM on a certain table. Would REINDEX fix it? Anything else we should check? This is PostgreSQL 7.4.2. Are there relevant fixes later in the 7.4 series? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Failed to re-find parent key
Peter Eisentraut <[EMAIL PROTECTED]> writes: > What does the error message > failed to re-find parent key in "tablename_pkey" > mean? This happens reproducibly during VACUUM on a certain table. If it happens during vacuum (not vacuum full) then it must be coming from _bt_pagedel, and it means that _bt_pagedel could not find the parent-level btree entry for the page it wants to remove from the index. > Would REINDEX fix it? Anything else we should check? REINDEX would fix it, but it would be interesting to find out what the actual cause is. I think we've seen one or two similar reports previously in 7.4.*, but there's never been enough info to track it down. Any chance of going in with a debugger, or capturing a tarball image of the database for someone else to look at? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Failed to re-find parent key
On Tue, Mar 22, 2005 at 12:31:55PM +0100, Peter Eisentraut wrote: > What does the error message > > failed to re-find parent key in "tablename_pkey" > > mean? This happens reproducibly during VACUUM on a certain table. This has been reported before, but no one has been able to reproduce it (not the VACUUM, but the steps that led the index to that state). This is probably a very subtle bug introduced after the page-reusing code was introduced in nbtree. I don't think it has been corrected in later releases. There are two ocurrences of this error message in the code: one is while trying to split a page, inserting the pointer to the new page in its parent. This one is not what you are seeing, because during vacuum no splitting takes place. The other ocurrence is at the first pass of page recovery, which happens at VACUUM. The code tries to find the parent page to delete the pointer that leads to the page being unlinked; if it can't find said pointer, the error you see is issued. I think it takes a lot of concurrency for the situation to arise. > Would REINDEX fix it? Anything else we should check? Maybe you could see exactly what page is causing the problem, pg_filedump it, and see what's the exact problem. Yes, a REINDEX fixes it (at least it did in Gaetano's case.) -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Hay quien adquiere la mala costumbre de ser infeliz" (M. A. Evans) ---(end of broadcast)--- TIP 3: 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: [HACKERS] Failed to re-find parent key
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I think it takes a lot of concurrency for > the situation to arise. Maybe. Since Peter can reproduce the error, there's not any concurrency misbehavior involved in VACUUM itself; what we are dealing with is probably corruption in the on-disk state of the index (or maybe a legal corner case that _bt_pagedel mishandles). There might have been concurrency to blame for getting into that state in the first place. Need data ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Failed to re-find parent key
Am Dienstag, 22. März 2005 15:54 schrieb Tom Lane: > Any chance of going in with a debugger, or capturing a > tarball image of the database for someone else to look at? Unfortunately, this database is restricted and I don't have access myself. I will tell the customer that they should provide a data directory tarball if they are interested in researching the problem, but I don't expect much. The database does have a lot of concurrent read and write access and extremely high load, but I am aware that this doesn't help pinpointing the problem. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Permissions on tables and views
Hi, I have a requirement where I have a table and a view on top of it. I want to make some changes so that a user in a given group would be able to insert/update only on the view (ofcourse i have a rule which inserts/updates the table) but not on the underlying table. I tried revoke the insert/update permissions on the table, but it fails even the inserts/updates on the view. How can i set these permissions ? Thanks, Subhash. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Proposal: OUT parameters for plpgsql
Pavel Stehule <[EMAIL PROTECTED]> writes: > On Mon, 21 Mar 2005, Tom Lane wrote: >> So typical call style would be >> SELECT * FROM foo(1,2,'xyzzy'); > I am not sure so this syntax is readable. I'm sure, so this solution is > possible and usefull, but you mix SRF style of calling and normal style. Well, what I am after here is basically to try to fix the function definition style to be more standard. The SELECT FROM calling style is admittedly not standard, but it's what we already support and will have to continue to support indefinitely. We can go back and work on that end of things in the future; I don't think this proposal forecloses anything as far as other call syntaxes go. > For anonymous out record (not OUT parameters) is better Firebird syntax > > CREATE FUNCTION fce (...) RETURNS (c1 integer, c2 integer) AS That might be more readable, but it's not standard and not flexible (no INOUT parameters), so I don't really see the advantage. > When I use OUT params I have to have DECLARE command for variables > DECLARE b integer; That's basically what I want to avoid, for the time being at least. Variables in straight SQL don't make any sense to me: variables go with conventional, imperative programming languages and SQL really isn't one. A variable for an OUT result should live in some program that is calling SQL, which means it's a feature for client-side code or a feature that exists inside a PL. The advantage of what I am proposing is basically that we can create functions that return OUT parameters without having to buy into inventing SQL variables. We can always do that later if we decide we want to. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] odd problem !
Hi there, below is the problem I just bitten when play with toy db. I did: 1.initdb -D ./t1 2. pg_ctl -D ./t1 start 3. createdb test 4. psql test -c "create table a (f integer);" 5. run script which populates table a in background perl bgupdate.pl & 6. cp -a ./t1 ./t2 6. pg_ctl -D ./t1 stop it's waiting for bgupdate.pl, so I killed script 7. pg_ct -D ./t2 start see, that everything is fine. 8. pg_ctl -D ./t2 stop 9. pg_ctl -D ./t1 start 10. [EMAIL PROTECTED]:~/test$ psql -l List of databases Name| Owner | Encoding ---+--+-- template0 | postgres | KOI8 template1 | postgres | KOI8 test | postgres | KOI8 (3 rows) 11. [EMAIL PROTECTED]:~/test$ psql test FATAL: database "test" does not exist psql: FATAL: database "test" does not exist So, how it's possible ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Another history question
I know that postgres was a project directed by Michael Stonebraker in Berkeley (1986-1994) and that soon Jolly Chen and Andrew Yu did postgres95. I understand that the main change in postgres95 was to implement SQL instead of POSTQUEL. Then after the appearance of postgres95 postgreSQL 6.0 arises. And what came later it is well-known history. My question is if the architecture of postgreSQL were inherited of postgres original project or postgreSQL were developed completely with a new concept. Thanks in advance. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] odd problem !
OK, here is more cleaner cut and paste from my notebook: [EMAIL PROTECTED]:~/test$ initdb -D ./t1 [EMAIL PROTECTED]:~/test$ pg_ctl -D ./t1 start postmaster starting [EMAIL PROTECTED]:~/test$ LOG: database system was shut down at 2005-03-23 01:09:34 MSK LOG: checkpoint record is at 0/A2C844 LOG: redo record is at 0/A2C844; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 544; next OID: 17230 LOG: database system is ready [EMAIL PROTECTED]:~/test$ createdb test CREATE DATABASE [EMAIL PROTECTED]:~/test$ psql test -c "create table a (f integer);" CREATE TABLE $i); commit;"; done COMMIT COMMIT COMMIT COMMIT COMMIT COMMIT COMMIT COMMIT COMMIT COMMIT [EMAIL PROTECTED]:~/test$ cp -a ./t1 ./t2 [EMAIL PROTECTED]:~/test$ rm ./t2/postmaster.pid rm: remove regular file ./t2/postmaster.pid'? y [EMAIL PROTECTED]:~/test$ pg_ctl -D ./t1 stop LOG: received smart shutdown request LOG: shutting down waiting for postmaster to shut downLOG: database system is shut down done postmaster stopped [EMAIL PROTECTED]:~/test$ LOG: database system was interrupted at 2005-03-23 01:10:06 MSK LOG: checkpoint record is at 0/A2C844 LOG: redo record is at 0/A2C844; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 544; next OID: 17230 LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/A2C880 LOG: record with zero length at 0/A4FD38 LOG: redo done at 0/A4FD10 LOG: database system is ready [EMAIL PROTECTED]:~/test$ psql test -c "select count(*) from a;" count --- 10 (1 row) [EMAIL PROTECTED]:~/test$ pg_ctl -D ./t2 stop waiting for postmaster to shut downLOG: received smart shutdown request LOG: shutting down LOG: database system is shut down done postmaster stopped [EMAIL PROTECTED]:~/test$ pg_ctl -D ./t1 start postmaster starting [EMAIL PROTECTED]:~/test$ LOG: database system was shut down at 2005-03-23 01:11:40 MSK LOG: checkpoint record is at 0/A4FD38 LOG: redo record is at 0/A4FD38; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 568; next OID: 17243 LOG: database system is ready [EMAIL PROTECTED]:~/test$ psql test -c "select count(*) from a;" ERROR: relation "a" does not exist ERROR: relation "a" does not exist So, where is my table 'a' ? Oleg On Wed, 23 Mar 2005, Oleg Bartunov wrote: Hi there, below is the problem I just bitten when play with toy db. I did: 1.initdb -D ./t1 2. pg_ctl -D ./t1 start 3. createdb test 4. psql test -c "create table a (f integer);" 5. run script which populates table a in background perl bgupdate.pl & 6. cp -a ./t1 ./t2 6. pg_ctl -D ./t1 stop it's waiting for bgupdate.pl, so I killed script 7. pg_ct -D ./t2 start see, that everything is fine. 8. pg_ctl -D ./t2 stop 9. pg_ctl -D ./t1 start 10. [EMAIL PROTECTED]:~/test$ psql -l List of databases Name| Owner | Encoding ---+--+-- template0 | postgres | KOI8 template1 | postgres | KOI8 test | postgres | KOI8 (3 rows) 11. [EMAIL PROTECTED]:~/test$ psql test FATAL: database "test" does not exist psql: FATAL: database "test" does not exist So, how it's possible ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] WAL: O_DIRECT and multipage-writer
On Tue, Jan 25, 2005 at 06:06:23PM +0900, ITAGAKI Takahiro wrote: > Environment: > OS : Linux kernel 2.6.9 > CPU: Pentium 4 3GHz > disk : ATA 5400rpm (Data and WAL are placed on same partition.) > memory : 1GB > config : shared_buffers=1, wal_buffers=256, >XLOG_SEG_SIZE=256MB, checkpoint_segment=4 Hi Itagaki, In light of this thread, have you compared the performance on Linux-2.4? Direct io on block device has performance regression on 2.6.x kernel http://www.ussg.iu.edu/hypermail/linux/kernel/0503.1/0328.html Mark ---(end of broadcast)--- TIP 3: 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: [HACKERS] odd problem !
Oleg Bartunov writes: > below is the problem I just bitten when play with toy db. I did: > 1.initdb -D ./t1 > 2. pg_ctl -D ./t1 start > 3. createdb test > 4. psql test -c "create table a (f integer);" > 5. run script which populates table a in background > perl bgupdate.pl & > 6. cp -a ./t1 ./t2 I would not really expect this to produce a usable copy at all... > 6. pg_ctl -D ./t1 stop > it's waiting for bgupdate.pl, so I killed script > 7. pg_ct -D ./t2 start > see, that everything is fine. Exactly how much did you test? However, that doesn't seem relevant to your subsequent problem with the original. > 8. pg_ctl -D ./t2 stop > 9. pg_ctl -D ./t1 start > 10. > [EMAIL PROTECTED]:~/test$ psql -l > List of databases > Name| Owner | Encoding > ---+--+-- > template0 | postgres | KOI8 > template1 | postgres | KOI8 > test | postgres | KOI8 > (3 rows) > 11. > [EMAIL PROTECTED]:~/test$ psql test > FATAL: database "test" does not exist > psql: FATAL: database "test" does not exist What PG version is this exactly? I suppose that you're seeing one of the GetRawDatabase corner cases, but I'm not quite sure why the shutdown and restart after the createdb wouldn't have fixed it. Does vacuuming pg_database and then checkpointing make it work? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] odd problem !
On Tue, 22 Mar 2005, Tom Lane wrote: template1 | postgres | KOI8 test | postgres | KOI8 (3 rows) 11. [EMAIL PROTECTED]:~/test$ psql test FATAL: database "test" does not exist psql: FATAL: database "test" does not exist What PG version is this exactly? I suppose that you're seeing one of the GetRawDatabase corner cases, but I'm not quite sure why the shutdown and restart after the createdb wouldn't have fixed it. Does vacuuming pg_database and then checkpointing make it work? REL8_0_STABLE, I believe. I posted another problem, now "cut and pasted". regards, tom lane Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Another history question
Juan Pablo Espino <[EMAIL PROTECTED]> writes: > My question is if the architecture of postgreSQL were inherited of > postgres original project or postgreSQL were developed completely with > a new concept. Thanks in advance. There hasn't been any fundamental rearchitecting since Berkeley days. For instance, look at Postgres 4.2 --- those sources are available on the net, and if you compare them to current CVS you'll find plenty that's recognizably the same code. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] odd problem !
Oleg Bartunov writes: >> What PG version is this exactly? > REL8_0_STABLE, I believe. I posted another problem, now "cut and pasted". I've been able to duplicate this here. What is happening is that the damage to ./t1 is being done when you start the postmaster in ./t2. It looks to me like the fundamental problem is that the t2 postmaster is replaying the WAL-logged CREATE DATABASE command from t1's xlog, and *that log entry contains an absolute path name*. So the CREATE replay is wiping out t1's "test" database subdirectory. This isn't a problem in normal use of course, but it'd be a serious issue for someone engaging in WAL-shipping, if their backup postmaster were living at a different absolute path. We probably need to think about whether we can make CREATE DATABASE log only relative paths. Log-shipping CREATE TABLESPACE commands is even more interesting :-(. Not sure how to deal with that. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [HACKERS] odd problem !
On Tue, 22 Mar 2005, Tom Lane wrote: Oleg Bartunov writes: What PG version is this exactly? REL8_0_STABLE, I believe. I posted another problem, now "cut and pasted". I've been able to duplicate this here. What is happening is that the damage to ./t1 is being done when you start the postmaster in ./t2. It looks to me like the fundamental problem is that the t2 postmaster is replaying the WAL-logged CREATE DATABASE command from t1's xlog, and *that log entry contains an absolute path name*. So the CREATE replay is wiping out t1's "test" database subdirectory. I suspected this. btw,is there any utility to see WAL log in human-readable format ? This isn't a problem in normal use of course, but it'd be a serious issue for someone engaging in WAL-shipping, if their backup postmaster were living at a different absolute path. We probably need to think right, this is normal situation if you backup to the same server. Not sure how it's usefull, but still about whether we can make CREATE DATABASE log only relative paths. any problem ? Log-shipping CREATE TABLESPACE commands is even more interesting :-(. Not sure how to deal with that. in general case it's impossible. Just speculating, what if we have some dedicated directory doing symbolical links there for all tablespaces ? regards, tom lane Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] odd problem !
Oleg Bartunov writes: >> This isn't a problem in normal use of course, but it'd be a serious >> issue for someone engaging in WAL-shipping, if their backup postmaster >> were living at a different absolute path. We probably need to think > right, this is normal situation if you backup to the same server. > Not sure how it's usefull, but still >> about whether we can make CREATE DATABASE log only relative paths. > any problem ? I've committed a fix --- please check that it solves your problem. (I have to leave for a few hours so I can't check it myself right away.) >> Log-shipping CREATE TABLESPACE commands is even more interesting :-(. >> Not sure how to deal with that. > in general case it's impossible. Yeah :-( ... needs more thought. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Prevent conflicting SET options from being set
Qingqing Zhou wrote: > > "Tom Lane" <[EMAIL PROTECTED]> writes > > > > We already have the ability to issue custom messages in assign_hooks, > > and I think that's sufficient in practice. > > Yes, I agree this is already sufficient - seems we need to remove that TODO > item in the list. Removed. I think we have all the conflicting options fixed already: < o Prevent conflicting SET options from being set < < This requires a checking function to be called after the server < configuration file is read. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using new copy libpq functions on a v2 protocol backend
Hey guys, I really need answer to this one, for the PHP code I just committed :P Chris Christopher Kings-Lynne wrote: Hi, If I use PQgetCopyData, PQputCopyData and PQputCopyEnd against a v2 protocol backend, will it work? I see no mention of it in the docs... Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway wrote: AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are adding triggers to (the PK table, in the case of ALTER TABLE). Is this necessary? I don't see why we can't allow SELECT queries on the table to proceed while the trigger is being added. Attached is a patch that changes both to use ShareRowExclusiveLock, and updates the documentation accordingly. I'll apply this later today, barring any objections. -Neil Index: doc/src/sgml/mvcc.sgml === RCS file: /var/lib/cvs/pgsql/doc/src/sgml/mvcc.sgml,v retrieving revision 2.47 diff -c -r2.47 mvcc.sgml *** doc/src/sgml/mvcc.sgml 26 Feb 2005 18:37:17 - 2.47 --- doc/src/sgml/mvcc.sgml 23 Mar 2005 00:46:36 - *** *** 654,661 ! This lock mode is not automatically acquired by any ! PostgreSQL command. --- 654,661 ! Acquired by CREATE TRIGGER and ALTER ! TABLE ADD FOREIGN KEY. Index: src/backend/commands/tablecmds.c === RCS file: /var/lib/cvs/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.148 diff -c -r1.148 tablecmds.c *** src/backend/commands/tablecmds.c 20 Mar 2005 22:00:52 - 1.148 --- src/backend/commands/tablecmds.c 23 Mar 2005 00:49:08 - *** *** 3829,3841 Oid constrOid; /* ! * Grab an exclusive lock on the pk table, so that someone doesn't ! * delete rows out from under us. (Although a lesser lock would do for ! * that purpose, we'll need exclusive lock anyway to add triggers to ! * the pk table; trying to start with a lesser lock will just create a ! * risk of deadlock.) */ ! pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock); /* * Validity and permissions checks --- 3829,3839 Oid constrOid; /* ! * Grab a lock on the pk table, so that someone doesn't delete ! * rows out from under us; ShareRowExclusive should be good ! * enough. */ ! pkrel = heap_openrv(fkconstraint->pktable, ShareRowExclusiveLock); /* * Validity and permissions checks Index: src/backend/commands/trigger.c === RCS file: /var/lib/cvs/pgsql/src/backend/commands/trigger.c,v retrieving revision 1.178 diff -c -r1.178 trigger.c *** src/backend/commands/trigger.c 20 Mar 2005 23:40:24 - 1.178 --- src/backend/commands/trigger.c 23 Mar 2005 00:49:53 - *** *** 87,93 ObjectAddress myself, referenced; ! rel = heap_openrv(stmt->relation, AccessExclusiveLock); if (stmt->constrrel != NULL) constrrelid = RangeVarGetRelid(stmt->constrrel, false); --- 87,98 ObjectAddress myself, referenced; ! /* ! * We need to prevent concurrent CREATE TRIGGER commands, as well ! * as concurrent table modifications (INSERT, DELETE, UPDATE), so ! * ShareRowExclusive is sufficient. ! */ ! rel = heap_openrv(stmt->relation, ShareRowExclusiveLock); if (stmt->constrrel != NULL) constrrelid = RangeVarGetRelid(stmt->constrrel, false); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
If you want to be my friend forever, then fix CLUSTER so that it uses sharerowexclusive as well :D Chris Neil Conway wrote: Neil Conway wrote: AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are adding triggers to (the PK table, in the case of ALTER TABLE). Is this necessary? I don't see why we can't allow SELECT queries on the table to proceed while the trigger is being added. Attached is a patch that changes both to use ShareRowExclusiveLock, and updates the documentation accordingly. I'll apply this later today, barring any objections. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Prevent conflicting SET options from being set
"Bruce Momjian" writes > Removed. I think we have all the conflicting options fixed already: > One more thing, there is a small typo in TODO list: duplidated "Allow a warm standby system to also allow read-only queries ". Regards, Qingqing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql and pg_dump using obselete copy commands
Christopher Kings-Lynne wrote: > Should psql and pg_dump be upgraded to use the new v3 protocol copy > functions if they are available, as they are currently using the > deprecated API. We have been telling people to use newer pg_dump's on older servers, but we only support reloading into the current PostgreSQL version, so I see no reason not to updated it to the current syntax. We added the new syntax in 7.3. Added to TODO: o Update pg_dump to use the newer COPY syntax -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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: [HACKERS] psql and pg_dump using obselete copy commands
We have been telling people to use newer pg_dump's on older servers, but we only support reloading into the current PostgreSQL version, so I see no reason not to updated it to the current syntax. We added the new syntax in 7.3. Added to TODO: o Update pg_dump to use the newer COPY syntax I think you misunderstood me - all I think we should do is switch to using PQgetCopyData instead of PQgetline (deprecated) if the libpq pg_dump is built against has it. (And the protocol supports it.) Same for PQputCopyData vs. PQputline. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] psql and pg_dump using obselete copy commands
Oh, if we do that, do we disallow connecting to older servers? Not at all, since the logic would be like this: if we have new copy functions and we have protocol version function and protocol version >= 3 then use new copy functions else use old copy functions That would be even simpler if someone would answer my question about the new copy functions working on a pre v3 server. (I'm setting up to test that atm) Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] psql and pg_dump using obselete copy commands
Christopher Kings-Lynne wrote: > > We have been telling people to use newer pg_dump's on older servers, but > > we only support reloading into the current PostgreSQL version, so I see > > no reason not to updated it to the current syntax. > > > > We added the new syntax in 7.3. > > > > Added to TODO: > > > > o Update pg_dump to use the newer COPY syntax > > I think you misunderstood me - all I think we should do is switch to > using PQgetCopyData instead of PQgetline (deprecated) if the libpq > pg_dump is built against has it. (And the protocol supports it.) > > Same for PQputCopyData vs. PQputline. Oh, if we do that, do we disallow connecting to older servers? TODO item removed. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
On Wed, 23 Mar 2005 12:40 pm, Christopher Kings-Lynne wrote: > If you want to be my friend forever, then fix CLUSTER so that it uses > sharerowexclusive as well :D > I don't think it's as easy as that, because you have to move tuples around in the cluster operation. Same sort of issue as vacuum full I would suggest. Russell Smith > Chris > > Neil Conway wrote: > > Neil Conway wrote: > > > >> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and > >> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are > >> adding triggers to (the PK table, in the case of ALTER TABLE). Is this > >> necessary? I don't see why we can't allow SELECT queries on the table > >> to proceed while the trigger is being added. > > > > > > Attached is a patch that changes both to use ShareRowExclusiveLock, and > > updates the documentation accordingly. I'll apply this later today, > > barring any objections. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 3: 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: [HACKERS] Using new copy libpq functions on a v2 protocol backend
Christopher Kings-Lynne wrote: > >> If I use PQgetCopyData, PQputCopyData and PQputCopyEnd against a v2 > >> protocol backend, will it work? > >> > >> I see no mention of it in the docs... > > OK, my testing proves that they work just fine against an older server, > so no problem. > OK, what is the TODO? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Using new copy libpq functions on a v2 protocol backend
If I use PQgetCopyData, PQputCopyData and PQputCopyEnd against a v2 protocol backend, will it work? I see no mention of it in the docs... OK, my testing proves that they work just fine against an older server, so no problem. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
If you want to be my friend forever, then fix CLUSTER so that it uses sharerowexclusive as well :D I don't think it's as easy as that, because you have to move tuples around in the cluster operation. Same sort of issue as vacuum full I would suggest. Cluster doesn't move rows... I didn't say it was easy. It would involve changing how cluster works. It would keep the old table around while building the new, then grab an exclusive lock to swap the two. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql and pg_dump using obselete copy commands
Christopher Kings-Lynne wrote: > > Oh, if we do that, do we disallow connecting to older servers? > > Not at all, since the logic would be like this: > > if we have new copy functions >and we have protocol version function > and protocol version >= 3 >then use new copy functions > else >use old copy functions > > That would be even simpler if someone would answer my question about the > new copy functions working on a pre v3 server. (I'm setting up to test > that atm) I just talked to Christopher via IM. He says the new fuctions work with older server, and pg_dump always uses the libpq version that shipped with that version (or a newer one) so we have for TODO added: o Update pg_dump and psql to use the new COPY libpq API -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] odd problem !
Tom Lane wrote: > Oleg Bartunov writes: > >> This isn't a problem in normal use of course, but it'd be a serious > >> issue for someone engaging in WAL-shipping, if their backup postmaster > >> were living at a different absolute path. We probably need to think > > > right, this is normal situation if you backup to the same server. > > Not sure how it's usefull, but still > > >> about whether we can make CREATE DATABASE log only relative paths. > > > any problem ? > > I've committed a fix --- please check that it solves your problem. > (I have to leave for a few hours so I can't check it myself right away.) > > >> Log-shipping CREATE TABLESPACE commands is even more interesting :-(. > >> Not sure how to deal with that. > > > in general case it's impossible. > > Yeah :-( ... needs more thought. Is this a TODO? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
On Wed, Mar 23, 2005 at 10:42:01AM +0800, Christopher Kings-Lynne wrote: > >>If you want to be my friend forever, then fix CLUSTER so that it uses > >>sharerowexclusive as well :D > > > >I don't think it's as easy as that, because you have to move tuples > >around in the cluster operation. Same sort of issue as vacuum full I > >would suggest. > > Cluster doesn't move rows... > > I didn't say it was easy. It would involve changing how cluster works. > It would keep the old table around while building the new, then grab > an exclusive lock to swap the two. Huh, cluster already does that. I don't remember what the rationale was for locking the table, leaving even simple SELECTs out. (In fact, IIRC the decision wasn't made by me, and it wasn't discussed at all.) -- Alvaro Herrera (<[EMAIL PROTECTED]>) "I would rather have GNU than GNOT." (ccchips, lwn.net/Articles/37595/) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Christopher Kings-Lynne wrote: If you want to be my friend forever, then fix CLUSTER so that it uses sharerowexclusive as well :D Hmm, this might be possible as well. During a CLUSTER, we currently - lock the heap relation with AccessExclusiveLock - lock the index we're clustering on with AccessExclusiveLock - create a temporary heap relation - fill with data from the old heap relation, via an index scan - swap the relfilenodes of the old and temporary heap relations - rebuild indexes We certainly can't allow concurrent modifications to either the table or the clustered index while this is happening. Allowing index scans *should* be safe -- an index scan could result in modifications to the index (e.g. updating "tuple is killed" bits), but those shouldn't be essential. We might also want to disallow SELECT FOR UPDATE, since we would end up invoking heap_mark4update() on the old heap relation. Not sure offhand how serious that would be. So I think it should be possible to lock both the heap relation and the index with ExclusiveLock, which would allow SELECTs on them. This would apply to both the single relation and multiple relation variants of CLUSTER (since we do each individual clustering in its own transaction). ... except that when we rebuild the relation's indexes, we acquire an AccessExclusiveLock on the index. This would introduce the risk of deadlock. It seems necessary to acquire an AccessExclusiveLock when rebuilding shared indexes, since we do the index build in-place, but I think we can get by with an ExclusiveLock in the non-shared case, for similar reasons as above: we build the new index and then swap relfilenodes. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Prevent conflicting SET options from being set
Thanks, fixed. --- Qingqing Zhou wrote: > > "Bruce Momjian" writes > > Removed. I think we have all the conflicting options fixed already: > > > > One more thing, there is a small typo in TODO list: duplidated "Allow a warm > standby system to also allow read-only queries ". > > Regards, > Qingqing > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Huh, cluster already does that. It does and it doesn't. Something like the first thing it does is muck with the old table's filenode IIRC, meaning that immediately the old table will no longer work. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway wrote: > So I think it should be possible to lock both the heap relation and the > index with ExclusiveLock, which would allow SELECTs on them. This would > apply to both the single relation and multiple relation variants of > CLUSTER (since we do each individual clustering in its own transaction). > > ... except that when we rebuild the relation's indexes, we acquire an > AccessExclusiveLock on the index. This would introduce the risk of > deadlock. It seems necessary to acquire an AccessExclusiveLock when > rebuilding shared indexes, since we do the index build in-place, but I > think we can get by with an ExclusiveLock in the non-shared case, for > similar reasons as above: we build the new index and then swap relfilenodes. Certainly we need to upgrade to an exclusive table lock to replace the heap table. Do we want to get a shared lock and possibly starve waiting for an exclusive lock on the table to swap the new one in? Do we do such escallation anywhere else? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Bruce Momjian wrote: Certainly we need to upgrade to an exclusive table lock to replace the heap table. Well, we will be holding an ExclusiveLock on the heap relation regardless. We "replace" the heap table by swapping its relfilenode, so ISTM we needn't hold an AccessExclusiveLock. Do we want to get a shared lock and possibly starve waiting for an exclusive lock on the table to swap the new one in? What I'm saying is that REINDEX on non-shared indexes need only acquire an ExclusiveLock, and hence not need to escalate its lock. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway <[EMAIL PROTECTED]> writes: >> AndrewSN pointed out on IRC that ALTER TABLE ... ADD FOREIGN KEY and >> CREATE TRIGGER both acquire AccessExclusiveLocks on the table they are >> adding triggers to (the PK table, in the case of ALTER TABLE). Is this >> necessary? I don't see why we can't allow SELECT queries on the table to >> proceed while the trigger is being added. > Attached is a patch that changes both to use ShareRowExclusiveLock, and > updates the documentation accordingly. I'll apply this later today, > barring any objections. I don't think this has been adequately thought through at all ... but at least make it ExclusiveLock. What is the use-case for allowing SELECT FOR UPDATE in parallel with this? One may suppose that someone doing SELECT FOR UPDATE intends an UPDATE. (No, don't tell me about foreign keys. Alvaro is going to fix that.) As Chris suggests nearby, this is really only the tip of the iceberg. I would prefer to see someone do a survey of all our DDL commands and put forward a coherent proposal for minimum required locks for all of them. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway <[EMAIL PROTECTED]> writes: > /* > ! * Grab an exclusive lock on the pk table, so that someone doesn't > ! * delete rows out from under us. (Although a lesser lock would do for > ! * that purpose, we'll need exclusive lock anyway to add triggers to > ! * the pk table; trying to start with a lesser lock will just create a > ! * risk of deadlock.) >*/ > ! pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock); > /* >* Validity and permissions checks > --- 3829,3839 > Oid constrOid; > /* > ! * Grab a lock on the pk table, so that someone doesn't delete > ! * rows out from under us; ShareRowExclusive should be good > ! * enough. >*/ BTW, the above comment change is seriously inadequate, because it removes the explanation of *why* that is the minimum required lock. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > It would keep the old table around while building the new, then grab > an exclusive lock to swap the two. Lock upgrading is right out. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway wrote: ... except that when we rebuild the relation's indexes, we acquire an AccessExclusiveLock on the index. This would introduce the risk of deadlock. It seems necessary to acquire an AccessExclusiveLock when rebuilding shared indexes, since we do the index build in-place, but I think we can get by with an ExclusiveLock in the non-shared case, for similar reasons as above: we build the new index and then swap relfilenodes. From looking at the code, it should be quite possible to do this. Further points from discussion on IRC: - TRUNCATE suffers from the same behavior (it acquires an AccessExclusiveLock where really an ExclusiveLock or similar should be good enough) - if we make these changes, we will need some way to delete a no-longer-visible relfilenode. It should be sufficient to delete a relfilenode when the expired pg_class row that refers to it is no longer visible to any transactions -- but this isn't necessarily going to be true when the transaction that executed the REINDEX/CLUSTER/TRUNCATE commits. We could perform this check in some kind of periodic process, perhaps -- like the bgwriter, at checkpoint time. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway <[EMAIL PROTECTED]> writes: > Well, we will be holding an ExclusiveLock on the heap relation > regardless. We "replace" the heap table by swapping its relfilenode, so > ISTM we needn't hold an AccessExclusiveLock. Utterly wrong. When you commit you will physically drop the old table. If there is a SELECT running against the old table it will be quite unhappy after that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Tom Lane wrote: Utterly wrong. When you commit you will physically drop the old table. If there is a SELECT running against the old table it will be quite unhappy after that. How can we drop the file at commit, given that a serializable transaction's snapshot should still be able to see old relfilenode's content? (If the serializable transaction has already acquired a read lock before the TRUNCATE begins, it will block the TRUNCATE -- but there is no guarantee that the operations will be ordered like that.) -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] swapping relfilenodes (was: Re: locks in CREATE TRIGGER, ADD FK)
On 2005-03-23, Neil Conway <[EMAIL PROTECTED]> wrote: > - swap the relfilenodes of the old and temporary heap relations While discussing this one further on IRC, I noticed the following: Everywhere I could find that currently replaces the relfilenode of a relation does so while holding an AccessExclusive lock, and assumes that this is sufficient to ensure that the old relfilenode can be killed when the transaction commits. This is not correct. Example: - backend A begins a serializable transaction - backend B truncates a table (and commits) - backend A, still in the same transaction, accesses the truncated table Currently backend A sees the truncated table as empty, which is obviously not right. This is obviously related to any attempt to weaken the locking on other operations that modify relfilenodes, because doing it right implies a mechanism to defer the removals past the commit of the modifying transaction and up to the point where the old data can no longer be seen by a live transaction. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway wrote: > Tom Lane wrote: > > Utterly wrong. When you commit you will physically drop the old table. > > If there is a SELECT running against the old table it will be quite > > unhappy after that. > > How can we drop the file at commit, given that a serializable > transaction's snapshot should still be able to see old relfilenode's > content? Vacuum will not remove any old rows because of the transaction xid so why does it care if the table is clustered/reindexed? It doesn't have the table open yet. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] swapping relfilenodes (was: Re: locks in CREATE TRIGGER,
Andrew - Supernews wrote: > On 2005-03-23, Neil Conway <[EMAIL PROTECTED]> wrote: > > - swap the relfilenodes of the old and temporary heap relations > > While discussing this one further on IRC, I noticed the following: > > Everywhere I could find that currently replaces the relfilenode of a > relation does so while holding an AccessExclusive lock, and assumes that > this is sufficient to ensure that the old relfilenode can be killed when > the transaction commits. This is not correct. > > Example: > > - backend A begins a serializable transaction > - backend B truncates a table (and commits) > - backend A, still in the same transaction, accesses the truncated table > > Currently backend A sees the truncated table as empty, which is obviously > not right. This is obviously related to any attempt to weaken the locking > on other operations that modify relfilenodes, because doing it right implies > a mechanism to defer the removals past the commit of the modifying > transaction and up to the point where the old data can no longer be seen by > a live transaction. This is a good point. While DELETE keeps the old rows around and VACUUM perserves them until the serialized transaction commits, truncate does not keep the old rows around. In fact, would a truncate during a backup cause the backup to be inconsistent because it wouldn't be a true snapshot of the database at backup start time? Seems so. The docs mention: TRUNCATE cannot be used if there are foreign-key refer- ences to the table from other tables. Checking validity in such cases would require table scans, and the whole point is not to do one. so it doesn't make the referential integrity inconsistent. Perhaps we should document this. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway <[EMAIL PROTECTED]> writes: > How can we drop the file at commit, given that a serializable > transaction's snapshot should still be able to see old relfilenode's > content? It isn't 100% MVCC, I agree. But it works because system catalog lookups are SnapshotNow, and so when another session comes and wants to look at the table it will see the committed new version of the pg_class row pointing at the new relfilenode file. What you have to prevent is somebody accessing the table *while* the changeover happens ... and that's why your lock has to be AccessExclusive. If you want to complain about MVCC violations in CLUSTER, think about the fact that it scans the table with SnapshotNow, and therefore loses rows that are committed-dead but might still be visible to somebody. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] swapping relfilenodes (was: Re: locks in CREATE TRIGGER,
Tom Lane wrote: > Bruce Momjian writes: > > In fact, would a truncate during a backup cause the backup to be > > inconsistent because it wouldn't be a true snapshot of the database at > > backup start time? Seems so. > > No, because pg_dump holds AccessShareLock on every table that it intends > to dump, thereby ensuring that TRUNCATE/CLUSTER/etc are held off. The > proposal to weaken the locks that those operations take would in fact > break pg_dump. Oh, it pre-locks. I didn't know that. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] swapping relfilenodes (was: Re: locks in CREATE TRIGGER,
Bruce Momjian writes: > In fact, would a truncate during a backup cause the backup to be > inconsistent because it wouldn't be a true snapshot of the database at > backup start time? Seems so. No, because pg_dump holds AccessShareLock on every table that it intends to dump, thereby ensuring that TRUNCATE/CLUSTER/etc are held off. The proposal to weaken the locks that those operations take would in fact break pg_dump. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [HACKERS] locks in CREATE TRIGGER, ADD FK
Neil Conway <[EMAIL PROTECTED]> writes: > - if we make these changes, we will need some way to delete a > no-longer-visible relfilenode. This is presuming that we abandon the notion that system catalog access use SnapshotNow. Which opens the question of what they should use instead ... to which "transaction snapshot" isn't the answer, because we have to be able to do system catalog accesses before we've set the snapshot. (Else forget issuing LOCK TABLE before the snapshot is set.) I really think that you haven't the faintest idea of the size of the can of worms you are opening here :-( regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] WAL: O_DIRECT and multipage-writer
Hi, Mark. Mark Wong <[EMAIL PROTECTED]> wrote: > In light of this thread, have you compared the performance on > Linux-2.4? No, but I'm just testing my patch on Linux-2.4 with a middle-range server. I will report the results sometime soon. By the way, I found the debug option (XLOG_MULTIPAGE_WRITER_DEBUG) was enabled in your prior benchmarks. It writes logs a lot. I hope performance doesn't fall at least without debug. > So the new baseline result with 8.0.1: > Throughput: 3639.97 > Results with the patch but open_direct not set: > Throughput: 3494.72 > Results with the patch and open_direct set: > Throughput: 3489.69 --- ITAGAKI Takahiro <[EMAIL PROTECTED]> NTT Cyber Space Laboratories ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Tom Lane wrote: It isn't 100% MVCC, I agree. But it works because system catalog lookups are SnapshotNow, and so when another session comes and wants to look at the table it will see the committed new version of the pg_class row pointing at the new relfilenode file. If by "works", you mean "provides correct transactional semantics", then that simply isn't true. Not making CLUSTER and similar DDL commands MVCC compliant isn't the end of the world, I agree, but that doesn't make it correct, either. If you want to complain about MVCC violations in CLUSTER, think about the fact that it scans the table with SnapshotNow, and therefore loses rows that are committed-dead but might still be visible to somebody. This seems like another facet of the same problem (a serializable transaction's snapshot effectively includes the relfilenodes that were visible when the snapshot was taken, and swapping in another relfilenode under its nose is asking for trouble). We could fix the CLUSTER bug, although not the TRUNCATE bug, by scanning the old relation with SnapshotAny (or ideally, "the snapshot such that we can see all tuples visible to any currently running transaction", if we can produce such a snapshot easily). Not sure if that's worth doing; it would be nice to solve the root problem (scanning system catalogs with SnapshotNow, per discussion elsewhere in thread). -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] locks in CREATE TRIGGER, ADD FK
Tom Lane wrote: This is presuming that we abandon the notion that system catalog access use SnapshotNow. Which opens the question of what they should use instead ... to which "transaction snapshot" isn't the answer, because we have to be able to do system catalog accesses before we've set the snapshot. I wonder if it would be possible to use SnapshotNow before the transaction's snapshot has been established, and the transaction's snapshot subsequently. Although it definitely makes me nervous to use multiple snapshots over the life of a single transaction... -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] "they only drink coffee at dec"
From src/backend/tcop/postgres.c: appendStringInfo(&str, "!\t%ld/%ld [%ld/%ld] filesystem blocks in/out\n", r.ru_inblock - Save_r.ru_inblock, /* they only drink coffee at dec */ r.ru_oublock - Save_r.ru_oublock, r.ru_inblock, r.ru_oublock); Been there as far back as CVS goes. Will I find other goodies by poking around? :-) #define COPYBUFSIZ 8192 /* size doesn't matter */ Is vaguely amusing :P Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] \x in psql
When you turn on \x mode for query output in psql, it wrecks the output of \d , etc. Should we change it so that the \d is unaffected by \x? What about for other \d commands? Chris ---(end of broadcast)--- TIP 3: 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: [HACKERS] locks in CREATE TRIGGER, ADD FK
Tom Lane wrote: I don't think this has been adequately thought through at all ... but at least make it ExclusiveLock. What is the use-case for allowing SELECT FOR UPDATE in parallel with this? Ok, patch applied -- I adjusted it to use ExclusiveLock, and fleshed out some of the comments. -Neil Index: doc/src/sgml/mvcc.sgml === RCS file: /var/lib/cvs/pgsql/doc/src/sgml/mvcc.sgml,v retrieving revision 2.47 diff -c -r2.47 mvcc.sgml *** doc/src/sgml/mvcc.sgml 26 Feb 2005 18:37:17 - 2.47 --- doc/src/sgml/mvcc.sgml 23 Mar 2005 07:33:36 - *** *** 677,685 ! This lock mode is not automatically acquired on user tables by any ! PostgreSQL command. However it is ! acquired on certain system catalogs in some operations. --- 677,686 ! Acquired by CREATE TRIGGER and ! ALTER TABLE ADD FOREIGN KEY. This lock ! mode can also be acquired on certain system catalogs in some ! operations. Index: src/backend/commands/tablecmds.c === RCS file: /var/lib/cvs/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.148 diff -c -r1.148 tablecmds.c *** src/backend/commands/tablecmds.c 20 Mar 2005 22:00:52 - 1.148 --- src/backend/commands/tablecmds.c 23 Mar 2005 07:27:09 - *** *** 3829,3841 Oid constrOid; /* ! * Grab an exclusive lock on the pk table, so that someone doesn't ! * delete rows out from under us. (Although a lesser lock would do for ! * that purpose, we'll need exclusive lock anyway to add triggers to ! * the pk table; trying to start with a lesser lock will just create a ! * risk of deadlock.) */ ! pkrel = heap_openrv(fkconstraint->pktable, AccessExclusiveLock); /* * Validity and permissions checks --- 3829,3841 Oid constrOid; /* ! * Grab a lock on the pk table, so that someone doesn't delete ! * rows out from under us. We will eventually need to add triggers ! * to the table, at which point we'll need to an ExclusiveLock -- ! * therefore we grab an ExclusiveLock now to prevent possible ! * deadlock. */ ! pkrel = heap_openrv(fkconstraint->pktable, ExclusiveLock); /* * Validity and permissions checks Index: src/backend/commands/trigger.c === RCS file: /var/lib/cvs/pgsql/src/backend/commands/trigger.c,v retrieving revision 1.178 diff -c -r1.178 trigger.c *** src/backend/commands/trigger.c 20 Mar 2005 23:40:24 - 1.178 --- src/backend/commands/trigger.c 23 Mar 2005 07:25:21 - *** *** 87,93 ObjectAddress myself, referenced; ! rel = heap_openrv(stmt->relation, AccessExclusiveLock); if (stmt->constrrel != NULL) constrrelid = RangeVarGetRelid(stmt->constrrel, false); --- 87,100 ObjectAddress myself, referenced; ! /* ! * We need to prevent concurrent CREATE TRIGGER commands, as well ! * as concurrent table modifications (INSERT, DELETE, UPDATE), so ! * acquire an ExclusiveLock -- it should be fine to allow SELECTs ! * to proceed. We could perhaps acquire ShareRowExclusiveLock, but ! * there seems little gain in allowing SELECT FOR UPDATE. ! */ ! rel = heap_openrv(stmt->relation, ExclusiveLock); if (stmt->constrrel != NULL) constrrelid = RangeVarGetRelid(stmt->constrrel, false); ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
