Re: [HACKERS] [v9.3] writable foreign tables
Alexander Korotkov wrote: 2) You wrote that FDW can support or don't support write depending on having corresponding functions. However it's likely some tables of same FDW could be writable while another are not. I think we should have some mechanism for FDW telling whether particular table is writable. I think that this would best be handled by a table option, if necessary. That allows maximum flexibility for the design of the FDW. In many cases it might be enough if the foreign data source raises an error on a write request. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fix errcontext() function
On 08.11.2012 07:59, Chen Huajun wrote:
I am sending patch for errcontext() function.
I use procedural languages to do some operation, but when error occurs
,the CONTEXT error messages from procedural languages doesn't display in
local language.
for example:
postgres=# CREATE OR REPLACE FUNCTION logfunc3 (logtxt text) RETURNS
timestamp AS $$
postgres$# BEGIN
postgres$# select * from db;
postgres$# RETURN 'now';
postgres$# END;
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select logfunc3('test');
ERROR: リレーションdbは存在しません
行 1: select * from db
QUERY: select * from db
CONTEXT: PL/pgSQL function logfunc3 line 3 at SQL ステートメント
but,“CONTEXT: PL/pgSQL 関数 logfunc3 の 3 行目の型 SQL ステートメント” is my expected.
There is the same problem in pl/perl and pl/python .
After checking and debuging the source code ,I found the reason.
The reason is that domian setted is wrong. For PL/pgSQL, domain pgsql should be setted,
but domain setted is postgres .
So I considered to fix the bug by updating errcontext() funtion.
Unfortunately not all compilers support varargs macros. I bumped into
this in February, see
http://archives.postgresql.org/message-id/4f3b72e0.8040...@enterprisedb.com.
My last attempt to fix this was at
http://archives.postgresql.org/pgsql-hackers/2012-04/msg00812.php. That
patch is probably good to go, I just got busy with other things and
forgot about it back then. Can you take a look at that patch and see if
I missed anything, please?
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extend libpq to support mixed text and binary results
Simon Riggs wrote: - I think that every feature of the line protocol should be exposed in the C API. Exposing every possible bug in ther underlying protocol isn't the best plan though, especially when doing so complicates the API just to support this. Well, I wouldn't call this a bug, but I got enough good points against the idea that I consider it dead. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 03:10, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: On 7 November 2012 22:04, Tom Lane t...@sss.pgh.pa.us wrote: This seems to me to be dangerous and unintuitive, not to mention underdocumented. I think it would be better to just not do anything if there is any INSTEAD rule, period. Is this any more dangerous than what already happens with qualified rules? If we did nothing here then it would go on to either fire any INSTEAD OF triggers or raise an error if there aren't any. The problem with that is that it makes trigger-updatable views and auto-updatable views inconsistent in their behaviour with qualified INSTEAD rules. Well, as submitted it's already pretty thoroughly inconsistent. The way the existing code works is that if there's no INSTEAD rule, and there's no INSTEAD trigger, you get an error *at runtime*. The reason for that is that the INSTEAD trigger might be added (or removed) between planning and execution. This code tries to decide at plan time whether there's a relevant trigger, and that's just not very safe. I realize that you can't deliver the specific error messages that currently appear in view_is_auto_updatable if you don't throw the error at plan time. But if you're going to claim that this ought to be consistent with the existing behavior, then I'm going to say we need to give that up and just have the runtime error, same as now. If you want the better error reporting (which I agree would be nice) then we need to revisit the interaction between INSTEAD triggers and INSTEAD rules anyway, and one of the things we probably should look at twice is whether it's sane at all to permit both a trigger and a qualified rule. I'd bet long odds that nobody is using such a thing in the field, and I think disallowing it might be a good idea in order to disentangle these features a bit better. OK, yes I think we do need to be throwing the error at runtime rather than at plan time. That's pretty easy if we just keep the current error message, but I think it would be very nice to have the more specific DETAIL text to go along with the error. We could save the value of is_view_auto_updatable() so that it's available to the executor, but that seems very ugly. A better approach might be to just call is_view_auto_updatable() again from the executor. At the point where we would be calling it, we would already know that the view isn't updatable, so we would just be looking for friendlier DETAIL text to give to the user. There's a chance that the view might have been changed structurally between planning an execution, making that DETAIL text incorrect, or even changing the fact that the view isn't updatable, but that seems pretty unlikely, and no worse than similar risks with tables. I think the whole thing with qualified rules is a separate issue. I don't really have a strong opinion on it because I never use qualified rules, but I am wary of changing the existing behaviour on backwards-compatibility grounds. I don't much like the way qualified rules work, but if we're going to support them then why should trigger/auto-updatable views be an exception to the way they work? Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On 19.10.2012 14:42, Amit kapila wrote: On Thursday, October 18, 2012 8:49 PM Fujii Masao wrote: Before implementing the timeout parameter, I think that it's better to change both pg_basebackup background process and pg_receivexlog so that they send back the reply message immediately when they receive the keepalive message requesting the reply. Currently, they always ignore such keepalive message, so status interval parameter (-s) in them always must be set to the value less than replication timeout. We can avoid this troublesome parameter setting by introducing the same logic of walreceiver into both pg_basebackup background process and pg_receivexlog. Please find the patch attached to address the modification mentioned by you (send immediate reply for keepalive). Both basebackup and pg_receivexlog uses the same function ReceiveXLogStream, so single change for both will address the issue. Thanks, committed this one after shuffling it around the changes I committed yesterday. I also updated the docs to not claim that -s option is required to avoid timeout disconnects anymore. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: question on foreign key lock
Hi,
maybe this is a better group for this question?
I can't see why creating foreign key on table A referencing table B,
generates an AccessExclusiveLock on B.
It seems (to a layman :-) ) that only writes to B should be blocked.
I'm really interested if this is either expected effect or any open TODO
item or suboptimal behavior of postgres.
Thanks
-- Forwarded message --
From: Filip Rembiałkowski filip.rembialkow...@gmail.com
Date: Thu, Nov 1, 2012 at 5:33 PM
Subject: question on foreign key lock
To: pgsql-general list pgsql-gene...@postgresql.org
Hello.
Why adding FK creates AccessExclusiveLock on referenced tabble?
{{{
CREATE TABLE A ( id integer, idb integer );
INSERT INTO A (id,idb) SELECT x, x%4 FROM generate_series(1,100) x;
CREATE TABLE B ( id int primary key );
INSERT INTO B VALUES (0),(1),(2),(3);
BEGIN;
ALTER TABLE A ADD CONSTRAINT a_idb_fkey FOREIGN KEY (idb) REFERENCES b;
SELECT * FROM pg_locks l, pg_class c WHERE l.pid = pg_backend_pid() AND
l.locktype='relation' AND l.mode ilike '%exclusive%' AND l.relation=c.oid;
ROLLBACK;
}}}
Last SELECT is showing AccessExclusive on B.
Why not Exclusive?
Thanks,
Filip
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Thursday, November 08, 2012 2:04 PM Heikki Linnakangas wrote: On 19.10.2012 14:42, Amit kapila wrote: On Thursday, October 18, 2012 8:49 PM Fujii Masao wrote: Before implementing the timeout parameter, I think that it's better to change both pg_basebackup background process and pg_receivexlog so that they send back the reply message immediately when they receive the keepalive message requesting the reply. Currently, they always ignore such keepalive message, so status interval parameter (-s) in them always must be set to the value less than replication timeout. We can avoid this troublesome parameter setting by introducing the same logic of walreceiver into both pg_basebackup background process and pg_receivexlog. Please find the patch attached to address the modification mentioned by you (send immediate reply for keepalive). Both basebackup and pg_receivexlog uses the same function ReceiveXLogStream, so single change for both will address the issue. Thanks, committed this one after shuffling it around the changes I committed yesterday. I also updated the docs to not claim that -s option is required to avoid timeout disconnects anymore. Thank you. However I think still the issue will not be completely solved. pg_basebackup/pg_receivexlog can still take long time to detect network break as they don't have timeout concept. To do that I have sent one proposal which is mentioned at end of mail chain: http://archives.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C3828 53BBED@szxeml509-mbs Do you think there is any need to introduce such mechanism in pg_basebackup/pg_receivexlog? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deferrable NOT NULL constraints in 9.3?
Hi hackers. Are there any plans to include DEFERRABLE NOT NULL constraints in 9.3 so one can do this? create table test(a varchar not null deferrable initially deferred); This works in Oracle and is quite handy when working with ORMs. Thanks. -- Andreas Joseph Krogh andr...@officenet.no mob: +47 909 56 963 Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no Public key: http://home.officenet.no/~andreak/public_key.asc
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
On Thursday, November 08, 2012 1:45 AM Robert Haas wrote: On Wed, Nov 7, 2012 at 2:50 PM, Josh Berkus j...@agliodbs.com wrote: Well, Magnus' proposed implementation supposed that the existing values *have* been loaded into the current session. I agree that with some locking and yet more code you could implement it without that. But this still doesn't seem to offer any detectable benefit over value-per- file. Well, value-per-file is ugly (imagine you've set 40 different variables that way) but dodges a lot of complicated issues. And I suppose ugly doesn't matter, because the whole idea of the auto-generated files is that users aren't supposed to look at them anyway. That's pretty much how I feel about it, too. I think value-per-file is an ugly wimp-out that shouldn't really be necessary to solve this problem. It can't be that hard to rewrite a file where every like is of the form: key = 'value' I also believe that it should be possible to rewrite a file without loading values into the current session. One of the solution if we assume that file is of fixed format and each record (key = 'value') of fixed length can be: 1. While writing .auto file, it will always assume that .auto file contain all config parameters. Now as this .auto file is of fixed format and fixed record size, it can directly write a given record to its particular position. 2. To handle locking issues, we can follow an approach similar to what GIT is doing for editing conf files (using .lock file): a. copy the latest content of .auto to .auto.lock b. make all the changes to auto.lock file. c. at the end of command rename the auto.lock file to .auto file d. otherwise if SQL COMMAND/function failed in-between we can delete the .auto.lock file 3. Two backends trying to write to .auto file we can use .auto.lock as the the lock by trying to create it in exclusive mode as the first step of the command. If it already exists then backend needs to wait. However, as Josh said upthread, +1 for the implementation that will get committed. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
Amit Kapila escribió: 3. Two backends trying to write to .auto file we can use .auto.lock as the the lock by trying to create it in exclusive mode as the first step of the command. If it already exists then backend needs to wait. So changing .auto settings would be nontransactional? The other way to define this would be to have a lock that you grab and keep until end of transaction, and the .auto.lock file is deleted if the transaction is aborted; so have the .auto.lock - .auto rename only happen at transaction commit. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 08:33, Dean Rasheed dean.a.rash...@gmail.com wrote: OK, yes I think we do need to be throwing the error at runtime rather than at plan time. That's pretty easy if we just keep the current error message... Oh wait, that's nonsense (not enough caffeine). The rewrite code needs to know whether there are INSTEAD OF triggers before it decides whether it's going to substitute the base relation. The fundamental problem is that the plans with and without triggers are completely different, and there's no way the executor is going to notice the addition of triggers if they weren't there when the query was rewritten and planned. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
On Thursday, November 08, 2012 12:28 AM Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Nov 7, 2012 at 12:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: ... we don't normally read the config file within-commands, and there are both semantic and implementation problems to overcome if you want to do so. Why would you need to? It seems to me that we ought to be able to rewrite a machine-generated configuration file without loading those values into the current session. Well, Magnus' proposed implementation supposed that the existing values *have* been loaded into the current session. I agree that with some locking and yet more code you could implement it without that. But this still doesn't seem to offer any detectable benefit over value-per-file. In value-per-file Approach if 2 sessions trying to update same variable (trying to write in same file), then won't there be chances that it can corrupt the file if there is no locking? Won't this have any impact on base backup/restore, restart and SIGHUP in terms of that it needs to open,read,close so many files instead of one file. Oracle and Git which provides mechanism to edit of conf file using a command doesn't use multiple file concept, which indicates that might be single file concept is better. Even if we say that user doesn't need to edit or change anything in config directory, but still some advanced database users/DBA's generally try to understand the meaning of each folder/file in database to manage it in a better way. So when we explain them the contents of this folder and explanation of same, they might not feel good based on their experience with Oracle or some other similar database. As per discussion and different opinions value-per-file Approach has merits over single-file in terms of design and implementation and single-file has merits over value-per-file in-terms of ugliness (usability or maintainence or ...) IMHO, to conclude it, we can see if it is possible to have some not so complex solution(design) to handle single-file Approach then we can use it, otherwise we can go for value-per-file Approach. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.3] writable foreign tables
On 08-Nov-2012, at 13:35, Albe Laurenz laurenz.a...@wien.gv.at wrote: Alexander Korotkov wrote: 2) You wrote that FDW can support or don't support write depending on having corresponding functions. However it's likely some tables of same FDW could be writable while another are not. I think we should have some mechanism for FDW telling whether particular table is writable. I think that this would best be handled by a table option, if necessary. That allows maximum flexibility for the design of the FDW. In many cases it might be enough if the foreign data source raises an error on a write request. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers +1 I agree, we should have a system where if the foreign data source raises an error on write, FDW can raise corresponding error on PostgreSQL side.exposing this as a table option is IMHO a bit risky, and the user may not know whether the foreign data source will accept writes or not. Atri -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
On Thursday, November 08, 2012 5:24 AM Greg Smith wrote: On 11/2/12 11:17 AM, Magnus Hagander wrote: -Add a configuration subdirectory to the default installation. Needs to follow the config file location, so things like the Debian relocation of postgresql.conf still work. Maybe it has zero files; maybe it has one that's named for this purpose, which defaults to the usual: What do you mean by needs to follow? In particular, do you mean that it should be relative to postgresql.conf? I think that would actually be a *problem* for any system that moves the config file away, like debian, since you'd then have to grant postgres write permissions on a directory in /etc/... I should have just said that the rules for the directly location are the ones implied by the include-dir feature. My understanding is that Debian Postgres installs already had writable config files in etc, so that you can modify the postgresql.conf, pg_hba.conf, etc. Here's a Squeeze server running the stock 8.4 plus 9.1 from backports, and /etc/postgresql/version/cluster is writable by the postgres user: $ ls -ld /etc/postgresql/9.1/main/ drwxr-xr-x postgres postgres /etc/postgresql/9.1/main/ $ ls -ld /etc/postgresql/8.4/main/ drwxr-xr-x postgres postgres /etc/postgresql/8.4/main/ $ ls -ld /etc/postgresql/9.1/main/postgresql.conf -rw-r--r-- postgres postgres /etc/postgresql/9.1/main/postgresql.conf $ ls -ld /etc/postgresql/8.4/main/postgresql.conf -rw-r--r-- postgres postgres /etc/postgresql/8.4/main/postgresql.conf So is it okay if we have absolute path of config directory in postgresql.conf? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
On Thursday, November 08, 2012 8:07 PM Alvaro Herrera wrote: Amit Kapila escribió: 3. Two backends trying to write to .auto file we can use .auto.lock as the the lock by trying to create it in exclusive mode as the first step of the command. If it already exists then backend needs to wait. So changing .auto settings would be nontransactional? No, it should behave the way you explained below. The points mentioned in above mail are just to explain the basic concept. The other way to define this would be to have a lock that you grab and keep until end of transaction, and the .auto.lock file is deleted if the transaction is aborted; so have the .auto.lock - .auto rename only happen at transaction commit. Is this behavior sane for Transaction block, as in transaction block some other backend might need to wait for little longer, if both issued a command to change config parameter? IMO it is okay, as the usage of command to change config parameters inside a transaction block would be less. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL
Amit Kapila escribió: On Thursday, November 08, 2012 8:07 PM Alvaro Herrera wrote: The other way to define this would be to have a lock that you grab and keep until end of transaction, and the .auto.lock file is deleted if the transaction is aborted; so have the .auto.lock - .auto rename only happen at transaction commit. Is this behavior sane for Transaction block, as in transaction block some other backend might need to wait for little longer, if both issued a command to change config parameter? IMO yes, it's sane to make the second backend wait until the first one commits. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 14:38, Dean Rasheed dean.a.rash...@gmail.com wrote: On 8 November 2012 08:33, Dean Rasheed dean.a.rash...@gmail.com wrote: OK, yes I think we do need to be throwing the error at runtime rather than at plan time. That's pretty easy if we just keep the current error message... Oh wait, that's nonsense (not enough caffeine). The rewrite code needs to know whether there are INSTEAD OF triggers before it decides whether it's going to substitute the base relation. The fundamental problem is that the plans with and without triggers are completely different, and there's no way the executor is going to notice the addition of triggers if they weren't there when the query was rewritten and planned. In fact doesn't the existing plan invalidation mechanism already protect us from this? Consider for example: create table foo(a int); create view foo_v as select a+1 as a from foo; create function foo_trig_fn() returns trigger as $$ begin insert into foo values(new.a-1); return new; end $$ language plpgsql; create trigger foo_trig instead of insert on foo_v for each row execute procedure foo_trig_fn(); Then I can do: prepare f(int) as insert into foo_v values($1); PREPARE execute f(1); INSERT 0 1 drop trigger foo_trig on foo_v; DROP TRIGGER execute f(2); ERROR: cannot insert into view foo_v DETAIL: Views with columns that are not simple references to columns in the base relation are not updatable. HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger. create trigger foo_trig instead of insert on foo_v for each row execute procedure foo_trig_fn(); CREATE TRIGGER execute f(3); INSERT 0 1 Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Wed, Nov 7, 2012 at 09:17:29PM -0500, Bruce Momjian wrote: Things look fine through 2k, but at 4k the duration of pg_dump, restore, and pg_upgrade (which is mostly a combination of these two) is 4x, rather than the 2x as predicted by the growth in the number of tables. To see how bad it is, 16k tables is 1.3 hours, and 32k tables would be 5.6 hours by my estimates. You can see the majority of pg_upgrade duration is made up of the pg_dump and the schema restore, so I can't really speed up pg_upgrade without speeding those up, and the 4x increase is in _both_ of those operations, not just one. Also, for 16k, I had to increase max_locks_per_transaction or the dump would fail, which kind of surprised me. I tested 9.2 and git head, but they produced identical numbers. I did use synchronous_commit=off. Any ideas? I am attaching my test script. Thinking this might be related to some server setting, I increased shared buffers, work_mem, and maintenance_work_mem, but this produced almost no improvement: tablespg_dump restore pg_upgrade 1 0.300.24 11.73(-) 1000 6.466.55 28.79(2.45) 2000 29.82 20.96 69.75(2.42) 4000 95.70 115.88 289.82(4.16) 8000 405.38 505.93 1168.60(4.03) shared_buffers=1GB tablespg_dump restore pg_upgrade 10.260.23 10006.227.00 2000 23.92 22.51 4000 88.44 111.99 8000 376.20 531.07 shared_buffers=1GB work_mem/maintenance_work_mem = 500MB 1 0.270.23 10006.398.27 2000 26.34 20.53 4000 89.47 104.59 8000 397.13 486.99 Any ideas what else I should test? It this O(2n) or O(n^2) behavior? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On Wed, Nov 07, 2012 at 05:55:32PM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: On Wed, Nov 07, 2012 at 05:04:48PM -0500, Tom Lane wrote: Should we be doing something about such cases, or is playing dumb correct? The SQL standard handles deciding the behavior based on whether WITH CHECK OPTION is included in the view DDL. See the section 2 of the SQL standard (Foundation) for details. Ah, I see it. So as long as we don't support WITH CHECK OPTION, we can ignore the issue. I don't think it's as simple as all that. WITH CHECK OPTION is how the SQL standard allows for creating update-able views in the first place, so we want to be at least aware of what the standard mandates. Here's what I'm able to apprehend from the standard. There are three different WITH CHECK OPTION options: WITH CHECK OPTION WITH CASCADED CHECK OPTION WITH LOCAL CHECK OPTION - WITH CHECK OPTION means that the results of INSERTs and UPDATEs on the view must be consistent with the view definition, i.e. INSERTs any of whose rows would be outside the view or UPDATEs which would push a row a row out of the view are disallowed. - WITH CASCADED CHECK OPTION is like the above, but stricter in that they ensure by checking views which depend on the view where the write operation is happening. INSERTs and UPDATEs have to stay in the lines for those dependent views. - WITH LOCAL CHECK OPTION allows INSERTs or UPDATEs that violate the view definition so long as they comply with the WITH CHECK OPTION on any dependent views. Apparently the LOCAL here means, delegate any CHECK OPTION checking to the dependent view, i.e. check it only locally and not right here. Oh, and I'm guessing at least one well-known financial services company would just love to have these :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
David Fetter da...@fetter.org writes: There are three different WITH CHECK OPTION options: WITH CHECK OPTION WITH CASCADED CHECK OPTION WITH LOCAL CHECK OPTION No, there are four: the fourth case being if you leave off the phrase altogether. That's the only case we accept, and it corresponds to the patch's behavior, ie, don't worry about it. Oh, and I'm guessing at least one well-known financial services company would just love to have these :) It might be material for a future patch, but it's not happening in this iteration. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: New log_destination 'fifo'
On 11/07/2012 02:46 PM, David Fetter wrote: On Wed, Nov 07, 2012 at 12:55:03PM -0800, Joshua D. Drake wrote: Hello, So it is possible to do this in other ways but I thought it might be interesting to allow people to define fifo or pipe as a log_desination. This would allow a person to tail the fifo to receive the outputs of the log as it happens but would not take up precious IO or space from the disk. I personally have found this valuable when diagnosing immediate unexpected problems. It would also work on Windows. I've often wanted this feature, and it's a pleasant surprise (at least to me) that Windows has the underlying capability. Alright, we will see what we can do to put this together. JD Cheers, David. -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RFC: New log_destination 'fifo'
On 11/07/2012 10:22 PM, Peter Eisentraut wrote: On Wed, 2012-11-07 at 12:55 -0800, Joshua D. Drake wrote: So it is possible to do this in other ways but I thought it might be interesting to allow people to define fifo or pipe as a log_desination. You could do this with a logging hook as a plugin, so you don't have to make the effort to get this integrated into the main line (yet). One problem to consider with fifos and pipes is what happens when the receiving side is down or not keeping up. You don't want the logging to become a potential source of failures. Good point. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On Thu, Nov 08, 2012 at 11:33:47AM -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: There are three different WITH CHECK OPTION options: WITH CHECK OPTION WITH CASCADED CHECK OPTION WITH LOCAL CHECK OPTION No, there are four: the fourth case being if you leave off the phrase altogether. That's the only case we accept, and it corresponds to the patch's behavior, ie, don't worry about it. Good point. I just wanted to get that out there in the archives, as it took a bit of cross-referencing, interpreting and contemplation to come up with something relatively concise. Oh, and I'm guessing at least one well-known financial services company would just love to have these :) It might be material for a future patch, but it's not happening in this iteration. Right. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Thu, Nov 8, 2012 at 2:22 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 16.10.2012 15:31, Heikki Linnakangas wrote: On 15.10.2012 19:31, Fujii Masao wrote: On Mon, Oct 15, 2012 at 11:27 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.10.2012 13:13, Heikki Linnakangas wrote: Oh, I didn't remember that we've documented the specific structs that we pass around. It's quite bogus anyway to explain the messages the way we do currently, as they are actually dependent on the underlying architecture's endianess and padding. I think we should refactor the protocol to not transmit raw structs, but use pq_sentint and friends to construct the messages. This was discussed earlier (see http://archives.postgresql.org/message-id/4fe2279c.2070...@enterprisedb.com), I think there's consensus that 9.3 would be a good time to do that as we changed the XLogRecPtr format anyway. This is what I came up with. The replication protocol is now architecture-independent. The WAL format itself is still architecture-independent, of course, but this is useful if you want to e.g use pg_receivexlog to back up a server that runs on a different platform. I chose the int64 format to transmit timestamps, even when compiled with --disable-integer-datetimes. Please review if you have the time.. Thanks for the patch! When I ran pg_receivexlog, I encountered the following error. Yeah, clearly I didn't test this near enough... I fixed the bugs you bumped into, new version attached. Committed this now, after fixing a few more bugs that came up during testing. As I suggested upthread, pg_basebackup and pg_receivexlog no longer need to check integer_datetimes before establishing the connection, thanks to this commit. If this is right, the attached patch should be applied. The patch just removes the check of integer_datetimes by pg_basebackup and pg_receivexlog. Regards, -- Fujii Masao dont_check_integer_datetimes_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Fri, Nov 9, 2012 at 1:40 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Nov 8, 2012 at 2:22 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 16.10.2012 15:31, Heikki Linnakangas wrote: On 15.10.2012 19:31, Fujii Masao wrote: On Mon, Oct 15, 2012 at 11:27 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 15.10.2012 13:13, Heikki Linnakangas wrote: Oh, I didn't remember that we've documented the specific structs that we pass around. It's quite bogus anyway to explain the messages the way we do currently, as they are actually dependent on the underlying architecture's endianess and padding. I think we should refactor the protocol to not transmit raw structs, but use pq_sentint and friends to construct the messages. This was discussed earlier (see http://archives.postgresql.org/message-id/4fe2279c.2070...@enterprisedb.com), I think there's consensus that 9.3 would be a good time to do that as we changed the XLogRecPtr format anyway. This is what I came up with. The replication protocol is now architecture-independent. The WAL format itself is still architecture-independent, of course, but this is useful if you want to e.g use pg_receivexlog to back up a server that runs on a different platform. I chose the int64 format to transmit timestamps, even when compiled with --disable-integer-datetimes. Please review if you have the time.. Thanks for the patch! When I ran pg_receivexlog, I encountered the following error. Yeah, clearly I didn't test this near enough... I fixed the bugs you bumped into, new version attached. Committed this now, after fixing a few more bugs that came up during testing. As I suggested upthread, pg_basebackup and pg_receivexlog no longer need to check integer_datetimes before establishing the connection, thanks to this commit. If this is right, the attached patch should be applied. The patch just removes the check of integer_datetimes by pg_basebackup and pg_receivexlog. Another comment that I made upthread is: In XLogWalRcvSendReply() and XLogWalRcvSendHSFeedback(), GetCurrentTimestamp() is called twice. I think that we can skip the latter call if integer-datetime is enabled because the return value of GetCurrentTimestamp() and GetCurrentIntegerTimestamp() is in the same format. It's worth reducing the number of GetCurrentTimestamp() calls, I think. Attached patch removes redundant GetCurrentTimestamp() call from XLogWalRcvSendReply() and XLogWalRcvSendHSFeedback(), if --enable-integer-datetimes. Regards, -- Fujii Masao reduce_get_current_timestamp_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE SERIALIZABLE and frozen COPY
On Wed, Nov 7, 2012 at 10:34 AM, Simon Riggs si...@2ndquadrant.com wrote: For 9.2 we discussed having COPY setting tuples as frozen. Various details apply. Earlier threads: RFC: Making TRUNCATE more MVCC-safe COPY with hints, rebirth I was unhappy with changing the behaviour of TRUNCATE, and still am. So the proposal here is to have a specific modifier on TRUNCATE command that makes it MVCC safe by throwing a serialization error. I don't think I understand the proposal. Under what circumstances would it throw a serialization error? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch, distinguish sections with an empty row in error code table
On Tue, Nov 6, 2012 at 5:44 PM, Karl O. Pinc k...@meme.com wrote:
In your generated output I see:
tr
td colspan=2span class=bold EMPHASIS c3Class 00 —
Successful Completion/span/td
/tr
It's just matter of CSS rule like
td .EMPHASIS { font-size: 140%; }
to make such labels more visible.
---snip--
If you have some way you'd like the css frobbed I can
do that. Or we can forget about it.
Ah, well, as to that, I think you'd have to take that suggestion to
pgsql-www. The style sheets used for the web site are - just to make
things exciting - stored in a completely different source code
repository to which I don't have access. Some kind of CSS
frobnication along the lines you suggest might be worth discussing,
but I don't really work on that stuff.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Thu, Nov 8, 2012 at 5:53 PM, Amit Kapila amit.kap...@huawei.com wrote: On Thursday, November 08, 2012 2:04 PM Heikki Linnakangas wrote: On 19.10.2012 14:42, Amit kapila wrote: On Thursday, October 18, 2012 8:49 PM Fujii Masao wrote: Before implementing the timeout parameter, I think that it's better to change both pg_basebackup background process and pg_receivexlog so that they send back the reply message immediately when they receive the keepalive message requesting the reply. Currently, they always ignore such keepalive message, so status interval parameter (-s) in them always must be set to the value less than replication timeout. We can avoid this troublesome parameter setting by introducing the same logic of walreceiver into both pg_basebackup background process and pg_receivexlog. Please find the patch attached to address the modification mentioned by you (send immediate reply for keepalive). Both basebackup and pg_receivexlog uses the same function ReceiveXLogStream, so single change for both will address the issue. Thanks, committed this one after shuffling it around the changes I committed yesterday. I also updated the docs to not claim that -s option is required to avoid timeout disconnects anymore. Thank you. However I think still the issue will not be completely solved. pg_basebackup/pg_receivexlog can still take long time to detect network break as they don't have timeout concept. To do that I have sent one proposal which is mentioned at end of mail chain: http://archives.postgresql.org/message-id/6C0B27F7206C9E4CA54AE035729E9C3828 53BBED@szxeml509-mbs Do you think there is any need to introduce such mechanism in pg_basebackup/pg_receivexlog? Are you planning to introduce the timeout mechanism in pg_basebackup main process? Or background process? It's useful to implement both. BTW, IIRC the walsender has no timeout mechanism during sending backup data to pg_basebackup. So it's also useful to implement the timeout mechanism for the walsender during backup. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deferrable NOT NULL constraints in 9.3?
On Thu, Nov 8, 2012 at 4:45 AM, andr...@officenet.no wrote: Are there any plans to include DEFERRABLE NOT NULL constraints in 9.3 so one can do this? create table test(a varchar not null deferrable initially deferred); This works in Oracle and is quite handy when working with ORMs. Not to my knowledge ... although I can't claim to know everything that anyone is working on. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
Dean Rasheed dean.a.rash...@gmail.com writes: On 8 November 2012 14:38, Dean Rasheed dean.a.rash...@gmail.com wrote: Oh wait, that's nonsense (not enough caffeine). The rewrite code needs to know whether there are INSTEAD OF triggers before it decides whether it's going to substitute the base relation. The fundamental problem is that the plans with and without triggers are completely different, and there's no way the executor is going to notice the addition of triggers if they weren't there when the query was rewritten and planned. That's a good point: if we apply the transform, then the view isn't the plan's target table at all anymore, and so whether it has INSTEAD triggers or not isn't going to be noticed at runtime. In fact doesn't the existing plan invalidation mechanism already protect us from this? I'd prefer not to trust that completely, ie the behavior should be somewhat failsafe if invalidation doesn't happen. Thinking about that, we have these cases for the auto-updatable case as submitted: 1. INSTEAD triggers added after planning: they'll be ignored, as per above, but the update on the base table should go through without surprises. 2. INSTEAD triggers removed after planning: you get an error at runtime, which seems fine. However, for the case of only-a-conditional-INSTEAD-rule, INSTEAD triggers added after planning will be fired. So that's not entirely consistent, but maybe that's all right if we expect that plan invalidation will normally prevent the case from occurring. Basically what I'm wondering about is whether the plan should get marked somehow to tell the executor that INSTEAD triggers are expected or not. This doesn't seem terribly easy though, since the rewriter is doing this well upstream of where we create a ModifyTable plan node. Maybe it's not worth it given that invalidation should usually protect us. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On 11/7/12 9:17 PM, Bruce Momjian wrote: As a followup to Magnus's report that pg_upgrade was slow for many tables, I did some more testing with many tables, e.g.: CREATE TABLE test991 (x SERIAL); I ran it for 0, 1k, 2k, ... 16k tables, and got these results: tablespg_dump restore pg_upgrade(increase) 0 0.300.24 11.73(-) 1000 6.466.55 28.79(2.45x) 2000 29.82 20.96 69.75(2.42x) 4000 95.70 115.88 289.82(4.16x) 8000 405.38 505.93 1168.60(4.03x) 160001702.23 2197.56 5022.82(4.30x) I can reproduce these numbers, more or less. (Additionally, it ran out of shared memory with the default setting when dumping the 8000 tables.) But this issue seems to be entirely the fault of sequences being present. When I replace the serial column with an int, everything finishes within seconds and scales seemingly linearly. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
Dean Rasheed dean.a.rash...@gmail.com writes: If we did nothing here then it would go on to either fire any INSTEAD OF triggers or raise an error if there aren't any. The problem with that is that it makes trigger-updatable views and auto-updatable views inconsistent in their behaviour with qualified INSTEAD rules. I don't think the existing interaction between trigger-updatable views and qualified INSTEAD rules is documented, so perhaps that's something that needs work. I'm still unhappy about this decision though, and after further thought I think I can explain why a bit better: it's actually *not* like the way rules work now. The current rule semantics are basically that: 1. The original query is done only if there are no unconditional INSTEAD rules and no conditional INSTEAD rule's condition is true. 2. Unconditional INSTEAD actions are done, well, unconditionally. 3. Each conditional INSTEAD action is done if its condition is true. I believe that the right way to think about the auto-update transformation is that it should act like a supplied-by-default unconditional INSTEAD rule. Which would mean that it happens unconditionally, per #2. As submitted, though, the auto-update query executes only if there are no unconditional INSTEAD rules *and* no conditional INSTEAD rule's condition is true. I do not think this is either consistent or useful. It's treating the auto-update replacement query as if it were the original, which it is not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch, distinguish sections with an empty row in error code table
On 11/08/2012 11:10:39 AM, Robert Haas wrote:
On Tue, Nov 6, 2012 at 5:44 PM, Karl O. Pinc k...@meme.com wrote:
In your generated output I see:
tr
td colspan=2span class=bold EMPHASIS c3Class 00 —
Successful Completion/span/td
/tr
It's just matter of CSS rule like
td .EMPHASIS { font-size: 140%; }
to make such labels more visible.
---snip--
If you have some way you'd like the css frobbed I can
do that. Or we can forget about it.
Ah, well, as to that, I think you'd have to take that suggestion to
pgsql-www. The style sheets used for the web site are - just to make
things exciting - stored in a completely different source code
repository to which I don't have access. Some kind of CSS
frobnication along the lines you suggest might be worth discussing,
but I don't really work on that stuff.
Without being able to pass additional style from the source
docs through to the html it seems a bit spooky to do this.
I'd be afraid of inadvertent styling. Someone who
knows more might not be so fearful.
Regards,
Karl k...@meme.com
Free Software: You don't pay back, you pay forward.
-- Robert A. Heinlein
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch, distinguish sections with an empty row in error code table
On 11/6/12 12:09 PM, Karl O. Pinc wrote: --snip On 11/06/2012 10:41:04 AM, Stefan Seefeld wrote: On 11/06/2012 11:27 AM, Karl O. Pinc wrote: Hi, I'm trying to improve a table of PostgreSQL error codes in the PostgreSQL docs. Postgresql uses Docbook 4.2 and openjade/dsssl style sheets. Is there even a remote chance for you to migrate to XSL stylesheets ? I'm not sure the DSSL ones are maintained at all at this point, so improvements, fixes, and customizations are much easier to get with XSL. --snip https://wiki.postgresql.org/wiki/Switching_PostgreSQL_documentation_from_SGML_to_XML -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] the number of pending entries in GIN index with FASTUPDATE=on
On Wed, Nov 7, 2012 at 4:34 AM, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 6, 2012 at 1:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Nov 2, 2012 at 12:48 PM, Fujii Masao masao.fu...@gmail.com wrote: Is there the way to know the number of pending entries in GIN index which was created with FASTUPDATE = on? If not, is it worth implementing the function returning that number? Seems useful to me. Seems like the appropriate place to expose this would be in a GIN-specific variant of contrib/pgstattuple's pgstatindex(). Yeah, that seems good to me, too. Or something in pgstatindex, anyway. Agreed. Attached patch introduces the pgstatginindex() which now reports GIN version number, number of pages in the pending list and number of tuples in the pending list, as information about a GIN index. Regards, -- Fujii Masao pgstatginindex_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deferrable NOT NULL constraints in 9.3?
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 8, 2012 at 4:45 AM, andr...@officenet.no wrote: Are there any plans to include DEFERRABLE NOT NULL constraints in 9.3 so one can do this? create table test(a varchar not null deferrable initially deferred); This works in Oracle and is quite handy when working with ORMs. Not to my knowledge ... although I can't claim to know everything that anyone is working on. You could get the effect by applying the not-null check in a custom constraint trigger. A bit tedious but it'd work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] False indication in pg_stat_replication.sync_state
On Fri, Oct 19, 2012 at 10:29 PM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Oct 19, 2012 at 5:46 PM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Ouch! I'm sorry to have sent truly buggy version, please abandon v2 patch sent just before. Added include access/transam.h to syncrep.c and corrected the name of XLByteEQ. Thanks for updating the patch! This looks good to me. Thank you for comment. I think this patch should be applied for 9.2.2 and 9.1.7. Looks good to me, though I don't think the source code comment needs to be updated in the way the patch does. Ok, the patch for walsender.c becomes 1 liner, quite simple. However, I've forgotten to treat other three portions in walsender.c and syncrep.c also does XLogRecPtrIsInvalid(XLogPtr which comes from WAL receiver). This new patch includes the changes for them. Good catch. Does any commiter pick up this? Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 17:37, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: If we did nothing here then it would go on to either fire any INSTEAD OF triggers or raise an error if there aren't any. The problem with that is that it makes trigger-updatable views and auto-updatable views inconsistent in their behaviour with qualified INSTEAD rules. I don't think the existing interaction between trigger-updatable views and qualified INSTEAD rules is documented, so perhaps that's something that needs work. I'm still unhappy about this decision though, and after further thought I think I can explain why a bit better: it's actually *not* like the way rules work now. The current rule semantics are basically that: 1. The original query is done only if there are no unconditional INSTEAD rules and no conditional INSTEAD rule's condition is true. 2. Unconditional INSTEAD actions are done, well, unconditionally. 3. Each conditional INSTEAD action is done if its condition is true. I believe that the right way to think about the auto-update transformation is that it should act like a supplied-by-default unconditional INSTEAD rule. Which would mean that it happens unconditionally, per #2. As submitted, though, the auto-update query executes only if there are no unconditional INSTEAD rules *and* no conditional INSTEAD rule's condition is true. I do not think this is either consistent or useful. It's treating the auto-update replacement query as if it were the original, which it is not. But if you treat the auto-update transformation as a supplied-by-default unconditional INSTEAD rule, and the user defines their own conditional INSTEAD rule, if the condition is true it would execute both the conditional rule action and the auto-update action, making it an ALSO rule rather than the INSTEAD rule the user specified. Taking a concrete example: create table foo(a int); create table bar(a int); create view foo_v as select * from foo; create rule foo_r as on insert to foo_v where new.a 0 do instead insert into bar values(new.a); I would expect that to put all positive values into foo, and all negative values into bar, which is indeed what happens as it stands. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] False indication in pg_stat_replication.sync_state
Fujii Masao escribió: On Fri, Oct 19, 2012 at 10:29 PM, Fujii Masao masao.fu...@gmail.com wrote: However, I've forgotten to treat other three portions in walsender.c and syncrep.c also does XLogRecPtrIsInvalid(XLogPtr which comes from WAL receiver). This new patch includes the changes for them. Good catch. Does any commiter pick up this? If not, please add to next commitfest so that we don't forget. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] auto_explain WAS: RFC: Timing Events
For the log volume, would it help if there was some unexpectedness threshold? That is, if a statement exceeds the duration threshold, it gets explained, But then it only gets logged if the actual duration divided by the cost estimate exceeds some threshold. Thing is, pg_stat_plans makes this kind of use of auto_explain obsolete. With a much more useful interface. Where autoexplain would be useful would be to get all of the plans, regardless of execution time, for a specific application session or a specific stored procedure. However, that requires the ability of the application session to toggle auto-explain logging settings. This was part of the functionality which Itagaki demonstrated for auto-explain when he first proposed it, but was later disabled for security reasons before commit IIRC. Writing a SECURITY DEFINER function to get around inability to toggle as a regular user has been a nonstarter when I've proposed it to clients. Also, logging only the long-running queries is less useful than people on this list seem to think. When I'm doing real performance analysis, I need to see *everything* which was run, not just the slow stuff. Often the real problem is a query which used to take 1.1ms, now takes 1.8ms, and gets run 400 times/second. Looking just at the slow queries won't tell you that. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tweaking ResolveNew's API
The submitted patch for auto-updatable views uses rewriteManip.c's
ResolveNew() function to replace Vars referencing the view with Vars
referencing the underlying table. That's mostly all right, except that
ResolveNew has some hard-wired choices about what it should do if a Var
to be replaced doesn't have any match in the replacement targetlist.
This should never occur in the auto-updatable view case, so really the
preferred behavior would be to throw an error, but that's not presently
one of the options.
What I'm thinking about doing is replacing ResolveNew's event argument
with a single-purpose enum listing the supported no-match actions,
along the lines of
enum {
RESOLVENEW_CHANGE_VARNO,
RESOLVENEW_SUBSTITUTE_NULL,
RESOLVENEW_REPORT_ERROR
}
A possible objection to this is that most C compilers wouldn't complain
if a call site is still trying to use the old convention of passing a
CmdType value. In the core code, there are only four call sites and
three are in rewriteHandler.c itself, so this isn't much of a problem
--- but if there's any third-party code such as FDWs that's trying to
make use of this function for querytree manipulation, there'd be a risk
of failing to notice the need to update the call.
One way to force a compile error would be to reorder the function's
argument list. But doing so in a way that would definitely get the
compiler's attention seems to require a fairly arbitrary choice of
argument order, and also it would add a little extra risk of not
making the code changes correctly. I'm inclined not to do that.
We have changed this function's API at least twice in the past, but each
time by adding new arguments, which will certainly draw a compile error;
so the lack of complaints about those changes doesn't necessarily prove
that nobody's using it outside core.
Thoughts, objections?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
Dean Rasheed dean.a.rash...@gmail.com writes: On 8 November 2012 17:37, Tom Lane t...@sss.pgh.pa.us wrote: I believe that the right way to think about the auto-update transformation is that it should act like a supplied-by-default unconditional INSTEAD rule. But if you treat the auto-update transformation as a supplied-by-default unconditional INSTEAD rule, and the user defines their own conditional INSTEAD rule, if the condition is true it would execute both the conditional rule action and the auto-update action, making it an ALSO rule rather than the INSTEAD rule the user specified. Well, that's how things work if you specify both a conditional and an unconditional INSTEAD action, so I don't find this so surprising. What you're arguing for would make some sense if the auto-update feature could be seen as something that acts ahead of, and independently of, INSTEAD rules and triggers. But it can't be treated that way: in particular, the fact that it doesn't fire when there's an INSTEAD trigger pretty much breaks the fiction that it's an independent feature. I would rather be able to explain its interaction with rules by saying it's a default implementation of an INSTEAD rule than by saying well, it has these weird interactions with INSTEAD rules, which are different for conditional and unconditional INSTEAD rules. Or we could go back to what I suggested to start with, which is that the auto-update transformation doesn't fire if there are *either* conditional or unconditional INSTEAD rules. That still seems like the best way if you want an arms-length definition of behavior; it means we can explain the interaction with INSTEAD rules exactly the same as the interaction with INSTEAD triggers, ie, having one prevents the transformation from being used. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tweaking ResolveNew's API
Tom Lane wrote: A possible objection to this is that most C compilers wouldn't complain if a call site is still trying to use the old convention of passing a CmdType value. In the core code, there are only four call sites and three are in rewriteHandler.c itself, so this isn't much of a problem --- but if there's any third-party code such as FDWs that's trying to make use of this function for querytree manipulation, there'd be a risk of failing to notice the need to update the call. Failing to notice such changes is easy if the compiler doesn't even issue a warning, so *some* way to have old code fail (even better if it's a hard error and not just a warning) would be nice. I'm not sure I have useful suggestions on how to do it, though, just a +1 to doing it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tweaking ResolveNew's API
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: A possible objection to this is that most C compilers wouldn't complain if a call site is still trying to use the old convention of passing a CmdType value. In the core code, there are only four call sites and three are in rewriteHandler.c itself, so this isn't much of a problem --- but if there's any third-party code such as FDWs that's trying to make use of this function for querytree manipulation, there'd be a risk of failing to notice the need to update the call. Failing to notice such changes is easy if the compiler doesn't even issue a warning, so *some* way to have old code fail (even better if it's a hard error and not just a warning) would be nice. I'm not sure I have useful suggestions on how to do it, though, just a +1 to doing it. Actually, it occurs to me that there's a really easy way to get the result: let's just rename the function. ResolveNew isn't an amazingly mnemonic name anyway. How about ReplaceVarsFromTargetList? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Tweaking ResolveNew's API
On Thu, Nov 08, 2012 at 02:35:34PM -0500, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: A possible objection to this is that most C compilers wouldn't complain if a call site is still trying to use the old convention of passing a CmdType value. In the core code, there are only four call sites and three are in rewriteHandler.c itself, so this isn't much of a problem --- but if there's any third-party code such as FDWs that's trying to make use of this function for querytree manipulation, there'd be a risk of failing to notice the need to update the call. Failing to notice such changes is easy if the compiler doesn't even issue a warning, so *some* way to have old code fail (even better if it's a hard error and not just a warning) would be nice. I'm not sure I have useful suggestions on how to do it, though, just a +1 to doing it. Actually, it occurs to me that there's a really easy way to get the result: let's just rename the function. ResolveNew isn't an amazingly mnemonic name anyway. How about ReplaceVarsFromTargetList? +1 for descriptive names :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE SERIALIZABLE and frozen COPY
On 8 November 2012 17:07, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 7, 2012 at 10:34 AM, Simon Riggs si...@2ndquadrant.com wrote: For 9.2 we discussed having COPY setting tuples as frozen. Various details apply. Earlier threads: RFC: Making TRUNCATE more MVCC-safe COPY with hints, rebirth I was unhappy with changing the behaviour of TRUNCATE, and still am. So the proposal here is to have a specific modifier on TRUNCATE command that makes it MVCC safe by throwing a serialization error. I don't think I understand the proposal. Under what circumstances would it throw a serialization error? If you ask for TRUNCATE SERIALIZABLE then if someone asks for data in the table and has a snapshot that can see earlier data then it will throw a serializable error. So its a new kind of TRUNCATE that is MVCC safe. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 19:29, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: On 8 November 2012 17:37, Tom Lane t...@sss.pgh.pa.us wrote: I believe that the right way to think about the auto-update transformation is that it should act like a supplied-by-default unconditional INSTEAD rule. But if you treat the auto-update transformation as a supplied-by-default unconditional INSTEAD rule, and the user defines their own conditional INSTEAD rule, if the condition is true it would execute both the conditional rule action and the auto-update action, making it an ALSO rule rather than the INSTEAD rule the user specified. Well, that's how things work if you specify both a conditional and an unconditional INSTEAD action, so I don't find this so surprising. To me, it's very surprising, so I must be thinking about it differently. I think that I'm really expecting auto-updatable views to behave like tables, so I keep coming back to the question what would happen if you did that to a table?. Taking another concrete example, I could use a conditional DO INSTEAD NOTHING rule on a table to prevent certain values from being inserted: create table foo(a int); create rule foo_r as on insert to foo where new.a 0 do instead nothing; insert into foo values(-1),(1); select * from foo; a --- 1 (1 row) So I would expect the same behaviour from an auto-updatable view: create table bar(a int); create view bar_v as select * from bar; create rule bar_r as on insert to bar_v where new.a 0 do instead nothing; insert into bar_v values(-1),(1); select * from bar_v; a --- 1 (1 row) Having that put both -1 and 1 into bar seems completely wrong to me. I could live with it raising a you need an unconditional instead rule error, but that makes the auto-update view seem a bit half-baked. This also seems like a much more plausible case where users might have done something like this with a trigger-updatable view, so I don't think the backwards-compatibility argument can be ignored. Regards, Dean What you're arguing for would make some sense if the auto-update feature could be seen as something that acts ahead of, and independently of, INSTEAD rules and triggers. But it can't be treated that way: in particular, the fact that it doesn't fire when there's an INSTEAD trigger pretty much breaks the fiction that it's an independent feature. I would rather be able to explain its interaction with rules by saying it's a default implementation of an INSTEAD rule than by saying well, it has these weird interactions with INSTEAD rules, which are different for conditional and unconditional INSTEAD rules. Or we could go back to what I suggested to start with, which is that the auto-update transformation doesn't fire if there are *either* conditional or unconditional INSTEAD rules. That still seems like the best way if you want an arms-length definition of behavior; it means we can explain the interaction with INSTEAD rules exactly the same as the interaction with INSTEAD triggers, ie, having one prevents the transformation from being used. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] AutoVacuum starvation from sinval messages
While doing a rather contrived test of something else, using: perl -le 'print create table foo (x serial); drop table foo; foreach (1..1e6)'|psql I noticed starvation of autovacuum launcher process and bloating of catalogs. Basically the WaitLatch in autovacuum.c line 602 (in head) never returns. This was introduced by a180776f7a1c4554f214b, Teach unix_latch.c to use poll() where available When the poll() gets EINTR by SIGUSR1 for the invalidation, it restarts at the full timeout (60 seconds in this case) with no accounting for the time already waited. It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Should gettimeofday be called before and after the poll() and then the difference deducted from timeout? Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
Dean Rasheed dean.a.rash...@gmail.com writes: create table bar(a int); create view bar_v as select * from bar; create rule bar_r as on insert to bar_v where new.a 0 do instead nothing; insert into bar_v values(-1),(1); select * from bar_v; a --- 1 (1 row) Having that put both -1 and 1 into bar seems completely wrong to me. Right now, what you get from that is ERROR: cannot insert into view bar_v HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger. and (modulo the contents of the HINT) I think that's still what you should get. If the user has got some DO INSTEAD rules we should not be second-guessing what should happen. This also seems like a much more plausible case where users might have done something like this with a trigger-updatable view, so I don't think the backwards-compatibility argument can be ignored. I think the most reasonable backwards-compatibility argument is that we shouldn't change the behavior if there are either INSTEAD rules or INSTEAD triggers. Otherwise we may be disturbing carefully constructed behavior (and no, I don't buy that throw an error couldn't be what the user intended). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
Jeff Janes jeff.ja...@gmail.com writes: Basically the WaitLatch in autovacuum.c line 602 (in head) never returns. This was introduced by a180776f7a1c4554f214b, Teach unix_latch.c to use poll() where available When the poll() gets EINTR by SIGUSR1 for the invalidation, it restarts at the full timeout (60 seconds in this case) with no accounting for the time already waited. Hmm. That seems like a problem, all right, but I don't believe the claim that it applies only to the poll() code path. On many platforms the select() path would have the same issue. It's a bit annoying to have to incur an extra gettimeofday call per WaitLatch operation in case this happens, but there doesn't seem to be a lot of choice. I guess we can arrange to incur the extra call only when a timeout is requested --- but IIRC that's going to be the majority of usages anyway ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
Jeff Janes jeff.ja...@gmail.com writes: On Thu, Nov 8, 2012 at 12:36 PM, Jeff Janes jeff.ja...@gmail.com wrote: Should gettimeofday be called before and after the poll() and then the difference deducted from timeout? Something like this? Meh. Not like that, because (1) we shouldn't add overhead when no timeout is requested. (2) it'd be better to do only one gettimeofday at the start and then one in the EINTR path, not one per loop; this saves syscalls and also avoids slippage of the timeout time. (3) the select() path needs a similar fix. But I think you have a good idea to use the INSTR_TIME macros instead of custom code, even though this is Unix-only so there's not more than one underlying implementation. Will fix this up and commit. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
I wrote: Jeff Janes jeff.ja...@gmail.com writes: When the poll() gets EINTR by SIGUSR1 for the invalidation, it restarts at the full timeout (60 seconds in this case) with no accounting for the time already waited. Hmm. That seems like a problem, all right, but I don't believe the claim that it applies only to the poll() code path. On many platforms the select() path would have the same issue. BTW, doesn't win32_latch.c have the identical problem? I don't see it updating the timeout after a signal, either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
On 8 November 2012 20:36, Jeff Janes jeff.ja...@gmail.com wrote: It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Yes, its a bug, but do you really believe the above? In what cases? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
On 2012-11-08 22:40:43 +, Simon Riggs wrote: On 8 November 2012 20:36, Jeff Janes jeff.ja...@gmail.com wrote: It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Yes, its a bug, but do you really believe the above? In what cases? Temporary tables might a good contender. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
On 11/08/2012 11:40 PM, Simon Riggs wrote: On 8 November 2012 20:36, Jeff Janes jeff.ja...@gmail.com wrote: It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Yes, its a bug, but do you really believe the above? In what cases? Could heavy use of temporary tables cause this ? Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TRUNCATE SERIALIZABLE and frozen COPY
On 11/08/2012 08:51 PM, Simon Riggs wrote: On 8 November 2012 17:07, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 7, 2012 at 10:34 AM, Simon Riggs si...@2ndquadrant.com wrote: For 9.2 we discussed having COPY setting tuples as frozen. Various details apply. Earlier threads: RFC: Making TRUNCATE more MVCC-safe COPY with hints, rebirth I was unhappy with changing the behaviour of TRUNCATE, and still am. So the proposal here is to have a specific modifier on TRUNCATE command that makes it MVCC safe by throwing a serialization error. I don't think I understand the proposal. Under what circumstances would it throw a serialization error? If you ask for TRUNCATE SERIALIZABLE then if someone asks for data in the table and has a snapshot that can see earlier data then it will throw a serializable error. So its a new kind of TRUNCATE that is MVCC safe. Can't we make it so that the reader with earlier snapshot sees the data from the pre-truncation file ? and we unlink the base file(s) only once nobody has a snapshot the can see it ? or are there some subtler problems (I was under impression that we already did this as described above) ? Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
Simon Riggs si...@2ndquadrant.com writes: On 8 November 2012 20:36, Jeff Janes jeff.ja...@gmail.com wrote: It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Yes, its a bug, but do you really believe the above? In what cases? It doesn't take a whole lot of DDL to provoke an sinval overrun, if the recipient process is just sitting idle and not servicing the messages. I think Jeff's concern is entirely valid. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: As a followup to Magnus's report that pg_upgrade was slow for many tables, I did some more testing with many tables, e.g.: ... Any ideas? I am attaching my test script. Have you reviewed the thread at: http://archives.postgresql.org/pgsql-performance/2012-09/msg3.php ? There is a known N^2 behavior when using pg_dump against pre-9.3 servers. There was a proposed patch to pg_dump to work around the problem when it is used against older servers, but it is was not accepted and not entered into a commitfest. For one thing because it there was doubts about how stable it would be at very large scale and it wasn't tested all that thoroughly, and for another, it would be a temporary improvement as once the server itself is upgraded to 9.3, the kludge in pg_dump would no longer be an improvement. The most recent version (that I can find) of that work-around patch is at: http://archives.postgresql.org/pgsql-performance/2012-06/msg00071.php I don't know if that will solve your particular case, but it is probably worth a try. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
On Thu, Nov 8, 2012 at 2:50 PM, Hannu Krosing ha...@2ndquadrant.com wrote: On 11/08/2012 11:40 PM, Simon Riggs wrote: On 8 November 2012 20:36, Jeff Janes jeff.ja...@gmail.com wrote: It does not seem outrageous to me that there would be real-world conditions in which invalidations would be sent more than once a minute over prolonged periods, so this total starvation seems like a bug. Yes, its a bug, but do you really believe the above? In what cases? We see lots of traffic on the mail list about people trying to dump several hundred thousand tables, or they can only create one database every two minutes, or truncating hundreds of tables at a time over and over again gets slow, etc. I know little about the internal of the invalidation code, but I would think doing that kind of thing must generate a lot of them. Could heavy use of temporary tables cause this ? It looks like they do. I'm not sure what is being invalidated in those cases, but something seems to be. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote: On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: As a followup to Magnus's report that pg_upgrade was slow for many tables, I did some more testing with many tables, e.g.: ... Any ideas? I am attaching my test script. Have you reviewed the thread at: http://archives.postgresql.org/pgsql-performance/2012-09/msg3.php ? There is a known N^2 behavior when using pg_dump against pre-9.3 servers. I am actually now dumping git head/9.3, so I assume all the problems we know about should be fixed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Enabling Checksums
As I understand it, the main part of the remaining work to be done for the checksums patch (at least the first commit) is to have a better way to enable/disable them. For the sake of simplicity (implementation as well as usability), it seems like there is agreement that checksums should be enabled or disabled for the entire instance, not per-table. I don't think a GUC entirely makes sense (in its current form, anyway). We basically care about 3 states: 1. Off: checksums are not written, nor are they verified. Pages that are newly dirtied have the checksum information in the header cleared. 2. Enabling: checksums are written for every dirty page, but only verified for pages where the checksum is present (as determined by information in the page header). 3. On: checksums are written for every dirty page, and verified for every page that's read. If a page does not have a checksum, it's corrupt. Does it make sense to store this information in pg_control? That doesn't require adding any new file, and it has the benefit that it's already checksummed. It's available during recovery and can be made available pretty easily in the places where we write data. And the next question is what commands to add to change state. Ideas: CHECKSUMS ENABLE; -- set state to Enabling CHECKSUMS DISABLE; -- set state to Off And then to get to the On state, you have to run a system-wide VACUUM while in the Enabling state. Or, if the above syntax causes problems, we can make all of these into VACUUM options. Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] AutoVacuum starvation from sinval messages
I wrote: Hmm. That seems like a problem, all right, but I don't believe the claim that it applies only to the poll() code path. On many platforms the select() path would have the same issue. BTW, doesn't win32_latch.c have the identical problem? I don't see it updating the timeout after a signal, either. I've committed a patch for this, but the win32_latch version is entirely innocent of any testing. The buildfarm will probably show any mistake there, but if someone would like to try Jeff's test case on Windows just to make sure the autovac launcher wakes up on time, it wouldn't hurt. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] commit 1eb1dde049ccfffc42c80c2 broke make -j2
On Wed, Nov 7, 2012 at 10:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Jeff Janes jeff.ja...@gmail.com writes: Since commit 1eb1dde049ccfffc42c80c2, Have make never delete intermediate files automatically, I've frequently been getting errors doing make -j2 after a maintainer-clean. Worksforme on a Fedora 16 box (make 3.82). What gmake version are you running? There are threads in the archives about make bugs causing problems like this. I've using GNU Make 3.82 as well. On openSuse 12.2. Oh btw ... if you're using --enable-depend, see https://bugzilla.redhat.com/show_bug.cgi?id=835424 Nope, just plain ./configure I'll poke around a bit and see what I can find out. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP checksums patch
On Mon, Nov 5, 2012 at 12:19 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 29, 2012 at 4:31 PM, Jim Nasby j...@nasby.net wrote: For whatever it's worth... we (and presumably others) still use londiste (or Slony) as our upgrade path, so we could tolerate a cluster-wide setting. We'd just set it when building new clusters via londiste and forget about it. So I'd rather see this get in at a cluster level than not make it at all while we wait for something better. Yeah. I definitely think that we could shed an enormous amount of complexity by deciding that this is, for now, an option that can only be selected at initdb time. That would remove approximately 85% of everything I've ever disliked about this patch - without, I think, precluding the possibility of improving things later. I see one thing to be concerned about, there... I imagine it would not be a totally happy thing if the only way to switch it on/off was to use Slony or Londiste to replicate into a database with the opposite setting. (e.g. - This implies that built-in replication may only replicate into a database with the identical checksum configuration.) It's not outrageous for it to be a pretty heavyweight operation to switch polarities, but there's such a thing as too heavy. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this?
Re: [HACKERS] Enabling Checksums
Jeff Davis wrote: And the next question is what commands to add to change state. Ideas: CHECKSUMS ENABLE; -- set state to Enabling CHECKSUMS DISABLE; -- set state to Off And then to get to the On state, you have to run a system-wide VACUUM while in the Enabling state. Or, if the above syntax causes problems, we can make all of these into VACUUM options. There's no such thing as a system-wide VACUUM. The most you can get is a database-wide VACUUM, which means you'd have to store the state per-database somewhere (presumably the pg_database catalog), and perhaps pg_control could have it as a system-wide value that's computed as the minimum of all database states (so it stays enabling until all databases have upgraded to on). -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Thu, Nov 8, 2012 at 12:30:11PM -0500, Peter Eisentraut wrote: On 11/7/12 9:17 PM, Bruce Momjian wrote: As a followup to Magnus's report that pg_upgrade was slow for many tables, I did some more testing with many tables, e.g.: CREATE TABLE test991 (x SERIAL); I ran it for 0, 1k, 2k, ... 16k tables, and got these results: tablespg_dump restore pg_upgrade(increase) 0 0.300.24 11.73(-) 1000 6.466.55 28.79(2.45x) 2000 29.82 20.96 69.75(2.42x) 4000 95.70 115.88 289.82(4.16x) 8000 405.38 505.93 1168.60(4.03x) 160001702.23 2197.56 5022.82(4.30x) I can reproduce these numbers, more or less. (Additionally, it ran out of shared memory with the default setting when dumping the 8000 tables.) But this issue seems to be entirely the fault of sequences being present. When I replace the serial column with an int, everything finishes within seconds and scales seemingly linearly. I did some more research and realized that I was not using --schema-only like pg_upgrade uses. With that setting, things look like this: --schema-only tablespg_dump restore pg_upgrade 1 0.270.2311.73(-) 10003.645.1828.79(2.45) 2000 13.07 14.6369.75(2.42) 4000 43.93 66.87 289.82(4.16) 8000 190.63 326.67 1168.60(4.03) 16000 757.80 1402.82 5022.82(4.30) You can still see the 4x increase, but it now for all tests --- basically, every time the number of tables doubles, the time to dump or restore a _single_ table doubles, e.g. for 1k tables, a single table takes 0.00364 to dump, for 16k tables, a single table takes 0.04736 to dump, a 13x slowdown. Second, with --schema-only, you can see the dump/restore is only 50% of the duration of pg_upgrade, and you can also see that pg_upgrade itself is slowing down as the number of tables increases, even ignoring the dump/reload time. This is all bad news. :-( I will keep digging. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote: On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian br...@momjian.us wrote: As a followup to Magnus's report that pg_upgrade was slow for many tables, I did some more testing with many tables, e.g.: ... Any ideas? I am attaching my test script. Have you reviewed the thread at: http://archives.postgresql.org/pgsql-performance/2012-09/msg3.php ? There is a known N^2 behavior when using pg_dump against pre-9.3 servers. I am actually now dumping git head/9.3, so I assume all the problems we know about should be fixed. Are sure the server you are dumping out of is head? Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to dump 16,000 tables (schema only) like your example, and it is definitely quadratic. But using head's pg_dump do dump tables out of head's server, it only took 24.95 seconds, and the quadratic term is not yet important, things still look linear. But even the 179.11 seconds is several times faster than your report of 757.8, so I'm not sure what is going on there. I don't think my laptop is particularly fast: Intel(R) Pentium(R) CPU B960 @ 2.20GHz Is the next value, increment, etc. for a sequence stored in a catalog, or are they stored in the 8kb file associated with each sequence? If they are stored in the file, than it is shame that pg_dump goes to the effort of extracting that info if pg_upgrade is just going to overwrite it anyway. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enabling Checksums
On Friday, November 09, 2012 6:32 AM Jeff Davis wrote: As I understand it, the main part of the remaining work to be done for the checksums patch (at least the first commit) is to have a better way to enable/disable them. For the sake of simplicity (implementation as well as usability), it seems like there is agreement that checksums should be enabled or disabled for the entire instance, not per-table. I don't think a GUC entirely makes sense (in its current form, anyway). We basically care about 3 states: 1. Off: checksums are not written, nor are they verified. Pages that are newly dirtied have the checksum information in the header cleared. 2. Enabling: checksums are written for every dirty page, but only verified for pages where the checksum is present (as determined by information in the page header). 3. On: checksums are written for every dirty page, and verified for every page that's read. If a page does not have a checksum, it's corrupt. Does it make sense to store this information in pg_control? That doesn't require adding any new file, and it has the benefit that it's already checksummed. It's available during recovery and can be made available pretty easily in the places where we write data. And the next question is what commands to add to change state. Ideas: CHECKSUMS ENABLE; -- set state to Enabling CHECKSUMS DISABLE; -- set state to Off And then to get to the On state, you have to run a system-wide VACUUM while in the Enabling state. Or, if the above syntax causes problems, we can make all of these into VACUUM options. I think one thing may needs to be taken care during such a VACUUM operation is not to allow user to say CHECKSUM DISABLE. Also how about following ways : 1. Allow CHECKSUM Enable only during initdb as mentioned by Robert. Allow user to only do CHECKSUM DISABLE after initdb. 2. Do the Checksum only for particular pages (SRLU) or to do for System tables only. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enabling Checksums
On 09/11/12 02:01, Jeff Davis wrote: As I understand it, the main part of the remaining work to be done for the checksums patch (at least the first commit) is to have a better way to enable/disable them. For the sake of simplicity (implementation as well as usability), it seems like there is agreement that checksums should be enabled or disabled for the entire instance, not per-table. I can definately see that simplicity is an argument here, but I can easily imagine that some performance hungry users would prefer to be able to disable the functionality on a per table level. UNCHECKSUMMED TABLES (similar to UNLOGGED TABLES). I would definately stuff our system in state = 2 in your description if it was available. -- Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
Jeff Janes jeff.ja...@gmail.com writes: Are sure the server you are dumping out of is head? I experimented a bit with dumping/restoring 16000 tables matching Bruce's test case (ie, one serial column apiece). The pg_dump profile seems fairly flat, without any easy optimization targets. But restoring the dump script shows a rather interesting backend profile: samples %image name symbol name 3086139.6289 postgres AtEOXact_RelationCache 9911 12.7268 postgres hash_seq_search 2682 3.4440 postgres init_sequence 2218 2.8482 postgres _bt_compare 2120 2.7223 postgres hash_search_with_hash_value 1976 2.5374 postgres XLogInsert 1429 1.8350 postgres CatalogCacheIdInvalidate 1282 1.6462 postgres LWLockAcquire 973 1.2494 postgres LWLockRelease 702 0.9014 postgres hash_any The hash_seq_search time is probably mostly associated with AtEOXact_RelationCache, which is run during transaction commit and scans the relcache hashtable looking for tables created in the current transaction. So that's about 50% of the runtime going into that one activity. There are at least three ways we could whack that mole: * Run the psql script in --single-transaction mode, as I was mumbling about the other day. If we were doing AtEOXact_RelationCache only once, rather than once per CREATE TABLE statement, it wouldn't be a problem. Easy but has only a narrow scope of applicability. * Keep a separate list (or data structure of your choice) so that relcache entries created in the current xact could be found directly rather than having to scan the whole relcache. That'd add complexity though, and could perhaps be a net loss for cases where the relcache isn't so bloated. * Limit the size of the relcache (eg by aging out not-recently-referenced entries) so that we aren't incurring O(N^2) costs for scripts touching N tables. Again, this adds complexity and could be counterproductive in some scenarios. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Thursday, November 08, 2012 10:42 PM Fujii Masao wrote: On Thu, Nov 8, 2012 at 5:53 PM, Amit Kapila amit.kap...@huawei.com wrote: On Thursday, November 08, 2012 2:04 PM Heikki Linnakangas wrote: On 19.10.2012 14:42, Amit kapila wrote: On Thursday, October 18, 2012 8:49 PM Fujii Masao wrote: Before implementing the timeout parameter, I think that it's better to change both pg_basebackup background process and pg_receivexlog so that they send back the reply message immediately when they receive the keepalive message requesting the reply. Currently, they always ignore such keepalive message, so status interval parameter (-s) in them always must be set to the value less than replication timeout. We can avoid this troublesome parameter setting by introducing the same logic of walreceiver into both pg_basebackup background process and pg_receivexlog. Please find the patch attached to address the modification mentioned by you (send immediate reply for keepalive). Both basebackup and pg_receivexlog uses the same function ReceiveXLogStream, so single change for both will address the issue. Thanks, committed this one after shuffling it around the changes I committed yesterday. I also updated the docs to not claim that -s option is required to avoid timeout disconnects anymore. Thank you. However I think still the issue will not be completely solved. pg_basebackup/pg_receivexlog can still take long time to detect network break as they don't have timeout concept. To do that I have sent one proposal which is mentioned at end of mail chain: http://archives.postgresql.org/message- id/6C0B27F7206C9E4CA54AE035729E9C3828 53BBED@szxeml509-mbs Do you think there is any need to introduce such mechanism in pg_basebackup/pg_receivexlog? Are you planning to introduce the timeout mechanism in pg_basebackup main process? Or background process? It's useful to implement both. By background process, you mean ReceiveXlogStream? For both. I think for background process, it can be done in a way similar to what we have done for walreceiver. But I have some doubts for how to do for main process: Logic similar to walreceiver can not be used incase network goes down during getting other database file from server. The reason for the same is to receive the data files PQgetCopyData() is called in synchronous mode, so it keeps waiting for infinite time till it gets some data. In order to solve this issue, I can think of following options: 1. Making this call also asynchronous (but now sure about impact of this). 2. In function pqWait, instead of passing hard-code value -1 (i.e. infinite wait), we can send some finite time. This time can be received as command line argument from respective utility and set the same in PGconn structure. In order to have timeout value in PGconn, we can have: a. Add new parameter in PGconn to indicate the receive timeout. b. Use the existing parameter connect_timeout for receive timeout also but this may lead to confusion. 3. Any other better option? Apart from above issue, there is possibility that if during connect time network goes down, then it might hang, because connect_timeout by default will be NULL and connectDBComplete will start waiting inifinitely for connection to become successful. So shall we have command line argument separately for this also or any other way as you suugest. BTW, IIRC the walsender has no timeout mechanism during sending backup data to pg_basebackup. So it's also useful to implement the timeout mechanism for the walsender during backup. Yes, its useful, but for walsender the main problem is that it uses blocking send call to send the data. I have tried using tcp_keepalive settings, but the send call doesn't comeout incase of network break. The only way I could get it out is: change in the corresponding file /proc/sys/net/ipv4/tcp_retries2 by using the command echo 8 /proc/sys/net/ipv4/tcp_retries2 As per recommendation, its value should be at-least 8 (equivalent to 100 sec) Do you have any idea, how it can be achieved? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Further pg_upgrade analysis for many tables
On Fri, Nov 9, 2012 at 7:53 AM, Ants Aasma a...@cybertec.at wrote: I also took two profiles (attached). AtEOXact_RelationCache seems to be the culprit for the quadratic growth. One more thing that jumps out as quadratic from the profiles is transfer_all_new_dbs from pg_upgrade (20% of total CPU time at 64k). Searching for non-primary files loops over the whole file list for each relation. This would be a lot faster if we would sort the file list first and use binary search to find the related files. Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proof of concept: auto updatable views [Review of Patch]
On 8 November 2012 21:13, Tom Lane t...@sss.pgh.pa.us wrote: Dean Rasheed dean.a.rash...@gmail.com writes: create table bar(a int); create view bar_v as select * from bar; create rule bar_r as on insert to bar_v where new.a 0 do instead nothing; insert into bar_v values(-1),(1); select * from bar_v; a --- 1 (1 row) Having that put both -1 and 1 into bar seems completely wrong to me. Right now, what you get from that is ERROR: cannot insert into view bar_v HINT: You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger. and (modulo the contents of the HINT) I think that's still what you should get. If the user has got some DO INSTEAD rules we should not be second-guessing what should happen. You say it's second-guessing what should happen, but in every example I've been able to think of, it does exactly what I would expect, and exactly what already happens for a table or a trigger-updatable view. Clearly though, what I expect/find surprising is at odds with what you expect/find surprising. If I think about it, I would summarise my expectations something like this: Given 2 identical tables table1 and table2, and view view2 defined as select * from table2, I would expect view2 to behave identically to table1 for all operations supported by both tables and views. In particular, given any set of rules defined on table1, if the matching set of rules is defined on view2, I would expect all queries on view2 to behave the same as the matching queries on table1. This also seems like a much more plausible case where users might have done something like this with a trigger-updatable view, so I don't think the backwards-compatibility argument can be ignored. I think the most reasonable backwards-compatibility argument is that we shouldn't change the behavior if there are either INSTEAD rules or INSTEAD triggers. Otherwise we may be disturbing carefully constructed behavior (and no, I don't buy that throw an error couldn't be what the user intended). The current behaviour, if there is only a conditional instead rule, is to throw an error whether or not that condition is satisfied. It's hard to imagine that's an error the user intended. However, given the niche nature of conditional instead rules, it doesn't seem so bad to say that auto-updatable views don't support them at the moment, so long as backwards compatibility is maintained in the table and trigger-updatable view cases. So I think the current behaviour to maintain is, for a relation with only a conditional instead rule: if the relation is a table: if the condition is satisfied: fire the rule action else: modify the table else if the relation is a view with triggers: if the condition is satisfied: fire the rule action else: modify the view using the triggers else: throw an error unconditionally That's backwards compatible and easy to document - views with conditional instead rules are not auto-updatable. If anyone cared enough about it, or could come up with a realistic use case, we could always add support for that case in the future. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
