Re: Best Approach for Swapping a Table with its Copy

> On 12 Feb 2025, at 22:27, Marcelo Fernandes wrote: > > On Thu, Feb 13, 2025 at 10:02 AM Adrian Klaver > > Also pulling in your question in the other reply: > >> Why can't you just add the exclusion constraint to the original table? > > With unique constraints, one can use a unique index t

Re: Best Approach for Swapping a Table with its Copy

On Fri, 2025-02-14 at 11:59 +1300, Marcelo Fernandes wrote: > On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: > > The big challenge here would be to do the swap in a safe way. How do > > you intend to guarantee that the foreign keys are valid without a table > > scan? How do you handle concu

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 12:35 PM Adrian Klaver wrote: > 1) In a previous post you said: > > "Yes, in this scenario the copy is already created, and triggers keep > the copy in sync with the original table." > > In that case the copy will already have TOAST tables associated with it. If we follow

Re: Best Approach for Swapping a Table with its Copy

On 2/13/25 14:59, Marcelo Fernandes wrote: On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: Yes, but only if you are willing to write C code that runs inside the database server. That way, you can do anything (and cause arbitrary damage). The big challenge here would be to do the swap in a

Re: Best Approach for Swapping a Table with its Copy

On Fri, Feb 14, 2025 at 4:09 AM Greg Sabino Mullane wrote: > It is surgically replacing all pointers to the old data with pointers to the > new data. Yes, with lots of system catalog shenanigans. Love your analogy Greg, thanks for that. > It's technically possible to do something similar for yo

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 7:37 PM Laurenz Albe wrote: > Yes, but only if you are willing to write C code that runs inside the > database server. That way, you can do anything (and cause arbitrary > damage). > > The big challenge here would be to do the swap in a safe way. How do > you intend to gu

Re: libc to libicu via pg_dump/pg_restore?

On 2/13/25 11:57, Paul Foerster wrote: Hi Adrian, I'm not at work anymore and won't be until Monday (long weekend 🤣). So I don't have the exact case handy. However, I tried on my home database clusters (15.10 and 17.3). Seems, at least here at home, only using -C works. I don't know (yet)

Re: libc to libicu via pg_dump/pg_restore?

Hi Adrian, > On 13 Feb 2025, at 19:05, Adrian Klaver wrote: > > Then run pg_restore -s -f db_name.sql against whatever is the dump file > produced by pg_dump -Fc -Z1 ... > > It will create a plain text version of the schema definitions, no data in the > file db_name.sql. Then you can see if G

Re: psql command line editing

On 2/13/25 12:11, Ron Johnson wrote: It appears that the RHEL default /etc/initrc file defines a bunch of escape sequences "for linux console and RH/Debian xterm" cursor keys when in emacs mode. Our servers are RHEL, my ssh client emulates xterm, and is in the default emacs mode.  Thus, th

Re: psql command line editing

On Wed, Feb 12, 2025 at 3:29 PM Adrian Klaver wrote: > On 2/12/25 12:03, Ron Johnson wrote: > > On Wed, Feb 12, 2025 at 2:29 PM Tom Lane > > wrote: > > > There's got to be something in your shell config which says "use emacs > > keys" instead of "use vi keys". > > Fro

Re: Ideas about presenting data coming from sensors

13.02.2025 10:54:05 Achilleas Mantzios - cloud : > If we followed a strict normalized approach then we would create additionally > 11 tables each tag of type c) . And we are not guaranteed that the same tags > would have the same structure over the whole fleet/manufacturers. So we are > thinki

Re: libc to libicu via pg_dump/pg_restore?

On 2/13/25 09:31, Paul Foerster wrote: Hi Adrian, On 13 Feb 2025, at 17:40, Adrian Klaver wrote: Per: https://www.postgresql.org/docs/current/ddl-priv.html "If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges en

Re: libc to libicu via pg_dump/pg_restore?

Hi Adrian, > On 13 Feb 2025, at 17:40, Adrian Klaver wrote: > > Per: > > https://www.postgresql.org/docs/current/ddl-priv.html > > "If the “Access privileges” column is empty for a given object, it means the > object has default privileges (that is, its privileges entry in the relevant > sys

Re: psql command line editing

On 2/13/25 09:20, Vincent Veyron wrote: On Wed, 12 Feb 2025 16:21:21 -0500 Tom Lane wrote: I do have export EDITOR=emacs Possibly libreadline is reacting to that, but I've not checked into it. Emacs combinations work on my machine without it. See my answer here: https://www.postgresql

Re: psql command line editing

On Wed, 12 Feb 2025 16:21:21 -0500 Tom Lane wrote: > I do have > > export EDITOR=emacs > > Possibly libreadline is reacting to that, but I've not checked > into it. > Emacs combinations work on my machine without it. -- Bien à vous, Vincent Veyron

Re: Ideas about presenting data coming from sensors

On 2/13/25 01:53, Achilleas Mantzios - cloud wrote: Now my problem is on the design . We have : a) tags that have primitive values, float4 lets say - this is the majority, e.g. 60% of all tags b) tags that contain alarms data also with defined structure, which have additional data such as ti

Re: Best Approach for Swapping a Table with its Copy

On 2/13/25 07:25, Dominique Devienne wrote: On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane > wrote: Thanks for the colorful analogy Greg :). Maybe the better option is to support ALTER TABLE to ADD an exclusion constraint, no? That exists: select version();

Re: libc to libicu via pg_dump/pg_restore?

On 2/13/25 08:17, Paul Foerster wrote: Hi Adrian, sorry for the late answer. I'm just too busy. On 7 Feb 2025, at 17:19, Adrian Klaver wrote: With create database being "template template0", this is what my script does. But I need the -cC options for pg_restore to get ACLs back. Leaving o

Re: libc to libicu via pg_dump/pg_restore?

Hi Adrian, sorry for the late answer. I'm just too busy. > On 7 Feb 2025, at 17:19, Adrian Klaver wrote: > >> With create database being "template template0", this is what my >> script does. But I need the -cC options for pg_restore to get ACLs back. >> Leaving out either one of them will no

Re: Best Approach for Swapping a Table with its Copy

On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane wrote: > On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes > wrote: > >> What I am after is the same, but I seek a deeper understanding of what it >> does, and why it does it. For example, it swaps relfilenode. Why? > > > It is surgically repla

Re: Best Approach for Swapping a Table with its Copy

On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes wrote: > What I am after is the same, but I seek a deeper understanding of what it > does, and why it does it. For example, it swaps relfilenode. Why? It is surgically replacing all pointers to the old data with pointers to the new data. Yes, wi

Re: Ideas about presenting data coming from sensors

On 1/30/25 18:45, Adrian Klaver wrote: On 1/30/25 06:18, Achilleas Mantzios - cloud wrote: Dear PostgreSQL people We have a project having to do with capturing sensor data and alarms from various machinery using various protocols/standards (e.g. NMEA , MODBUS). We have about 150 sites (vess