Re: [HACKERS] Fix Windows socket error checking for MinGW
On 08/21/2013 10:06 PM, Noah Misch wrote: I concur, but our field experience doing it this way lessens my concern. I see this change has hit master. I've pulled in the new patch for the Fedora MinGW package. Thanks, Michael -- 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] plpgsql_check_function - rebase for 9.3
rebased Regards Pavel 2013/8/22 Peter Eisentraut pete...@gmx.net On Wed, 2013-03-27 at 23:25 +0100, Pavel Stehule wrote: I redesigned output from plpgsql_check_function. Now, it returns table everytime. Litlle bit code simplification. This patch is in the 2013-09 commitfest but needs a rebase. plpgsql_check_function_20130822.patch.gz Description: GNU Zip compressed 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] proposal: lob conversion functionality
On 8/12/13 1:08 PM, Pavel Stehule wrote: IF (bytes != LENGTH($1)) THEN RAISE EXCEPTION 'Not all data copied to blob'; END IF; PERFORM lo_close(fd); FWIW, it's probably better to close before raising the exception... -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] System catalog vacuum issues
On 8/19/13 7:23 PM, Sergey Konoplev wrote: On Thu, Aug 15, 2013 at 7:03 PM, Vlad Arkhipovarhi...@dc.baikal.ru wrote: Do you have some processes that intensively create tables or columns and then delete them or create them in transaction and rollback the transaction? There are many processes that create and drop temporary tables. That is the problem. Exactly what Jim was writing about. Autovacuum have no chance to clean dead tuples at the end of the table because they are created too intensively. In the latest versions autovacuum behaves so it would stop working when a concurrent lock is acquired. As he suggested you should use vacuum in cron, however it might make other procecess, that create/drop tables to wait. Hrm... even if vacuum cost delay is set? I recall some talk about doing some minimal waiting for the lock, but thought that'd only happen if cost delay was 0. That really doesn't matter though. The whole idea of a cron'd vacuum is to *stop bloat from happening to begin with*. If there's no bloat to begin with, getting the lock to truncate will be a non-issue. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] HeapTupleSatisfiesDirty fails to test HEAP_XMAX_IS_LOCKED_ONLY for TransactionIdIsInProgress(...)
On 8/5/13 10:18 PM, Craig Ringer wrote: The main issue with the test is that it's a dirty hack. What I really want is a way to block a statement at a certain point - to say block after a snapshot is acquired for example - and release that at a time of my choosing. ... Opinions? Is this something that's even worth thinking about further or a non-starter? +1 Something that sets Enova apart from many other places is we have extensive database unit tests. One of the reasons for that is you can do way more testing at the database level than you can trying to go through the application. This seems like just another case of that. We could either try to come up with some C level unit tests (which I suspect wouldn't work in this case), or expose some hooks. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] Design proposal: fsync absorb linear slider
On 7/26/13 7:32 AM, Tom Lane wrote: Greg Smith g...@2ndquadrant.com writes: On 7/26/13 5:59 AM, Hannu Krosing wrote: Well, SSD disks do it in the way proposed by didier (AFAIK), by putting random fs pages on one large disk page and having an extra index layer for resolving random-to-sequential ordering. If your solution to avoiding random writes now is to do sequential ones into a buffer, you'll pay for it by having more expensive random reads later. What I'd point out is that that is exactly what WAL does for us, ie convert a bunch of random writes into sequential writes. But sooner or later you have to put the data where it belongs. FWIW, at RICon East there was someone from Seagate that gave a presentation. One of his points is that even spinning rust is moving to the point where the drive itself has to do some kind of write log. He notes that modern filesystems do the same thing, and the overlap is probably stupid (I pointed out that the most degenerate case is the logging database on the logging filesystem on the logging drive...) It'd be interesting for Postgres to work with drive manufacturers to study ways to get rid of the extra layers of stupid... -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] docbook-xsl version for release builds
Peter Eisentraut pete...@gmx.net writes: On Fri, 2013-07-12 at 12:30 +0200, Magnus Hagander wrote: Given that, I'm fine with just bumping the version on borka to that version. Any objections? This was not done for 9.3rc1, AFAICT. Let's please do it for the next release builds. Um ... touching borka's toolchain post-rc1 sure sounds like a recipe for making ourselves look like idiots in a high-profile release. Wouldn't it be better to wait till after 9.3.0? 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] proposal: lob conversion functionality
2013/8/21 Jim Nasby j...@nasby.net On 8/12/13 1:08 PM, Pavel Stehule wrote: IF (bytes != LENGTH($1)) THEN RAISE EXCEPTION 'Not all data copied to blob'; END IF; PERFORM lo_close(fd); FWIW, it's probably better to close before raising the exception... it should to be done automatically - lo handler is released after transaction end Pavel -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net
Re: [HACKERS] CAST Within EXCLUSION constraint
Noah Misch n...@leadboat.com writes: On Wed, Aug 21, 2013 at 10:13:15AM -0400, Tom Lane wrote: The reason for that is you'd get randomly different results on another installation. In this particular application, I think David doesn't really care about what values he gets as long as they're distinct, so this might be an OK workaround for him. But that's the reasoning for the general prohibition. While a WITHOUT FUNCTION cast does *guarantee* that flaw, working around the restriction with a cast function is all too likely to create the same flaw. Here's the comment about the restriction: * Theoretically you could build a user-defined base type that is * binary-compatible with a composite, enum, or array type. But we * disallow that too, as in practice such a cast is surely a mistake. * You can always work around that by writing a cast function. That's reasonable enough, but we could reduce this to a WARNING. Alexander shows a credible use case. A superuser can easily introduce breakage through careless addition of WITHOUT FUNCTION casts. Permitting borderline cases seems more consistent with the level of user care already expected in this vicinity. Well, if we're gonna allow it, let's just allow it --- I don't see much point in a WARNING here. As you say, superusers are presumed to be responsible adults. 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] PL/pgSQL, RAISE and error context
Hello I played with this topic little bit If I understand, the main problem is in console (or pgAdmin) output. create or replace function foo() returns void as $$ begin for i in 1..5 loop raise notice ' *'; end loop; raise exception '***'; end; $$ language plpgsql; postgres=# select foo(); NOTICE: * NOTICE: * NOTICE: * NOTICE: * NOTICE: * ERROR: *** Time: 2.024 ms postgres=# \set VER VERBOSITY VERSION postgres=# \set VERBOSITY postgres=# \set VERBOSITY postgres=# \set VERBOSITY terse postgres=# select foo(); NOTICE: * NOTICE: * NOTICE: * NOTICE: * NOTICE: * ERROR: *** Time: 0.908 ms postgres=# \set VERBOSITY verbose postgres=# select foo(); NOTICE: 0: * LOCATION: exec_stmt_raise, pl_exec.c:3051 NOTICE: 0: * LOCATION: exec_stmt_raise, pl_exec.c:3051 NOTICE: 0: * LOCATION: exec_stmt_raise, pl_exec.c:3051 NOTICE: 0: * LOCATION: exec_stmt_raise, pl_exec.c:3051 NOTICE: 0: * LOCATION: exec_stmt_raise, pl_exec.c:3051 ERROR: P0001: *** LOCATION: exec_stmt_raise, pl_exec.c:3051 Time: 0.314 ms I see a two little bit not nice issues: a) in terse mode missing a CONTEXT for RAISED error b) in verbose mode missing a CONTEXT for messages, for error too, and useless LOCATION is showed. LOCATION is absolutely useless for custom messages. so I removed a context filtering postgres=# select foo(); NOTICE: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE NOTICE: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE NOTICE: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE NOTICE: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE NOTICE: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE ERROR: *** CONTEXT: PL/pgSQL function foo() line 7 at RAISE Time: 3.842 ms postgres=# \set VERBOSITY verbose postgres=# select foo(); NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 ERROR: P0001: *** CONTEXT: PL/pgSQL function foo() line 7 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 Time: 0.761 ms We should not see a CONTEXT for DEFAULT verbosity and NOTICE level, after little bit change I got a satisfied output postgres=# select foo(); NOTICE: * NOTICE: * NOTICE: * NOTICE: * NOTICE: * ERROR: *** CONTEXT: PL/pgSQL function foo() line 7 at RAISE Time: 2.434 ms postgres=# \set VERBOSITY verbose postgres=# select foo(); NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 NOTICE: 0: * CONTEXT: PL/pgSQL function foo() line 5 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 ERROR: P0001: *** CONTEXT: PL/pgSQL function foo() line 7 at RAISE LOCATION: exec_stmt_raise, pl_exec.c:3046 Time: 0.594 ms Probably we can introduce a new level of verbosity, but I am thinking so this behave is reasonable. Everybody who use a VERBOSE level expect lot of balast and it show expected info (context of error) Can be this design good enough for you? Regards Pavel plpgsql_raise_context.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] docbook-xsl version for release builds
On Thu, Aug 22, 2013 at 8:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: On Fri, 2013-07-12 at 12:30 +0200, Magnus Hagander wrote: Given that, I'm fine with just bumping the version on borka to that version. Any objections? This was not done for 9.3rc1, AFAICT. Let's please do it for the next release builds. Um ... touching borka's toolchain post-rc1 sure sounds like a recipe for making ourselves look like idiots in a high-profile release. Wouldn't it be better to wait till after 9.3.0? I agree that doing it after the RC is a bad idea. We should probably try to do it more or less directly after the release though, so we (I..) don't forget it again... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
* Amit Kapila (amit.kapil...@gmail.com) wrote: This can resolve the problem of whether to read auto file rather cleanly, so the idea is: Enable/Disable reading of auto file - a. Have a new include in postresql.conf #include_auto_conf_filepostgresql.auto.conf as it is a special include, we can read this file relative to data directory. Enable/Disable Alter System command --- This can be achieved in 3 ways: a. Check before executing Alter System if include directive is disabled, then just issue a warning to user and proceed with command. b. Check before executing Alter System if include directive is disabled, then just issue an error and stop. It doesn't make sense for it to be a 'warning' with this- the parameter specifies the file to use. If you don't know what file to use, how you can possibly do anything but return an error? Note that I *like* that about this approach. There are a few other considerations with this- - What should the default be? (Still thinking 'off' myself) - What happens if the user specifies 'postgresql.conf'? I'm thinking we would disallow such insanity (as that's what it is, imv..) by having an identifier in the file that this is the PG auto conf file. - Should we have such an identifier in auto.conf to indicate that we created it, to prevent the user from setting it to something they shouldn't? - What's the bootstrap mode; iow, if a user enables the option but the file doesn't exist, what do we do? With this approach, I'd be inclined to say we simply create it and put the marker to indicate it's our file. - Should we allow it to be outside of the data dir? We could simply log an error and ignore the parameter if it's more than a simple filename. There are probably other considerations also.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PL/pgSQL, RAISE and error context
On Thu, Aug 22, 2013 at 2:08 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Probably we can introduce a new level of verbosity, but I am thinking so this behave is reasonable. Everybody who use a VERBOSE level expect lot of balast and it show expected info (context of error) Can be this design good enough for you? yep :-). merlin -- 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] pg_system_identifier()
On Thu, Aug 22, 2013 at 6:43 AM, Vik Fearing vik.fear...@dalibo.com wrote: After someone in IRC asked if there was an equivalent to MySQL's server_id, it was noted that we do have a system identifier but it's not very accessible. The attached patch implements a pg_system_identifier() function that exposes it. What's the use case? IIUC, PostgreSQL's system identifier is not equal to MySQL server-id. In PostgreSQL, in replication, the master and all the standbys must have the same system identifier. OTOH, in MySQL, they have the different server-ids. No? 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] pg_system_identifier()
On 08/22/2013 02:51 PM, Fujii Masao wrote: On Thu, Aug 22, 2013 at 6:43 AM, Vik Fearing vik.fear...@dalibo.com wrote: After someone in IRC asked if there was an equivalent to MySQL's server_id, it was noted that we do have a system identifier but it's not very accessible. The attached patch implements a pg_system_identifier() function that exposes it. What's the use case? It's information about the server that's only accessible through pg_controldata. I don't know if that's justification enough, which is why I didn't add it to the commitfest yet. IIUC, PostgreSQL's system identifier is not equal to MySQL server-id. In PostgreSQL, in replication, the master and all the standbys must have the same system identifier. OTOH, in MySQL, they have the different server-ids. No? I have zero experience with MySQL. -- Vik -- 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] StrategyGetBuffer optimization, take 2
On Tue, Aug 20, 2013 at 1:57 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-08-19 15:17:44 -0700, Jeff Janes wrote: On Wed, Aug 7, 2013 at 7:40 AM, Merlin Moncure mmonc...@gmail.com wrote: I agree; at least then it's not unambiguously better. if you (in effect) swap all contention on allocation from a lwlock to a spinlock it's not clear if you're improving things; it would have to be proven and I'm trying to keep things simple. Attached is a scaled down version of the patch that keeps the freelist lock but still removes the spinlock during the clock sweep. This still hits the major objectives of reducing the chance of scheduling out while holding the BufFreelistLock and mitigating the worst case impact of doing so if it does happen. An even more scaled down version would keep the current logic exactly as is except for replacing buffer lock in the clock sweep with a trylock (which is IMNSHO a no-brainer). Since usage_count is unsigned, are you sure that changing the tests from buf-usage_count == 0 to buf-usage_count = 0 accomplishes what you need it to? If usage_count gets decremented when it already zero, it will wrap around to 65,535, at least on some compilers some of the time, won't it? Overflow of *unsigned* variables is actually defined and will always wrap around. It's signed variables which don't have such a clear behaviour. Hm, well, even better would be to leave things as they are and try to guarantee that usage_count is updated via assignment vs increment; that way it would be impossible to wander out of bounds. I bet changing: i--; to i=(i-1); isn't going to do much against modern compilers. But what about assignment from a volatile temporary? volatile v = usage_count; if (v 0) v--; usage_count = v; something like that. Or maybe declaring usage_count as volatile might be enough? merlin -- 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] pg_system_identifier()
On Thu, Aug 22, 2013 at 9:53 PM, Vik Fearing vik.fear...@dalibo.com wrote: On 08/22/2013 02:51 PM, Fujii Masao wrote: On Thu, Aug 22, 2013 at 6:43 AM, Vik Fearing vik.fear...@dalibo.com wrote: After someone in IRC asked if there was an equivalent to MySQL's server_id, it was noted that we do have a system identifier but it's not very accessible. The attached patch implements a pg_system_identifier() function that exposes it. What's the use case? It's information about the server that's only accessible through pg_controldata. BTW, you can see the system identifier by executing IDENTIFY_SYSTEM command in replication connection as follows: 1. Change the server settings so that the server can accept the replication connection 2. Connect to the server in replication mode 3. Execute IDENTIFY_SYSTEM command in replication connection $ psql replication=1 =# IDENTIFY_SYSTEM; systemid | timeline | xlogpos -+--+--- 5914930202950905854 |1 | 0/183F720 (1 row) This is not good way for a user, though ;P I don't know if that's justification enough, which is why I didn't add it to the commitfest yet. You can add the patch to CF, and then hear the opinions from other people during CF. 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] proposal: lob conversion functionality
+1
badly need the large object and bytea convert function.
Once I have to use the ugly pg_read_file() to put some text to pg,I tried
to use large object but find it is useless without function to convert
large object to bytea.
Jov
blog: http:amutu.com/blog http://amutu.com/blog
2013/8/10 Pavel Stehule pavel.steh...@gmail.com
Hello
I had to enhance my older project, where XML documents are parsed and
created on server side - in PLpgSQL and PLPerl procedures. We would to
use a LO API for client server communication, but we have to
parse/serialize LO on server side.
I found so there are no simple API for working with LO from PL without
access to file system. I had to use a ugly hacks:
CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
RETURNS oid AS $$
DECLARE
_loid oid;
_substr bytea;
BEGIN
_loid := lo_creat(-1);
FOR i IN 0..length($1)/2048
LOOP
_substr := substring($1 FROM i * 2048 + 1 FOR 2048);
IF _substr '' THEN
INSERT INTO pg_largeobject(loid, pageno, data)
VALUES(_loid, i, _substr);
END IF;
END LOOP;
EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO ohs', _loid);
RETURN _loid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';
and
CREATE OR REPLACE FUNCTION fbuilder.attachment_to_xml(attachment oid)
RETURNS xml AS $$
DECLARE
b_cum bytea = '';
b bytea;
BEGIN
FOR b IN SELECT l.data
FROM pg_largeobject l
WHERE l.loid = attachment_to_xml.attachment
ORDER BY l.pageno
LOOP
b_cum := b_cum || b;
END LOOP;
IF NOT FOUND THEN
RETURN NULL;
ELSE
RETURN xmlelement(NAME attachment,
encode(b_cum, 'base64'));
END IF;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';
These functions can be simplified if we supports some functions like
encode, decode for LO
So my proposal is creating functions:
* lo_encode(loid oid) .. returns bytea
* lo_encode(loid oid, encoding text) .. returns text
* lo_make(loid oid, data bytea)
* lo_make(loid oid, data text, encoding text)
This can simplify all transformation between LO and VARLENA. Known
limit is 1G for varlena, but it is still relative enough high.
Notes. comments?
Regards
Pavel
--
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] Backup throttling
On 08/22/2013 01:39 PM, PostgreSQL - Hans-Jürgen Schönig wrote: what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest. I tend to agree. If anything we're likely to want the reverse - the ability to throttle WAL *generation* on the master so streaming can keep up. I see a lot of value in throttling base backup transfer rates. It's something PgBarman does per-tablespace using rsync at the moment, but it'd be nice if it available as an option possible over the streaming replication protocol via pg_basebackup so it was easier for people to use ad-hoc and without all the shell access wrangling. -- Craig Ringer 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] pg_system_identifier()
On 08/22/2013 03:21 PM, Fujii Masao wrote: I don't know if that's justification enough, which is why I didn't add it to the commitfest yet. You can add the patch to CF, and then hear the opinions from other people during CF. Added. -- Vik -- 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] Backup throttling
On 2013-08-22 07:39:41 +0200, PostgreSQL - Hans-Jürgen Schönig wrote: regarding the client side implementation: we have chosen this way because it is less invasive. i cannot see a reason to do this on the server side because we won't have 10 pg_basebackup-style tools making use of this feature anyway. The problem is that receiver side throttling over TCP doesn't always work all that nicely unless you have a low rate of transfer and/or very low latency . Quite often you will have OS buffers/the TCP Window being filled in bursts where the sender sends at max capacity and then a period where nothing happens on the sender. That's often not what you want when you need to throttle. Besides, I can see some value in e.g. normal streaming replication also being rate limited... what would be a reasonable scenario where limiting streaming would make sense? i cannot think of any to be honest. It's not an unreasonable goal if you have several streaming replicas with only some of them being synchronous replicas. Also, analytics replicas that need to catchup don't really need priority over local operations. Greetings, Andres Freund -- Andres Freund 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] PL/pgSQL, RAISE and error context
On 8/22/13 9:08 AM, Pavel Stehule wrote: Probably we can introduce a new level of verbosity, but I am thinking so this behave is reasonable. Everybody who use a VERBOSE level expect lot of balast and it show expected info (context of error) Can be this design good enough for you? I like the idea, but I think this should be a new verbosity level. With this patch you would have to go full VERBOSE just to debug PL/pgSQL code with NOTICEs and DEBUGs in it, and that output then becomes harder to parse with the useless C-code information. Regards, Marko Tiikkaja -- 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] pg_system_identifier()
On 2013-08-22 21:51:22 +0900, Fujii Masao wrote: On Thu, Aug 22, 2013 at 6:43 AM, Vik Fearing vik.fear...@dalibo.com wrote: After someone in IRC asked if there was an equivalent to MySQL's server_id, it was noted that we do have a system identifier but it's not very accessible. The attached patch implements a pg_system_identifier() function that exposes it. What's the use case? IIUC, PostgreSQL's system identifier is not equal to MySQL server-id. In PostgreSQL, in replication, the master and all the standbys must have the same system identifier. OTOH, in MySQL, they have the different server-ids. No? FWIW I've wished for that function repeatedly. Mostly just to make sure I am actually connected to the same network of replicas and not some other. It's also useful if you're providing support for a limited number of machines and you want some form of identifying a node. Greetings, Andres Freund -- Andres Freund 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] pg_system_identifier()
On Thu, Aug 22, 2013 at 6:42 AM, Andres Freund and...@2ndquadrant.comwrote: FWIW I've wished for that function repeatedly. Mostly just to make sure I am actually connected to the same network of replicas and not some other. It's also useful if you're providing support for a limited number of machines and you want some form of identifying a node. There's a hostname function at PGXN which serves some use-cases: http://pgxn.org/dist/hostname/
[HACKERS] Hstore: Query speedups with Gin index
Hey everyone, I'm looking for feedback on a contrib/hstore patch. We've been experiencing slow @ queries involving an hstore column that's covered by a Gin index. At the current postgresql git HEAD, the hstore - gin interface produces the following text items to be indexed: hstore: 'a'='1234', 'b'='test' Produces indexed text items: Ka, V1234, Kb, Vtest For the size of our production table (10s of millions of rows), I observed significant query speedups by changing the index strategy to the following: hstore: 'a'='1234', 'b'='test' Produces indexed text items: Ka, KaV1234, Kb, KbVtest The combined entry is used to support contains (@) queries, and the key only item is used to support key contains (?) queries. This change seems to help especially with hstore keys that have high cardinalities. Downsides of this change is that it requires an index rebuild, and the index will be larger in size. Patch attached. Any thoughts on this change? Thanks, Blake hstore_gin_speedup.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] pg_system_identifier()
All, Vik's feature would be useful for detecting an accidental split in a replication cluster. That is, it would be another tool for detecting if you've made a mistake and created two masters. So +1 from me. It will also be useful for me for sharding. Right now, I'm doing a hackish version of Vik's function, so I'd be glad to have it in core. However, given that the value is the same for all servers in a replication set, are we sure we want to call it system_identifier? Is there a better name? -- 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
Re: [HACKERS] pg_system_identifier()
bricklen brick...@gmail.com writes: On Thu, Aug 22, 2013 at 6:42 AM, Andres Freund and...@2ndquadrant.comwrote: FWIW I've wished for that function repeatedly. Mostly just to make sure I am actually connected to the same network of replicas and not some other. It's also useful if you're providing support for a limited number of machines and you want some form of identifying a node. There's a hostname function at PGXN which serves some use-cases: http://pgxn.org/dist/hostname/ I have a very vague recollection that we might've intentionally decided not to expose the system identifier at the SQL level. This could be all wet, but it'd be worth trolling the archives to see if there was such a conversation and if so whether the arguments still have merit. See also recent discussion about changing how the identifier is computed --- it'd be a good idea to fix that before we expose the identifier to users, if we decide to do so. 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] pg_system_identifier()
On 2013-08-22 08:45:38 -0700, Josh Berkus wrote: All, Vik's feature would be useful for detecting an accidental split in a replication cluster. That is, it would be another tool for detecting if you've made a mistake and created two masters. So +1 from me. It will also be useful for me for sharding. Right now, I'm doing a hackish version of Vik's function, so I'd be glad to have it in core. However, given that the value is the same for all servers in a replication set, are we sure we want to call it system_identifier? Is there a better name? Given it's been named that and visible via pg_controldata for years I am against introducing confusion by renaming it. Greetings, Andres Freund -- Andres Freund 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] pg_system_identifier()
Josh Berkus j...@agliodbs.com writes: Vik's feature would be useful for detecting an accidental split in a replication cluster. That is, it would be another tool for detecting if you've made a mistake and created two masters. So +1 from me. We don't change the ID when promoting a slave to master, do we? So how would this help for that? However, given that the value is the same for all servers in a replication set, are we sure we want to call it system_identifier? Is there a better name? I think there are definitely use cases for a system identifier of some kind, I'm just not sure that what we have got right now is especially useful to users. I'd rather see some thought go into what's needed before we expose the existing definition (and consume the valuable namespace of pg_system_identifier). 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: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thu, Aug 22, 2013 at 08:36:37AM -0400, Stephen Frost wrote: * Amit Kapila (amit.kapil...@gmail.com) wrote: This can resolve the problem of whether to read auto file rather cleanly, so the idea is: Enable/Disable reading of auto file - a. Have a new include in postresql.conf #include_auto_conf_filepostgresql.auto.conf as it is a special include, we can read this file relative to data directory. The big advantage of using 'include_auto_conf_file' and not simply 'include' is that we can issue an error from ALTER SYSTEM SET if that is not set. Enable/Disable Alter System command --- This can be achieved in 3 ways: a. Check before executing Alter System if include directive is disabled, then just issue a warning to user and proceed with command. b. Check before executing Alter System if include directive is disabled, then just issue an error and stop. It doesn't make sense for it to be a 'warning' with this- the parameter specifies the file to use. If you don't know what file to use, how you can possibly do anything but return an error? Agreed. No sense in allowing users to add things to the 'auto' file when the auto file is inactive. Note that I *like* that about this approach. There are a few other considerations with this- - What should the default be? (Still thinking 'off' myself) Probably, but we might need to wait until we have a final API for a decision on that. - What happens if the user specifies 'postgresql.conf'? I'm thinking we would disallow such insanity (as that's what it is, imv..) by having an identifier in the file that this is the PG auto conf file. I am thinking they can't include a value equal to 'config_file', which is normally postgresql.conf. I am not a big fan of looking for special text in files. This might be complex to check, though, because of path changes --- we might just disallow the basement from matching the basename of config_file. -- 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] pg_system_identifier()
Andres Freund and...@2ndquadrant.com writes: On 2013-08-22 08:45:38 -0700, Josh Berkus wrote: However, given that the value is the same for all servers in a replication set, are we sure we want to call it system_identifier? Is there a better name? Given it's been named that and visible via pg_controldata for years I am against introducing confusion by renaming it. I agree that if we have a function named pg_system_identifier(), it ought to return the same value that pg_controldata prints under that name. But that doesn't really answer any questions about how that value is obtained. I think the question on the table right now is whether we like the way that value behaves, in the context of a user-visible system ID. In particular, do we want to think about changing things so that (1) a slave has a different ID than the master, and/or (2) a slave's ID changes on promotion to master. I don't know the answers to these things --- but once we make it user visible it's going to be too late to change its behavior, so now's the time to consider. 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] pg_system_identifier()
On 2013-08-22 12:06:03 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2013-08-22 08:45:38 -0700, Josh Berkus wrote: However, given that the value is the same for all servers in a replication set, are we sure we want to call it system_identifier? Is there a better name? Given it's been named that and visible via pg_controldata for years I am against introducing confusion by renaming it. I agree that if we have a function named pg_system_identifier(), it ought to return the same value that pg_controldata prints under that name. But that doesn't really answer any questions about how that value is obtained. I think the question on the table right now is whether we like the way that value behaves, in the context of a user-visible system ID. In particular, do we want to think about changing things so tha (1) a slave has a different ID than the master, and/or We currently use the system identifier to know that we're replicating between the same/compatible systems (c.f. libpqwalreceiver.c: libpqrcv_identify_system()), so I don't think a change of definition like that is realistic. (2) a slave's ID changes on promotion to master. We also cannot change the identifier here, because then other standbys won't be able to follow the promotion because the identifier doesn't match anymore But essentially we already have something like that by the combination of system identifier and timeline id. Admittedly there's the weakness that the timelineid can increase the same on several machines in the cluster but that's a weakness we ought to fix sometime independent of this. So maybe the answer is to also expose the current timeline? An alternative would be to have a pg_controldata_values() SRF... Greetings, Andres Freund -- Andres Freund 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] pg_system_identifier()
On Thu, Aug 22, 2013 at 06:18:39PM +0200, Andres Freund wrote: But essentially we already have something like that by the combination of system identifier and timeline id. Admittedly there's the weakness that the timelineid can increase the same on several machines in the cluster but that's a weakness we ought to fix sometime independent of this. So maybe the answer is to also expose the current timeline? An alternative would be to have a pg_controldata_values() SRF... It seems the value is more of a _cluster_ identifier than a system identifier. We don't allow cross-major-version replication, so I am confused why we can't rename it in 9.4. -- 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] pg_system_identifier()
Andres Freund wrote: On 2013-08-22 12:06:03 -0400, Tom Lane wrote: I agree that if we have a function named pg_system_identifier(), it ought to return the same value that pg_controldata prints under that name. But that doesn't really answer any questions about how that value is obtained. I think the question on the table right now is whether we like the way that value behaves, in the context of a user-visible system ID. In particular, do we want to think about changing things so tha (1) a slave has a different ID than the master, and/or We currently use the system identifier to know that we're replicating between the same/compatible systems (c.f. libpqwalreceiver.c: libpqrcv_identify_system()), so I don't think a change of definition like that is realistic. We could split the value; make sure that the first, way, 96 bits are identical in master and slaves (and change the code to only compare those bits); but the last 32 bits are system specific and cannot appear twice in the same replica network. Also, perhaps we should reserve the last (say) 4 bits, so that means master and 0001 means standby (it changes on promotion), and the rest of the values are reserved for future use. Not necessarily that exact encoding, but hopefully you get my point. -- Á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] pg_system_identifier()
On 2013-08-22 12:18:41 -0400, Bruce Momjian wrote: On Thu, Aug 22, 2013 at 06:18:39PM +0200, Andres Freund wrote: But essentially we already have something like that by the combination of system identifier and timeline id. Admittedly there's the weakness that the timelineid can increase the same on several machines in the cluster but that's a weakness we ought to fix sometime independent of this. So maybe the answer is to also expose the current timeline? An alternative would be to have a pg_controldata_values() SRF... It seems the value is more of a _cluster_ identifier than a system identifier. We don't allow cross-major-version replication, so I am confused why we can't rename it in 9.4. For one, it would introduce confusion for the not inconsiderable number of people already knowing the variable. For another, it's exposed via the replication protocol's IDENTIFY SYSTEM. Greetings, Andres Freund -- Andres Freund 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] pg_system_identifier()
On 2013-08-22 12:20:19 -0400, Alvaro Herrera wrote: Andres Freund wrote: On 2013-08-22 12:06:03 -0400, Tom Lane wrote: I agree that if we have a function named pg_system_identifier(), it ought to return the same value that pg_controldata prints under that name. But that doesn't really answer any questions about how that value is obtained. I think the question on the table right now is whether we like the way that value behaves, in the context of a user-visible system ID. In particular, do we want to think about changing things so tha (1) a slave has a different ID than the master, and/or We currently use the system identifier to know that we're replicating between the same/compatible systems (c.f. libpqwalreceiver.c: libpqrcv_identify_system()), so I don't think a change of definition like that is realistic. We could split the value; make sure that the first, way, 96 bits are identical in master and slaves (and change the code to only compare those bits); but the last 32 bits are system specific and cannot appear twice in the same replica network. Also, perhaps we should reserve the last (say) 4 bits, so that means master and 0001 means standby (it changes on promotion), and the rest of the values are reserved for future use. Why? This seems to be making a simple thing into something way much more complex? Imo this proposal is about further exposing an already existing, already exposed (via pg_controldata, via replication protocol) variable, not more. It seems better to make sure the other datapoints are *also* exposed if they aren't yet. Some are: * port (SHOW port;) * standby/primary (SELECT pg_is_in_recovery();) Some are not easily: * system identifier (pg_controldata, replication protocol) * current timeline identifier (pg_controldata, replication protocol) * host identifier/hostname (which actually is hard) Greetings, Andres Freund -- Andres Freund 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] pg_system_identifier()
Andres Freund and...@2ndquadrant.com writes: On 2013-08-22 12:20:19 -0400, Alvaro Herrera wrote: We could split the value; make sure that the first, way, 96 bits are identical in master and slaves (and change the code to only compare those bits); but the last 32 bits are system specific and cannot appear twice in the same replica network. Also, perhaps we should reserve the last (say) 4 bits, so that means master and 0001 means standby (it changes on promotion), and the rest of the values are reserved for future use. Why? This seems to be making a simple thing into something way much more complex? Imo this proposal is about further exposing an already existing, already exposed (via pg_controldata, via replication protocol) variable, not more. It seems better to make sure the other datapoints are *also* exposed if they aren't yet. Agreed, this seems like overloading the identifier too much. Currently we consider it an 8-byte value with unspecified internal structure, and I think we should probably maintain that approach rather than defining APIs that assume it can be subdivided. For one thing, reducing the width of the unique part increases our risk of chance collisions. Do we have a reliable way of generating a unique identifier for each slave (independently of how that might be exposed)? 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] pg_system_identifier()
On 2013-08-22 12:37:36 -0400, Tom Lane wrote: Do we have a reliable way of generating a unique identifier for each slave (independently of how that might be exposed)? I'd like one, but it's not easy. The best I can think of is to mash together: * system_identifier * mac address of primary interface we're listening to * port * primary/standby in some pseudo-cryptographic manner. But that's less than convincing because it can change after simple configuration or hardware changes or even reboot :(. Really identifying a particular host seems hard in anything resembling a portable solution. Greetings, Andres Freund -- Andres Freund 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
[HACKERS] Allow child table to be missing nullable column from parent.
Currently a child table has to have all the columns the parent table has: create table foo1 (x integer, y text, z bool); create table foo2 (x integer, y text, q text); alter table foo2 inherit foo1 ; ERROR: child table is missing column z In theory it seems like this could be allowed as long as the parent column is nullable, then the column is just deemed to be all null in the child. You can emulate such a situation using views rather than inheritance: create view foo4 as (select * from foo1 union all select x,y, NULL::bool as z from foo2); I would have found the ability to do this via inheritance to be convenient a couple times, as a temporary measure while doing some refactoring. Or at least I think I would found it convenient, perhaps I would have actually just shot myself in the foot with it for reasons I don't understand yet. Is this something we don't want, or something we do want provided it can be implemented in a reasonable way? I have not mapped out how easy it would be to implement. I didn't find a discussion of this possibility in the archives, but it is not the easiest thing to search for. 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] Allow child table to be missing nullable column from parent.
Jeff Janes jeff.ja...@gmail.com writes: Currently a child table has to have all the columns the parent table has: create table foo1 (x integer, y text, z bool); create table foo2 (x integer, y text, q text); alter table foo2 inherit foo1 ; ERROR: child table is missing column z In theory it seems like this could be allowed as long as the parent column is nullable, then the column is just deemed to be all null in the child. TBH this doesn't seem like a very good idea. It suggests strongly that you messed up the inheritance, and even if it was intentional, what did you save by not having the child column? To point out just one practical problem, what is supposed to happen when you do UPDATE foo1 SET z = true WHERE ... with a condition that selects some rows in foo2? 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] PL/pgSQL, RAISE and error context
2013/8/22 Marko Tiikkaja ma...@joh.to On 8/22/13 9:08 AM, Pavel Stehule wrote: Probably we can introduce a new level of verbosity, but I am thinking so this behave is reasonable. Everybody who use a VERBOSE level expect lot of balast and it show expected info (context of error) Can be this design good enough for you? I like the idea, but I think this should be a new verbosity level. With this patch you would have to go full VERBOSE just to debug PL/pgSQL code with NOTICEs and DEBUGs in it, and that output then becomes harder to parse with the useless C-code information. word DEBUG is not good - it is used for Postgres debugging as log level Pavel Regards, Marko Tiikkaja
[HACKERS] Does larger i/o size make sense?
Hello, A few days before, I got a question as described in the subject line on a discussion with my colleague. In general, larger i/o size per system call gives us wider bandwidth on sequential read, than multiple system calls with smaller i/o size. Probably, people knows this heuristics. On the other hand, PostgreSQL always reads database files by BLCKSZ (= usually, 8KB) when referenced block was not on the shared buffer, however, it doesn't seem to me it can pull maximum performance of modern storage system. I'm not certain whether we had discussed this kind of ideas, or not. So, I'd like to see the reason why we stick on the fixed length i/o size, if similar ideas were rejected before. An idea that I'd like to investigate is, PostgreSQL allocates a set of continuous buffers to fit larger i/o size when block is referenced due to sequential scan, then invokes consolidated i/o request on the buffer. It probably make sense if we can expect upcoming block references shall be on the neighbor blocks; that is typical sequential read workload. Of course, we shall need to solve some complicated stuff, like prevention of fragmentation on shared buffers, or enhancement of internal APIs of storage manager to accept larger i/o size. Furthermore, it seems to me this idea has worth to investigate. Any comments please. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Does larger i/o size make sense?
On Thu, Aug 22, 2013 at 2:53 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hello, A few days before, I got a question as described in the subject line on a discussion with my colleague. In general, larger i/o size per system call gives us wider bandwidth on sequential read, than multiple system calls with smaller i/o size. Probably, people knows this heuristics. On the other hand, PostgreSQL always reads database files by BLCKSZ (= usually, 8KB) when referenced block was not on the shared buffer, however, it doesn't seem to me it can pull maximum performance of modern storage system. I'm not certain whether we had discussed this kind of ideas, or not. So, I'd like to see the reason why we stick on the fixed length i/o size, if similar ideas were rejected before. An idea that I'd like to investigate is, PostgreSQL allocates a set of continuous buffers to fit larger i/o size when block is referenced due to sequential scan, then invokes consolidated i/o request on the buffer. It probably make sense if we can expect upcoming block references shall be on the neighbor blocks; that is typical sequential read workload. Of course, we shall need to solve some complicated stuff, like prevention of fragmentation on shared buffers, or enhancement of internal APIs of storage manager to accept larger i/o size. Furthermore, it seems to me this idea has worth to investigate. Any comments please. Thanks, Isn't this dealt with at least in part by effective i/o concurrency and o/s readahead? merlin -- 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] Does larger i/o size make sense?
Merlin Moncure mmonc...@gmail.com writes: On Thu, Aug 22, 2013 at 2:53 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: An idea that I'd like to investigate is, PostgreSQL allocates a set of continuous buffers to fit larger i/o size when block is referenced due to sequential scan, then invokes consolidated i/o request on the buffer. Isn't this dealt with at least in part by effective i/o concurrency and o/s readahead? I should think so. It's very difficult to predict future block-access requirements for anything except a seqscan, and for that, we expect the OS will detect the access pattern and start reading ahead on its own. Another point here is that you could get some of the hoped-for benefit just by increasing BLCKSZ ... but nobody's ever demonstrated any compelling benefit from larger BLCKSZ (except on specialized workloads, if memory serves). The big-picture problem with work in this area is that no matter how you do it, any benefit is likely to be both platform- and workload-specific. So the prospects for getting a patch accepted aren't all that bright. 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] System catalog vacuum issues
On Wed, Aug 21, 2013 at 2:33 PM, Jim Nasby j...@nasby.net wrote: That is the problem. Exactly what Jim was writing about. Autovacuum have no chance to clean dead tuples at the end of the table because they are created too intensively. In the latest versions autovacuum behaves so it would stop working when a concurrent lock is acquired. As he suggested you should use vacuum in cron, however it might make other procecess, that create/drop tables to wait. Hrm... even if vacuum cost delay is set? I recall some talk about doing some minimal waiting for the lock, but thought that'd only happen if cost delay was 0. That really doesn't matter though. The whole idea of a cron'd vacuum is to *stop bloat from happening to begin with*. If there's no bloat to begin with, getting the lock to truncate will be a non-issue. Well, according to the pgstattuple log OP showed, free percent jumps from 1.82 to 70.07 in one minute, so I suppose an empty tail is inevitable anyway, so there should be locks to truncate by vacuum, if I understand things correct. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- 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] PL/pgSQL PERFORM with CTE
On 08/20/2013 05:48 AM, Merlin Moncure wrote: On Tue, Aug 20, 2013 at 7:44 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2013/8/20 Merlin Moncure mmonc...@gmail.com On Tue, Aug 20, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com I think the way forward is to remove the restriction such that data returning queries must be PERFORM'd I disagree, current rule has sense. Curious what your thinking is there. I have to agree with Merlin. I've always thought the PERFORM thing was a wart we'd get around to removing eventually. In what way is it a feature? -- 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
Re: [HACKERS] PL/pgSQL PERFORM with CTE
Josh Berkus j...@agliodbs.com writes: I have to agree with Merlin. I've always thought the PERFORM thing was a wart we'd get around to removing eventually. In what way is it a feature? I'd always assumed it was a PL/SQL compatibility thing, but a look in a PL/SQL reference doesn't turn up any such statement. So far as I can see, the situation in Oracle PL/SQL is: * SELECT must have an INTO clause; * there isn't any way to execute a SELECT and just discard the result. Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. 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] PL/pgSQL PERFORM with CTE
Tom, Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. For my own part, I have to correct forgetting to substitute PERORM for SELECT around 200 times each major PL/pgSQL project. So it would be user-friendly for it to go away. -- 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
Re: [HACKERS] PL/pgSQL PERFORM with CTE
2013/8/23 Josh Berkus j...@agliodbs.com Tom, Jan might remember more about his thought process here, but I'm thinking that he copied the SELECT-must-have-INTO rule and then chose to invent a new statement for the case of wanting to discard the result. I think you could make an argument for that being good from an oversight-detection standpoint, but it's not a really strong argument. Particularly in view of the difficulty we'd have in supporting WITH ... PERFORM ... nicely, it doesn't seem unreasonable to just allow SELECT-without-INTO. For my own part, I have to correct forgetting to substitute PERORM for SELECT around 200 times each major PL/pgSQL project. So it would be user-friendly for it to go away. But it can have a different reason. In T-SQL (Microsoft or Sybase) or MySQL a unbound query is used to direct transfer data to client side. There BEGIN SELECT 10; END; doesn't mean ignore result of query, but it means push result to client. And we doesn't support this functionality, so I prefer doesn't allow this syntax. Regards Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thu, Aug 22, 2013 at 6:06 PM, Stephen Frost sfr...@snowman.net wrote: * Amit Kapila (amit.kapil...@gmail.com) wrote: This can resolve the problem of whether to read auto file rather cleanly, so the idea is: Enable/Disable reading of auto file - a. Have a new include in postresql.conf #include_auto_conf_filepostgresql.auto.conf as it is a special include, we can read this file relative to data directory. Enable/Disable Alter System command --- This can be achieved in 3 ways: a. Check before executing Alter System if include directive is disabled, then just issue a warning to user and proceed with command. b. Check before executing Alter System if include directive is disabled, then just issue an error and stop. It doesn't make sense for it to be a 'warning' with this- the parameter specifies the file to use. If you don't know what file to use, how you can possibly do anything but return an error? As the file and location are fixed, we can go-ahead and write to it, but I think now we are deciding if someone disables include dir, then we can just disable Alter System, so it is better to return error in such situation. Note that I *like* that about this approach. There are a few other considerations with this- - What should the default be? (Still thinking 'off' myself) default 'off' is a safe option, as it won't allow users to make any change to parameter values until/unless they read from manual, how to use it and what can go wrong, on the other side it will be bit hassle for user to use this command. I think 'on' would be better. - What happens if the user specifies 'postgresql.conf'? I'm thinking we would disallow such insanity (as that's what it is, imv..) by having an identifier in the file that this is the PG auto conf file. I think we can detect by name and give error. - Should we have such an identifier in auto.conf to indicate that we created it, to prevent the user from setting it to something they shouldn't? I think if user plays with this file manually, it can lead to problems, that's why earlier we have decided to keep a note on top of file which will indicate, do not edit this file manually. I believe that should be sufficient. - What's the bootstrap mode; iow, if a user enables the option but the file doesn't exist, what do we do? With this approach, I'd be inclined to say we simply create it and put the marker to indicate it's our file. Alter System will create the file if doesn't exist. - Should we allow it to be outside of the data dir? We could simply log an error and ignore the parameter if it's more than a simple filename. This should be an error, the file location and name will be fixed. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER SYSTEM SET command to change postgresql.conf parameters (RE: [HACKERS] Proposal for Allow postgresql.conf values to be changed via SQL [review])
On Thu, Aug 22, 2013 at 9:34 PM, Bruce Momjian br...@momjian.us wrote: On Thu, Aug 22, 2013 at 08:36:37AM -0400, Stephen Frost wrote: * Amit Kapila (amit.kapil...@gmail.com) wrote: This can resolve the problem of whether to read auto file rather cleanly, so the idea is: Enable/Disable reading of auto file - a. Have a new include in postresql.conf #include_auto_conf_filepostgresql.auto.conf as it is a special include, we can read this file relative to data directory. The big advantage of using 'include_auto_conf_file' and not simply 'include' is that we can issue an error from ALTER SYSTEM SET if that is not set. Enable/Disable Alter System command --- This can be achieved in 3 ways: a. Check before executing Alter System if include directive is disabled, then just issue a warning to user and proceed with command. b. Check before executing Alter System if include directive is disabled, then just issue an error and stop. It doesn't make sense for it to be a 'warning' with this- the parameter specifies the file to use. If you don't know what file to use, how you can possibly do anything but return an error? Agreed. No sense in allowing users to add things to the 'auto' file when the auto file is inactive. Note that I *like* that about this approach. There are a few other considerations with this- - What should the default be? (Still thinking 'off' myself) Probably, but we might need to wait until we have a final API for a decision on that. - What happens if the user specifies 'postgresql.conf'? I'm thinking we would disallow such insanity (as that's what it is, imv..) by having an identifier in the file that this is the PG auto conf file. I am thinking they can't include a value equal to 'config_file', which is normally postgresql.conf. I am not a big fan of looking for special text in files. This might be complex to check, though, because of path changes --- we might just disallow the basement from matching the basename of config_file. Right, I also think that as file and location are fixed, so it can be detected with name. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- 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] Does larger i/o size make sense?
2013/8/23 Tom Lane t...@sss.pgh.pa.us: Merlin Moncure mmonc...@gmail.com writes: On Thu, Aug 22, 2013 at 2:53 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: An idea that I'd like to investigate is, PostgreSQL allocates a set of continuous buffers to fit larger i/o size when block is referenced due to sequential scan, then invokes consolidated i/o request on the buffer. Isn't this dealt with at least in part by effective i/o concurrency and o/s readahead? I should think so. It's very difficult to predict future block-access requirements for anything except a seqscan, and for that, we expect the OS will detect the access pattern and start reading ahead on its own. Another point here is that you could get some of the hoped-for benefit just by increasing BLCKSZ ... but nobody's ever demonstrated any compelling benefit from larger BLCKSZ (except on specialized workloads, if memory serves). The big-picture problem with work in this area is that no matter how you do it, any benefit is likely to be both platform- and workload-specific. So the prospects for getting a patch accepted aren't all that bright. Hmm. I might overlook effect of readahead on operating system level. Indeed, sequential scan has a workload that easily launches it, so smaller i/o size in application level will be hidden. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
