Re: [GENERAL] configuring library path for debian build of postgres 9.2
On 2012-05-25, Marc Munro m...@bloodnok.com wrote: $ /usr/lib/postgresql/9.2/bin/psql: symbol lookup error: /usr/lib/postgresql/9.2/bin/psql: undefined symbol: PQconnectdbParams At times like that I run /sbin/ldconfig Sometimes it helps. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forcefully adding a CHECK constrained
On Tue, 2012-05-15 at 12:52 +0300, Catalin(ux) M. BOIE wrote: The old_stats is so big that I cannot afford to add a check constraint. But, I know that all values of the itime field are before 2012_04, so, would be great if I could run something like: ALTER TABLE old_stats ADD CONSTRAINT xxx CHECK (itime 2012_04_timestamp) FORCE; I never looked at PostgreSQL sources, but the commit Enable CHECK constraints to be declared NOT VALID http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=897795240cfaaed724af2f53ed2c50c9862f951f inspired me to dive. Is PostgreSQL's team willing to accept such a feature? It looks like you already found the answer! Create the constraint using NOT VALID, and then sometime later (when you can afford the full scan) do a VALIDATE CONSTRAINT. Unfortunately, this is only available in 9.2, which is still in beta. http://www.postgresql.org/docs/9.2/static/sql-altertable.html CHECK constraints don't use indexes, so CREATE INDEX CONCURRENTLY doesn't help you. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Not understanding this behavior of a subselect + volatile function
There is behavior in the following code that has me confused, and I'd like to understand it, as it goes against how I thought that MVCC worked in psql: create table t1 (a integer primary key, b integer default 0); insert into t1 (a) values (1); create function f1() returns int as $$ declare ret int; begin select a from t1 into ret where b 1 for update; update t1 set b = b + 1 where a = ret; return ret; end; $$ language plpgsql; select * from t1 where a = (select f1()); The final line, the select, will return the row as it was before the function ran, (1,0) instead of (1,1). It's as if the outer select locked its view of the table in place before the inner select ran. What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated. I was under the impression that the transaction will have a consistent view of the table, and so the subselect should see the same data as the outer select. That's definitely not happening here, and I'm wondering why -- is it a property of volatile functions? Do they get their own, separate view of the data, even inside the same transaction? Thanks for any insight on this puzzler, -- Brian Palmer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
On Sun, May 27, 2012 at 8:17 AM, Brian Palmer br...@codekitchen.net wrote: There is behavior in the following code that has me confused, and I'd like to understand it, as it goes against how I thought that MVCC worked in psql: ... select a from t1 into ret where b 1 for update; update t1 set b = b + 1 where a = ret; ... The final line, the select, will return the row as it was before the function ran, (1,0) instead of (1,1). It's as if the outer select locked its view of the table in place before the inner select ran. What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated. As Frederic said, Most curious! Most absurdly whimsical! The function is actually immaterial to this; the same thing occurs with this single statement: with t1upd as (update t1 set b = b + 1 where b 1 returning a) select * from t1 join t1upd using (a); Poking around with the latter form of the statement and Google showed up this: http://stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres I don't fully understand the exact interactions between transactions, snapshots, and statements, but according to the accepted answer on stackoverflow, the entire statement sees the database as at the beginning of the statement. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
On May 26, 2012, at 5:22 PM, Chris Angelico wrote: The function is actually immaterial to this; the same thing occurs with this single statement: with t1upd as (update t1 set b = b + 1 where b 1 returning a) select * from t1 join t1upd using (a); Poking around with the latter form of the statement and Google showed up this: http://stackoverflow.com/questions/7191902/cannot-select-from-update-returning-clause-in-postgres That's a good link, thanks Chris. I'm not sure it entirely answers what I'm seeing though. It does explain why the outer select doesn't see the updated values, but the other thing that I'm seeing is that sometimes the function will update a row that was just inserted, and then the outer select will return 0 results. It behaves as if from the view of the outer select, that row doesn't exist yet. So I end up with a row in the table that's been updated by the function, but never returned to the caller. With 45 clients doing this select in a continuous loop, and ~100 clients inserting into the table a few times a second, this only happens a couple times a day, so it's been difficult to debug so far. Is it possible for the subselect to have a view from a slightly different point in time than the outer select? I wouldn't think so, but I'm not sure how else to explain what is happening. -- Brian
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
On Sun, May 27, 2012 at 11:36 AM, Brian Palmer br...@codekitchen.net wrote: That's a good link, thanks Chris. I'm not sure it entirely answers what I'm seeing though. It does explain why the outer select doesn't see the updated values, but the other thing that I'm seeing is that sometimes the function will update a row that was just inserted, and then the outer select will return 0 results. It behaves as if from the view of the outer select, that row doesn't exist yet. So I end up with a row in the table that's been updated by the function, but never returned to the caller. I seem to recall reading somewhere that the WHERE is processed like an intervening if clause in Magic: The Gathering - it's checked both before and after the function is called. Perhaps this is solvable, but if not, I'd be inclined to treat it like C and avoid referencing and altering a variable in one expression (eg arr[i++]=i; is a bad idea). There are experts on this list, though, and I am not one :) ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
On May 26, 2012, at 7:45 PM, Chris Angelico wrote: I'd be inclined to treat it like C and avoid referencing and altering a variable in one expression (eg arr[i++]=i; is a bad idea). I agree, we're already working on changing it to a two-step process where we select f1(), and then select * where a = $a . I'm still really curious about what's going on here though. Thanks for the insight! -- Brian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
Brian Palmer br...@codekitchen.net writes: The final line, the select, will return the row as it was before the function ran, (1,0) instead of (1,1). It's as if the outer select locked its view of the table in place before the inner select ran. Yes, that's exactly correct. A plain SELECT always returns data that is visible as of its snapshot, ignoring anything that happened later --- even volatile functions executing in the same transaction. What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated. Volatile functions have their own snapshot that is independent of the calling query's. So it's definitely possible for a volatile function to see a row that was committed just after the outer select took its snapshot. That row cannot be seen by the outer query, though. You can tweak these rules to some extent by using SELECT FOR UPDATE and different transaction isolation modes. See the concurrency control chapter in the manual. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not understanding this behavior of a subselect + volatile function
Thanks so much tom! I feel a lot better going with this fix now that I know for sure what was going wrong. -- Brian On May 26, 2012, at 8:08 PM, Tom Lane wrote: Brian Palmer br...@codekitchen.net writes: The final line, the select, will return the row as it was before the function ran, (1,0) instead of (1,1). It's as if the outer select locked its view of the table in place before the inner select ran. Yes, that's exactly correct. A plain SELECT always returns data that is visible as of its snapshot, ignoring anything that happened later --- even volatile functions executing in the same transaction. What seems even stranger to me is that if a row is inserted at just the right moment, the inner function can select it and update it, then return its primary key, but the outer select won't even see that row, and so it will return 0 rows even though the row got updated. Volatile functions have their own snapshot that is independent of the calling query's. So it's definitely possible for a volatile function to see a row that was committed just after the outer select took its snapshot. That row cannot be seen by the outer query, though. You can tweak these rules to some extent by using SELECT FOR UPDATE and different transaction isolation modes. See the concurrency control chapter in the manual. regards, tom lane
