Detecting renamed columns via pgouput in logical replication ?

Hello, when using Postgres 10 or higher, it seems that pgoutput can be used as an output plugin for logical replication. Does this allow to detect column renames ? Or is there a ticket for adding support if the feature does not exist (if it is feasible to implement) ?

Re: Could not resolve host name error in psycopg2

Thank you, I've changed it to use the server's IP address since, so I'll wait to see if the error happens again. On Fri, 17 Apr 2020 at 09:00, Adrian Klaver wrote: > On 4/16/20 5:38 PM, derwin theduck wrote: > > We have been getting this error intermittently (about once a week) in > > Django

Re: performance of first exec of prepared statement

On 4/16/20 6:15 PM, Adrian Klaver wrote: On 4/16/20 4:59 PM, Ted Toth wrote: On Thu, Apr 16, 2020 at 6:29 PM Ted Toth > wrote:     I've noticed that the first exec of an INSERT prepared statement     takes ~5 time longer (I'm using libpq in C and wrapping the calls

Re: Could not resolve host name error in psycopg2

On 4/16/20 5:38 PM, derwin theduck wrote: We have been getting this error intermittently (about once a week) in Django with channels since switching from a local database server to a hosted one: could not translate host name "timescaledb" to address: Name or service not known Connecting

Re: timestamp and timestamptz

Adrian Klaver wrote: > On 4/16/20 1:23 AM, raf wrote: > > Steve Baldwin wrote: > > > > > I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' > > > time zone in a timestamptz column. > > > > > > Try doing this before re-running your test: > > > > > > set timezone to 'utc'; >

Could not resolve host name error in psycopg2

We have been getting this error intermittently (about once a week) in Django with channels since switching from a local database server to a hosted one: could not translate host name "timescaledb" to address: Name or service not known Connecting directly to the server with psycopg2 in the python

Re: Using of --data-checksums

On Thu, Apr 16, 2020 at 03:47:34PM -0700, Jeremy Schneider wrote: > Data checksums are a hard requirement across the entire RDS PostgreSQL > fleet - we do not allow it to be disabled in RDS. I've definitely seen a > lot of hard evidence (for example, customer cases I've personally been > involved

Re: performance of first exec of prepared statement

On 4/16/20 4:59 PM, Ted Toth wrote: On Thu, Apr 16, 2020 at 6:29 PM Ted Toth > wrote: I've noticed that the first exec of an INSERT prepared statement takes ~5 time longer (I'm using libpq in C and wrapping the calls to time them) then subsequent exec's

Re: performance of first exec of prepared statement

On Thu, Apr 16, 2020 at 6:29 PM Ted Toth wrote: > I've noticed that the first exec of an INSERT prepared statement takes ~5 > time longer (I'm using libpq in C and wrapping the calls to time them) then > subsequent exec's is this the expected behavior and if so is there any > thing I can do to

performance of first exec of prepared statement

I've noticed that the first exec of an INSERT prepared statement takes ~5 time longer (I'm using libpq in C and wrapping the calls to time them) then subsequent exec's is this the expected behavior and if so is there any thing I can do to mitigate this affect? Ted

Re: Using unlogged tables for web sessions

Stephen Carboni writes: > Hello. > > I was wondering if anyone was using unlogged tables for website > sessions in production. I'm interested if it breaks the prevailing > opinion that you don't put sessions in PG. This really depends on what you define as website session data and what

Re: File Foreign Table Doesn't Exist when in Exception

On 4/16/20 3:59 PM, Virendra Kumar wrote: Please reply to list also. Ccing list. Thank you Adrian! I know the data is malformed I am more concerned about the behavior that the foreign table itself doesn't exists when it has malformed data and is being queried in anonymous block.

Re: File Foreign Table Doesn't Exist when in Exception

On 4/16/20 3:39 PM, Virendra Kumar wrote: Hello Everyone, I have a weird situation with file_fdw extension when I am creating a foreign table in anonymous block. Here is setup: Create extension and server: == postgres=# create extension file_fdw; CREATE EXTENSION

File Foreign Table Doesn't Exist when in Exception

Hello Everyone, I have a weird situation with file_fdw extension when I am creating a foreign table in anonymous block. Here is setup: Create extension and server:== postgres=# create extension file_fdw; CREATE EXTENSION postgres=# CREATE SERVER log_server FOREIGN DATA WRAPPER

Re: Using of --data-checksums

On Sun, Apr 12, 2020 at 4:23 PM Tom Lane wrote: > Magnus Hagander writes: > > And FWIW, I do think we should change the default. And maybe spend some > > extra effort on the message coming out of pg_upgrade in this case to make > > it clear to people what their options are and exactly what to

Need for box type with 1/4 precision and gist indexes

Hi all! I would like to use postgres for time-series data and need geometric index. For this reason I am using timescale extension and GiST index on box type. Overall ingest and query performance is fantastic! But I would like to optimize disk usage a bit. More technically my table schema: CREATE

Using unlogged tables for web sessions

Hello. I was wondering if anyone was using unlogged tables for website sessions in production. I'm interested if it breaks the prevailing opinion that you don't put sessions in PG.

Re: possibilities for SQL optimization

On Thu, Apr 16, 2020 at 10:47 AM Michael Lewis wrote: > My other thought was to range partition by pixelID + brin index. > > I would expect brin index to be INSTEAD of partitioning. You didn't share > buffer hits, which I expect were 100% on the subsequent explain analyze > runs, but

Re: Recursive Queries

thanks for the suggestion. tablefunc extension might be the easiest one On Thu, Apr 16, 2020 at 9:46 PM Edward Macnaghten wrote: > On 16/04/2020 14:36, Edward Macnaghten wrote: > > On 16/04/2020 09:35, Alex Magnum wrote: > >> Hi, > >> I have a simple table with singup timestamps > >> > >> What

Re: timestamp and timestamptz

On 4/16/20 1:23 AM, raf wrote: Steve Baldwin wrote: I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' time zone in a timestamptz column. Try doing this before re-running your test: set timezone to 'utc'; What you are seeing in your test is an artifact of that timezone

Re: possibilities for SQL optimization

> > My other thought was to range partition by pixelID + brin index. >>> I would expect brin index to be INSTEAD of partitioning. You didn't share buffer hits, which I expect were 100% on the subsequent explain analyze runs, but the index scan may still be faster if the planner knows it only

Re: possibilities for SQL optimization

Hi Chris, 40ms for a select on 302 millions rows sounds reasonable. What I would try to do is group by binary patterns on pixelId to reduce the number of cases (use some arithmetics) and use a calculated index but I'm not sure of how much you would save. On Thu, Apr 16, 2020 at 4:57 PM Chris

Re: possibilities for SQL optimization

disastrous :) Planning Time: 7.569 ms Execution Time: 316969.474 ms On Thu, Apr 16, 2020 at 9:23 AM Pavel Stehule wrote: > > > čt 16. 4. 2020 v 16:08 odesílatel Chris Stephens > napsal: > >> PG12 >> RHEL 8 >> >> I suspect there's little I can do to get the following query to run >>

Re: possibilities for SQL optimization

čt 16. 4. 2020 v 16:08 odesílatel Chris Stephens napsal: > PG12 > RHEL 8 > > I suspect there's little I can do to get the following query to run > faster/more efficiently but thought I'd post to list and confirm. > > Caveat: I'm coming from an Oracle background and am extremely wet behind > ears

possibilities for SQL optimization

PG12 RHEL 8 I suspect there's little I can do to get the following query to run faster/more efficiently but thought I'd post to list and confirm. Caveat: I'm coming from an Oracle background and am extremely wet behind ears w/ respect to postgresql (please be kind :)). Right now, we can't

Re: Recursive Queries

On 16/04/2020 14:36, Edward Macnaghten wrote: > On 16/04/2020 09:35, Alex Magnum wrote: >> Hi, >> I have a simple table with singup timestamps >> >> What I would like to do is to create a table as shown below that >> displays the counts per our for the past n dates. SELECT hour, SUM(CASE(WHEN date

Re: Recursive Queries

Hi Alex, On Thu, Apr 16, 2020 at 10:36 AM Alex Magnum wrote: > Hi, > I have a simple table with singup timestamps > > What I would like to do is to create a table as shown below that displays > the counts per our for the past n dates. > > I can do this with a function but is there an easy way

Re: Recursive Queries

On 16/04/2020 09:35, Alex Magnum wrote: > Hi, > I have a simple table with singup timestamps > > What I would like to do is to create a table as shown below that > displays the counts per our for the past n dates. Various ways, but for me... SELECT hour, SUM(CASE(WHEN date = date THEN 1 ELSE

Re: Recursive Queries

You don't want recursion, you want pivot table (Excel) behavior to reformat rows into columns. The easiest way to get this data in its raw form would be to group by date and hour of day and compute the count. If you have the option to add extensions in your environment, then you should be able to

Re: Recursive Queries

On Thu, Apr 16, 2020 at 2:49 PM Rob Northcott wrote: > > From: Alex Magnum > > What I would like to do is to create a table as shown below that displays the > counts per our for the past n dates. > > > > I can do this with a function but is there an easy way to use recursive > queries? > >

RE: Recursive Queries

From: Alex Magnum Sent: 16 April 2020 09:36 To: Postgres General Subject: Recursive Queries Hi, I have a simple table with singup timestamps What I would like to do is to create a table as shown below that displays the counts per our for the past n dates. I can do this with a function but is

Re: pg_restore: could not close data file: Success

At Thu, 16 Apr 2020 14:40:09 +0900, Michael Paquier wrote in > On Thu, Apr 16, 2020 at 12:08:09PM +0900, Kyotaro Horiguchi wrote: > > I'm surprised to find an old thread about the same issue. > > > >

SV: timestamp and timestamptz

Fra: Magnus Hagander Sendt: 16. april 2020 10:28 Til: Niels Jespersen Cc: pgsql-general@lists.postgresql.org Emne: Re: timestamp and timestamptz On Thu, Apr 16, 2020 at 6:12 AM Niels Jespersen mailto:n...@dst.dk>> wrote: Fra: Magnus Hagander mailto:mag...@hagander.net>> Sendt: 15. april

Recursive Queries

Hi, I have a simple table with singup timestamps What I would like to do is to create a table as shown below that displays the counts per our for the past n dates. I can do this with a function but is there an easy way to use recursive queries? * Counts per hour for given date* *HR

Re: timestamp and timestamptz

On Thu, Apr 16, 2020 at 6:12 AM Niels Jespersen wrote: > > > > > *Fra:* Magnus Hagander > *Sendt:* 15. april 2020 20:05 > *Til:* Niels Jespersen > *Cc:* pgsql-general@lists.postgresql.org > *Emne:* Re: timestamp and timestamptz > > > > > > > > On Wed, Apr 15, 2020 at 7:50 PM Niels Jespersen

Re: timestamp and timestamptz

Steve Baldwin wrote: > I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' > time zone in a timestamptz column. > > Try doing this before re-running your test: > > set timezone to 'utc'; > > What you are seeing in your test is an artifact of that timezone setting. > >

Re: timestamp and timestamptz

čt 16. 4. 2020 v 9:19 odesílatel raf napsal: > David G. Johnston wrote: > > > On Wed, Apr 15, 2020 at 4:53 PM raf wrote: > > > > > I don't see much difference in storing a timestamptz in UTC or a > > > timestamptz > > > in CET. As long as the intended offset from UTC is recorded (which it > is

Re: timestamp and timestamptz

I'm pretty sure you are mistaken. Postgres doesn't store the 'creating' time zone in a timestamptz column. Try doing this before re-running your test: set timezone to 'utc'; What you are seeing in your test is an artifact of that timezone setting. Steve On Thu, Apr 16, 2020 at 5:19 PM raf

Re: timestamp and timestamptz

David G. Johnston wrote: > On Wed, Apr 15, 2020 at 4:53 PM raf wrote: > > > I don't see much difference in storing a timestamptz in UTC or a > > timestamptz > > in CET. As long as the intended offset from UTC is recorded (which it is > > in a timestamptz) it should be fine. > > I only really