Re: procedures and plpgsql PERFORM

On Thu, Dec 14, 2017 at 11:56 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2017-12-14 18:33 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: >> >> On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule <pavel.steh...@gmail.com> >> wrote: >

Re: procedures and plpgsql PERFORM

On Thu, Dec 14, 2017 at 10:46 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > > > 2017-12-14 17:10 GMT+01:00 David G. Johnston <david.g.johns...@gmail.com>: >> >> On Thu, Dec 14, 2017 at 8:22 AM, Merlin Moncure <mmonc...@gmail.com> >> wrote: >

Re: procedures and plpgsql PERFORM

On Thursday, December 14, 2017, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Hi, > We allow a function to be invoked as part of PERFORM statement in plpgsql > do $$ > begin perform pg_relation_size('t1'); end; $$ language plpgsql; > DO > > But we do not allow a procedure to be

Re: [HACKERS] Transaction control in procedures

On Wed, Nov 15, 2017 at 3:42 PM, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > On 11/15/17 09:54, Merlin Moncure wrote: >> ... I noticed that: >> *) now() did not advance with commit and, >> *) xact_start via pg_stat_activity did not advance >> &

feature request: consume asynchronous notification via a function

Hackers, Currently the only way that I know of to consume async notifications via SQL (as opposed to a client application) is via dblink_get_notify. This method isn't very good; it requires some extra support coding, eats a connection and a backend, and doesn't have any timeout facilities. The

Re: [HACKERS] Transaction control in procedures

On Wed, Nov 8, 2017 at 5:48 PM, Simon Riggs wrote: > On 31 October 2017 at 15:38, Peter Eisentraut > wrote: >> Here is a patch that implements transaction control in PL/Python >> procedures. (This patch goes on top of "SQL procedures"

Re: feature request: consume asynchronous notification via a function

On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >>> I am very much looking at the new stored procedure funct

Re: feature request: consume asynchronous notification via a function

On Tue, Nov 21, 2017 at 2:16 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Tue, Nov 21, 2017 at 12:20 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> Robert Haas <robertmh...@gmail.com> writes: >>> On Tue, Nov 21, 2017 at 11:32 AM, Merlin Moncure <mmonc..

Re: pspg - psql pager

On Thu, Nov 16, 2017 at 11:04 AM, Pavel Stehule wrote: > 2017-11-16 16:40 GMT+01:00 Aaron W. Swenson : >> I likes it. I likes it a lot. >> >> And, you’ve got a PR on GitHub from me. >> >> It’s available on Gentoo now! This would be great package

Re: Transaction control in procedures

On Thu, Nov 16, 2017 at 12:36 PM, Peter Eisentraut wrote: > On 11/16/17 07:04, legrand legrand wrote: >> We are just opening the "close cursors on/at commit" specification ;o) >> >> - MS SQL server: cursor_close_on_commit >> - Firebird: close_cursors_at_commit

Re: [HACKERS] Transaction control in procedures

On Thu, Nov 16, 2017 at 5:35 PM, Simon Riggs wrote: > On 14 November 2017 at 13:09, Peter Eisentraut > wrote: > >>> *) Will pg_cancel_backend() cancel the currently executing statement >>> or the procedure? (I guess probably the procedure

Re: pspg - psql pager

On Wed, Nov 15, 2017 at 4:45 AM, Andreas Joseph Krogh wrote: > På onsdag 15. november 2017 kl. 10:41:45, skrev Pavel Stehule > : > > Hi > > I finished new pager, that can be interesting for postgresql expert users. > Thanks for making this, "-X

Re: [HACKERS] Transaction control in procedures

On Wed, Nov 15, 2017 at 7:38 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Tue, Nov 14, 2017 at 5:27 PM, Peter Eisentraut >>> Can we zero in on this? The question implied, 'can you do this >>> without being in a transaction'? PERFORM do_stuff() is a implicit &

Re: [HACKERS] Transaction control in procedures

On Wed, Dec 6, 2017 at 8:41 AM, Peter Eisentraut wrote: > On 12/5/17 13:33, Robert Haas wrote: >> On Tue, Dec 5, 2017 at 1:25 PM, Peter Eisentraut >> wrote: >>> I think ROLLBACK in a cursor loop might not make sense, because the

Re: Built-in connection pooling

On Thu, May 3, 2018 at 12:01 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, Apr 27, 2018 at 4:43 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> What _I_ (maybe not others) want is a >> faster pgbouncer that is integrated into the database; IMO it does

Re: Built-in connection pooling

On Fri, May 4, 2018 at 2:25 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Fri, May 4, 2018 at 11:22 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >> If we are breaking 1:1 backend:session relationship, what controls >> would we have to manage resource consumpti

Re: [HACKERS] Clock with Adaptive Replacement

On Wed, May 9, 2018 at 11:00 AM Robert Haas wrote: > Independently of that, it would be probably also useful to avoid > bumping the reference count multiple times when a buffer is accessed > by the same backend several times in quick succession. Perhaps this > could even

Re: Built-in connection pooling

On Thu, Apr 26, 2018 at 6:04 AM, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: > On 25.04.2018 20:02, Merlin Moncure wrote: >> Yep. The main workaround today is to disable them. Having said that, >> it's not that difficult to imagine hooking prepared statement cre

Re: Built-in connection pooling

On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: > On 27.04.2018 16:49, Merlin Moncure wrote: >> *) How are you pinning client connections to an application managed >> transaction? (IMNSHO, this feature is useless without being able to

Re: Built-in connection pooling

On Fri, Apr 27, 2018 at 11:44 AM, Konstantin Knizhnik <k.knizh...@postgrespro.ru> wrote: > > > On 27.04.2018 18:33, Merlin Moncure wrote: >> On Fri, Apr 27, 2018 at 10:05 AM, Konstantin Knizhnik >> <k.knizh...@postgrespro.ru> wrote: >>> On 27.04.2018 16

Re: I'd like to discuss scaleout at PGCon

On Wed, May 30, 2018 at 9:26 PM Robert Haas wrote: > The FDW approach, of which I have been a supporter for some years now, > is really aiming at a different target, which is to allow efficient > analytics queries across a multi-node cluster. I think we're getting > pretty close to being able to

Re: I'd like to discuss scaleout at PGCon

On Fri, Jun 22, 2018 at 12:34 PM Bruce Momjian wrote: > > On Fri, Jun 1, 2018 at 11:29:43AM -0500, Merlin Moncure wrote: > > FWIW, Distributed analytical queries is the right market to be in. > > This is the field in which I work, and this is where the action is at. > &g

Re: JIT compiling with LLVM v9.0

On Wed, Jan 31, 2018 at 1:45 PM, Robert Haas wrote: > On Wed, Jan 31, 2018 at 1:34 PM, Andres Freund wrote: >>> The first one is a problem that's not going to go away. If the >>> problem of JIT being enabled "magically" is something we're concerned >>>

Re: JIT compiling with LLVM v9.0

On Thu, Feb 1, 2018 at 8:16 PM, Thomas Munro wrote: > On Fri, Feb 2, 2018 at 2:05 PM, Andres Freund wrote: >> On 2018-02-01 09:32:17 -0800, Jeff Davis wrote: >>> On Wed, Jan 31, 2018 at 12:03 AM, Konstantin Knizhnik >>>

Re: JIT compiling with LLVM v9.0

On Thu, Jan 25, 2018 at 9:40 AM, Konstantin Knizhnik wrote: > As far as I understand generation of native code is now always done for all > supported expressions and individually by each backend. > I wonder it will be useful to do more efforts to understand when

Re: Built-in connection pooling

On Wed, Apr 25, 2018 at 12:34 AM, Christophe Pettus <x...@thebuild.com> wrote: > >> On Apr 24, 2018, at 06:52, Merlin Moncure <mmonc...@gmail.com> wrote: >> Why does it have to be completely transparent? > > The main reason to move it into core is to avoid the limi

Re: Built-in connection pooling

On Mon, Apr 23, 2018 at 3:14 PM, Robert Haas wrote: > In other words, transparent connection pooling is going to require > some new mechanism, which third-party code will have to know about, > for tracking every last bit of session state that might need to be > preserved or

Re: Built-in connection pooling

On Wed, Apr 25, 2018 at 9:43 AM, Christophe Pettus <x...@thebuild.com> wrote: > >> On Apr 25, 2018, at 07:00, Merlin Moncure <mmonc...@gmail.com> wrote: >> The limitations headaches that I suffer with pgbouncer project (which >> I love and use often) are main

Re: INOUT parameters in procedures

On Wed, Feb 28, 2018 at 4:28 PM, Peter Eisentraut wrote: > This patch set adds support for INOUT parameters to procedures. > Currently, INOUT and OUT parameters are not supported. > > A top-level CALL returns the output parameters as a result row. In > PL/pgSQL,

Re: INOUT parameters in procedures

On Tue, Mar 20, 2018 at 9:09 AM, Pavel Stehule wrote: >> Edit: In one case, after dropping the function and recreating it, I >> got the procedure to return 0 where it had not before, so this smells >> like a bug. >> postgres=# call p(); >> 2018-03-20 09:04:50.543 CDT

Re: INOUT parameters in procedures

On Tue, Mar 20, 2018 at 10:09 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > 2018-03-20 15:18 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: >> >> postgres=# create or replace procedure p(a inout int default 7) as $$ >> >> begin return; end; $$ l

Re: INOUT parameters in procedures

On Tue, Mar 20, 2018 at 4:19 PM, Merlin Moncure <mmonc...@gmail.com> wrote: > A) you can't assign output variables with into: > CALL p(1) INTO i; // gives syntax error > > B) you can't assign via assignment > i := p(1); // gives error, 'use CALL' > > C) but you *can* vi

Re: Built-in connection pooling

On Wed, Apr 25, 2018 at 2:58 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Apr 25, 2018 at 10:00 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >> systems. If we get that tor free I'd be all for it but reading >> Robert's email I'm skeptical there

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

On Fri, Nov 2, 2018 at 5:15 PM Andrew Dunstan wrote: > On 11/02/2018 05:20 PM, Andres Freund wrote: > > Hi, > > > > On 2018-11-02 17:02:24 -0400, Andrew Dunstan wrote: > >> On 11/02/2018 11:34 AM, Merlin Moncure wrote: > >>> Binary format c

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

On Fri, Nov 2, 2018 at 11:15 AM Stephen Frost wrote: > > Greetings, > > * Merlin Moncure (mmonc...@gmail.com) wrote: > > On Fri, Nov 2, 2018 at 10:53 AM Tom Lane wrote: > > > Andres' point about alignment is a pretty good one as well, if it applies > > &g

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

On Wed, Oct 31, 2018 at 10:23 AM Andres Freund wrote: > > Hi, > > On 2018-10-31 11:13:13 -0400, Andrew Dunstan wrote: > > I agree that just sending a blob of the internal format isn't a great idea. > > It's entirely unacceptable afaict. Besides the whole "exposing > internals" issue, it's also at

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

On Fri, Nov 2, 2018 at 10:53 AM Tom Lane wrote: > Merlin Moncure writes: > > On Wed, Oct 31, 2018 at 10:23 AM Andres Freund wrote: > >> It's entirely unacceptable afaict. Besides the whole "exposing > >> internals" issue, it's also at least not endi

Re: WIP Patch: Add a function that returns binary JSONB as a bytea

On Fri, Nov 2, 2018 at 2:34 PM Stephen Frost wrote: > * Merlin Moncure (mmonc...@gmail.com) wrote: > As for what language it's written in- I don't think that matters much. > I'd very much expect it to be more performant to use binary if you're > working in C, of course, but ther

Re: transction_timestamp() inside of procedures

On Wed, Sep 26, 2018 at 10:55 AM Alvaro Herrera wrote: > > On 2018-Sep-26, Tom Lane wrote: > > > Alvaro Herrera writes: > > > On 2018-Sep-26, Tom Lane wrote: > > >> I agree that it would be surprising for transaction timestamp to be newer > > >> than statement timestamp. So for now at least,

Re: Early WIP/PoC for inlining CTEs

On Tue, Jul 24, 2018 at 6:10 PM Andres Freund wrote: > My read of the concensus (in which I am in the majority, so I might be > biased) is that we do want inlining to be the default. We were thinking > that it'd be necessary to provide a way to force inlining on the SQL > level for individual

Re: Stored procedures and out parameters

On Mon, Sep 17, 2018 at 7:45 AM Jonathan S. Katz wrote: > > Hi, > > On 9/2/18 4:32 PM, Robert Haas wrote: > > On Thu, Aug 30, 2018 at 4:14 PM, Dave Cramer wrote: > >> Reading this from the (JDBC) drivers perspective, which is probably a > >> fairly > >> popular one, > >> We now have a standard

crosstab/repivot...any interest?

On Fri, Jan 25, 2019 at 9:14 PM Morris de Oryx wrote: > > Hello, I'm not a C coder and can't helpbut I love cross-tab/pivot tables. They're the best, and just fantastic for preparing data to feed into various analysis tools. The tablefunc module is helpful, but a bit awkward to use (as I

Re: crosstab/repivot...any interest?

On Tue, Feb 26, 2019 at 8:31 AM Joe Conway wrote: > On 2/25/19 8:34 PM, Merlin Moncure wrote: > > The interface I'm looking at is: > > SELECT repivot( > > query TEXT, > > static_attributes INT, /* number of static attributes that are > > unchanged aroun

Re: Early WIP/PoC for inlining CTEs

On Thu, Feb 14, 2019 at 10:02 AM Alvaro Herrera wrote: > > On 2019-Feb-14, Peter Eisentraut wrote: > > > On 14/02/2019 16:11, Tom Lane wrote: > > > ... so, have we beaten this topic to death yet? Can we make a decision? > > > > > > Personally, I'd be happy with either of the last two patch

Re: Early WIP/PoC for inlining CTEs

On Tue, Jan 29, 2019 at 1:53 PM Tom Lane wrote: > > Michael Paquier writes: > > On Mon, Jan 28, 2019 at 05:05:32PM -0500, Tom Lane wrote: > >> Yeah, I thought about that too, but it doesn't seem like an improvement. > >> If the query is very long (which isn't unlikely) I think people would > >>

Re: Early WIP/PoC for inlining CTEs

On Tuesday, January 29, 2019, Tom Lane wrote: > Merlin Moncure writes: > > On Tue, Jan 29, 2019 at 1:53 PM Tom Lane wrote: > >> I propose that we implement and document this as > >> WITH ctename AS [ MATERIALIZE { ON | OFF } ] ( query ) > > > I hate to bi

crosstab/repivot...any interest?

Hackers, We have a strong need to make a variant to the crosstab interface so that data that is pivoted one way would be sent through a crosstab like function so that it would be pivoted another way. For example, if you had row 0: a1, a2, a3, k1, c1, c2, ... row 1: a1, a2, a3, k2, c1, c2, ...

Re: crosstab/repivot...any interest?

On Fri, Jan 25, 2019 at 3:16 PM David Fetter wrote: > > On Fri, Jan 25, 2019 at 02:21:55PM -0600, Merlin Moncure wrote: > > Hackers, > > > > We have a strong need to make a variant to the crosstab interface so > > that data that is pivoted one way would be se

Re: pg11.5: ExecHashJoinNewBatch: glibc detected...double free or corruption (!prev)

On Mon, Aug 26, 2019 at 12:01 PM Tom Lane wrote: > > Justin Pryzby writes: > > On Mon, Aug 26, 2019 at 12:45:24PM -0400, Tom Lane wrote: > >> However ... there is some pretty interesting info at > >> https://bugzilla.redhat.com/show_bug.cgi?id=1338673 > >> suggesting that compiling with a

Re: pg11.5: ExecHashJoinNewBatch: glibc detected...double free or corruption (!prev)

On Sun, Aug 25, 2019 at 9:35 PM Thomas Munro wrote: > > On Mon, Aug 26, 2019 at 1:44 PM Justin Pryzby wrote: > > On Mon, Aug 26, 2019 at 01:09:19PM +1200, Thomas Munro wrote: > > > On Sun, Aug 25, 2019 at 3:15 PM Peter Geoghegan wrote: > > > > I was reminded of this issue from last year, which

Re: pg11.5: ExecHashJoinNewBatch: glibc detected...double free or corruption (!prev)

On Tue, Aug 27, 2019 at 5:52 PM Merlin Moncure wrote: > > On Mon, Aug 26, 2019 at 12:01 PM Tom Lane wrote: > > > > Justin Pryzby writes: > > > On Mon, Aug 26, 2019 at 12:45:24PM -0400, Tom Lane wrote: > > >> However ... there is some pretty interesting inf

no mailing list hits in google

Hackers, [apologies if this is the incorrect list or is already discussed material] I've noticed that mailing list discussions in -hackers and other mailing lists appear to not be indexed by google -- at all. We are also not being tracked by any mailing list aggregators -- in contrast to a

Re: Hstore OID bigger than an integer

On Fri, Aug 23, 2019 at 9:26 AM Roberto Mireles wrote: > > Hello team, > > This is the first time I post here, if you can provide some help, would be > much appreciated. > > I have an application that can not access the database due to OID value for > hstore extension is bigger than an integer

double free in ExecHashJoin, 9.6.12

Server is generally running pretty well, and is high volume. This query is not new and is also medium volume. Database rebooted in about 4 seconds with no damage; fast enough we didn't even trip alarms (I noticed this troubleshooting another issue). We are a couple of bug fixes releases behind

Re: double free in ExecHashJoin, 9.6.12

On Wed, Jul 24, 2019 at 11:01 PM Thomas Munro wrote: > > On Thu, Jul 25, 2019 at 2:39 AM Merlin Moncure wrote: > > Server is generally running pretty well, and is high volume. This > > query is not new and is also medium volume. Database rebooted in > > about 4 seco

Re: Why JIT speed improvement is so modest?

On Mon, Nov 25, 2019 at 9:09 AM Konstantin Knizhnik wrote: > JIT was not able to significantly (times) increase speed on Q1 query? > Experiment with VOPS shows that used aggregation algorithm itself is not > a bottleneck. > Profile also give no answer for this question. > Any ideas? Well, in the

Re: inherits clause for CREATE TYPE? -

On Wed, Dec 18, 2019 at 12:38 PM Pavel Stehule wrote: > > Hi > > I had a talk with one boy about development in plpgsql. He uses table's > functions. More times he uses returns types based on some table type + few > attributes. Now he use a ugly hack - he create a view on table plus some >

Re: Error on failed COMMIT

On Sun, Feb 23, 2020 at 7:59 PM Dave Cramer wrote: > > I think the fact that this is a violation of the SQL SPEC lends considerable > credence to the argument for changing the behaviour. > Since this can lead to losing a transaction I think there is even more reason > to look at changing the

Re: Error on failed COMMIT

On Mon, Feb 24, 2020 at 4:06 PM Vladimir Sitnikov wrote: > > Merlin>My biggest sense of alarm with the proposed change is that it could > Merlin>leave applications in a state where the transaction is hanging there > > How come? > The spec says commit ends the transaction. > Can you please clarify

Re: [HACKERS] emergency outage requiring database restart

On Tue, Jan 3, 2017 at 1:05 PM Peter Eisentraut wrote: > > On 11/7/16 5:31 PM, Merlin Moncure wrote: > > Regardless, it seems like you might be on to something, and I'm > > inclined to patch your change, test it, and roll it out to production. > > If it helps or at least

Re: DB running out of memory issues after upgrade

On Tue, Feb 18, 2020 at 12:10 PM Nagaraj Raj wrote: > > Below are the same configurations ins .conf file before and after updagrade > > show max_connections; = 1743 > show shared_buffers = "4057840kB" > show effective_cache_size = "8115688kB" > show maintenance_work_mem = "259MB" > show

Re: Greatest Common Divisor

On Sat, Dec 28, 2019 at 12:15 PM Fabien COELHO wrote: > > > Bonsoir Vik, > > > I recently came across the need for a gcd function (actually I needed > > lcm) and was surprised that we didn't have one. > > Why not. Proliferation of code in the public namespace; it can displace code that is

Re: Greatest Common Divisor

On Fri, Jan 3, 2020 at 10:24 AM Robert Haas wrote: > > On Fri, Jan 3, 2020 at 10:23 AM Tom Lane wrote: > > Now, those functions were just exposing libc functionality, so there > > wasn't a lot of code to write. There might be a good argument that > > gcd isn't useful enough to justify the

Re: Greatest Common Divisor

On Fri, Jan 3, 2020 at 1:32 PM Robert Haas wrote: > > On Fri, Jan 3, 2020 at 2:27 PM Chapman Flack wrote: > > On 1/3/20 2:11 PM, Robert Haas wrote: > > > and moving things to another schema does not help with that. It does > > > potentially help with the namespace pollution issue, but how much

Re: Greatest Common Divisor

On Mon, Jan 6, 2020 at 6:52 AM Fabien COELHO wrote: > > > Hello Robert, > > >> if (arg1 == PG_INT32_MIN) > >> if (arg2 == 0 || arg2 == PG_INT32_MIN) > >> > >> And possibly a "likely" on the while. > > > > I don't think decoration the code with likely() and unlikely() all > > over the place is

database stuck in __epoll_wait_nocancel(). Are infinite timeouts safe?

I have 5 servers in a testing environment that are comprise a data warehousing cluster. They will typically get each get exactly the same query at approximately the same time. Yesterday, around 1pm, 3 of the five got stuck on the same query. Each of them yields similar stack traces. This

Re: database stuck in __epoll_wait_nocancel(). Are infinite timeouts safe?

On Fri, Mar 13, 2020 at 2:28 PM Andres Freund wrote: > > Hi, > > On March 13, 2020 12:08:32 PM PDT, Merlin Moncure wrote: > >I have 5 servers in a testing environment that are comprise a data > >warehousing cluster. They will typically get each get exactly the > &

Re: Optimize memory allocation code

On Fri, Sep 25, 2020 at 7:32 PM Li Japin wrote: > > > > > On Sep 26, 2020, at 8:09 AM, Julien Rouhaud wrote: > > > > Hi, > > > > On Sat, Sep 26, 2020 at 12:14 AM Li Japin wrote: > >> > >> Hi, hackers! > >> > >> I find the palloc0() is similar to the palloc(), we can use palloc() > >> inside

Re: dblnk_is_busy returns 1 for dead connecitons

On Sun, Aug 2, 2020 at 9:55 PM Merlin Moncure wrote: > > On Sun, Aug 2, 2020 at 7:18 PM Merlin Moncure wrote: > > > > Hackers, > > > > I have a situation that I am observing where dblink_is_busy returns 1 > > even though the connection is long gone. tcp keep

Re: dblnk_is_busy returns 1 for dead connecitons

On Sun, Aug 2, 2020 at 7:18 PM Merlin Moncure wrote: > > Hackers, > > I have a situation that I am observing where dblink_is_busy returns 1 > even though the connection is long gone. tcp keepalives are on and > the connection has been dead for several hours. L

dblnk_is_busy returns 1 for dead connecitons

Hackers, I have a situation that I am observing where dblink_is_busy returns 1 even though the connection is long gone. tcp keepalives are on and the connection has been dead for several hours. Looking at the call for dblink_is_busy, I see that it is a thin wrapper to PQusBusy(). If I attempt

Re: plpgsql variable assignment with union is broken

On Wed, Jan 6, 2021 at 9:39 PM Tom Lane wrote: > > Merlin Moncure writes: > > On Tue, Jan 5, 2021 at 3:40 PM Tom Lane wrote: > >> easter...@verfriemelt.org writes: > >>> i found, that the behaviour of variable assignment in combination with > >&g

Re: plpgsql variable assignment with union is broken

On Tue, Jan 5, 2021 at 3:40 PM Tom Lane wrote: > > easter...@verfriemelt.org writes: > > i found, that the behaviour of variable assignment in combination with > > union is not working anymore: > > DO $$ > > DECLARE t bool; > > begin > > t := a FROM ( SELECT true WHERE false ) t(a)

Re: Zedstore - compressed in-core columnar storage

On Thu, Nov 12, 2020 at 4:40 PM Tomas Vondra wrote: >masterzedstore/pglzzedstore/lz4 > - >copy 1855680922131 >dump 751 905 811 > > And the size of the lineitem

Re: Zedstore - compressed in-core columnar storage

On Mon, Nov 16, 2020 at 10:07 AM Tomas Vondra wrote: > > > On 11/16/20 1:59 PM, Merlin Moncure wrote: > > On Thu, Nov 12, 2020 at 4:40 PM Tomas Vondra > > wrote: > >>masterzedstore/pglzzedstore/lz4 > >> ---

Re: plpgsql variable assignment with union is broken

On Thu, Jan 7, 2021 at 10:55 AM Pavel Stehule wrote: >> Again, if this is narrowly confined to assignment into set query >> operations, maybe this is not so bad. But is it? > > PLpgSQL_Expr: opt_target_list > <--><--><-->from_clause where_clause > <--><--><-->group_clause having_clause

Re: plpgsql variable assignment with union is broken

On Wed, Jan 6, 2021 at 11:07 PM Pavel Stehule wrote: > čt 7. 1. 2021 v 4:20 odesílatel Merlin Moncure napsal: >> >> On Tue, Jan 5, 2021 at 3:40 PM Tom Lane wrote: >> > >> > easter...@verfriemelt.org writes: >> > > i found, that the be

Re: unconstrained memory growth in long running procedure stored procedure after upgrading 11-12

On Tue, Mar 30, 2021 at 7:14 PM Tom Lane wrote: > > Justin Pryzby writes: > > On Tue, Mar 30, 2021 at 04:17:03PM -0500, Merlin Moncure wrote: > >> We just upgraded from postgres 11 to 12.6 and our server is running > >> out of memory and rebooting about 1-2 time

unconstrained memory growth in long running procedure stored procedure after upgrading 11-12

Hello all, We just upgraded from postgres 11 to 12.6 and our server is running out of memory and rebooting about 1-2 times a day.Application architecture is a single threaded stored procedure, executed with CALL that loops and never terminates. With postgres 11 we had no memory issues.

weird interaction between asynchronous queries and pg_sleep

Consider the following snippet create table data as select generate_series(1,100) s; do $d$ begin PERFORM * FROM dblink_connect('test',''); PERFORM * from dblink_send_query('test', 'SELECT * FROM data'); LOOP if dblink_is_busy('test') = 0 THEN PERFORM * FROM

Re: weird interaction between asynchronous queries and pg_sleep

On Thu, Apr 8, 2021 at 1:05 PM Merlin Moncure wrote: > This effect is only noticeable when the remote query is returning > volumes of data. My question is, is there any way to sleep loop > client side without giving up 3x performance penalty? Why is that > that when more local s

Re: MERGE ... RETURNING

On Wed, Nov 1, 2023 at 5:12 AM Dean Rasheed wrote: > On Tue, 31 Oct 2023 at 23:19, Vik Fearing wrote: > > > > On 10/31/23 19:28, Jeff Davis wrote: > > > > > Assuming we have one RETURNING clause at the end, then it creates the > > > problem of how to communicate which WHEN clause a tuple came

Re: MERGE ... RETURNING

On Tue, Oct 24, 2023 at 2:11 PM Jeff Davis wrote: > On Wed, 2023-08-23 at 11:58 +0100, Dean Rasheed wrote: > > Updated version attached, fixing an uninitialized-variable warning > > from the cfbot. > > I took another look and I'm still not comfortable with the special > IsMergeSupportFunction()

Re: Request for comment on setting binary format output per session

On Wed, Oct 4, 2023 at 9:17 AM Peter Eisentraut wrote: > I think intuitively, this facility ought to work like client_encoding. > There, the client declares its capabilities, and the server has to > format the output according to the client's capabilities. That works, > and it also works

Memory knob testing (was Re: Let's make PostgreSQL multi-threaded)

On Fri, Aug 25, 2023 at 8:35 AM Stephen Frost wrote: > Greetings, > > This is getting a bit far afield in terms of this specific thread, but > there's an ongoing effort to give PG administrators knobs to be able to > control how much actual memory is used rather than depending on the > kernel to

Re: dubious warning: FORMAT JSON has no effect for json and jsonb types

On Wed, Aug 16, 2023 at 8:55 AM Peter Eisentraut wrote: > This warning comes from parse_expr.c transformJsonValueExpr() and is > triggered for example by the following test case: > > SELECT JSON_OBJECT('foo': NULL::json FORMAT JSON); > WARNING: FORMAT JSON has no effect for json and jsonb types

Re: pgcon unconference / impact of block size on performance

On Sat, Jun 4, 2022 at 6:23 PM Tomas Vondra wrote: > Hi, > > At on of the pgcon unconference sessions a couple days ago, I presented > a bunch of benchmark results comparing performance with different > data/WAL block size. Most of the OLTP results showed significant gains > (up to 50%) with

feature request: IN clause optimized through append nodes with UNION ALL

In the script below, the presence of an IN clause forces the internal components of the UNION ALL clause to fully compute even though they are fully optimizable. = ANY doesn't have this issue, so I wonder if there is any opportunity to convert the 'slow' variant (see below) to the 'fast' variant.

Re: Request for comment on setting binary format output per session

On Mon, Mar 13, 2023 at 3:33 PM Dave Cramer wrote: > > Dave Cramer > > > On Sat, 4 Mar 2023 at 19:39, Dave Cramer wrote: > >> >> >> On Sat, 4 Mar 2023 at 19:06, Tom Lane wrote: >> >>> Jeff Davis writes: >>> > On Sat, 2023-03-04 at 18:04 -0500, Dave Cramer wrote: >>> >> Most of the clients

Re: Request for comment on setting binary format output per session

On Tue, Mar 21, 2023 at 4:47 PM Jeff Davis wrote: > On Tue, 2023-03-21 at 09:22 -0400, Dave Cramer wrote: > > As Jeff mentioned there is a visibility problem if the search path is > > changed. The simplest solution IMO is to look up the OID at the time > > the format is requested and use the OID

Re: Request for comment on setting binary format output per session

On Mon, Mar 20, 2023 at 7:11 PM Dave Cramer wrote: > > > > On Mon, 20 Mar 2023 at 19:10, Merlin Moncure wrote: > >> >> >> On Mon, Mar 13, 2023 at 3:33 PM Dave Cramer wrote: >> >>> >>> OIDs are a pain to deal with IMO. They will

Re: Request for comment on setting binary format output per session

On Thu, Apr 20, 2023 at 2:52 PM Dave Cramer wrote: > > As promised here is a patch with defines for all of the protocol messages. > I created a protocol.h file and put it in src/includes > I'm fairly sure that some of the names I used may need to be changed but > the grunt work of finding and

Re: Request for comment on setting binary format output per session

On Mon, Apr 3, 2023 at 11:29 AM Dave Cramer wrote: > > participating clients to receive GUC configured format. I do not > >> > think that libpq's result format being able to be overridden by GUC >>> > is a good idea at all, the library has to to participate, and I >>> > think can be made to so

Re: Request for comment on setting binary format output per session

On Wed, Mar 29, 2023 at 11:04 AM Jeff Davis wrote: > I'm not clear on what proposal you are making and/or endorsing? > ha -- was just backing up dave's GUC idea. > 1. Fix our own clients, like psql, to check for binary data they can't > process. > This ought to be impossible IMO. All libpq

Re: Request for comment on setting binary format output per session

On Tue, Mar 21, 2023 at 8:22 AM Dave Cramer wrote: > > On Tue, 21 Mar 2023 at 07:35, Merlin Moncure wrote: > >> >> >> On Mon, Mar 20, 2023 at 7:11 PM Dave Cramer wrote: >> >>> >>> >>> >>> On Mon, 20 Mar 2023 at 19:10, Merl

Re: Let's make PostgreSQL multi-threaded

On Mon, Jun 5, 2023 at 12:25 PM Heikki Linnakangas wrote: > We currently bend over backwards to make all allocations fixed-sized in > shared memory. You learn to live with that, but a lot of things would be > simpler if you could allocate and free in shared memory more freely. > It's no panacea,

Re: Let's make PostgreSQL multi-threaded

On Thu, Jul 27, 2023 at 8:28 AM David Geier wrote: > Hi, > > On 6/7/23 23:37, Andres Freund wrote: > > I think we're starting to hit quite a few limits related to the process > model, > > particularly on bigger machines. The overhead of cross-process context > > switches is inherently higher

Re: Re: How to solve the problem of one backend process crashing and causing other processes to restart?

On Mon, Nov 13, 2023 at 3:14 AM yuansong wrote: > Enhancing the overall fault tolerance of the entire system for this > feature is quite important. No one can avoid bugs, and I don't believe that > this approach is a more advanced one. It might be worth considering adding > it to the roadmap so

Re: MERGE ... RETURNING

On Thu, Feb 29, 2024 at 1:49 PM Jeff Davis wrote: > > Can we get some input on whether the current MERGE ... RETURNING patch > is the right approach from a language standpoint? > MERGE_CLAUSE_NUMBER() seems really out of place to me, it feels out of place to identify output set by number rather