[HACKERS] Re: [BUGS] BUG #14849: jsonb_build_object doesn't like VARIADIC calls very much

2017-10-25 Thread Marko Tiikkaja
On Wed, Oct 25, 2017 at 5:32 PM, Michael Paquier wrote: > On Mon, Oct 23, 2017 at 6:50 AM, Michael Paquier > wrote: > > Okay, attached is what I think a fully implemented patch should look > > like. On top of what Andrew has done, I added

Re: [HACKERS] Query started showing wrong result after Ctrl+c

2017-10-12 Thread Marko Tiikkaja
On Thu, Oct 12, 2017 at 12:03 PM, tushar wrote: > postgres=# SELECT * FROM ( SELECT n from tv where n= (select * from > (select n from tv limit 1) c)) as c ; > n > -- > 3713 > (1 row) > > This time , query is started showing wrong result. Is this an

Re: [HACKERS] Index expression syntax

2017-09-29 Thread Marko Tiikkaja
On Fri, Sep 29, 2017 at 9:31 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > I wonder why syntax error is produced in this case: > > postgres=# create index metaindex on foo using gin(to_tsvector('english', > x)||to_tsvector('english',y)); > ERROR: syntax error at or near "||" >

Re: [HACKERS] 200 = 199 + 1?

2017-09-27 Thread Marko Tiikkaja
On Wed, Sep 27, 2017 at 5:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Marko Tiikkaja <ma...@joh.to> writes: > > I wonder if the nested loop shouldn't have some kind of a cap on its own > > estimate if it's wildly off of what you'd get by multiplying the child >

[HACKERS] 200 = 199 + 1?

2017-09-27 Thread Marko Tiikkaja
Hi, I just came across this very peculiar behavior: =# create table foo(id int primary key); CREATE TABLE =# insert into foo select generate_series(1, 100); INSERT 0 100 =# set enable_hashjoin to false; set enable_mergejoin to false; SET SET =# explain select * from foo where id in

Re: [HACKERS] [PATCH] Pattern based listeners for asynchronous messaging (LISTEN/NOTIFY)

2017-09-07 Thread Marko Tiikkaja
Hi Markus, On Sun, Aug 20, 2017 at 9:56 PM, Markus Sintonen wrote: > I also encountered this when I built it with different configuration. I > attached updated patch with the correct number of arguments to > 'similar_escape'. I also added preliminary documentation to

Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

2017-09-04 Thread Marko Tiikkaja
On Mon, Sep 4, 2017 at 7:46 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Sep 4, 2017 at 10:05 AM, Marko Tiikkaja <ma...@joh.to> wrote: > But I'm generally against > > interfaces which put arbitrary restrictions on what power users can do on > > the basis that

Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

2017-09-04 Thread Marko Tiikkaja
On Mon, Sep 4, 2017 at 4:09 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Tue, Aug 15, 2017 at 12:17 AM, Marko Tiikkaja <ma...@joh.to> wrote: > > On Tue, Aug 15, 2017 at 7:43 AM, Peter Geoghegan <p...@bowt.ie> wrote: > >> > >> On Mon, Aug 14, 2017 at

Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

2017-08-15 Thread Marko Tiikkaja
On Tue, Aug 15, 2017 at 7:43 AM, Peter Geoghegan <p...@bowt.ie> wrote: > On Mon, Aug 14, 2017 at 6:23 PM, Marko Tiikkaja <ma...@joh.to> wrote: > > Attached is a patch for $SUBJECT. It might still be a bit rough around > the > > edges and probably light on docs and t

[HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]

2017-08-14 Thread Marko Tiikkaja
Hi, Attached is a patch for $SUBJECT. It might still be a bit rough around the edges and probably light on docs and testing, but I thought I'd post it anyway so I won't forget. .m insert_conflict_select_v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Allow INSTEAD OF DELETE triggers to modify the tuple for RETURNING

2017-08-13 Thread Marko Tiikkaja
On Fri, Jul 1, 2016 at 2:12 AM, I wrote: > Currently the tuple returned by INSTEAD OF triggers on DELETEs is only > used to determine whether to pretend that the DELETE happened or not, which > is often not helpful enough; for example, the actual tuple might have been > concurrently UPDATEd by

Re: [HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

2017-06-06 Thread Marko Tiikkaja
On Tue, Jun 6, 2017 at 10:25 PM, Kevin Grittner wrote: > Nice as it would be to add a SQL standard feature and advance the > effort to get to incremental maintenance of materialized views, and > much as I really appreciate the efforts Thomas has put into trying > to solve

[HACKERS] PG10 transition tables, wCTEs and multiple operations on the same table

2017-06-02 Thread Marko Tiikkaja
Since the subject of transition tables came up, I thought I'd test how this case works: =# create table qwr(a int); CREATE TABLE =# create function qq() returns trigger as $$ begin raise notice '%', (select count(*) from oo); return null; end $$ language plpgsql; CREATE FUNCTION =# create

Re: [HACKERS] COMPRESS VALUES feature request

2017-05-09 Thread Marko Tiikkaja
On Tue, May 9, 2017 at 8:18 PM, Erez Segal wrote: > In the IRC channel - johto suggested an implementation: > > if you want to get really fancy you could have two columns where > only one of set; one would be the value (if reasonably unique) and the > other the id (if not)

Re: [HACKERS] Why type coercion is not performed for parameters?

2017-05-05 Thread Marko Tiikkaja
On Fri, May 5, 2017 at 10:58 AM, Konstantin Knizhnik < k.knizh...@postgrespro.ru> wrote: > If I evaluate expression typename('literal'), then type coercion is > performed and the function is successfully resolved, i.e. > > SELECT regnamespace('"pg_catalog"'); > > But if I want to prepare this

Re: [HACKERS] PG 10 release notes

2017-04-27 Thread Marko Tiikkaja
On Thu, Apr 27, 2017 at 4:13 PM, Bruce Momjian wrote: > On Thu, Apr 27, 2017 at 08:00:28AM +0530, Amit Kapila wrote: > > > Oh, so non-correlated subqueries can be run in parallel. Yes, that is > > > something we should have in the release notes. How is this? > > > > > >

[HACKERS] Logical replication SnapBuildInitalSnapshot spelling

2017-03-24 Thread Marko Tiikkaja
Hi, Commit 7c4f52409a8c7d85ed169bbbc1f6092274d03920 seems to have introduced an alternative spelling of "initial". Fixed in the attached. .m logical_inital.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Contrib: pqasyncnotifier.c -- a shell command client for LISTEN

2017-01-23 Thread Marko Tiikkaja
On Tue, Jan 24, 2017 at 12:40 AM, Nico Williams wrote: > psql(1) does not output notifications asynchronously, as it does not > check for them when idle. This makes it difficult to script handling of > NOTIFYs. > > Attached is pqasyncnotifier.c, a simple command that

Re: [HACKERS] plpgsql - additional extra checks

2017-01-13 Thread Marko Tiikkaja
On Fri, Jan 13, 2017 at 2:46 AM, Jim Nasby wrote: > On 1/11/17 5:54 AM, Pavel Stehule wrote: > >> +too_many_rows >> + >> + >> + When result is assigned to a variable by INTO >> clause, >> + checks if query returns more than one row. In this case

Re: [HACKERS] plpgsql - additional extra checks

2017-01-11 Thread Marko Tiikkaja
On Wed, Jan 11, 2017 at 2:54 PM, Pavel Stehule wrote: > 1. strict_multi_assignment - checks the number of target variables and > source values. > I've proposed this before (maybe around a year ago), except the checks were done at parse time, rather than runtime. I much

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-10 Thread Marko Tiikkaja
On Tue, Jan 10, 2017 at 2:26 PM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > It's not like PL/pgSQL is the king of brevity. This is essentially saying "PL/PgSQL isn't perfect, so we shouldn't try and make it better". I hear this argument a lot, and as long as people keep

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-09 Thread Marko Tiikkaja
On Tue, Jan 10, 2017 at 1:03 AM, Jim Nasby <jim.na...@bluetreble.com> wrote: > On 1/9/17 5:53 PM, Marko Tiikkaja wrote: > >> My idea was that the currently unsupported combination of NOT >> NULL and >> no DEFAULT would mean "has

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-09 Thread Marko Tiikkaja
On Tue, Jan 10, 2017 at 12:47 AM, Jim Nasby <jim.na...@bluetreble.com> wrote: > On 1/9/17 5:30 PM, Marko Tiikkaja wrote: > My idea was that the currently unsupported combination of NOT NULL and >> no DEFAULT would mean "has to be assigned to a non-NULL value before

Re: [HACKERS] merging some features from plpgsql2 project

2017-01-09 Thread Marko Tiikkaja
On Mon, Jan 9, 2017 at 12:37 AM, Jim Nasby wrote: > If we're going to create a brand new language then I think it would be > extremely foolish to keep *any* of the current pain points around. Off the > top of my head: > > - variables must have an identifier (what $ in

Re: [HACKERS] SELECT FOR UPDATE regression in 9.5

2016-09-07 Thread Marko Tiikkaja
On 07/09/16 7:29 PM, Alvaro Herrera wrote: Marko, does this fix your reported problem too? Both the assertion and the overall test case that causes it to fire? The test case never realized anything was wrong, but the assertion is gone. So yup, problem solved on this end, at least. .m

Re: [HACKERS] kqueue

2016-09-06 Thread Marko Tiikkaja
On 2016-06-03 01:45, Thomas Munro wrote: On Fri, Jun 3, 2016 at 4:02 AM, Alvaro Herrera wrote: Tom Lane wrote: Andres Freund writes: pg_strtoi? I think that's what Thomas did upthread. Are you taking this one then? I'd go with just

Re: [HACKERS] SELECT FOR UPDATE regression in 9.5

2016-09-06 Thread Marko Tiikkaja
On 2016-09-06 6:02 PM, Marti Raudsepp wrote: This issue is also reproducible on the current master branch. In an assertions-enabled build, it traps an assertion in HeapTupleHeaderGetCmax called by heap_lock_tuple. The following test case demonstrates the issue... I think you found a

Re: [HACKERS] Change error code for hstore syntax error

2016-09-04 Thread Marko Tiikkaja
Hi Sherrylyn, On 2016-05-09 19:42, Sherrylyn Branchaw wrote: I'm attaching a revised patch; please let me know if there are any other issues before I submit to the commitfest. I think this is mostly good, but these two should be changed: errmsg("unexpected end of string: \"%s\"",

Re: [HACKERS] INSERT .. SET syntax

2016-08-31 Thread Marko Tiikkaja
Hello hello, Here's a rebased and updated patch for $SUBJECT for the September commit fest. .m *** a/doc/src/sgml/ref/insert.sgml --- b/doc/src/sgml/ref/insert.sgml *** *** 22,33 PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] ! INSERT INTO

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-11 Thread Marko Tiikkaja
On 11/08/16 8:48 AM, Michael Paquier wrote: On Thu, Aug 11, 2016 at 8:09 AM, Marko Tiikkaja <ma...@joh.to> wrote: On 2016-08-11 12:09 AM, Alvaro Herrera wrote: BTW this is not a regression, right? It's been broken all along. Or am I mistaken? You're probably right. I just hadn't re

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-10 Thread Marko Tiikkaja
On 2016-08-11 12:09 AM, Alvaro Herrera wrote: BTW this is not a regression, right? It's been broken all along. Or am I mistaken? You're probably right. I just hadn't realized I could run our app against 9.5 with --enable-cassert until last week. .m -- Sent via pgsql-hackers mailing

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-10 Thread Marko Tiikkaja
On 2016-08-10 11:01 PM, Alvaro Herrera wrote: Oh, I see ... so there's an update chain, and you're updating a earlier tuple. But the later tuple has a multixact and one of the members is the current transaction. I wonder how can you lock a tuple that's not the latest, where that update chain

Re: [HACKERS] Assertion failure in REL9_5_STABLE

2016-08-10 Thread Marko Tiikkaja
On 2016-08-10 19:28, Alvaro Herrera wrote: Umm. AFAICS HeapTupleSatisfiesUpdate() only returns SelfUpdated after already calling HeapTupleHeaderGetCmax() (which obviously hasn't caught the same assertion). Something is odd there ... HeapTupleSatisfiesUpdate() returns HeapTupleBeingUpdated in

[HACKERS] Assertion failure in REL9_5_STABLE

2016-08-10 Thread Marko Tiikkaja
Hi, Running one specific test from our application against REL9_5_STABLE (current as of today) gives me this: #2 0x7effb59595be in ExceptionalCondition ( conditionName=conditionName@entry=0x7effb5b27a88 "!(CritSectionCount > 0 || TransactionIdIsCurrentTransactionId((

Re: [HACKERS] Oddity with NOT IN

2016-08-04 Thread Marko Tiikkaja
On 2016-08-04 11:23 PM, Jim Nasby wrote: I've got a customer that discovered something odd... SELECT f1 FROM v1 WHERE f2 not in (SELECT bad FROM v2 WHERE f3 = 1); does not error, even though bad doesn't exist, but I'm guessing there's a v1.bad? This is a common mistake, and also why I

[HACKERS] INSERT .. SET syntax

2016-07-03 Thread Marko Tiikkaja
Hi, Here's a patch for $SUBJECT. I'll probably work on the docs a bit more before the next CF, but I thought I'd post it anyway. .m diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index e710cf4..33e577b 100644 --- a/doc/src/sgml/ref/insert.sgml +++

[HACKERS] Column COMMENTs in CREATE TABLE?

2016-07-02 Thread Marko Tiikkaja
Hi, Currently we have CREATE TABLE statements in a git repository that look roughly like this: CREATE TABLE foo( -- the first field f1 int NOT NULL, -- the second field f2 int NOT NULL, ... ); But the problem is that those comments don't obviously make it all the way to the

[HACKERS] Allow INSTEAD OF DELETE triggers to modify the tuple for RETURNING

2016-06-30 Thread Marko Tiikkaja
Hi, Currently the tuple returned by INSTEAD OF triggers on DELETEs is only used to determine whether to pretend that the DELETE happened or not, which is often not helpful enough; for example, the actual tuple might have been concurrently UPDATEd by another transaction and one or more of the

Re: [HACKERS] Feature suggestions: "dead letter"-savepoint.

2016-06-23 Thread Marko Tiikkaja
On 2016-06-23 12:34, Terje Elde wrote: Typically the flow would be something like: BEGIN; SELECT id FROM targets WHERE status=‘scheduled’ FOR UPDATE SKIP LOCKED LIMIT 1; UPDATE targets SET status=‘in-flight’ WHERE id =%(id); COMMIT; — Do the work. BEGIN; UPDATE targets SET status=‘completed’

Re: [HACKERS] pg_dump -j against standbys

2016-05-25 Thread Marko Tiikkaja
On 25/05/16 15:59, Magnus Hagander wrote: On Tue, May 24, 2016 at 5:39 PM, Tom Lane wrote: This patch will cause pg_dump to fail entirely against pre-9.0 servers. You need to execute the test conditionally. Ugh. can I blame coding while too jetlagged? You could try

Re: [HACKERS] Calling json_* functions with JSONB data

2016-05-23 Thread Marko Tiikkaja
On 2016-05-23 18:55, Peter van Hardenberg wrote: I talked this over with Andrew who had no objections and suggested I float it on the list before writing a patch. Looks pretty straightforward, just a few new data rows in pg_proc.h. Anyone have any concerns or suggestions? What about cases

Re: [HACKERS] [PATCH] Alter or rename enum value

2016-03-27 Thread Marko Tiikkaja
On 2016-03-27 19:30, Dagfinn Ilmari Mannsåker wrote: ilm...@ilmari.org (Dagfinn Ilmari Mannsåker) writes: I was bored and thought "how hard could it be?", and a few hours' hacking later, I have something that seems to work. It doesn't do IF NOT EXISTS yet, and the error messaging could do

Re: [HACKERS] pl/pgsql exported functions

2016-03-02 Thread Marko Tiikkaja
On 11/02/16 18:29, Magnus Hagander wrote: Most of the pl/pgsql functions and variables are prefixed plpgsql_, so they don't risk conflicting with other shared libraries loaded. There are a couple that are not prefixed. Attached patch fixes that. It's mostly a cleanup, but I think it's something

Re: [HACKERS] proposal: schema PL session variables

2016-02-09 Thread Marko Tiikkaja
On 08/02/16 14:16, Pavel Stehule wrote: 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <ma...@joh.to>: Yeah, and that's exactly what I don't want, because that means that CREATE SCHEMA VARIABLE suddenly breaks existing code. theoretically yes, but this conflict can be 100% detected - so no

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 09:16, Pavel Stehule wrote: Usage = DROP SCHEMA IF EXISTS test_schema CASCADE; SET SCHEMA test_schema; CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0; CREATE OR REPLACE FUNCTION increment_counter() RETURNS void AS $$ BEGIN local_counter := local_counter + 1; END; $$

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 13:17, Pavel Stehule wrote: 2016-02-08 13:03 GMT+01:00 Marko Tiikkaja <ma...@joh.to>: How does this function know which schema variables are visible? function see all schema variables from same schema as function's schema Personally I find that undesirable. I don't kno

Re: [HACKERS] proposal: schema PL session variables

2016-02-08 Thread Marko Tiikkaja
On 08/02/16 13:41, Pavel Stehule wrote: 2016-02-08 13:22 GMT+01:00 Marko Tiikkaja <ma...@joh.to>: Personally I find that undesirable. I don't know what oracle does, but variables being visible without schema-qualifying them can introduce variable conflicts in PL/PgSQL. I'd prefer if you

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-05 Thread Marko Tiikkaja
On 2016-02-05 05:06, Tom Lane wrote: I wrote: Pavel Stehule writes: [ num_nulls_v6.patch ] I started looking through this. It seems generally okay, but I'm not very pleased with the function name "num_notnulls". I think it would be better as "num_nonnulls", as I

Re: [HACKERS] ALTER TABLE behind-the-scenes effects' CONTEXT

2016-01-30 Thread Marko Tiikkaja
On 2016-01-21 04:17, Simon Riggs wrote: Marko, I was/am waiting for an updated patch. Could you comment please? Sorry, I've not found time to work on this recently. Thanks for everyone's comments so far. I'll move this to the next CF and try and get an updated patch done in time for that

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-26 Thread Marko Tiikkaja
On 25/01/16 19:57, Pavel Stehule wrote: Marco is a author of this patch, so - Marco, please, send final version of this patch I don't really care about the tests. Can we not use the v5 patch already in the thread? As far as I could tell there were no reviewer's comments on it anymore.

Re: [HACKERS] proposal: function parse_ident

2016-01-22 Thread Marko Tiikkaja
Hi Pavel, Sorry for the lack of review here. I didn't realize I was still the reviewer for this after it had been moved to another commitfest. That said, I think I've exhausted my usefulness here as a reviewer. Marking ready for committer. .m -- Sent via pgsql-hackers mailing list

Re: [HACKERS] SET syntax in INSERT

2016-01-14 Thread Marko Tiikkaja
Hi, SET syntax for INSERT was brought up a few years ago here: http://www.postgresql.org/message-id/2c5ef4e30908251010s46d9d566m1da21357891ba...@mail.gmail.com From the discussion it seems that one committer was against, one committer was not against, and one committer saw something good in

Re: [HACKERS] SET syntax in INSERT

2016-01-14 Thread Marko Tiikkaja
On 2016-01-14 8:06 PM, Pavel Stehule wrote: Probably there is less risk than 7 years ago, but still creating own syntax isn't the best idea. This is syntactic sugar only and different from ANSi SQL or common standard. So is RETURNING, UPSERT, PL/PgSQL and many other useful features. .m --

Re: [HACKERS] SET syntax in INSERT

2016-01-14 Thread Marko Tiikkaja
On 2016-01-14 20:50, Vitaly Burovoy wrote: On 1/14/16, Tom Lane wrote: Assume a table with an int-array column, and consider INSERT INTO foo SET arraycol[2] = 7, arraycol[4] = 11; Right part is a column name, not an expression. Isn't it? So "arraycol[2]" is not possible

Re: [HACKERS] SET syntax in INSERT

2016-01-14 Thread Marko Tiikkaja
On 2016-01-14 20:33, Tom Lane wrote: Pavel Stehule writes: Probably there is less risk than 7 years ago, but still creating own syntax isn't the best idea. This is syntactic sugar only and different from ANSi SQL or common standard. It's more than syntactic sugar;

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 Thread Marko Tiikkaja
On 13/01/16 14:27, Vladimir Sitnikov wrote: TL;DR: I suggest to create "generic plan" with regard to current bind values. What's wrong with that approach? I don't understand how this is supposed to work. Say you already have a plan which looks like this: Seq Scan on foo

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 Thread Marko Tiikkaja
On 13/01/16 14:12, Pavel Stehule wrote: so the strategy - if cost of generic plan is less than some MAGIC CONSTANT (can be specified by GUC), then use generic plan. Elsewhere use a custom plan everytime. It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use custom plan

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 Thread Marko Tiikkaja
On 13/01/16 14:36, Vladimir Sitnikov wrote: Say you already have a plan which looks like this: Now the plan gets invoked with $1 = 5. What exactly in your mind would happen here? A sequential scan with $1=5 condition. What else could be there? I don't know, it's your proposal :-) But it

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 Thread Marko Tiikkaja
On 13/01/16 15:26, Vladimir Sitnikov wrote: 2) It is likely to be more performant. We just need to explain users that "if different plans required, just use different statements". This completely screws over PL/PgSQL, among other things. .m -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 Thread Marko Tiikkaja
On 13/01/16 15:34, Vladimir Sitnikov wrote: This completely screws over PL/PgSQL, among other things. Can you elaborate a bit? You just write a query like this: SELECT * FROM foo WHERE bar = _Variable; so you don't get to (or want to) have any control over the underlying prepared

Re: [HACKERS] Question about DROP TABLE

2016-01-12 Thread Marko Tiikkaja
On 12/01/16 12:17, Pavel Stehule wrote: 2016-01-12 12:14 GMT+01:00 Michal Novotny : Hi Pavel, thanks for the information. I've been doing more investigation of this issue and there's autovacuum running on the table however it's automatically starting even if there

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-12 Thread Marko Tiikkaja
On 12/01/16 13:00, Dave Cramer wrote: We have an interesting problem, and the reporter has been kind enough to provide logs for which we can't explain. I'd be interested to hear any plausible explanations for a prepared plan suddenly going from 2ms to 60ms for the same input values ? This is

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-12 Thread Marko Tiikkaja
On 03/01/16 22:49, Jim Nasby wrote: In the unit test, I'd personally prefer just building a table with the test cases and the expected NULL/NOT NULL results, at least for all the calls that would fit that paradigm. That should significantly reduce the size of the test. Not a huge deal though...

Re: [HACKERS] Add numeric_trim(numeric)

2016-01-09 Thread Marko Tiikkaja
On 2016-01-07 1:11 AM, Tom Lane wrote: A different approach is that I'm not real sure why we want a function that returns a modified numeric value at all. To the extent I understood Marko's original use case, it seems like what you'd invariably do with the result is extract its scale(). Well,

Re: [HACKERS] Passing initially_valid values instead of !skip_validation to StoreRelCheck() in AddRelationNewConstraints()

2015-12-03 Thread Marko Tiikkaja
On 12/3/15 12:44 PM, amul sul wrote: On Thursday, 3 December 2015 4:36 PM, Amit Langote wrote: The user will have to separately validate the constraint by issuing a ALTER TABLE VALIDATE CONSTRAINT command at a time of their choosing. This could be time

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2015-11-30 Thread Marko Tiikkaja
On 2015-12-01 05:00, David Rowley wrote: We already allow a SELECT's target list to contain non-aggregated columns in a GROUP BY query in cases where the non-aggregated column is functionally dependent on the GROUP BY clause. For example a query such as; SELECT p.product_id,p.description,

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Marko Tiikkaja
On 2015-11-22 21:17, Jim Nasby wrote: On 11/22/15 11:34 AM, Marko Tiikkaja wrote: On 2015-11-22 18:29, Jim Nasby wrote: Only if you know how many columns there already are. Or does this not work if you hand it a row? It "works" in the sense that it tells you whether the r

Re: [HACKERS] custom function for converting human readable sizes to bytes

2015-11-22 Thread Marko Tiikkaja
On 2015-11-22 18:30, Jim Nasby wrote: On 11/21/15 12:49 AM, Pavel Stehule wrote: I propose inversion function to pg_size_pretty function - like pg_human_size. Usage: SELECT * FROM pg_class WHERE pg_table_size(oid) > pg_human_size('2GB'); I'm not a big fan of the name, but +1 on the

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Marko Tiikkaja
On 2015-11-22 18:29, Jim Nasby wrote: Only if you know how many columns there already are. Or does this not work if you hand it a row? It "works" in the sense that it tells you whether the row is NULL or not. I.e. the answer will always be 0 or 1. .m -- Sent via pgsql-hackers mailing

Re: [HACKERS] COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-11-21 Thread Marko Tiikkaja
On 2015-11-16 08:24, Michael Paquier wrote: On Sun, Nov 1, 2015 at 2:49 AM, Marko Tiikkaja <ma...@joh.to> wrote: Attached is a patch for being able to do $SUBJECT without a CTE. The reasons this is better than a CTE version are: 1) It's not obvious why a CTE version works but a pla

Re: [HACKERS] COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-11-20 Thread Marko Tiikkaja
On 11/19/15 7:39 PM, Michael Paquier wrote: On Thu, Nov 19, 2015 at 9:22 PM, Marko Tiikkaja <ma...@joh.to> wrote: Of course, something might break if we added a new statement type which supported RETURNING, but I'm really not worried about that. I'm not dead set against adding some Asse

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja
On 2015-11-21 06:02, I wrote: Here's a patch implementing this under the name num_nulls(). For January's CF, of course. I forgot to update the some references in the documentation. Fixed in v3, attached. .m *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** ***

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja
On 2015-11-21 06:06, Tom Lane wrote: Marko Tiikkaja <ma...@joh.to> writes: Here's a patch implementing this under the name num_nulls(). For January's CF, of course. What's this do that "count(*) - count(x)" doesn't? This is sort of a lateral version of count(x); the

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja
Hello, Here's a patch implementing this under the name num_nulls(). For January's CF, of course. .m *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 182,188 !Comparison Operators comparison --- 182,188 !

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja
On 2015-11-21 06:52, Jim Nasby wrote: On 11/20/15 11:12 PM, Marko Tiikkaja wrote: On 2015-11-21 06:02, I wrote: Here's a patch implementing this under the name num_nulls(). For January's CF, of course. I forgot to update the some references in the documentation. Fixed in v3, attached. I

Re: [HACKERS] onlyvalue aggregate (was: First Aggregate Funtion?)

2015-11-20 Thread Marko Tiikkaja
Hi Dean, Here's v2 of the patch. How's this look? .m *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** *** 12635,12640 NULL baz(3 rows) --- 12635,12660 + single_value + + + single_value(expression) +

Re: [HACKERS] proposal: LISTEN *

2015-11-19 Thread Marko Tiikkaja
On 11/19/15 4:48 AM, Pavel Stehule wrote: 2015-11-19 4:40 GMT+01:00 Marko Tiikkaja <ma...@joh.to>: I've in the past wanted to listen on all notification channels in the current database for debugging purposes. But recently I came across another use case. Since having multiple po

Re: [HACKERS] COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-11-19 Thread Marko Tiikkaja
On 11/19/15 1:17 PM, Michael Paquier wrote: On Thu, Nov 19, 2015 at 11:04 AM, Marko Tiikkaja wrote: Further, if someone's going to add new stuff to PreparableStmt, she should probably think about whether it would make sense to add it to COPY and CTEs from day one, too, and in that case

Re: [HACKERS] proposal: LISTEN *

2015-11-19 Thread Marko Tiikkaja
On 11/19/15 4:21 PM, Tom Lane wrote: Marko Tiikkaja <ma...@joh.to> writes: I've in the past wanted to listen on all notification channels in the current database for debugging purposes. But recently I came across another use case. Since having multiple postgres backends lis

Re: [HACKERS] proposal: LISTEN *

2015-11-19 Thread Marko Tiikkaja
On 11/19/15 5:32 PM, Tom Lane wrote: Marko Tiikkaja <ma...@joh.to> writes: On 11/19/15 4:21 PM, Tom Lane wrote: ... and then you gotta get the notifications to the clients, so it seems like this just leaves the performance question hanging. I'm not sure which performance question you

Re: [HACKERS] COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-11-18 Thread Marko Tiikkaja
On 2015-11-16 08:24, Michael Paquier wrote: On Sun, Nov 1, 2015 at 2:49 AM, Marko Tiikkaja <ma...@joh.to> wrote: Attached is a patch for being able to do $SUBJECT without a CTE. The reasons this is better than a CTE version are: 1) It's not obvious why a CTE version works but a pla

[HACKERS] Add numeric_trim(numeric)

2015-11-18 Thread Marko Tiikkaja
Hi, Here's a patch for the second function suggested in 5643125e.1030...@joh.to. This is my first patch trying to do anything with numerics, so please be gentle. I'm sure it's full of stupid. January's commit fest, feedback welcome, yada yada.. .m *** a/doc/src/sgml/func.sgml ---

[HACKERS] Add scale(numeric)

2015-11-18 Thread Marko Tiikkaja
Hi, As suggested in 5643125e.1030...@joh.to, here's a patch for extracting the scale out of a numeric. This is 2016-01 CF material, but if someone wants to bas^H^H^Hsay nice things in the meanwhile, feel free. .m *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml ***

[HACKERS] proposal: LISTEN *

2015-11-18 Thread Marko Tiikkaja
Hi, I've in the past wanted to listen on all notification channels in the current database for debugging purposes. But recently I came across another use case. Since having multiple postgres backends listening for notifications is very inefficient (one Thursday I found out 30% of our CPU

Re: [HACKERS] proposal: function parse_ident

2015-11-16 Thread Marko Tiikkaja
On 9/11/15 12:25 PM, Pavel Stehule wrote: new update of parse_ident function patch Nice! I've certainly wanted something like this a number of times. Some comments about the v2 of the patch: - The patch doesn't apply anymore, so it should be rebased. - The docs don't even try and

Re: [HACKERS] psql: add \pset true/false

2015-11-12 Thread Marko Tiikkaja
On 11/12/15 1:50 PM, Michael Paquier wrote: FWIW, I am -1 on the concept of enforcing output values for particular datatypes because that's not the job of psql In my view, the job of psql is to make working with a postgres database easy for us human beings. That means (among other things)

[HACKERS] proposal: numeric scale functions

2015-11-11 Thread Marko Tiikkaja
Hi, Dealing with "numeric"s right now in cases where it's really important that the scale is correct is quite painful. For example, if I want to accept a EUR amount as an input, I often want to reject values such as '21.413', but I'd be fine with e.g. '21.41'. My suggestion is to add

Re: [HACKERS] WIP: Fix parallel workers connection bug in pg_dump (Bug #13727)

2015-11-05 Thread Marko Tiikkaja
On 11/5/15 4:11 PM, Zeus Kronion wrote: On Nov 1, 2015 5:04 PM, "Marko Tiikkaja" <ma...@joh.to> wrote: However, I don't quite like the way the password cache is kept up to date in the old *or* the new code. It seems to me that it should instead look like: if (PQconnectio

Re: [HACKERS] onlyvalue aggregate (was: First Aggregate Funtion?)

2015-11-02 Thread Marko Tiikkaja
On 11/2/15 12:40 PM, Dean Rasheed wrote: I'm not sure what you mean when you say accepting NULLs can hide bugs. I think that if the input values to the aggregate were 1,1,1,NULL,1,1,1 then it should raise an error. ITSM that that is more likely to reveal problems with your underlying data or the

Re: [HACKERS] onlyvalue aggregate (was: First Aggregate Funtion?)

2015-11-02 Thread Marko Tiikkaja
On 11/2/15 9:32 AM, Dean Rasheed wrote: On 28 October 2015 at 16:50, Marko Tiikkaja <ma...@joh.to> wrote: Here's a patch for the aggregate function outlined by Corey Huinker in CADkLM=foA_oC_Ri23F9PbfLnfwXFbC3Lt8bBzRu3=cb77g9...@mail.gmail.com . +1. I've wanted something like this a few

Re: [HACKERS] WIP: Fix parallel workers connection bug in pg_dump (Bug #13727)

2015-11-01 Thread Marko Tiikkaja
On 10/25/15 10:55 PM, Zeus Kronion wrote: Parallel workers were failing to connect to the database when running pg_dump with a connection string. The first of the following two commands runs without errors, while the second one fails: pg_dump

Re: [HACKERS] September 2015 Commitfest

2015-11-01 Thread Marko Tiikkaja
On 11/1/15 11:36 AM, Michael Paquier wrote: On Sun, Nov 1, 2015 at 1:53 AM, Marko Tiikkaja <ma...@joh.to> wrote: Are we doing these in an Australian time zone now? It was quite unpleasant to find that the 2015-11 is "in progress" already and two of my patches will not be

[HACKERS] COPY (INSERT/UPDATE/DELETE .. RETURNING ..)

2015-10-31 Thread Marko Tiikkaja
Hi, Attached is a patch for being able to do $SUBJECT without a CTE. The reasons this is better than a CTE version are: 1) It's not obvious why a CTE version works but a plain one doesn't 2) This one has less overhead (I measured a ~12% improvement on a not-too-unreasonable test case)

Re: [HACKERS] September 2015 Commitfest

2015-10-31 Thread Marko Tiikkaja
On 10/31/15 12:42 AM, Michael Paquier wrote: So, seeing nothing happening I have done the above, opened 2015-11 CF and closed the current one. Are we doing these in an Australian time zone now? It was quite unpleasant to find that the 2015-11 is "in progress" already and two of my patches

Re: [HACKERS] psql: add \pset true/false

2015-10-29 Thread Marko Tiikkaja
On 10/29/15 11:51 AM, Daniel Verite wrote: Marko Tiikkaja wrote: Since the default t/f output for booleans is not very user friendly, attached is a patch which enables you to do for example the following: Personally I think it would be worth having, but how about booleans inside ROW

[HACKERS] onlyvalue aggregate (was: First Aggregate Funtion?)

2015-10-28 Thread Marko Tiikkaja
Hi, Here's a patch for the aggregate function outlined by Corey Huinker in CADkLM=foA_oC_Ri23F9PbfLnfwXFbC3Lt8bBzRu3=cb77g9...@mail.gmail.com . I called it "onlyvalue", which is a horrible name, but I have nothing better to offer. (Corey called it "only", but that doesn't really work since

Re: [HACKERS] onlyvalue aggregate (was: First Aggregate Funtion?)

2015-10-28 Thread Marko Tiikkaja
On 10/28/15 5:53 PM, Pavel Stehule wrote: what is use case for this function and why it should be in core? Corey had one example in his email, but I can offer another one which came up this week at $work. The query looked something like this: SELECT a, sum(amount), onlyvalue(rolling_count)

[HACKERS] psql: add \pset true/false

2015-10-28 Thread Marko Tiikkaja
Hello hello, Since the default t/f output for booleans is not very user friendly, attached is a patch which enables you to do for example the following: =# \pset true TRUE Boolean TRUE display is "TRUE". =# \pset false FALSE Boolean FALSE display is "FALSE". =# select true, false; bool |

Re: [HACKERS] psql: add \pset true/false

2015-10-28 Thread Marko Tiikkaja
On 10/28/15 11:52 PM, Robert Haas wrote: -0 on this concept from me. I'm not going to vigorously oppose it, but: 1. You can always do it in the query if you really want it. True, but not always practical. 2. If you're the sort of person liable to be confused by t/f, you probably aren't in

Re: [HACKERS] Questionable behavior regarding aliasing

2015-10-09 Thread Marko Tiikkaja
On 2015-10-09 10:31 PM, Jim Nasby wrote: I was about to report this as a bug until Marko Tiikkaja pointed out on IRC that now was being treated as an alias for relname. I'm not sure if this is required by the spec, but can we at least emit a WARNING if not reject this case outright? I think

  1   2   3   4   5   6   7   >