Re: Even more OT: Ditto machines [was: bottom / top posting]

On 11/6/21 4:52 am, Dean Gibson (DB Administrator) wrote: Tom mentioned "old-timers."  Remember "Ditto" machines?  Remember the odor?  They were in common use when I was in high school (1960). https://en.wikipedia.org/wiki/Mimeograph Was intimately acquainted with them during my military servi

Re: Postgresql crashdown and recovery failure

On 6/10/21 10:30 PM, xiebin (F) wrote: Hi, My database (Postgresql 12.5) server uses remote storage as data directory. I powered off the database server and started postgresql (same version) on another server, with same data. However it failed. I checked wal logs and found that the content a

Postgresql crashdown and recovery failure

Hi, My database (Postgresql 12.5) server uses remote storage as data directory. I powered off the database server and started postgresql (same version) on another server, with same data. However it failed. I checked wal logs and found that the content around latest checkpoint is empty. (about 1

Postgresql crashdown and recovery failure

Hi, My database (Postgresql 12.5) server uses remote storage as data directory. I powered off the database server and started postgresql (same version) on another server, with same data. However it failed. I checked wal logs and found that the content around latest checkpoint is empty. (about 1

Re: bottom / top posting

On Wed, Jun 9, 2021 at 11:42 PM Dean Gibson (DB Administrator) wrote: ... > Top-posting has been the predominantly common practice in the business & > government world for decades, & it is easy to adapt to. Just like HTML eMail (within reason) & more than 80 columns on a line. Yeah, but I disli

Re: Even more OT: Ditto machines [was: bottom / top posting]

On Thu, Jun 10, 2021 at 10:52 PM Dean Gibson (DB Administrator) wrote: > Tom mentioned "old-timers." Remember "Ditto" machines? Remember the odor? > They were in common use when I was in high school (1960). > https://en.wikipedia.org/wiki/Mimeograph I was in the spanish equivalent of high sch

Re: bottom / top posting

Because otherwise it's hard to figure out what some top posters are talking about. Basques, Bob (CI-StPaul) said on Thu, 10 Jun 2021 16:08:14 + >All, > >First, I AM an old-timer, and prefer the top posting for a number of >reasons. I’ve tried the Digest versions of lists in the past and the

Adding table partition slow when there is default partition with data (primary key not used to check partition condition)

Hi all, I am doing following: CREATE TABLE "change" ( transaction_id uuid NOT NULL, id int4 NOT NULL, change_type varchar NOT NULL, object_type varchar NOT NULL, object_content jsonb NOT NULL, category_id uuid NOT NULL, CONSTRAINT change_pkey PRIMARY KEY (transaction_id, id) ) parti

Re: Even more OT: Ditto machines [was: bottom / top posting]

Indeed I "ditto" remember them! And the smell was the prize for running an errand for the teacher to pick up copies from the Mimeograph room. -Tom Hand-cranked Gestetner, anyone?  Blotchy ink, indelible mess but made one feel a brother of Gutenberg.

Re: Even more OT: Ditto machines [was: bottom / top posting]

On Thu, Jun 10, 2021 at 15:52 Dean Gibson (DB Administrator) < postgre...@mailpen.com> wrote: > On 2021-06-10 13:21, Peter J. Holzer wrote: > > On 2021-06-09 14:41:47 -0700, Dean Gibson (DB Administrator) wrote: > > ... when paper memos were the norm ... > > ... before photocopiers were invented..

Even more OT: Ditto machines [was: bottom / top posting]

On 2021-06-10 13:21, Peter J. Holzer wrote: On 2021-06-09 14:41:47 -0700, Dean Gibson (DB Administrator) wrote: ... when paper memos were the norm ... ... before photocopiers were invented... Tom mentioned "old-timers."  Remember "Ditto" machines?  Remember the odor?  They were in common use

Re: bottom / top posting

On 6/10/21 12:08 PM, Basques, Bob (CI-StPaul) wrote: Alternatively, where I deem appropriate I will do inline posting/clipping, but I always announce that at the top (posting) of the response. "where you deem appropriate" you will do that, and where "I deem appropriate" I will stop reading w

Re: index unique

> On 8 Jun 2021, at 22:50, Thomas Kellerer wrote: > > Marc Millas schrieb am 03.06.2021 um 22:51: >> on a table we need a primary key and to get a unique combinaison, we need 3 >> columns of that table: >> 1 of type integer, >> 1 of type text, >> 1 of type geometry >> > > How do you define t

Re: bottom / top posting

On 2021-06-10 01:23:34 -0400, Steve Litt wrote: > Dean Gibson (DB Administrator) said on Wed, 9 Jun 2021 14:41:47 -0700 > >Top-posting has been the predominantly common practice in the business > >& government world for decades, & it is easy to adapt to.  > > OF COURSE! In business you need the C

Re: bottom / top posting

On 2021-06-09 14:41:47 -0700, Dean Gibson (DB Administrator) wrote: > > On Mon, Jun 7, 2021 at 07:53:30PM +0200, Francisco Olarte wrote: > > ... properly scanning a top posted one takes much longer. > > > Not here. > > > I find top-posting moderately offensive, like sayin

Re: Implicit table removal from logical replication publication

pg_subscription_rel pg_publication_rel have the relation part of the publication concerned. OP has an issue to figure out if publication has a list of tables not in sync in subscription and has subscription broken. there may be ways to query tables on subscriber dbs via dblink or fdw, but there is

Re: Implicit table removal from logical replication publication

On Thu, Jun 10, 2021 at 12:39 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Wow, the drop table silently removes entry from publication without any > logs. > > I could not find any monitoring view to help me figure out if the > publication is broken due to ddl change. > pg_stat_re

Re: Implicit table removal from logical replication publication

Wow, the drop table silently removes entry from publication without any logs. I could not find any monitoring view to help me figure out if the publication is broken due to ddl change. pg_stat_replication on publisher, and pg_stat_subscription on subscriber only help with lsn based lag. unless thi

Re: How to pass a parameter in a query to postgreSQL 12

On 2021-06-09 14:51:46 -0500, Hassan Camacho Cadre wrote: > I recently installed a postgreSQL v12, in previous version 8.3 in all my > queries I pass parameters using the character : > > SELECT > >   public.tabla.id > > FROM  > >   public.tabla > > WHERE   > >   public.tabla.id = :a That loo

Implicit table removal from logical replication publication

Hi Experts I had a case where a team member deleted and recreated an empty table which participated in logical replication. After that action the table no longer was part of the replication and new inserts were not copied to the subscribers. How can I check existing publication for the list o

Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging

On Thu, Jun 10, 2021, 09:06 Hannes Kühtreiber wrote: > so we have to wait for psql14, or is there something else to this effect > in an earlier release, that I failed to find? > By definition every feature in a vX.0 release note is new as of that release. The sentence at the top of that section

Re: bottom / top posting

All, First, I AM an old-timer, and prefer the top posting for a number of reasons. I’ve tried the Digest versions of lists in the past and they didn’t quite work out (for me). Top post, newest on top, older stuff indented, can be clipped wherever, although I prefer to see everything if I need

Re: Logical Replication: SELECT pg_catalog.set_config Statement

Hello Jeremy, thanks for your input (and sorry for the delay). for our monitoring we query like this SELECT EXTRACT(epoch FROM (LOCALTIMESTAMP - pg_stat_activity.query_start))::integer AS age FROM pg_stat_activity WHERE pg_stat_activity.state = 'active' AND query NOT LIKE 'autovacuum:%' ORDER

Re: Logical Replication: SELECT pg_catalog.set_config Statement appears to be hanging

Hello Tom, thanks for the clarification. We are stumbling about this in psql (11.11) I have now searched the release logs to find out when this will be resolved. I found the following matching entry: https://www.postgresql.org/docs/14/release-14.html

Re: SELECT in VCHAR column for strings with TAB

Matthias Apitz writes: > I want to search in a VCHAR column for a string with two TAB at the end. > I tried some things w/o any luck, like: > select * from acq_vardata where name=concat('Test202112', 9, 9); > select * from acq_vardata where name=concat('Test202112', '\t\t'); By default, backslas

Re: SELECT in VCHAR column for strings with TAB

On 10/06/2021 14:30, Matthias Apitz wrote: Hello, I want to search in a VCHAR column for a string with two TAB at the end. I tried some things w/o any luck, like: select * from acq_vardata where name=concat('Test202112', 9, 9); select * from acq_vardata where name=concat('Test202112', '\t\t');

Re: order by

Luca Ferrari writes: > The ORDER BY rejects non existent columns (right) but accepts the > table itself as an ordering expression. As others have noted, this is basically taking the table name as a whole-row variable, and then sorting per the rules for composite types. I write to point out that

SELECT in VCHAR column for strings with TAB

Hello, I want to search in a VCHAR column for a string with two TAB at the end. I tried some things w/o any luck, like: select * from acq_vardata where name=concat('Test202112', 9, 9); select * from acq_vardata where name=concat('Test202112', '\t\t'); Any ideas? Thx matthias -- Matth

Re: index unique

Thanks Thomas, but, as stated after the first post, the need was for a PK as asked by postgres (ie. for tech needs, not for functionnal needs) up to now, looks like we must create a PK (and so, the associated index) just to answer logical replication needs.(and qgis which also needs a PK) that ind

Questions about support function and abbreviate.

Hi all, I am trying to implement a sort support function for geometry data types in PostGIS with the new feature `SortSupport`. However, I have a question about this. I think it is hardly to apply a sort support function to a complex data type without the `abbrev_converter` to simply the data str

Re: order by

On Thu, 2021-06-10 at 10:39 +0200, Luca Ferrari wrote: > I also realized that this "table to tuples" expansion works for GROUP BY too. > However, I'm not able to find this documented in GROUP BY, WHERE, > ORDER BY clauses sections into select documentation > https://www.postgresql.org/docs/12/sql-s

Re: order by

On Thu, Jun 10, 2021 at 8:40 PM Luca Ferrari wrote: > On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain > > when you provide a table in query in the order by clause, it is > > ordered by cols of that table in that order. > > Clever, thanks! > I also realized that this "table to tuples" expansion wo

Re: order by

On Thu, Jun 10, 2021 at 10:15 AM Vijaykumar Jain wrote: > you can run an explain analyze to check what is going on, > when you provide a table in query in the order by clause, it is > ordered by cols of that table in that order. Clever, thanks! I also realized that this "table to tuples" expansio

Re: order by

> Any hint? you can run an explain analyze to check what is going on, when you provide a table in query in the order by clause, it is ordered by cols of that table in that order. create table t(id int, value int); postgres=# explain (analyze,verbose) select * from t order by t;

order by

Hi all, this could be trivial, but I found as an accident the following: pgbench=> create table t as select v from generate_series( 1, 2 ) v; SELECT 2 pgbench=> select * from t order by foo; ERROR: column "foo" does not exist LINE 1: select * from t order by foo;

Re: PostgreSQL replication lag - Suggestions and questions

> My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication. I am not sure of the difference between read replica and a hot standby. At least with later versions hot standby allows read queries. I mean unless you do not want quer