Re: [HACKERS] ADD FOREIGN KEY locking
On Wed, Feb 18, 2015 at 9:06 AM, James Sewell james.sew...@lisasoft.com wrote: I've just noticed something in the Commit fest post - Reducing lock strength of trigger and foreign key DDL This reduces the lock taken for ADD FOREIGN KEY to ShareRowExclusiveLock, authorizing SELECT and SELECT FOR [SHARE | UPDATE ... ]. Perhaps I just need to be more patient. Yup. -- Michael
[HACKERS] ADD FOREIGN KEY locking
Hello all, When I add a FK with a statement like this: ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id); I see a lock on table b: select locktype,mode,granted from pg_locks, pg_stat_activity where relation::regclass::text = 'b' AND pg_locks.pid = pg_stat_activity.pid; locktype | relation mode | AccessShareLock granted | t query | SOME LONG RUNNING QUERY WHICH SELECTS FROM b locktype | relation mode | AccessExclusiveLock granted | f query | ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id); This means that my add key won't complete until my long running query does. That seems a bit odd to me? In this database there are lots of datawarehouse type queries running, which makes it a bit hard for me to schedule this operation. Is this just a manifestation of adding the key being in an ALTER TABLE, which always needs an AccessExclusiveLock? Or am I missing some edge case when this lock would be required in this circumstance? No real urgency on this question, I just found it a bit strange and thought someone might be able to shed some light. James Sewell, Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [HACKERS] ADD FOREIGN KEY locking
Oh, I've just noticed something in the Commit fest post - Reducing lock strength of trigger and foreign key DDL Perhaps I just need to be more patient. Cheers, James Sewell, Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Wed, Feb 18, 2015 at 10:57 AM, James Sewell james.sew...@lisasoft.com wrote: Hello all, When I add a FK with a statement like this: ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id); I see a lock on table b: select locktype,mode,granted from pg_locks, pg_stat_activity where relation::regclass::text = 'b' AND pg_locks.pid = pg_stat_activity.pid; locktype | relation mode | AccessShareLock granted | t query | SOME LONG RUNNING QUERY WHICH SELECTS FROM b locktype | relation mode | AccessExclusiveLock granted | f query | ALTER TABLE a ADD FOREIGN KEY (id) REFERENCES b(id); This means that my add key won't complete until my long running query does. That seems a bit odd to me? In this database there are lots of datawarehouse type queries running, which makes it a bit hard for me to schedule this operation. Is this just a manifestation of adding the key being in an ALTER TABLE, which always needs an AccessExclusiveLock? Or am I missing some edge case when this lock would be required in this circumstance? No real urgency on this question, I just found it a bit strange and thought someone might be able to shed some light. James Sewell, Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [HACKERS] ADD FOREIGN KEY
Christopher Browne [EMAIL PROTECTED] writes: I would, given an ideal world, prefer to be able to have a connection or two live during this to let me monitor the DB and even get an early peek at the data. On that note, how hard would it be to implement a read-dirty mode in postgres? This would be useful for few things, the only thing I can think of are progress indicators for long-running updates/inserts. It seems like it falls naturally out of the MVCC algorithm, simply have it set the transaction id of the current transaction to be a magic value that compares greater than any transaction id. So all uncommitted transactions are seen as having been committed in the past. I don't see any real need for updates or inserts, but reasonable semantics for them also fall out of MVCC. Any updates or inserts should be seen as being committed infinitely far in the future. So they can only be seen by other read-dirty transactions. The main use for this that I see are doing select count(*) on tables being imported or inserted into. Or perhaps being able to peek at records being updated by another session in a long-running job. If nothing else it'll save the load on the mailing list every time people ask how to calculate how much longer their data load is going to take based on the size of the files in the postgres data directory. I'm sure I'm skipping a few steps. What I said doesn't quite make sense on its own. I think I'm missing some key elements of the postgres MVCC system. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ADD FOREIGN KEY
On Tue, 30 Sep 2003 08:00:07 -0400, Christopher Browne [EMAIL PROTECTED] wrote: I would be pretty game for a near-single-user-mode approach that would turn off some of the usual functionality that we knew we didn't need because the data source was an already-committed-and-FK-checked set of data. Single user mode is a good idea, IMHO. But it should only make sure that there is not more than one user connected to the database (or to the postmaster). Everything else should depend on special GUC variables that are only settable in single user mode: db= SET disable-fk-verification = true; ERROR: disable-fk-verification can only be set in single user mode db= SET SINGLE USER MODE ON; ERROR: permission denied HINT: Must be superuser or owner of database db. db= \c - dbo You are now connected as new user dbo. db= SET SINGLE USER MODE ON; ERROR: cannot enter single user mode HINT: You are not the only user connected to database db. -- after other users have logged out ... db= SET SINGLE USER MODE ON; SET db= SET disable-fk-verification = true; SET Single user mode would also help in several cases where now a standalone backend is required ... Servus Manfred ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ADD FOREIGN KEY
[EMAIL PROTECTED] (Manfred Koizar) writes: On Tue, 30 Sep 2003 08:00:07 -0400, Christopher Browne [EMAIL PROTECTED] wrote: I would be pretty game for a near-single-user-mode approach that would turn off some of the usual functionality that we knew we didn't need because the data source was an already-committed-and-FK-checked set of data. Single user mode is a good idea, IMHO. But it should only make sure that there is not more than one user connected to the database (or to the postmaster). Well, there already exists an honest-to-goodness single-user mode, where you start a postmaster directly. This is the way that you need to connect to PG in order to be able to regenerate indexes for any nailed system tables. If I could be certain that a pg_fast_recovery program could run several times faster than the existing approach of psql recoveryfile.sql, then it might well be worthwhile to have something invoked something like the following: % zcat /backups/latest_backup.gz | postmaster -D $PGDATA -F -N 0 --fast-recovery-off-ACID --log /tmp/recovery.log mydb -N 0 means that there won't even be as many as one user connected to the database. I would, given an ideal world, prefer to be able to have a connection or two live during this to let me monitor the DB and even get an early peek at the data. But if I could save a few hours of recovery time, it might be livable to lose that. -- select 'cbbrowne' || '@' || 'libertyrms.info'; http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ADD FOREIGN KEY
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Tom Lane) would write: Since there's no performance difference at pg_dump time, I can't see any advantage to freezing your decision then. This parallels the common suggestion of throwing an ANALYZE in at the bottom of a pg_dump script. On that particular note, I'd think it preferable to analyze after loading each table, since the data for the specific table will still be in memory. But that's a _bit_ of a change of subject. This looks like something where a hook would be valuable such that there is something in the pg_dump that can be configured AFTER the fact to control how it's loaded. It would surely seem valuable to have a way of making loads go As Fast As Possible, even with the possibility of breakneck speed offering the possibility of actually getting seriously injured (breaking one's neck?). If the hardware fails during the recovery, consider that you were _recovering_ from a _backup_; that surely ought to be an eminently redoable operation, quite unlike accepting a random SQL request from a user. I have done some recoveries recently (well, more precisely, installs) by taking a tarball of a pre-existing database and dropping it into place. I had no problem with the fact that if my hand slipped and hit ^C at the wrong moment (quelle horreur!), I would be forced to restart the cd $TARGETDIR; tar xfvz Flex.tgz process. I would be pretty game for a near-single-user-mode approach that would turn off some of the usual functionality that we knew we didn't need because the data source was an already-committed-and-FK-checked set of data. -- output = reverse(ac.notelrac.teneerf @ 454aa) http://www.ntlug.org/~cbbrowne/spiritual.html Another result of the tyranny of Pascal is that beginners don't use function pointers. --Rob Pike ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
