delete inside for plpgsql loop on same relation?

Hi, Are there guarantees about how this plpgsql behaves? It’s deleting from a table while it loops over it. So far it seems like the delete is logically after the select, as I hoped, and doesn’t interfere. for row in select * from some_stuff loop delete from some_stuff where …

Re: Is array_append O(n)?

> On Jun 18, 2019, at 7:37 PM, Alvaro Herrera wrote: > > On 2019-Jun-18, Rob Nikander wrote: > >> Does `array_append(arr, elt)` create a new array and copy everything? >> In other words, is it O(n) or O(1)? […] > > Starting with 9.5, there's an optim

Is array_append O(n)?

Hi, Does `array_append(arr, elt)` create a new array and copy everything? In other words, is it O(n) or O(1)? I’m trying to use plpgsql and realizing I don’t really have my usual data structures for basic algorithms the way I’d normally write them. I probably shouldn’t be treating arrays like J

Re: Async client libraries - not worth it?

> On Jun 17, 2019, at 3:57 PM, Dave Cramer wrote: > […] Postgres can pipeline requests if the client is written correctly so it > is conceivable that this would be much faster. Can the JDBC driver do this? I don’t see it documented anywhere.

Re: Async client libraries - not worth it?

> On Jun 17, 2019, at 1:12 PM, Dave Cramer wrote: > > https://www.techempower.com/benchmarks/#section=data-r17&hw=ph&test=db > > > Seems to be worth it. > > Now it appears that ADBA is going to die on the vine, R2DBC an

Async client libraries - not worth it?

Hi, I’m writing a new web app, and I’ve been experimenting with some async DB access libraries [1]. I also see some discussion online about a future Java standard to replace or supplement JDBC with an async API. While I understand the benefits of async in some situations, it seems to me that t

Re: arrays of composite types, and client drivers like JDBC

> On Jun 15, 2019, at 1:47 PM, Dave Cramer wrote: > > Basically because java would have to create a type dynamically to parse the > data into. > There's nothing inherently difficult about parsing the data, the problem is > what do we put it into ? (I accidentally replied off-list, so resend

arrays of composite types, and client drivers like JDBC

Hi, I'm experimenting with Java client libraries (the usual JDBC and some other async projects, eg [1]). So far, I'm not finding ways to select/read composite types without ugly string parsing. The simple cases are okay, but if I have a column that is an array of composites, the client library mig

Re: procedures and transactions

> On Feb 20, 2019, at 10:07 AM, Peter Eisentraut > wrote: > > You can run SET TRANSACTION ISOLATION LEVEL in a procedure. I tried that before but I get this error: create or replace procedure t_test(n integer) as $$ begin set transaction isolation level serializable;

Re: procedures and transactions

ation’)`. Rob > On Feb 19, 2019, at 2:38 PM, David G. Johnston > wrote: > > On Tuesday, February 19, 2019, Rob Nikander <mailto:rob.nikan...@gmail.com>> wrote: > Are procedures not allowed to commit/rollback if they are called within in > an outer transaction? > &g

procedures and transactions

Hi, I’m trying to understand how procedures work with transactions. I tried the code below - it’s a simple procedure to print some notices and commit a transaction. If I call it from psql after a `begin`, then it gives an error. What does that error mean? Are procedures not allowed to commit/ro

Re: querying both text and non-text properties

> On Dec 4, 2018, at 4:59 PM, Laurenz Albe wrote: > > You have two options: > > A combined index: > > CREATE EXTENSION btree_gin; > CREATE INDEX ON fulltext USING gin (to_tsvector('english', doc), color); > > That is the perfect match for a query with > > WHERE color = 'red' AND to_tsve

querying both text and non-text properties

Hi, I’ve got an application where I’d like to search a collection of objects based on various properties, some text and others non-text (bools, enums, ints, etc). I’ve used full text search before, following the PG docs to set up a index on a ts_vector. And of course I’ve used normal indexes b

Re: help with aggregation query across a second text array column

> On Nov 12, 2018, at 9:40 AM, Scot Kreienkamp > wrote: > … I’m not too confident in my answer here (there could be a better way), but this might help. You could use the `unnest` function to transform the array into multiple rows. For example, given a table like create table t1 (env t

Re: query patterns for multipass aggregating

> On Nov 11, 2018, at 12:54 PM, Andrew Gierth > wrote: > … Thank you that is very helpful. Could the CTE’s theoretically be optimized in a future version of PG, to work like the subqueries? I like to use them to give names to intermediate results, but I’ll stay away from them for now. Rob

query patterns for multipass aggregating

Hi, I’ve run into this pattern a few times, and I usually get a little confused. I’m wondering if there are some common solutions or techniques. Simplified example: I have tables `items`, `colors`, and `images`. Items have many colors, and many images. I want a query to list items, with their

Re: reclaiming space from heavily used tables?

I don’t see how I can use that extension. If some people are saying it’s dangerous and others are saying it’s fine, I don’t have the time to drill down into PG internals so that I can judge for myself. I’ll probably try the two table idea outlined in my original message. > On Dec 18, 2017, at 5

reclaiming space from heavily used tables?

Hi, I've got a large table from which I'd like to completely reclaim space. I read the docs and it sounds like I can’t run `vacuum full`, because this table is accessed constantly and can’t have downtime. Assuming that’s true, what do you think of the following idea? Is there a better alternati