Re: Slick way to update multiple tables.

On 4/1/21 11:16 AM, Theodore M Rolle, Jr. wrote: I SELECT items from three tables. It seems that the only way to put updated fields into their proper table is to query /each/ field, then UPDATE /each/ table. Am I missing something here? Is there a way to do this automatically? Did you join

Re: Is replacing transactions with CTE a good idea?

On 4/1/21 10:04 AM, Rob Sargent wrote: On 4/1/21 8:58 AM, Brian Dunavant wrote: On Thu, Apr 1, 2021 at 10:49 AM Glen Huang > wrote: If I decide to replace all my transaction code with CTE, will I shoot myself in the foot down the road? I do this all the time a

Re: Upgrading from 11 to 13

On 4/3/21 12:47 PM, Koen De Groote wrote: If an upgrade process has a part where old functionality doesn't work anymore, or anything at all breaks, then it's not "you can just upgrade no problems". Major versions are allowed to break compatibility, so there is always a chance for issues. Th

Re: Upgrading from 11 to 13

On Sat, Apr 3, 2021 at 09:47:10PM +0200, Koen De Groote wrote: > If an upgrade process has a part where old functionality doesn't work anymore, > or anything at all breaks, then it's not "you can just upgrade no problems". Well, stopping at every major release between major releases dosen't help

Re: Upgrading from 11 to 13

If an upgrade process has a part where old functionality doesn't work anymore, or anything at all breaks, then it's not "you can just upgrade no problems". On Sat, Apr 3, 2021 at 8:50 PM Adrian Klaver wrote: > On 4/3/21 10:37 AM, Koen De Groote wrote: > > Yes that's it. Probably something to ale

What is the expected difference in performance between querying a all partitions of a partitioned table and non partitioned one?

Hi there, my question is, shouldn't a query that spans all partitions in a partionend table be roughly the same in performance as one on a non partionend table? I'm getting roughly 50% slower performance on a partioned table. I have tried this on a macbook and ubuntu server. More details are belo

Re: Upgrading from 11 to 13

On 2021-04-01 21:56:17 -0400, Bruce Momjian wrote: > On Thu, Apr 1, 2021 at 09:55:28PM -0400, Bruce Momjian wrote: > > Here it is with descriptions: > > Sorry, please ignore. Too late. Now we all know the code names for previous PostgreSQL releases. hp -- _ | Peter J. Holzer|

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

On 2021-03-30 12:36:16 -0700, Adrian Klaver wrote: > On 3/30/21 10:31 AM, Bryn Llewellyn wrote: > > > adrian.kla...@aklaver.com wrote: > > > > > > The point is horology is cultural, see non-Western calendars and > > > alternate time keeping methods. Trying to maintain a distinction > > > between t

Re: Upgrading from 11 to 13

On 4/3/21 10:37 AM, Koen De Groote wrote: Yes that's it. Probably something to alert people to. If they have this set up, they can't "just to straight to 13". Yes you can. You run into the same issue going from 11 --> 12 --> 13, you would just hit it in a different portion of the process.

Re: Debugging leaking memory in Postgresql 13.2/Postgis 3.1

On 31.03.2021 20:27, Paul Ramsey wrote: On Mar 31, 2021, at 11:24 AM, Tom Lane wrote: If that's not it, then the leak must be accumulating through plain old malloc calls. There's not much of that in the core backend (although if you use ispell text search dictionaries, maybe [1] is relevant),

Re: How to deny access to Postgres when connected from host/non-local

On 4/2/21 7:06 PM, A. Reichstadt wrote: Hello, I try to deny access to all databases on my server if the user “postgres" tries to connect from a non-local host. Here is what I did in pg_hba.conf: # TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix do

How to deny access to Postgres when connected from host/non-local

Hello, I try to deny access to all databases on my server if the user “postgres" tries to connect from a non-local host. Here is what I did in pg_hba.conf: # TYPE DATABASEUSERADDRESS METHOD # "local" is for Unix domain socket connections only local all

Re: Upgrading from 11 to 13

Yes that's it. Probably something to alert people to. If they have this set up, they can't "just to straight to 13". On Fri, Apr 2, 2021 at 1:32 AM Alvaro Herrera wrote: > On 2021-Apr-02, Koen De Groote wrote: > > > I seem to recall that going from 11 to 12, a certain configuration file > was >

Re: ignore tablespace in schema definition queries

Hi Allan and Mark, Thank you for your thoughts. I ended up writing a 1000 lines SQL file with the SQL commands for creation of the 1000 tablespaces. I keep the file with my bootstrap scripts and I'm loading with the psql client. It works just fine and it is quite very fast. I was a bit afraid it

Re: ignore tablespace in schema definition queries

The solution depends on how you are creating the tables. For example: the pg_restore has option —-no-tablespaces. With this option, all objects will be created in whichever tablespace is the default during restore. The pg_dump has similar. If you are running CREATE TABLE statements that have ha

Re: questions about wraparound

On Fri, Apr 2, 2021 at 10:29 AM Jehan-Guillaume de Rorthais wrote: > > On Thu, 18 Mar 2021 09:56:16 +0100 > Luca Ferrari wrote: > [...] > > Therefore my question is: shouldn't autovacuum be able to freeze other > > tables/databases? I mean, the wraparound problem in this scenario will > > cause p

Re: ignore tablespace in schema definition queries

On Sat, Apr 3, 2021 at 1:59 PM Joao Miguel Ferreira < joao.miguel.c.ferre...@gmail.com> wrote: > Hello all, > > I have a big set of migration queries (that I do not control) that I must > run on my automatic test database, in order to set ip up and run tests. > These queries create all sorts of th

ignore tablespace in schema definition queries

Hello all, I have a big set of migration queries (that I do not control) that I must run on my automatic test database, in order to set ip up and run tests. These queries create all sorts of things like indexes, tables, and so. But they also include the specification of the tablespace they expect