Re: Clarification of behaviour when dropping partitions
Yes, this is what I have done.
But the whole point of declaring the foreign key constraint on the
partitioned table is to have it automatically created on subsequent/future
partitions.
On Wed, Dec 4, 2024 at 6:20 PM Laurenz Albe
wrote:
> On Wed, 2024-12-04 at 14:22 +0100, Bolaji Wahab wrote:
> > I have these two partitioned tables, with referential integrity. The
> tables
> > are structured in such a way that we have 1 to 1 mapping between their
> > partitions. This is achieved with a foreign key.
> >
> > CREATE TABLE parent (
> > partition_date date NOT NULL,
> > id uuid NOT NULL,
> > external_transaction_id uuid NOT NULL,
> >
> > CONSTRAINT parent_pkey
> > PRIMARY KEY (id, partition_date),
> >
> > CONSTRAINT parent_external_transaction_id_key
> > UNIQUE (external_transaction_id, partition_date)
> > ) PARTITION BY RANGE (partition_date);
> >
> > CREATE TABLE parent_2024_12_01
> > PARTITION OF public.parent
> > FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> >
> > CREATE TABLE parent_2024_12_02
> > PARTITION OF public.parent
> > FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
> >
> > [...]
> >
> > CREATE TABLE child (
> > partition_date date NOT NULL,
> > transaction_id uuid NOT NULL,
> > keytext NOT NULL,
> > value text NOT NULL,
> >
> > CONSTRAINT child_pkey
> > PRIMARY KEY (transaction_id, key, partition_date),
> >
> > CONSTRAINT child_transaction_id_fkey
> > FOREIGN KEY (transaction_id, partition_date)
> > REFERENCES parent (id, partition_date)
> > ) PARTITION BY RANGE (partition_date);
> >
> > CREATE TABLE child_2024_12_01
> > PARTITION OF child
> > FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
> >
> > CREATE TABLE child_2024_12_02
> > PARTITION OF public.child
> > FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
>
> I recommend that you don't create the foreign key constraint between the
> partitioned tables, but between the individual partitions.
>
> That will make detaching and dropping partitions easier, and you will have
> the same integrity guarantees.
>
> Yours,
> Laurenz Albe
>
Re: Updated Fedora 40 and Fedora 41 RPM packages of Pgpool-II 4.5.5 in the repo
Hello, I'm talking about PGDG maintained repository (and not Fedora maintained) where pgpool is located: https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-40-x86_64/ https://download.postgresql.org/pub/repos/yum/common/fedora/fedora-41-x86_64/ Currently installed package: pgpool-II-4.5.4-1PGDG.f40.x86_64 Thnx. Ciao, Gerhard On 05.12.2024 08:43, Christiano Anderson wrote: Hi, I'm Fedora package maintainer (but not of the pgpool package). It's the package maintainer's responsibility to update the package in different Fedora versions (40, 41 and Rawhide). There is already a ticket for this update: https://bugzilla.redhat.com/show_bug.cgi?id=2305049 Maybe it's a matter of time until the new version is available, but you can also contact the developer directly: https://src.fedoraproject.org/rpms/postgresql-pgpool-II Best, Christiano On 05/12/2024 07:16, Gerhard Wiesinger wrote: Hello, Any chance to get Updated Fedora 40 and Fedora 41 RPM packages of Pgpool-II 4.5.5 in the repo quickly? Thnx. Ciao, Gerhard
Re: Errors when restoring backup created by pg_dumpall
On 12/5/24 14:32, PopeRigby wrote:
On 12/1/24 13:55, Tom Lane wrote:
Adrian Klaver writes:
On 12/1/24 13:14, Tom Lane wrote:
It would be useful to know what is the command at line 4102
of all.sql.
It is here:
https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
CREATE TABLE public.geodata_places (
id integer NOT NULL,
name character varying(200) NOT NULL,
longitude double precision NOT NULL,
latitude double precision NOT NULL,
"countryCode" character(2) NOT NULL,
"admin1Code" character varying(20),
"admin2Code" character varying(80),
"modificationDate" date NOT NULL,
"earthCoord" public.earth GENERATED ALWAYS AS
(public.ll_to_earth(latitude, longitude)) STORED,
"admin1Name" character varying,
"admin2Name" character varying,
"alternateNames" character varying
);
Ah! Then the failure occurs because we do a planning pass on the
GENERATED expression (I don't remember exactly why that's needed
during CREATE TABLE). So maybe messing with the dump script's
search_path setting *would* be enough to get you past that.
Having said that, the CREATE should have been seeing the new-style
definition of ll_to_earth() if the 1.2 version of earthdistance
was correctly installed.
regards, tom lane
So, is there anything I can do to fix this particular backup? I'm kind
of stuck here. There's also the issue with it for some reason failing to
connect to the lldap database after it literally just created it. Some
weird things going on.
In the pg_dumpall sql script did you change:
SELECT pg_catalog.set_config('search_path', '', false);
to
SELECT pg_catalog.set_config('search_path', 'public', false);
?
Show us the connection error you got for the lldap database.
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Best Practices for Managing Schema Changes Dynamically with libpq
On 2024-12-03 18:43 +0100, Sasmit Utkarsh wrote: > I am working on a project that uses libpq along with C language to interact > with PostgreSQL, and we face challenges with managing schema changes > dynamically in production while avoiding downtime. Specifically, we need > guidance on handling table structure changes/additions without tightly > coupling these changes to application updates. > > *Current Approach:* > Schema changes are deployed first, followed by application updates to align > with the new structure. > > *Challenges:* > Ensuring application stability during the transitional phase when the > schema and code are not fully in sync. > Handling table structure changes (e.g., adding new columns) dynamically > without requiring immediate code changes. What you're looking for is the "Expand and Contract" pattern[1][2]. The transitional phase between expand and contract has to support both old and new code until the old code is migrated as well. How you keep the schema compatible with the old code for some time depends on the kind of schema changes. Some use cases from the top of my head: 1) expand: add unconstrained columns transition: adapt code to use new columns contract: add constraints 2) expand: rename tables/columns transition: add (updatable) views that expose the old names until the code is adapted to the new names contract: drop views 3) expand: add columns with constraints transition: backfill new columns with triggers contract: drop triggers [1] https://www.tim-wellhausen.de/papers/ExpandAndContract/ExpandAndContract.html [2] https://martinfowler.com/articles/evodb.html#everything_refactoring -- Erik
Re: Errors when restoring backup created by pg_dumpall
On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston wrote: > On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson > wrote: > >> Another alternative is to open the .sql file in Notepad++, then add >> "public." before all the unqualified "earth" and "ll_to_earth" references. >> > > And as discussed there are none in that file because those references are > within an extension's objects and only create extension appears in the file. > Then why would changing search_path work? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Errors when restoring backup created by pg_dumpall
On Thu, Dec 5, 2024 at 5:32 PM PopeRigby wrote: > On 12/1/24 13:55, Tom Lane wrote: > > Adrian Klaver writes: > >> On 12/1/24 13:14, Tom Lane wrote: > >>> It would be useful to know what is the command at line 4102 > >>> of all.sql. > >> It is here: > >> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49 > >> CREATE TABLE public.geodata_places ( > >> id integer NOT NULL, > >> name character varying(200) NOT NULL, > >> longitude double precision NOT NULL, > >> latitude double precision NOT NULL, > >> "countryCode" character(2) NOT NULL, > >> "admin1Code" character varying(20), > >> "admin2Code" character varying(80), > >> "modificationDate" date NOT NULL, > >> "earthCoord" public.earth GENERATED ALWAYS AS > >> (public.ll_to_earth(latitude, longitude)) STORED, > >> "admin1Name" character varying, > >> "admin2Name" character varying, > >> "alternateNames" character varying > >> ); > > Ah! Then the failure occurs because we do a planning pass on the > > GENERATED expression (I don't remember exactly why that's needed > > during CREATE TABLE). So maybe messing with the dump script's > > search_path setting *would* be enough to get you past that. > > > > Having said that, the CREATE should have been seeing the new-style > > definition of ll_to_earth() if the 1.2 version of earthdistance > > was correctly installed. > > > > regards, tom lane > > So, is there anything I can do to fix this particular backup? I'm kind > of stuck here. There's also the issue with it for some reason failing to > connect to the lldap database after it literally just created it. Some > weird things going on. > Another alternative is to open the .sql file in Notepad++, then add "public." before all the unqualified "earth" and "ll_to_earth" references. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Errors when restoring backup created by pg_dumpall
On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson wrote: > Another alternative is to open the .sql file in Notepad++, then add > "public." before all the unqualified "earth" and "ll_to_earth" references. > And as discussed there are none in that file because those references are within an extension's objects and only create extension appears in the file. David J.
Re: Errors when restoring backup created by pg_dumpall
On 12/1/24 13:55, Tom Lane wrote: Adrian Klaver writes: On 12/1/24 13:14, Tom Lane wrote: It would be useful to know what is the command at line 4102 of all.sql. It is here: https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49 CREATE TABLE public.geodata_places ( id integer NOT NULL, name character varying(200) NOT NULL, longitude double precision NOT NULL, latitude double precision NOT NULL, "countryCode" character(2) NOT NULL, "admin1Code" character varying(20), "admin2Code" character varying(80), "modificationDate" date NOT NULL, "earthCoord" public.earth GENERATED ALWAYS AS (public.ll_to_earth(latitude, longitude)) STORED, "admin1Name" character varying, "admin2Name" character varying, "alternateNames" character varying ); Ah! Then the failure occurs because we do a planning pass on the GENERATED expression (I don't remember exactly why that's needed during CREATE TABLE). So maybe messing with the dump script's search_path setting *would* be enough to get you past that. Having said that, the CREATE should have been seeing the new-style definition of ll_to_earth() if the 1.2 version of earthdistance was correctly installed. regards, tom lane So, is there anything I can do to fix this particular backup? I'm kind of stuck here. There's also the issue with it for some reason failing to connect to the lldap database after it literally just created it. Some weird things going on.
Re: Errors when restoring backup created by pg_dumpall
On 12/5/24 18:44, Ron Johnson wrote: On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson mailto:ronljohnso...@gmail.com>> wrote: Another alternative is to open the .sql file in Notepad++, then add "public." before all the unqualified "earth" and "ll_to_earth" references. And as discussed there are none in that file because those references are within an extension's objects and only create extension appears in the file. Then why would changing search_path work? Because that is set for each database before the objects in that database are created. That means anything that is created subsequently will be have access to all other objects created in the path. This would include objects created indirectly from an extension as well as objects that include non-schema qualified names. -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster! -- Adrian Klaver adrian.kla...@aklaver.com
Re: Errors when restoring backup created by pg_dumpall
On Thursday, December 5, 2024, Ron Johnson wrote: > On Thu, Dec 5, 2024 at 6:24 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Thu, Dec 5, 2024 at 4:04 PM Ron Johnson >> wrote: >> >>> Another alternative is to open the .sql file in Notepad++, then add >>> "public." before all the unqualified "earth" and "ll_to_earth" references. >>> >> >> And as discussed there are none in that file because those references are >> within an extension's objects and only create extension appears in the file. >> > > Then why would changing search_path work? > > Because (I presume) function inlining during execution of create table keeps the search_path of the session executing create table which will then result in the parser resolving the unqualified “earth” function name to the one existing in the public schema when looking through the session’s search_path. David J.
