Re: [HACKERS] proposal: lob conversion functionality
Hello 2013/8/12 Pavel Stehule pavel.steh...@gmail.com: 2013/8/10 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I found so there are no simple API for working with LO from PL without access to file system. What? See lo_open(), loread(), lowrite(), etc. so simplified functionality should have a little bit different names than original LO API: /* * saving bytea to lo (with possibility enter a target loid) */ FUNCTION make_lo(src bytea, loid oid DEFAULT -1) RETURNS oid /* * loading from lo to bytea */ FUNCTION load_lo(loid) RETURNS bytea This API is simple and friendly to PL languages, and for more complex and specific work, there is still older LO server side API Regards Pavel yes, so there are three problems with these functions: a) probably (I didn't find) undocumented b) design with lo handler is little bit PL/pgSQL unfriendly. CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea) RETURNS oid AS $$ DECLARE loid oid; fd integer; bytes integer; BEGIN loid := lo_creat(-1); fd := lo_open(loid, 131072); bytes := lowrite(fd, $1); IF (bytes != LENGTH($1)) THEN RAISE EXCEPTION 'Not all data copied to blob'; END IF; PERFORM lo_close(fd); RETURN loid; END; $$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog'; CREATE OR REPLACE FUNCTION fbuilder.attachment_to_bytea(attachment oid) RETURNS bytea AS $$ DECLARE fdinteger; size integer; BEGIN fd := lo_open(attachment, 262144); size := lo_lseek(fd, 0, 2); PERFORM lo_lseek(fd, 0, 0); RETURN loread(fd, size); EXCEPTION WHEN undefined_object THEN PERFORM lo_close(fd); RETURN NULL; END; $$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog'; I had to use lot of magic constants, and getting size is not size too. I believe so almost all reading will be a complete read, and then it should be supported (maybe loread(fd, -1)). c) probably there is a bug - it doesn't expect handling errors postgres=# select fbuilder.attachment_to_xml(0); WARNING: Snapshot reference leak: Snapshot 0x978f6f0 still referenced attachment_to_xml ─── [null] (1 row) Time: 0.809 ms These functions can be simplified if we supports some functions like encode, decode for LO I do not see any good reason to tie encode/decode to LOs. It can save a one transformations - but it is not too important and can be easy done with current bytea API. 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] Modyfication Sort Merge Join Alghoritm
On Mon, Aug 12, 2013 at 08:16:59PM -0700, Jeff Janes wrote: On Mon, Aug 12, 2013 at 1:31 PM, tubadzin tubad...@o2.pl wrote: Hi users. I want to sure, that is no this implementation in Postgresql: For Sorte Merge Join Alghoritm: If the large input arrives sorted, g-join joins its pages with the buffer pool contents by strictly increasing join key values and the join output is also strictly sorted. What is a g-join? If you have a reference to that algorithm, it would help clarify what you are asking. There's a paper that includes the phrase g-join from 2011 here: http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/users/haerder/2011/JoinAndGrouping.pdf Is that it? 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] updatable/deletable terminology
On 13 August 2013 00:01, Peter Eisentraut pete...@gmx.net wrote: On Wed, 2013-08-07 at 21:19 -0400, Peter Eisentraut wrote: To make the view updatable, provide an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger. I think it's a bit strange to claim that adding a DELETE rule/trigger makes a view *updatable*. I suspect someone thought they would apply the term updatable in an SQL standard sense, but that seems backwards, because you get to these error conditions exactly because the view as defined was not Updatable(tm). After some consideration, I think the best fix here is to revert to the 9.2 wording You need an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger. That's how I had it in the patch I submitted, but perhaps it should be You need an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. to reflect the fact that the docs now recommend triggers ahead of rules. The addition of the term updatable was simply wrong here. In the docs we're using udpatable as a generic term meaning support for INSERT, UPDATE and DELETE, and we're not using the terms insertable or deletable. Also the error detail that immediately precedes this hint uses the term updatable. For example: CREATE VIEW one AS SELECT 1 AS val; INSERT INTO one VALUES (1); ERROR: cannot insert into view one DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To make the view insertable, provide an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger. so if there is a problem there, it's in the mix of terminology between the detail and the hint (updatable vs insertable). But at least in this case the hint is technically correct -- adding such a rule or trigger would make the view insertable. In the UPDATE and DELETE cases, following the hint's suggestion and adding just an UPDATE rule or trigger, or just a DELETE rule or trigger, wouldn't actually make the view updatable according to our current interpretation of the spec, which would require both. So on balance I think you're right, and it would be better to simply say: ERROR: cannot insert into view one DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: You need an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. 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] 9.3 release notes suggestions
Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the suggestions coming. One small suggestion: listitem para Allow link linkend=SQL-CREATEFOREIGNDATAWRAPPERforeign data wrappers/link to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei) /para /listitem This is the in-core functionality, so ISTM it would be better that this is stated in the section of Object Manipulation rather than in that of Additional Modules. Please find attached a patch. Thanks, Best regards, Etsuro Fujita REL9_3_BETA2_release.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
[HACKERS] How to create read-only view on 9.3
Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from PostgreSQL 9.2 to PostgreSQL 9.3 and if the databse has views, all views are updatable on the restored database. I want to make these views read-only like PostgreSQL9.2. How can I do this? Should I make access control on users ? (Sorry, I couldn't find any explanations on document.) regards, NTT Software Corporation Tomonari Katsumata -- 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] How to create read-only view on 9.3
On 13 August 2013 11:43, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from PostgreSQL 9.2 to PostgreSQL 9.3 and if the databse has views, all views are updatable on the restored database. I want to make these views read-only like PostgreSQL9.2. How can I do this? Should I make access control on users ? (Sorry, I couldn't find any explanations on document.) regards, NTT Software Corporation Tomonari Katsumata Could you show an example? Szymon
Re: [HACKERS] How to create read-only view on 9.3
Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -c create table tbl(i int) psql testdb -c insert into tbl values (generate_series(1,10)) psql testdb -c create view v as select * from tbl [9.3beta2] pg_dump -p port of 9.2.4 testdb /tmp/92dmp.dmp initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -f /tmp/92dmp.dmp After all, the view v became updatable view. --- $ psql testdb psql (9.3beta2) Type help for help. testdb=# select * from v; i 1 2 3 4 5 6 7 8 9 10 (10 rows) testdb=# insert into v values (11); INSERT 0 1 testdb=# select * from v; i 1 2 3 4 5 6 7 8 9 10 11 (11 rows) regards, NTT Software Corporation Tomonari Katsumata (2013/08/13 19:16), Szymon Guz wrote: On 13 August 2013 11:43, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from PostgreSQL 9.2 to PostgreSQL 9.3 and if the databse has views, all views are updatable on the restored database. I want to make these views read-only like PostgreSQL9.2. How can I do this? Should I make access control on users ? (Sorry, I couldn't find any explanations on document.) regards, NTT Software Corporation Tomonari Katsumata Could you show an example? Szymon -- 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] Regarding BGworkers
On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: That seems more mess than just keeping that function in postmaster.c. I agree with moving the other one. Please find attached a patch for that can be applied on master branch. do_start_bgworker is renamed to StartBackgroundWorker and moved to bgworker.c. At the same time, bgworker_quickdie, bgworker_die and bgworker_sigusr1_handler are moved to bgworker.c as they are used in do_start_bgworker. This particular formulation doesn't seem quite good to me, because we'd end up with a function called StartBackgroundWorker() and another called StartOneBackgroundWorker() doing related but different things. Maybe we can name things a bit better? -- 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] timeline signedness
On Wed, 2013-08-07 at 21:55 -0400, Peter Eisentraut wrote:
WAL timelines are unsigned 32-bit integers everywhere, except the
replication parser (replication/repl_gram.y and
replication/repl_scanner.l) treats them as signed 32-bit integers. It's
obviously a corner case, but it would be prudent to be correct about
this. It should be easy to fix in those grammar files.
Here is a patch to fix this.
diff --git a/src/backend/replication/repl_gram.y b/src/backend/replication/repl_gram.y
index bce18b8..f465530 100644
--- a/src/backend/replication/repl_gram.y
+++ b/src/backend/replication/repl_gram.y
@@ -56,7 +56,7 @@ Node *replication_parse_result;
%union {
char *str;
bool boolval;
- int32 intval;
+ uint32 uintval;
XLogRecPtrrecptr;
Node *node;
@@ -66,7 +66,7 @@ Node *replication_parse_result;
/* Non-keyword tokens */
%token str SCONST
-%token intval ICONST
+%token uintval UCONST
%token recptr RECPTR
/* Keyword tokens. */
@@ -85,7 +85,7 @@ Node *replication_parse_result;
%type node base_backup start_replication identify_system timeline_history
%type list base_backup_opt_list
%type defelt base_backup_opt
-%type intval opt_timeline
+%type uintval opt_timeline
%%
firstcmd: command opt_semicolon
@@ -175,14 +175,7 @@ start_replication:
;
opt_timeline:
- K_TIMELINE ICONST
-{
- if ($2 = 0)
- ereport(ERROR,
-(errcode(ERRCODE_SYNTAX_ERROR),
- (errmsg(invalid timeline %d, $2;
- $$ = $2;
-}
+ K_TIMELINE UCONST { $$ = $2; }
| /* nothing */ { $$ = 0; }
;
@@ -190,15 +183,10 @@ opt_timeline:
* TIMELINE_HISTORY %d
*/
timeline_history:
- K_TIMELINE_HISTORY ICONST
+ K_TIMELINE_HISTORY UCONST
{
TimeLineHistoryCmd *cmd;
- if ($2 = 0)
- ereport(ERROR,
-(errcode(ERRCODE_SYNTAX_ERROR),
- (errmsg(invalid timeline %d, $2;
-
cmd = makeNode(TimeLineHistoryCmd);
cmd-timeline = $2;
diff --git a/src/backend/replication/repl_scanner.l b/src/backend/replication/repl_scanner.l
index b4743e6..3d930f1 100644
--- a/src/backend/replication/repl_scanner.l
+++ b/src/backend/replication/repl_scanner.l
@@ -83,8 +83,8 @@ TIMELINE_HISTORY { return K_TIMELINE_HISTORY; }
;
{digit}+ {
- yylval.intval = pg_atoi(yytext, sizeof(int32), 0);
- return ICONST;
+ yylval.uintval = strtoul(yytext, NULL, 10);
+ return UCONST;
}
{hexdigit}+\/{hexdigit}+ {
--
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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -c create table tbl(i int) psql testdb -c insert into tbl values (generate_series(1,10)) psql testdb -c create view v as select * from tbl [9.3beta2] pg_dump -p port of 9.2.4 testdb /tmp/92dmp.dmp initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -f /tmp/92dmp.dmp After all, the view v became updatable view. I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql --single-transaction does not work as expected
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I want to report that psql --single-transaction does not work as one can expect after reading the help information for psql. psql --help says: - -1 (one), --single-transaction: execute command file as a single transaction If you run psql -1 sql_file.sql insteed of psql -1 -f sql_file.sql the single-transaction parameter will not work. Test case: == CREATE DATABASE test; \c test CREATE TABLE check_psql(id int, code text); \q # cat check_psql.sql INSERT INTO check_psql (id,code) VALUES (1,'code1'); INSERT INTO check_psql (id,code) VALUES (2,code2); # psql -1 test -f check_psql.sql INSERT 0 1 psql:check_psql.sql:2: ERROR: column code2 does not exist LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2); ^ # psql -1 test -c SELECT * FROM check_psql id | code - +-- (0 rows) # psql -1 test /tmp/check_psql.sql INSERT 0 1 ERROR: column code2 does not exist LINE 1: INSERT INTO check_psql (id,code) VALUES (2,code2); ^ #psql -1 test -c SELECT * FROM check_psql id | code - +--- 1 | code1 (1 row) The docs for psql at http://www.postgresql.org/docs/current/static/app-psql.html have the right information, -1 --single-transaction When psql executes a script with the -f option I think we should either update the psql --help information for - --single-transaction and say that this parameter only works together with -f or update the psql code so psql -1 file.sql also works. regards, - -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.14 (GNU/Linux) iEYEARECAAYFAlIKPQEACgkQBhuKQurGihQaGwCggvy+Fgiw1TlseZKM8oq4U/na cgQAnRN4sw9NHBajG57wL0P+08p6Nb3y =cNHB -END PGP SIGNATURE- -- 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] psql --single-transaction does not work as expected
On Tue, Aug 13, 2013 at 04:04:50PM +0200, Rafael Martinez wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I want to report that psql --single-transaction does not work as one can expect after reading the help information for psql. psql --help says: - -1 (one), --single-transaction: execute command file as a single transaction If you run psql -1 sql_file.sql insteed of psql -1 -f sql_file.sql the single-transaction parameter will not work. This will be fixed in PG 9.3; from the release notes: Allow the psql --single-transaction mode to work when reading from standard input (Fabien Coelho, Robert Haas) Previously this option only worked when reading from a 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] Foreground vacuum and buffer access strategy
On Mon, Aug 12, 2013 at 11:47 PM, Jeff Janes jeff.ja...@gmail.com wrote: Reviving a very old thread, because I've run into the issue again. On Tue, May 29, 2012 at 11:58 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, May 25, 2012 at 4:06 PM, Jeff Janes jeff.ja...@gmail.com wrote: If I invoke vacuum manually and do so with VacuumCostDelay == 0, I have basically declared my intentions to get this pain over with as fast as possible even if it might interfere with other processes. Under that condition, shouldn't it use BAS_BULKWRITE rather than BAS_VACUUM? The smaller ring size leads to a lot of synchronous WAL flushes which I think can slow the vacuum down a lot. Of course, an autovacuum of a really big table could run too slowly, too, even though it's not a foreground task. True. But almost by definition, an autovacuum is not trying to run inside a maintenance window. Would it be reasonable to upgrade the ring buffer size whenever VacuumCostDelay is zero, regardless of whether it is a manual or an auto vac? One thing I worry about is that many people may have changed autovacuum_vacuum_cost_delay from 20 directly to 0 or -1, and the accidental throttling on WAL syncs might be the only thing preventing their system from falling over each time autovac of a large table kicks in. I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to vacuum as fast as possible, and using a small ring buffer is not the way to do that. Now, tying that to VacuumCostDelay doesn't seem right, because setting that to 0 shouldn't suddenly change the behavior in other ways, as well. In general, the approach we've taken so far has been to try to hide the ring-buffer behavior from users and not make it tunable, but I'm not sure we can really get away with that in this case. Increasing the ring-buffer size has system-wide performance implications which could be very good (less bloat) or very bad (I/O starvation of concurrent activity). I don't think the system knows enough to guess which one will be better in any particular case. -- 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] Regarding BGworkers
Robert Haas escribió: On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: That seems more mess than just keeping that function in postmaster.c. I agree with moving the other one. Please find attached a patch for that can be applied on master branch. do_start_bgworker is renamed to StartBackgroundWorker and moved to bgworker.c. At the same time, bgworker_quickdie, bgworker_die and bgworker_sigusr1_handler are moved to bgworker.c as they are used in do_start_bgworker. This particular formulation doesn't seem quite good to me, because we'd end up with a function called StartBackgroundWorker() and another called StartOneBackgroundWorker() doing related but different things. Maybe we can name things a bit better? Yeah, we also have start_bgworker(). I agree that we should rename things so that they make as much sense as possible. In the current code, we have this: StartOneBackgroundWorker() in postmaster.c start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c With this patch we would have StartOneBackgroundWorker() in postmaster.c start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c I think we should rename to something like this: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c (I would also rename the functions in 9.3 to avoid inconsistency). Not wedded to those particular names, but (1) I would add the maybe prefix because that's what that function does; and (2) it seems to me that stuff in bgworker.c tend to use CamelCaseNaming and postmaster.c uses names_with_stuffed_underscores. (My convention tends to be that internal stuff uses underscores while exposed APIs use CamelCase. I probably fail to do it really consistently.) -- Á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] How to create read-only view on 9.3
On 08/13/2013 03:25 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -c create table tbl(i int) psql testdb -c insert into tbl values (generate_series(1,10)) psql testdb -c create view v as select * from tbl [9.3beta2] pg_dump -p port of 9.2.4 testdb /tmp/92dmp.dmp initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -f /tmp/92dmp.dmp After all, the view v became updatable view. I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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_dump and schema names
On Fri, Aug 9, 2013 at 02:15:31PM -0400, Bruce Momjian wrote: Well, it's certainly not immediately obvious why we shouldn't merge them. But I would have expected the function's header comment to now explain that the output is intentionally not schema-qualified and assumes that the search path is set for the object's schema if any. OK, done with the attached patch. The dump output is unchanged. Also, this seems like dead code as there is no test for INDEX in the if() block it exists in: /* * Pre-7.3 pg_dump would sometimes (not always) put a fmtId'd name * into te-tag for an index. This check is heuristic, so make its * scope as narrow as possible. */ if (AH-version K_VERS_1_7 te-tag[0] == '' te-tag[strlen(te-tag) - 1] == '' strcmp(type, INDEX) == 0) appendPQExpBuffer(buf, %s, te-tag); else Huh, yeah it is dead code, since _printTocEntry doesn't call this function for INDEX objects. And anyway I doubt anybody still cares about reading 7.2-era archive files. No objection to removing that. Removed. Patch applied. -- 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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? I thought so initially until I learned that views are expressly read-write per the standard; we're not changing behavior but implementing required functionality. So (at the least) I don't think it's fair to expect users who don't care about this point to have to go re-GRANT the appropriate privs -- so if you did that I think it would have to be an optional switch to pg_dump. That said, it's pretty much a given this is going to burn some people and given the potential security considerations maybe some action is warranted. Personally, I'd be satisfied with a dump time warning though or perhaps a strongly worded note in the documentation? merlin 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] How to create read-only view on 9.3
On 08/13/2013 12:09 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? I thought so initially until I learned that views are expressly read-write per the standard; we're not changing behavior but implementing required functionality. So (at the least) I don't think it's fair to expect users who don't care about this point to have to go re-GRANT the appropriate privs -- so if you did that I think it would have to be an optional switch to pg_dump. That said, it's pretty much a given this is going to burn some people and given the potential security considerations maybe some action is warranted. Personally, I'd be satisfied with a dump time warning though or perhaps a strongly worded note in the documentation? In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. cheers andrew -- 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] 9.3 release notes suggestions
On Tue, Aug 13, 2013 at 05:59:05PM +0900, Etsuro Fujita wrote: Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the suggestions coming. One small suggestion: listitem para Allow link linkend=SQL-CREATEFOREIGNDATAWRAPPERforeign data wrappers/link to support writes (inserts/updates/deletes) on foreign tables (KaiGai Kohei) /para /listitem This is the in-core functionality, so ISTM it would be better that this is stated in the section of Object Manipulation rather than in that of Additional Modules. Please find attached a patch. Agreed, done. -- 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] danger of stats_temp_directory = /dev/shm
On Thu, Apr 25, 2013 at 8:24 AM, Peter Eisentraut pete...@gmx.net wrote: On 4/25/13 12:09 AM, Tom Lane wrote: I think we need it fixed to reject any stats_temp_directory that is not postgres-owned with restrictive permissions. The problem here is not with what it deletes, it's with the insanely insecure configuration. Yeah, the requirements should be similar to what initdb requires for PGDATA and pg_xlog. Is this a blocker for 9.3? If it is a concern of not what is deleted but rather that someone can inject a poisoned stats file into the directory, does it need to be back-patched all the way, as that could be done before the split patch? 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] How to create read-only view on 9.3
All, In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. I think it would be better to supply a script which revoked write permissions from all views from all users, and distribute it with PostgreSQL. I think that's doable as a DO $$ script. If I wrote something like that, where would we drop it? The fact that it won't revoke permissions from superusers isn't a real problem, IMNSHO. If anyone is relying on superusers not being able to do something, they're in for pain in several other areas. -- 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] danger of stats_temp_directory = /dev/shm
On 08/13/2013 09:57 AM, Jeff Janes wrote: Is this a blocker for 9.3? Why would it be? This issue doesn't originate with 9.3. If it is a concern of not what is deleted but rather that someone can inject a poisoned stats file into the directory, does it need to be back-patched all the way, as that could be done before the split patch? I'd say it's a backpatch. We'll need to warn the heck out of users, though. -- 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] How to create read-only view on 9.3
On 08/13/2013 06:23 PM, Andrew Dunstan wrote: On 08/13/2013 12:09 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying on non-standard behavior essentially. I admit this is a pretty big pain (and I'm a real stickler for backwards compatibility) but it's pretty hard to argue with the standard. Workarounds are to revoke various privileges. Perhaps pg_dump from 9.3 should add REVOKE ALL ...; GRANT SELECT ...; when dumping views from older postgreSQL versions ? I thought so initially until I learned that views are expressly read-write per the standard; we're not changing behavior but implementing required functionality. In this case implementing required functionality does change behaviour in quite substantial way. If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. You also probably did not GRANT only SELECT to your views as this was the default anyway, So (at the least) I don't think it's fair to expect users who don't care about this point to have to go re-GRANT the appropriate privs -- so if you did that I think it would have to be an optional switch to pg_dump. That said, it's pretty much a given this is going to burn some people and given the potential security considerations maybe some action is warranted. Personally, I'd be satisfied with a dump time warning though or perhaps a strongly worded note in the documentation? In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. Superuser can easily disable or drop the trigger as well. cheers andrew -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- 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] How to create read-only view on 9.3
* Hannu Krosing (ha...@2ndquadrant.com) wrote: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. You also probably did not GRANT only SELECT to your views as this was the default anyway, I'm not really convinced that we should be catering to this argument of well, I knew it was gonna end up being read-only anyway, so I just GRANT'd ALL- consider that rules can make view writable, even in existing releases. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] How to create read-only view on 9.3
Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. Unless you'd explicitly granted those users insert/update/delete privilege on the view, they wouldn't suddenly be able to do something new in 9.3, because no such privileges are granted by default. If you had granted such privileges, you don't have much of a leg to stand on for complaining that now they can do it. I think this whole thread is nonsense. We expended a good deal of sweat in 9.3 to add a feature that's *required by SQL standard*, and now people are acting like we should turn it off. I do not believe that there are many users for which this will be a problem; and we shouldn't let one complaint drive us to do something silly. In fact, I'm not sure there are *any* users for which this is a problem. AFAICS there are two cases: 1. The view in question is owned by you. Then you have insert etc privileges on it by default, and so 9.3 will let you insert into it by default. But the view grants you no capability that you didn't have anyway, just by inserting directly into the underlying table. 2. The view in question is not owned by you. Then you don't have insert (or any other) privilege on it by default. There's no security hole here; if someone can do something that they couldn't do before, it's because you explicitly granted them privileges to do so. I don't think you have a lot of room to complain if those privileges now do what the SQL standard says they should do. 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] How to create read-only view on 9.3
On 08/13/2013 01:33 PM, Hannu Krosing wrote: In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. Superuser can easily disable or drop the trigger as well. That's true, but it requires positive action to do so. Thus the trigger can give you some protection in cases of stupidity, if not cases of malice. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Release schedule for PG 9.3
It seems that the volume of 9.3-specific bug reports is tailing off. After some discussion, the core committee has agreed to produce a 9.3rc1 version next week (that is, wrap Monday the 19th for public announcement Thursday the 22nd). If no showstopper bugs are reported in the next couple of weeks, we'll wrap 9.3.0 on Monday Sept 2 for public announcement Monday Sept 9. (This scheduling leaves a couple extra days for package-making because of the proximity to the Labor Day holiday.) 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] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's no security hole here; if someone can do something that they couldn't do before, it's because you explicitly granted them privileges to do so. This point is completely bogus. Very, very few applications I've run across in the entirety of my career use database enforced security at all; it's generally done at the application level with the application role as owner (or perhaps even superuser). You can call people names or whatever for doing that but the point is it's common usage and people *will* be affected. I don't think you have a lot of room to complain if those privileges now do what the SQL standard says they should do. This point is completely correct and makes the previous argument moot. This is not a 'security hole' but an 'obfuscation hole' so automatic correction is not warranted. With the options on the table, I'd prefer doing nothing or perhaps more strongly worded note in the docs and possibly the release notes with a slight preference on doing nothing. 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] Review: UNNEST (and other functions) WITH ORDINALITY
On Tue, Aug 6, 2013 at 6:10 PM, Greg Stark st...@mit.edu wrote: The only other case I could come up with in my regression tests is pretty esoteric: CREATE COLLATION nulls (locale='C'); ALTER OPERATOR CLASS text_ops USING btree RENAME TO first; CREATE TABLE nulls_first(t text); CREATE INDEX nulls_first_i ON nulls_first(t COLLATE nulls first); I'm not 100% sure there aren't other cases where this can occur though. Blech. Well, that's why we need to stop hacking the lexer before we shoot a hole through our foot that's too large to ignore. But it's not this patch's job to fix that problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] How to create read-only view on 9.3
On Tue, Aug 13, 2013 at 10:24:32AM -0700, Josh Berkus wrote:
All,
In any case, using permissions is a somewhat leaky bandaid, since
superusers have overriding access privileges anyway. A better way to do
what the OP wants might be to have a view trigger that raises an exception.
I think it would be better to supply a script which revoked write
permissions from all views from all users, and distribute it with
PostgreSQL. I think that's doable as a DO $$ script.
If I wrote something like that, where would we drop it?
The fact that it won't revoke permissions from superusers isn't a real
problem, IMNSHO. If anyone is relying on superusers not being able to
do something, they're in for pain in several other areas.
Something like this?
DO LANGUAGE plpgsql
$$
DECLARE v TEXT;
BEGIN
FOR v IN SELECT pg_catalog.quote_ident(schemaname) || '.' ||
pg_catalog.quote_ident(viewname)
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
EXECUTE 'REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON ' || v || ' FROM
PUBLIC';
END LOOP;
END;
$$;
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] Foreground vacuum and buffer access strategy
On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas robertmh...@gmail.com wrote: I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to vacuum as fast as possible, and using a small ring buffer is not the way to do that. I'm not convinced using a ring buffer is necessarily that bad even if you want to vacuum as fast as possible. The reason we use a small ring buffer is to avoid poisoning the entire cache with vacuum pages, not to throttle the speed of vacuum by introducing synchronous wal flushes. I think we should increase the size of the ring buffer if we hit a synchronous wal buffer flush and there is less than some amount of wal pending. That amount is the relevant thing people might want to limit to avoid slowing down other transaction commits. The walwriter might even provide a relevant knob already for how much wal should be the maximum pending. -- greg -- 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] How to create read-only view on 9.3
On 08/13/2013 11:18 AM, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. Unless you'd explicitly granted those users insert/update/delete privilege on the view, they wouldn't suddenly be able to do something new in 9.3, because no such privileges are granted by default. If you had granted such privileges, you don't have much of a leg to stand on for complaining that now they can do it. Ah, ok. I hadn't gotten to the testing phase yet. I think we should have a script available for revoking all write privs on all views and link it from somewhere (the release notes?), but I don't see any need to change anything in the release. -- 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] Review: UNNEST (and other functions) WITH ORDINALITY
On Tue, Aug 13, 2013 at 8:20 PM, Robert Haas robertmh...@gmail.com wrote: Blech. Well, that's why we need to stop hacking the lexer before we shoot a hole through our foot that's too large to ignore. But it's not this patch's job to fix that problem. Hm. I thought it was. However it turns out the NULLS FIRST and the WITH* problems are not exactly analogous. Because NULLS and FIRST are both unreserved keywords whereas WITH is a reserved keyword the problems are really different. Whereas WITH can be fixed by going through all the places in the grammar where WITH appears, NULLS FIRST really can't be fixed without reserving NULLS. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO request: multi-dimensional arrays in PL/pythonU
All, Currently PL/python has 1 dimension hardcoded for returning arrays: create or replace function nparr () returns float[][] language plpythonu as $f$ from numpy import array x = ((1.0,2.0),(3.0,4.0),(5.0,6.0),) return x $f$; josh=# select nparr() ; ERROR: invalid input syntax for type double precision: (1.0, 2.0) CONTEXT: while creating return value PL/Python function nparr josh=# I'd like to add the following TODO to the TODO list: PL/Python [] Allow functions to return multi-dimensional arrays from lists or numpy arrays. -- 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] [GENERAL] Possible bug with row_to_json
I wrote: Jack Christensen j...@jackchristensen.com writes: It ignored the rename. I looked into this and found that the culprit is the optimization that skips ExecProject() if a scan plan node is not doing any useful projection. Further poking at this issue shows that there are related behaviors that aren't fixed by my proposed patch. The original complaint can be replicated in the regression database like this: select row_to_json(i8) from (select q1 as a, q2 from int8_tbl offset 0) i8; where we'd expect row_to_json to emit column names a/q2 but we actually get q1/q2. But consider this variant: select row_to_json(i8) from (select q1,q2 from int8_tbl offset 0) i8(x,y); Arguably, this should show column names x/y but what you get is q1/q2, even with my patch. Related cases include select row_to_json(v) from (values(1,2) limit 1) v(x,y); select row_to_json((select i8 from int8_tbl i8(x,y) limit 1)); In the first two of those, the planner isn't bothering to install the column aliases into the plan's target lists. While we could fix that, it wouldn't help the last case, where the whole-row Var for int8_tbl is evaluated at scan level; the code for that is looking at the relation's tuple descriptor not the scan node's result descriptor. I'm not even sure what a clean fix for that case would look like. Since this behavior can also be demonstrated in 9.2 (and maybe further back using xml features?), I don't think we should consider it a blocker bug for 9.3. I'm planning to set it on the back burner for the moment and go worry about the planner's LATERAL bugs. 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] [GENERAL] Possible bug with row_to_json
On Tue, Aug 13, 2013 at 4:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Since this behavior can also be demonstrated in 9.2 (and maybe further back using xml features?), I don't think we should consider it a blocker bug for 9.3. I'm planning to set it on the back burner for the moment and go worry about the planner's LATERAL bugs. +1 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] Regarding BGworkers
On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c This formulation is fine, thanks. Instead of maybe_start_bgworker, what about start_bgworker_if_necessary? -- 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] UNNEST with multiple args, and TABLE with multiple funcs
On 08/13/2013 06:54 AM, Andrew Gierth wrote: Summary: This patch implements a method for expanding multiple SRFs in parallel that does not have the surprising LCM behaviour of SRFs-in-select-list. (Functions returning fewer rows are padded with nulls instead.) BTW, if anyone is unsure of the use-case for this, I have some uses for it: 1. denormalized data stored in same-length arrays (usually for compression reasons) 2. use with PL/Python-Numpy and PL/R functions which return multiple arrays or 2D arrays. In other words, I have *lots* of uses for this functionality, and I think the analytics crowd will like it. Which means that I need to get on testing it, of course ... -- 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] UNNEST with multiple args, and TABLE with multiple funcs
On 08/14/2013 08:22 AM, Josh Berkus wrote: On 08/13/2013 06:54 AM, Andrew Gierth wrote: Summary: This patch implements a method for expanding multiple SRFs in parallel that does not have the surprising LCM behaviour of SRFs-in-select-list. (Functions returning fewer rows are padded with nulls instead.) BTW, if anyone is unsure of the use-case for this, I have some uses for it: 1. denormalized data stored in same-length arrays (usually for compression reasons) 2. use with PL/Python-Numpy and PL/R functions which return multiple arrays or 2D arrays. In other words, I have *lots* of uses for this functionality, and I think the analytics crowd will like it. Which means that I need to get on testing it, of course ... Similarly, I see uses for this come up a lot, and usually have to work around it with ugly invocations of multiple SRFs in the SELECT list in a subquery. I was thinking of implementing multi-argument unnest directly with `any` parameters if I could get it to work, but hadn't started on it yet. This looks like a really clever approach and it handles multiple spec-compliance items. I'll grab the patch and try it out. -- 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] Regarding BGworkers
On Tue, Aug 13, 2013 at 8:07 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c This formulation is fine, thanks. Instead of maybe_start_bgworker, what about start_bgworker_if_necessary? I think Alvaro's suggestion is better. It's shorter, and makes clear that at most one will be started. -- 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
CREATE TRANSFORM syntax (was Re: [HACKERS] [PATCH] Add transforms feature)
On Mon, 2013-07-08 at 23:00 -0700, Hitoshi Harada wrote: On Sun, Jul 7, 2013 at 12:06 PM, Peter Eisentraut pete...@gmx.net wrote: On Thu, 2013-07-04 at 02:18 -0700, Hitoshi Harada wrote: as someone suggested in the previous thread, it might be a variant of CAST. CREATE CAST (hstore AS plpython2u) ? Or CREATE LANGUAGE TRANSFORM might sound better. In either case, I think we are missing the discussion on the standard overloading. LANGUAGE isn't a concept limited to the server side in the SQL standard. I could go with something like CREATE SERVER TRANSFORM. I like it better than the current one. I had started to work on making this adjustment, but found the result very ugly. It also created a confusing association with CREATE SERVER, which is something different altogether. My next best idea is CREATE TRANSFORM FOR hstore SERVER LANGUAGE plperl, which preserves the overall idea but still distinguishes server from client languages. Comments? -- 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] How to create read-only view on 9.3
Hi, (2013/08/14 5:24), Josh Berkus wrote: On 08/13/2013 11:18 AM, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. Unless you'd explicitly granted those users insert/update/delete privilege on the view, they wouldn't suddenly be able to do something new in 9.3, because no such privileges are granted by default. If you had granted such privileges, you don't have much of a leg to stand on for complaining that now they can do it. Ah, ok. I hadn't gotten to the testing phase yet. I think we should have a script available for revoking all write privs on all views and link it from somewhere (the release notes?), but I don't see any need to change anything in the release. Yes, I was not thinking about changing current 9.3 behavior. So I think it's enough to know the impact and how to avoid that on the release notes. thanks a lot! regards, --- NTT Software Corporation Tomonari Katsumata -- 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] danger of stats_temp_directory = /dev/shm
On Tuesday, August 13, 2013, Josh Berkus wrote: On 08/13/2013 09:57 AM, Jeff Janes wrote: Is this a blocker for 9.3? Why would it be? This issue doesn't originate with 9.3. Before 9.3, it would delete one specific file from a potentially shared directory. In 9.3 it deletes the entire contents of a potentially shared directory. That is a massive expansion in the surface area for unintentional deletion. If we will disallow using shared directories before the time 9.3 is released, that would fix it one way, but I don't know if that is the plan or not. Cheers, Jeff
Re: [HACKERS] Foreground vacuum and buffer access strategy
On Wed, Aug 14, 2013 at 1:41 AM, Greg Stark st...@mit.edu wrote: On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas robertmh...@gmail.com wrote: I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to vacuum as fast as possible, and using a small ring buffer is not the way to do that. I'm not convinced using a ring buffer is necessarily that bad even if you want to vacuum as fast as possible. The reason we use a small ring buffer is to avoid poisoning the entire cache with vacuum pages, not to throttle the speed of vacuum by introducing synchronous wal flushes. I think we should increase the size of the ring buffer if we hit a synchronous wal buffer flush and there is less than some amount of wal pending. It will be better if the decision to increase ring buffer also consider other activity, otherwise it can lead to more I/O due to buffer replacements by backend. I am not sure currently there is any way to check that, but if we maintain buffers on free list, then it can be used to check the current activity (if there are enough buffers on free list, then ring size can be increased as it is an indication that the system is relatively less busy). That amount is the relevant thing people might want to limit to avoid slowing down other transaction commits. The walwriter might even provide a relevant knob already for how much wal should be the maximum pending. 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
[HACKERS] pgstat_reset_remove_files ignores its argument
in 9.3 and 9.4, pgstat_reset_remove_files uses the global variable
pgstat_stat_directory rather than the argument it is passed, directory.
On crash recovery, this means the tmp directory gets cleared twice and the
permanent pg_stat doesn't get cleared at all.
It seems like the obvious one line change would fix it, but I haven't
tested it because I don't know how to cause a crash without pg_stat already
being empty.
pgstat_reset_remove_files(const char *directory)
{
DIR*dir;
struct dirent *entry;
charfname[MAXPGPATH];
dir = AllocateDir(pgstat_stat_directory);
Cheers,
Jeff
Re: [HACKERS] System catalog vacuum issues
I used to use VACUUM FULL periodically to resolve the issue, but the
problem arises again in 2-3 months.
Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07.
dcdb=# select date, relpages, reltuples, table_len, tuple_count,
tuple_percent, dead_tuple_count, dead_tuple_len, free_space,
free_percent, autovacuum_count from public.table_statistics where
relname = 'pg_attribute' order by date;
date| relpages | reltuples | table_len | tuple_count |
tuple_percent | dead_tuple_count | dead_tuple_len | free_space |
free_percent | autovacuum_count
+--+---+---+-+---+--+++--+--
2013-08-08 |39029 |109096 | 319725568 | 37950 |
1.66 |52540 |7355600 | 296440048 |92.72
| 6359
2013-08-09 |12382 | 95848 | 101433344 | 38232 |
5.28 |57443 |8042020 | 83862864 |82.68
| 6711
2013-08-10 |11365 |105073 | 93102080 | 37789 |
5.68 |65599 |9183860 | 74483104 | 80
| 7002
2013-08-12 | 9447 | 95289 | 77389824 | 37811 |
6.84 |57154 |8001560 | 60479736 |78.15
| 7161
2013-08-13 |47841 | 82877 | 391913472 | 38536 |
1.38 |30461 |4264540 | 369093756 |94.18
| 7347
2013-08-14 |70265 |104926 | 575610880 | 38838 |
0.94 |34649 |4850860 | 546449480 |94.93
| 7398
(6 rows)
Autovacuum is running on this table, however it keeps growing.
On 08/06/2013 09:35 PM, Tom Lane wrote:
Vlad Arkhipov arhi...@dc.baikal.ru writes:
On 08/06/2013 04:26 PM, Sergey Konoplev wrote:
What pgstattuple shows on this table?
dcdb=# select * from pgstattuple('pg_catalog.pg_attribute');
table_len | tuple_count | tuple_len | tuple_percent |
dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space |
free_percent
+-+---+---+--++++--
6363938816 | 48786 | 6830040 | 0.11 | 1459439 |
204321460 | 3.21 | 5939017376 | 93.32
(1 row)
So the problem isn't so much that you have lots of dead tuples, it's that
the file is full of free space. I suspect the key issue is that
autovacuum is unable to truncate the file because of too many concurrent
accesses. There was a fix in 9.2.3 that was meant to ameliorate that
problem, but maybe that's not getting the job done for you. Or maybe the
bloat we're looking at is left over from when you were running earlier
9.2.x releases; in which case a one-time VACUUM FULL should fix it.
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
