[HACKERS] SELECT FOR UPDATE regression in 9.5

2016-09-06 Thread Marti Raudsepp
ct; UPDATE testcase SET balance = balance - 100 WHERE id=1; ROLLBACK TO SAVEPOINT subxact; -- "division by zero" shouldn't occur because I never deleted any rows SELECT 1/count(*) from ( SELECT * FROM testcase WHERE id=1 FOR UPDATE )x; ROLLBACK; Regards, Marti Raudsepp

Re: [HACKERS] [PATCH] Skip ALTER x SET SCHEMA if the schema didn't change

2015-11-16 Thread Marti Raudsepp
Haribabu Kommi > <kommi.harib...@gmail.com> wrote: > > On Tue, Sep 29, 2015 at 12:17 AM, Marti Raudsepp <ma...@juffo.org> > wrote: > >> Hi list > >> > >> The attached patch changes the behavior of multiple ALTER x SET SCHEMA > >

Re: [HACKERS] BRIN indexes for MAX, MIN, ORDER BY?

2015-09-28 Thread Marti Raudsepp
Hi Gavin Note that Alexander Korotkov already started work in 2013 on a somewhat similar feature called partial sort: http://www.postgresql.org/message-id/CAPpHfdscOX5an71nHd8WSUH6GNOCf=V7wgDaTXdDd9=gon-...@mail.gmail.com In particular, see the 2nd patch for KNN sort -- it uses known bounding

[HACKERS] [PATCH] Skip ALTER x SET SCHEMA if the schema didn't change

2015-09-28 Thread Marti Raudsepp
Hi list The attached patch changes the behavior of multiple ALTER x SET SCHEMA commands, to skip, rather than fail, when the old and new schema is the same. The advantage is that it's now easier to write DDL scripts that are indempotent. This already matches the behavior of ALTER EXTENSION SET

Re: [HACKERS] Less than ideal error reporting in pg_stat_statements

2015-09-25 Thread Marti Raudsepp
On Wed, Sep 23, 2015 at 3:01 AM, Peter Geoghegan wrote: > I think that the real problem here is that garbage collection needs to > deal with OOM more appropriately. +1 I've also been seeing lots of log messages saying "LOG: out of memory" on a server that's hosting development

Re: [HACKERS] [PATCH] pg_upgrade fails when postgres/template1 isn't in default tablespace

2015-07-22 Thread Marti Raudsepp
On Wed, Jul 22, 2015 at 5:00 PM, Robert Haas robertmh...@gmail.com wrote: +1. I would recommend adding it to the CF *immediately* to have it get attention. The CF app is basically our patch tracker. Thanks, I have done so now: https://commitfest.postgresql.org/6/313/ Regards, Marti --

Re: [HACKERS] [PATCH] pg_upgrade fails when postgres/template1 isn't in default tablespace

2015-07-20 Thread Marti Raudsepp
On Mon, Jun 22, 2015 at 9:20 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jun 19, 2015 at 12:10 PM, Marti Raudsepp ma...@juffo.org wrote: One of my databases failed to upgrade successfully and produced this error in the copying phase: error while copying relation

[HACKERS] [PATCH] pg_upgrade fails when postgres/template1 isn't in default tablespace

2015-06-19 Thread Marti Raudsepp
? Regards, Marti Raudsepp 0001-Fix-pg_upgrade-when-postgres-template1-aren-t-in-def.patch Description: binary/octet-stream -- 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] alter user/role CURRENT_USER

2014-10-27 Thread Marti Raudsepp
On Fri, Oct 24, 2014 at 11:29 AM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: - 0001-ALTER-ROLE-CURRENT_USER_v2.patch - the patch. +RoleId:CURRENT_USER{ $$ = current_user;} + | USER { $$ =

Re: [HACKERS] [PATCH] Simplify EXISTS subqueries containing LIMIT

2014-10-26 Thread Marti Raudsepp
to merge etc? Or is this not a problem? Patch attached with all above changes. Regards, Marti From 28543dda9febe8d8b5fc91060a4323c08f3c4a8a Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Wed, 1 Oct 2014 02:17:21 +0300 Subject: [PATCH] Simplify EXISTS subqueries containing LIMIT

Re: [HACKERS] btree_gin and ranges

2014-10-22 Thread Marti Raudsepp
Hi On Wed, Oct 22, 2014 at 1:55 PM, Teodor Sigaev teo...@sigaev.ru wrote: With patch it's possible to rewrite query with ranges SELECT * FROM test_int4 WHERE i @ '[-1, 1]'::int4range and GIN index will support this query with single scan from -1 to 1. Shouldn't this be implemented in a more

Re: [HACKERS] Patch: Add launchd Support

2014-10-21 Thread Marti Raudsepp
On Tue, Oct 21, 2014 at 3:53 AM, Wim Lewis w...@omnigroup.com wrote: I think the idea of OnDemand is for launchd items to act a bit like inetd does: launchd creates the listening socket (or mach port or file-change notification) on the port specified in the plist, and only starts the process

Re: [HACKERS] [PATCH] Simplify EXISTS subqueries containing LIMIT

2014-10-21 Thread Marti Raudsepp
Hi Thanks for taking a look. On Sun, Oct 19, 2014 at 1:22 PM, David Rowley dgrowle...@gmail.com wrote: the argument for this would have been much stronger if anti join support had just been added last week. It's been quite a few years now and the argument for this must be getting weaker with

Re: [HACKERS] Support UPDATE table SET(*)=...

2014-10-17 Thread Marti Raudsepp
On Oct 17, 2014 6:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: A more realistic objection goes like this: create table foo(f int, g int); update foo x set x = (1,2); -- works alter table foo add column x int; update foo x set x = (1,2,3); -- no longer works It's not a real good thing if a

Re: [HACKERS] Support UPDATE table SET(*)=...

2014-10-15 Thread Marti Raudsepp
Hi On Wed, Oct 15, 2014 at 11:02 AM, Atri Sharma atri.j...@gmail.com wrote: Please find attached a patch which implements support for UPDATE table1 SET(*)=... I presume you haven't read Tom Lane's proposal and discussion about multiple column assignment in UPDATE:

[HACKERS] [PATCH] Cleanup: unify checks for catalog modification

2014-10-14 Thread Marti Raudsepp
Hi list, I happened to notice that there are no less than 14 places in the code that check whether a relation is a system catalog and throwing the error permission denied: foo is a system catalog The attached patch factors all of those into a single ForbidSystemTableMods() function. Is this

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-10-09 Thread Marti Raudsepp
On Thu, Oct 9, 2014 at 11:11 AM, Peter Geoghegan p...@heroku.com wrote: On Thu, Oct 9, 2014 at 12:38 AM, Simon Riggs si...@2ndquadrant.com wrote: Do not use CONFLICTING() which looks like it is a function. So is ROW(). Or COALESCE(). ROW and COALESCE behave almost like functions: they operate

Re: [HACKERS] Log notice that checkpoint is to be written on shutdown

2014-10-09 Thread Marti Raudsepp
On Thu, Oct 2, 2014 at 4:21 PM, Michael Banck michael.ba...@credativ.de wrote: we have seen repeatedly that users can be confused about why PostgreSQL is not shutting down even though they requested it. Usually, this is because `log_checkpoints' is not enabled and the final checkpoint is

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-10-08 Thread Marti Raudsepp
On Wed, Oct 8, 2014 at 3:47 AM, Peter Geoghegan p...@heroku.com wrote: It seems like what you're talking about here is just changing the spelling of what I already have. I think there's a subtle difference in expectations too. The current BEFORE INSERT trigger behavior is somewhat defensible

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-10-08 Thread Marti Raudsepp
On Tue, Oct 7, 2014 at 2:27 PM, Marti Raudsepp ma...@juffo.org wrote: but the new approach seems surprising: changes from BEFORE INSERT get persisted in the database, but AFTER INSERT is not fired. I am sorry, I realize now that I misunderstood the current proposed trigger behavior, I thought

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-10-08 Thread Marti Raudsepp
On Wed, Oct 8, 2014 at 12:28 PM, Peter Geoghegan p...@heroku.com wrote: On Wed, Oct 8, 2014 at 1:36 AM, Marti Raudsepp ma...@juffo.org wrote: I think there's a subtle difference in expectations too. The current BEFORE INSERT trigger behavior is somewhat defensible with an INSERT-driven syntax

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-08 Thread Marti Raudsepp
On Thu, Oct 9, 2014 at 1:51 AM, Peter Geoghegan p...@heroku.com wrote: On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner kgri...@ymail.com wrote: Although the last go-around does suggest that there is at least one point of difference on the semantics. You seem to want to fire the BEFORE INSERT

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-08 Thread Marti Raudsepp
On Thu, Oct 9, 2014 at 2:46 AM, Peter Geoghegan p...@heroku.com wrote: Indeed, the current behavior breaks even the canonical keep track of how many posts are in a thread trigger example use an AFTER trigger for this kind of thing, and all of these issues go away. In the latest patches from

Re: [HACKERS] UPSERT wiki page, and SQL MERGE syntax

2014-10-08 Thread Marti Raudsepp
On Thu, Oct 9, 2014 at 4:56 AM, Marti Raudsepp ma...@juffo.org wrote: create trigger ev1 before insert on evt_type execute procedure ins(); create trigger ev2 before update on evt_type execute procedure upd(); create trigger ev3 after insert on evt_type execute procedure ins(); create trigger

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-10-07 Thread Marti Raudsepp
On Thu, Sep 4, 2014 at 12:13 AM, Peter Geoghegan p...@heroku.com wrote: On Wed, Sep 3, 2014 at 9:51 AM, Robert Haas robertmh...@gmail.com wrote: INSERT INTO upsert(key, val) VALUES(1, 'insert') ON CONFLICT WITHIN upsert_pkey UPDATE SET val = 'update'; It seems to me that it would be better to

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-06 Thread Marti Raudsepp
On Mon, Oct 6, 2014 at 5:12 AM, Marti Raudsepp ma...@juffo.org wrote: On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: CREATE INDEX ... [ IF NOT EXISTS [ name ] ] ON ... I think this one is wrong now. I see now, I think you meant: CREATE INDEX

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-06 Thread Marti Raudsepp
On Mon, Oct 6, 2014 at 4:27 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: create_index_if_not_exists_v7.patch Looks good to me. Marking ready for committer. If you have any feedback about my reviews, I would gladly hear it. I'm quite new to this. PS: You seem to be submitting many

Re: [HACKERS] Add generate_series(numeric, numeric)

2014-10-06 Thread Marti Raudsepp
I'm a bit confused about who I should be replying to, but since you were the last one with a patch... On Mon, Oct 6, 2014 at 11:44 AM, Ali Akbar the.ap...@gmail.com wrote: Thanks for the review. Attached the formatted patch according to your suggestion. + select * from

Re: [HACKERS] Add generate_series(numeric, numeric)

2014-10-06 Thread Marti Raudsepp
On Mon, Oct 6, 2014 at 6:12 PM, Ali Akbar the.ap...@gmail.com wrote: User apaan is me. When i added to the commitfest, the patch is listed there by me (apaan). That's fine I think, it's just for tracking who made the changes in the CommitFest app. What actually matters is what you write in the

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-05 Thread Marti Raudsepp
On Fri, Oct 3, 2014 at 7:25 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: I agree with your grammar change. Cool. The version 5 (attached) contains all discussed until now. From documentation: CREATE INDEX ... [ IF NOT EXISTS name | name ] ON ... Maybe I'm just slow, but it

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-05 Thread Marti Raudsepp
On Mon, Oct 6, 2014 at 4:17 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Sun, Oct 5, 2014 at 9:52 AM, Marti Raudsepp ma...@juffo.org wrote: CREATE INDEX ... [ IF NOT EXISTS name | name ] ON ... Maybe I'm just slow, but it took me a few minutes to understand what this means

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-02 Thread Marti Raudsepp
On Wed, Oct 1, 2014 at 2:42 PM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: So, what's the correct/best grammar? CREATE [ IF NOT EXISTS ] [ UNIQUE ] INDEX index_name or CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] index_name I've elected myself as the reviewer for this patch. Here are

Re: [HACKERS] Patch to add support of IF NOT EXISTS to others CREATE statements

2014-10-02 Thread Marti Raudsepp
On Tue, Aug 26, 2014 at 4:20 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 04/14/2014 10:31 PM, Fabrízio de Royes Mello wrote: The attached patch contains CINE for sequences. I just strip this code from the patch rejected before. Committed with minor changes Hmm, the CommitFest

Re: [HACKERS] CREATE IF NOT EXISTS INDEX

2014-10-02 Thread Marti Raudsepp
On Fri, Oct 3, 2014 at 2:15 AM, Marti Raudsepp ma...@juffo.org wrote: + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_TABLE), + errmsg(relation \%s\ already exists, skipping, + indexRelationName))); 1. Clearly relation should

Re: [HACKERS] Final Patch for GROUPING SETS

2014-09-19 Thread Marti Raudsepp
On Fri, Sep 19, 2014 at 4:45 AM, Andrew Gierth and...@tao11.riddles.org.uk wrote: GroupAggregate (cost=1122.39..1197.48 rows=9 width=8) Group Key: two, four Group Key: two Group Key: () Grouping Sets: [ [two, four], [two], [] +1 looks good to me.

Re: [HACKERS] Join consolidation / Removing duplicate joins

2014-09-17 Thread Marti Raudsepp
On Wed, Sep 17, 2014 at 2:00 PM, David Rowley dgrowle...@gmail.com wrote: Anyway... I've been thinking of writing some code that converts these sub plans into left joins where it can be proved that the subquery would only at most produce 1 row Does anyone have any thoughts on this? +1, I've

Re: [HACKERS] Final Patch for GROUPING SETS

2014-09-17 Thread Marti Raudsepp
On Fri, Sep 12, 2014 at 9:41 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: gsp1.patch - phase 1 code patch (full syntax, limited functionality) gsp2.patch - phase 2 code patch (adds full functionality using the new chained aggregate mechanism) I gave

Re: [HACKERS] delta relations in AFTER triggers

2014-09-03 Thread Marti Raudsepp
On Wed, Sep 3, 2014 at 10:49 PM, Kevin Grittner kgri...@ymail.com wrote: Marti Raudsepp ma...@juffo.org wrote: The concept of lightweight relations that pop into existence when a certain kind of trigger definition is used somewhere in the function stack, without a CREATE TABLE, without being

Re: [HACKERS] delta relations in AFTER triggers

2014-09-02 Thread Marti Raudsepp
On Mon, Sep 1, 2014 at 9:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: OTOH, I agree with Kevin that the things we're talking about are lightweight relations not variables. My worry is that PL/pgSQL and Postgres's SQL dialect is turning into a Frankenstein monster with many ways to do the same

Re: [HACKERS] delta relations in AFTER triggers

2014-08-28 Thread Marti Raudsepp
On Thu, Aug 28, 2014 at 12:03 AM, Kevin Grittner kgri...@ymail.com wrote: In essence, make the relations work like PL/pgSQL variables do. If you squint a little, the new/old relation is a variable from the function's point of view, and a parameter from the planner/executor's point of view.

Re: [HACKERS] pg_shmem_allocations view

2014-08-15 Thread Marti Raudsepp
Hi On Thu, May 8, 2014 at 4:28 PM, Andres Freund and...@2ndquadrant.com wrote: Ok. A new version of the patches implementing that are attached. Including a couple of small fixups and docs. The latter aren't extensive, but that doesn't seem to be warranted anyway. Is it really actually useful

Re: [HACKERS] jsonb format is pessimal for toast compression

2014-08-12 Thread Marti Raudsepp
On Fri, Aug 8, 2014 at 10:50 PM, Hannu Krosing ha...@2ndquadrant.com wrote: How hard and how expensive would it be to teach pg_lzcompress to apply a delta filter on suitable data ? So that instead of integers their deltas will be fed to the real compressor Has anyone given this more thought?

Re: [HACKERS] PostrgeSQL vs oracle doing 1 million sqrts am I doing it wrong?

2014-08-05 Thread Marti Raudsepp
On Mon, Aug 4, 2014 at 11:48 PM, testman1316 danilo.rami...@hmhco.com wrote: In both we ran code that did 1 million square roots (from 1 to 1 mill). Then did the same but within an If..Then statement. Note: once we started running queries on the exact same data in Oracle and PostgreSQL we saw

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

2014-07-31 Thread Marti Raudsepp
On Thu, Jul 31, 2014 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: I certainly like that better than poor-man; but proxy, to me, fails to convey inexactness. Maybe abbreviated, abridged, minified? Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

Re: [HACKERS] delta relations in AFTER triggers

2014-07-29 Thread Marti Raudsepp
On Tue, Jul 29, 2014 at 9:49 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I dislike this proposal - it is strongly inconsistent with current trigger design The real point I was trying to convey (in my previous email) is that these declarations should be part of the trigger *function* not

Re: [HACKERS] delta relations in AFTER triggers

2014-07-28 Thread Marti Raudsepp
On Sat, Jul 5, 2014 at 5:38 PM, Kevin Grittner kgri...@ymail.com wrote: it seems to me that we need the full tuple to support triggers on FDWs, so the TID approach would be an optimization for a subset of the cases, and would probably be more appropriate, if we do it at all, in a follow-on

Re: [HACKERS] delta relations in AFTER triggers

2014-07-28 Thread Marti Raudsepp
On Mon, Jul 28, 2014 at 6:24 PM, Kevin Grittner kgri...@ymail.com wrote: Do you have some other suggestion? Keep in mind that it must allow the code which will *generate* the transition tables to know whether any of the attached triggers use a given transition table for the specific

Re: [HACKERS] pg_xlogdump --stats

2014-07-01 Thread Marti Raudsepp
On Wed, Jun 4, 2014 at 1:47 PM, Abhijit Menon-Sen a...@2ndquadrant.com wrote: Here's a patch to make pg_xlogdump print summary statistics instead of individual records. Thanks! I had a use for this feature so I backported the (first) patch to PostgreSQL 9.3. It's a rush job so it's ugly and may

Re: [HACKERS] pg_xlogdump --stats

2014-07-01 Thread Marti Raudsepp
On Tue, Jul 1, 2014 at 11:13 PM, Abhijit Menon-Sen a...@2ndquadrant.com wrote: In CF terms, did you form any opinion while porting the patch I posted about whether it's sensible/ready for inclusion in 9.5? I didn't look at the code more than necessary to make the build work. As far as

Re: [HACKERS] Quantify small changes to predicate evaluation

2014-06-25 Thread Marti Raudsepp
On Tue, Jun 17, 2014 at 5:23 PM, Dennis Butterstein soullinu...@web.de wrote: I tried the proposed tweaks and see some differences regarding the measurements. Unfortunately the variance between the runs seems to remain high. Using these techniques I managed to get standard deviation below 1.5%

Re: [HACKERS] Quantify small changes to predicate evaluation

2014-06-13 Thread Marti Raudsepp
On Fri, Jun 13, 2014 at 12:46 PM, Dennis Butterstein soullinu...@web.de wrote: I expect my current changes to be resposible for about 0.2-0.3s for this query but because of the huge time differences I am not able to quantify my changes. Maybe somebody can tell me about a better approach to

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Marti Raudsepp
On Wed, Jun 11, 2014 at 11:53 AM, David Rowley dgrowle...@gmail.com wrote: The only way to consistently guarantee nullability is through primary key constraints. Fortunately that addresses most of the use cases of NOT IN(), in my experience. See the comment in check_functional_grouping: I

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Marti Raudsepp
On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. There's a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Marti Raudsepp
On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. I believe

Re: [HACKERS] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-28 Thread Marti Raudsepp
On Fri, Apr 25, 2014 at 8:58 PM, Josh Berkus j...@agliodbs.com wrote: Well, I've already had collisions with UUID-OSSP, in production, with only around 20 billion values. So clearly there aren't 122bits of true randomness in OSSP. I can't speak for other implementations because I haven't

Re: [HACKERS] 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Wed, Apr 23, 2014 at 11:26 AM, David Rowley dgrowle...@gmail.com wrote: but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very

Re: [HACKERS] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Thu, Apr 24, 2014 at 8:40 PM, Josh Berkus j...@agliodbs.com wrote: A pseudo-random UUID is frankly pretty useless to me because (a) it's not really unique This is FUD. A pseudorandom UUID contains 122 bits of randomness. As long as you can trust the random number generator, the chances of a

Re: [HACKERS] UUIDs in core WAS: 9.4 Proposal: Initdb creates a single table

2014-04-24 Thread Marti Raudsepp
On Fri, Apr 25, 2014 at 3:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: Of course, the weak spot in this analysis is the assumption that there are actually 122 independent bits in the value. It's not difficult to imagine that systems with crummy random() implementations might only have something

Re: Fwd: [HACKERS] Proposal: variant of regclass

2014-03-23 Thread Marti Raudsepp
On Sun, Mar 23, 2014 at 7:57 AM, Amit Kapila amit.kapil...@gmail.com wrote: Anyone has any objection for this behaviour difference between usage of ::regclass and to_regclass()? No, I think that makes a lot of sense given the behavior -- if the object is not there, to_regclass() just returns

Re: [HACKERS] plpgsql.warn_shadow

2014-03-18 Thread Marti Raudsepp
On Tue, Mar 18, 2014 at 9:29 PM, Petr Jelinek p...@2ndquadrant.com wrote: Attached V4 uses shadowed-variables instead of shadow. I think that should be shadowed_variables for consistency; GUC values usually have underscores, not dashes. (e.g. intervalstyle=sql_standard,

Re: [HACKERS] Disk usage for intermediate results in join

2014-03-14 Thread Marti Raudsepp
On Tue, Mar 11, 2014 at 1:24 PM, Parul Lakkad parul.lak...@gmail.com wrote: I am trying to figure out when disk is used to store intermediate results while performing joins in postgres. Joins can also cause a Nested Loop+Materialize plan, which spills to disk if the materialize result set is

Re: [HACKERS] Display oprcode and its volatility in \do+

2014-02-21 Thread Marti Raudsepp
On Thu, Jan 16, 2014 at 5:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: but adding volatility here seems like probably a waste of valuable terminal width. I think that the vast majority of operators have immutable or at worst stable underlying functions, so this doesn't seem like the first bit of

Re: [HACKERS] Selecting large tables gets killed

2014-02-20 Thread Marti Raudsepp
On Thu, Feb 20, 2014 at 12:07 PM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: That seems a good idea. We will get rid of FETCH_COUNT then, wouldn't we? No, I don't think we want to do that. FETCH_COUNT values greater than 1 are still useful to get reasonably tabulated output without

Re: [HACKERS] Draft release notes up for review

2014-02-19 Thread Marti Raudsepp
On Sun, Feb 16, 2014 at 10:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any comments before I start transposing them into the back branches? Sorry I'm late. Shore up GRANT ... WITH ADMIN OPTION restrictions (Noah Misch) I'm not familiar with the phrase Shore up, I think it should use more

Re: [HACKERS] PoC: Partial sort

2014-02-19 Thread Marti Raudsepp
On Wed, Feb 12, 2014 at 11:54 PM, Marti Raudsepp ma...@juffo.org wrote: With partial-sort-basic-1 and this fix on the same test suite, the planner overhead is now a more manageable 0.5% to 1.3%; one test is faster by 0.5%. Ping, Robert or anyone, does this overhead seem bearable

Re: [HACKERS] PoC: Partial sort

2014-02-12 Thread Marti Raudsepp
is definitely not a fluke, however; it happens every time. On Mon, Feb 10, 2014 at 2:33 PM, Marti Raudsepp ma...@juffo.org wrote: AFAICT this only happens once per plan and the overhead is O(n) to the number of pathkeys? I was of course wrong about that, it also adds extra overhead when iterating over

Re: [HACKERS] PoC: Partial sort

2014-02-10 Thread Marti Raudsepp
On Sun, Feb 9, 2014 at 7:37 PM, Alexander Korotkov aekorot...@gmail.com wrote: This is not only place that worry me about planning overhead. See get_cheapest_fractional_path_for_pathkeys. I had to estimate number of groups for each sorting column in order to get right fractional path. AFAICT

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Marti Raudsepp
On Thu, Feb 6, 2014 at 5:31 AM, Robert Haas robertmh...@gmail.com wrote: Hmm, sounds a little steep. Why is it so expensive? I'm probably missing something here, because I would have thought that planner support for partial sorts would consist mostly of considering the same sorts we consider

Re: Fwd: [HACKERS] Proposal: variant of regclass

2014-02-06 Thread Marti Raudsepp
On Tue, Jan 28, 2014 at 10:38 AM, Yugo Nagata nag...@sraoss.co.jp wrote: I revised the patch. Could you please review this? I didn't test the patch due to the duplicate OID compilation error. But a few things stuck out from the diffs: * You added some unnecessary spaces at the beginning of the

Re: [HACKERS] PoC: Partial sort

2014-02-06 Thread Marti Raudsepp
On Thu, Feb 6, 2014 at 9:15 PM, Robert Haas robertmh...@gmail.com wrote: It may be that having the capability to do a partial sort makes it seem worth spending more CPU looking for merge joins, but I'd vote for making any such change a separate patch. Agreed. Alexander, should I work on

Re: [HACKERS] PoC: Partial sort

2014-02-05 Thread Marti Raudsepp
On Tue, Jan 28, 2014 at 7:51 AM, Alexander Korotkov aekorot...@gmail.comwrote: On Tue, Jan 28, 2014 at 7:41 AM, Marti Raudsepp ma...@juffo.org wrote: But some benchmarks of planning performance are certainly warranted. I didn't test it, but I worry that overhead might be high. If it's true

Re: [HACKERS] PoC: Partial sort

2014-01-28 Thread Marti Raudsepp
On Tue, Jan 28, 2014 at 7:51 AM, Alexander Korotkov aekorot...@gmail.com wrote: I didn't test it, but I worry that overhead might be high. If it's true then it could be like constraint_exclusion option which id off by default because of planning overhead. I see, that makes sense. I will try

Re: [HACKERS] plpgsql.warn_shadow

2014-01-27 Thread Marti Raudsepp
On Sun, Jan 26, 2014 at 11:19 AM, Simon Riggs si...@2ndquadrant.com wrote: For 9.4, we should cut down the patch so it has plpgsql.warnings = none (default) | all | [individual item list] plpgsql.warnings_as_errors = off (default) | on I hope I'm not late for the bikeshedding :) Why not

Re: [HACKERS] PoC: Partial sort

2014-01-27 Thread Marti Raudsepp
On Mon, Jan 27, 2014 at 9:26 PM, Alexander Korotkov aekorot...@gmail.com wrote: For now, I have attempt to fix extra columns in mergejoin problem. It would be nice if you test it. Yes, it solves the test cases I was trying with, thanks. 1) With enable_partialsort = off all mergejoin logic

Re: [HACKERS] PoC: Partial sort

2014-01-26 Thread Marti Raudsepp
On Mon, Jan 20, 2014 at 2:43 PM, Alexander Korotkov aekorot...@gmail.com wrote: Another changes in this version of patch: 1) Applied patch to don't compare skipCols in tuplesort by Marti Raudsepp 2) Adjusting sort bound after processing buckets. Hi, Here's a patch with some whitespace

Re: [HACKERS] Proposal: variant of regclass

2014-01-22 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 9:28 AM, Yugo Nagata nag...@sraoss.co.jp wrote: Here is the patch to implement to_regclass, to_regproc, to_regoper, and to_regtype. + static Datum regclass_guts(char *class_name_or_oid, bool raiseError); Minor bikeshedding, a lot of code currently uses an argument named

Re: [HACKERS] Proposal: variant of regclass

2014-01-22 Thread Marti Raudsepp
On Wed, Jan 22, 2014 at 1:44 PM, Yugo Nagata nag...@sraoss.co.jp wrote: On Wed, 22 Jan 2014 20:04:12 +0900 (JST) Tatsuo Ishii is...@postgresql.org wrote: parseTypeString() is called by some other functions and I avoided influences of modifying the definition on them, since this should raise

Re: [HACKERS] improve the help message about psql -F

2014-01-21 Thread Marti Raudsepp
On Mon, Jan 20, 2014 at 2:04 PM, Jov am...@amutu.com wrote: reasonable,I removed the set,patch attached. Hi Jov, A new commitfest was just opened, due on 2014-06. Please add your patch here: https://commitfest.postgresql.org/action/commitfest_view?id=22 (You'll need a community account if you

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

2014-01-20 Thread Marti Raudsepp
On Mon, Jan 20, 2014 at 1:51 AM, Dave Chinner da...@fromorbit.com wrote: Postgres is far from being the only application that wants this; many people resort to tmpfs because of this: https://lwn.net/Articles/499410/ Yes, we covered the possibility of using tmpfs much earlier in the thread,

Re: [HACKERS] PoC: Partial sort

2014-01-20 Thread Marti Raudsepp
Hi, On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: On Tue, Jan 14, 2014 at 12:54 AM, Marti Raudsepp ma...@juffo.org wrote: I've been trying it out in a few situations. I implemented a new enable_partialsort GUC to make it easier to turn on/off, this way it's

Re: [HACKERS] Add value to error message when size extends

2014-01-19 Thread Marti Raudsepp
On Sun, Jan 19, 2014 at 8:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: Complaining about a too-long varchar string in this style seems practically guaranteed to violate that. Agreed. But I think it would be useful to add the length of the string being inserted; we already have it in the len

Re: [HACKERS] improve the help message about psql -F

2014-01-19 Thread Marti Raudsepp
2014/1/17 Jov am...@amutu.com but in the psql --help,-F say: set field separator (default: |) if user don't read the offical doc carefully,he can use: psql -F , -c 'select ...' But can't get what he want. It is a bad user Experience. +1 from me, patch applies and is helpful. After

Re: [HACKERS] better atomics - v0.2

2014-01-19 Thread Marti Raudsepp
On Tue, Nov 19, 2013 at 6:38 PM, Andres Freund and...@2ndquadrant.com wrote: The attached patches compile and make check successfully on linux x86, amd64 and msvc x86 and amd64. This time and updated configure is included. The majority of operations still rely on CAS emulation. Note that the

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
Memory: 27kB - Index Scan using longtext_a_idx on longtext (cost=0.65..1691.65 rows=1000 width=1160) (actual time=0.013..2.094 rows=1000 loops=1) Total runtime: 5.418 ms Regards, Marti From fbc6c31528018bca64dc54f65e1cd292f8de482a Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
Funny, I just wrote a patch to do that some minutes ago (didn't see your email yet). http://www.postgresql.org/message-id/CABRT9RCK=wmFUYZdqU_+MOFW5PDevLxJmZ5B=etjjnubvya...@mail.gmail.com Regards, Marti On Sat, Jan 18, 2014 at 7:10 PM, Jeremy Harris j...@wizmail.org wrote: On 13/01/14

Re: [HACKERS] PoC: Partial sort

2014-01-18 Thread Marti Raudsepp
On Sat, Jan 18, 2014 at 7:22 PM, Marti Raudsepp ma...@juffo.org wrote: Total runtime: 5.418 ms Oops, shouldn't have rushed this. Clearly the timings should have tipped me off that it's broken. I didn't notice that cmpSortSkipCols was re-using tuplesort's sortkeys. Here's a patch that actually

[HACKERS] [patch] Potential relcache leak in get_object_address_attribute

2014-01-18 Thread Marti Raudsepp
Hi list, It looks like the get_object_address_attribute() function has a potential relcache leak. When missing_ok=true, the relation is found but attribute is not, then the relation isn't closed, nor is it returned to the caller. I couldn't figure out any ways to trigger this, but it's best to

Re: [HACKERS] Linux kernel impact on PostgreSQL performance

2014-01-18 Thread Marti Raudsepp
On Wed, Jan 15, 2014 at 5:34 AM, Jim Nasby j...@nasby.net wrote: it's very common to create temporary file data that will never, ever, ever actually NEED to hit disk. Where I work being able to tell the kernel to avoid flushing those files unless the kernel thinks it's got better things to do

Re: [HACKERS] plpgsql.consistent_into

2014-01-17 Thread Marti Raudsepp
On Wed, Jan 15, 2014 at 8:23 AM, Jim Nasby j...@nasby.net wrote: Do we actually support = right now? We already support v_field := field FROM table ... ; and I think it's a bad idea to have different meaning for = and :=. That was already discussed before. Yes, we support both = and := and

Re: [HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 5:13 AM, Marti Raudsepp ma...@juffo.org wrote: You can use the auto_explain contrib module I just remembered that there's also the pg_stat_plans extension, which is closer to what you asked: https://github.com/2ndQuadrant/pg_stat_plans . This one you'll have to build

Re: [HACKERS] plpgsql.consistent_into

2014-01-14 Thread Marti Raudsepp
in the language without breaking backwards compatibility. On Tue, Jan 14, 2014 at 4:30 AM, Marko Tiikkaja ma...@joh.to wrote: On 2014-01-14 02:54, Marti Raudsepp wrote: But PL/pgSQL already has an assignment syntax with the behavior you want: According to the docs, that doesn't set FOUND which would

Re: [HACKERS] Inheritance and indexes

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 12:07 PM, knizhnik knizh...@garret.ru wrote: But is it possible to use index for derived table at all? Yes, the planner will do an index scan when it makes sense. Why sequential search is used for derived table in the example below: insert into derived_table values

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 5:49 PM, Alexander Korotkov aekorot...@gmail.com wrote: I implemented a new enable_partialsort GUC to make it easier to turn on/off I though about such option. Generally not because of testing convenience, but because of overhead of planning. This way you implement it

Re: [HACKERS] PoC: Partial sort

2014-01-14 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 9:28 PM, Alexander Korotkov aekorot...@gmail.comwrote: On Tue, Jan 14, 2014 at 11:16 PM, Marti Raudsepp ma...@juffo.org wrote: Oh, this actually highlights a performance regression with the partial sort patch. Interesting. Could you share the dataset? It occurs

Re: [HACKERS] PoC: Partial sort

2014-01-13 Thread Marti Raudsepp
: http://www.postgresql.org/mailpref/pgsql-hackers From 3f05447e7feb99583336b381df60ff013a144bab Mon Sep 17 00:00:00 2001 From: Marti Raudsepp ma...@juffo.org Date: Mon, 13 Jan 2014 22:24:20 +0200 Subject: [PATCH] Add enable_partialsort GUC for disabling partial sorts --- doc/src/sgml/config.sgml

Re: [HACKERS] Where do we stand on 9.3 bugs?

2014-01-13 Thread Marti Raudsepp
On Mon, Jan 13, 2014 at 5:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: What remaining issues are there blocking a 9.3.3 release? Well hardly a blocker since this has missed 2 releases already, but I'm still hopeful to get many PGXS-based extensions to build again without the dreaded install: will

Re: [HACKERS] plpgsql.consistent_into

2014-01-13 Thread Marti Raudsepp
On Sun, Jan 12, 2014 at 7:51 AM, Marko Tiikkaja ma...@joh.to wrote: the behaviour of SELECT .. INTO when the query returns more than one row. Some of you might know that no exception is raised in this case Agreed. But I also agree with the rest of the thread about changing current INTO behavior

Re: [HACKERS] Capturing EXPLAIN ANALYZE for a query without discarding the normal result set

2014-01-13 Thread Marti Raudsepp
On Tue, Jan 14, 2014 at 5:06 AM, Dave Cole davejohnc...@gmail.com wrote: It would be really cool if you could direct the EXPLAIN ANALYZE output to a temporary table so that the query being analyzed could execute normally. You can use the auto_explain contrib module to log the query plans of

Re: [HACKERS] PostgreSQL 9.3 beta breaks some extensions make install

2013-11-01 Thread Marti Raudsepp
Hi Andrew, On Mon, Sep 23, 2013 at 6:43 PM, Andrew Dunstan and...@dunslane.net wrote: I'm working on it. It appears to have a slight problem or two I want to fix at the same time, rather than backpatch something broken. Any progress on this? I notice that the fixes didn't make it into 9.3.1.

Re: [HACKERS] PostgreSQL 9.3 beta breaks some extensions make install

2013-09-23 Thread Marti Raudsepp
On Fri, Sep 13, 2013 at 8:17 PM, Marti Raudsepp ma...@juffo.org wrote: On Fri, Sep 13, 2013 at 6:42 PM, Cédric Villemain ced...@2ndquadrant.com wrote: Andrew is about to commit (well...I hope) a doc patch about that and also a little fix. Imho this is a bugfix so I hope it will be applyed

  1   2   3   >