Re: Find out the version of the server
On 11/28/24 17:31, Igor Korot wrote: Hi, ALL, In April of 2017 I got the source code of the Postgresql, build the libpq and then deleted everything else. I was using MSVC 3010 at the time. Now I moved to MSVC 2017 but unfortunately I can't reuse the library built, since MSVC changed one of its headers. Reuse it for what? Is it possible to find what version of the server was used for the build? Im looking at the Windows Explorer timestamp, which gives me 4 September 2017. At any point in time there are 5 supported versions of Postgres in play. Given that you could have also built against one of the unsupported versions a file timestamp will not help much. Though if you want to guess, version 10 was released October 5, 2017. It is plausible you built against a RC version in advance of the production release. libpq is backwards compatible, so why not try building against a current version of Postgres and see whats happens? Thank you. P.s. please forgive for any typos as it's been written from the Android phone. -- Adrian Klaver adrian.kla...@aklaver.com
Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
On 2024-Nov-27, Tom Lane wrote:
> I doubt that there's anything actually wrong with the catalog state at
> this point (perhaps Alvaro would confirm that). That leads to the
> conclusion that what's wrong is the release notes' query for fingering
> broken constraints, and it needs some additional test to avoid
> complaining about (I suspect) self-reference cases.
Yes, I think the catalog state is correct and the release notes query is
wrong. I propose a repaired version below. But first, I think there's
still a problem specific to partition creation when a self-referencing
FKs exists. If you do create table / create partition / add FK, then
the query from the release notes does report the FK. But if you do
create table / add FK / create partition, nothing is reported. Clearly,
both those things cannot be simultaneously correct.
-- Case 1: create the partition when the FK already exists
drop table if exists p_ci_pipelines;
create table p_ci_pipelines(partition_id int, id int, primary
key(partition_id,id), auto_canceled_by_partition_id int, auto_canceled_by_id
int) partition by LIST (partition_id);
ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY
(auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines
(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
create table ci_pipelines partition of p_ci_pipelines FOR VALUES IN ('100',
'101', '102');
-- Case 2: create both tables, then add the FK
drop table if exists p_ci_pipelines;
create table p_ci_pipelines(partition_id int, id int, primary
key(partition_id,id), auto_canceled_by_partition_id int, auto_canceled_by_id
int) partition by LIST (partition_id);
create table ci_pipelines partition of p_ci_pipelines FOR VALUES IN ('100',
'101', '102');
ALTER TABLE p_ci_pipelines ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY
(auto_canceled_by_partition_id, auto_canceled_by_id) REFERENCES p_ci_pipelines
(partition_id, id) ON UPDATE CASCADE ON DELETE SET NULL;
Naturally, if in any of those situations you drop and recreate the FK,
it degenerates to case 2, so if you do what the release notes say, it'll
continue to report the FK.
We can use the following query (which lists the constraint and its derivate
pg_constraint rows) to see what goes wrong:
WITH RECURSIVE arrh AS (
SELECT oid, conrelid, conname, confrelid, NULL::name AS conparent
FROM pg_constraint
WHERE connamespace = 'public'::regnamespace AND
contype = 'f' AND conparentid = 0
UNION ALL
SELECT c.oid, c.conrelid, c.conname, c.confrelid,
(pg_identify_object('pg_constraint'::regclass, arrh.oid,
0)).identity
FROM pg_constraint c
JOIN arrh ON c.conparentid = arrh.oid
) SELECT conrelid::regclass, conname, confrelid::regclass, conparent
FROM arrh
ORDER BY conrelid::regclass::text, conname;
For case 2, this is the result:
conrelid│ conname
│ confrelid│conparent
┼─┼┼──
ci_pipelines │ fk_262d4c2d19_p
│ p_ci_pipelines │ fk_262d4c2d19_p on public.p_ci_pipelines
p_ci_pipelines │ fk_262d4c2d19_p
│ p_ci_pipelines │
p_ci_pipelines │
p_ci_pipelines_auto_canceled_by_partition_id_auto_canceled_fkey │ ci_pipelines
│ fk_262d4c2d19_p on public.p_ci_pipelines
For case 1, where the release notes query reports nothing, we get the
following list of constraints instead:
conrelid│ conname │ confrelid│conparent
┼─┼┼──
ci_pipelines │ fk_262d4c2d19_p │ p_ci_pipelines │ fk_262d4c2d19_p on
public.p_ci_pipelines
p_ci_pipelines │ fk_262d4c2d19_p │ p_ci_pipelines │
(2 filas)
Let's look at the triggers. For case 1 we have the following triggers:
WITH RECURSIVE arrh AS (
SELECT t.oid, t.tgrelid::regclass as tablename, tgname,
t.tgfoid::regproc as trigfn,
(pg_identify_object('pg_constraint'::regclass, c.oid,
0)).identity as constr,
NULL::bool as samefunc,
NULL::name AS parent
FROM pg_trigger t
LEFT JOIN pg_constraint c ON c.oid = t.tgconstraint
WHERE (SELECT relnamespace FROM pg_class WHERE oid = t.tgrelid) =
'public'::regnamespace
AND c.contype = 'f' AND t.tgparentid = 0
UNION ALL
SELECT t2.oid, t2.tgrelid::regclass as tablename, t2.tgname,
t2.tgfoid::regproc as trigfn,
(pg_identify_object('pg_cons
Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Hi Alvaro,
> On 29 Nov 2024, at 18:15, Alvaro Herrera wrote:
>
> This all was to say that the query in the release notes is undoubtedly
> wrong. After thinking some more about it, I think the fix is to add 1
> to the number of constraints:
>
> SELECT conrelid::pg_catalog.regclass AS "constrained table",
> conname AS constraint,
> confrelid::pg_catalog.regclass AS "references",
> pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
> conrelid::pg_catalog.regclass, conname) AS "drop",
> pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
> conrelid::pg_catalog.regclass, conname,
> pg_catalog.pg_get_constraintdef(oid)) AS "add"
> FROM pg_catalog.pg_constraint c
> WHERE contype = 'f' AND conparentid = 0 AND
> (SELECT count(*) FROM pg_catalog.pg_constraint c2
>WHERE c2.conparentid = c.oid) <>
> ((SELECT count(*) FROM pg_catalog.pg_inherits i
>WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
> EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
> WHERE partrelid = i.inhparent)) +
>CASE when pg_partition_root(conrelid) = confrelid THEN 1 ELSE 0 END);
>
> This reports case 2 as OK and case 1 as bogus, as should be. I tried
> adding more partitions and this seems to hold correctly. I was afraid
> though that this would fail if we create an FK in an intermediate level
> of the partition hierarchy ... but experimentation doesn't seem to give
> that result. I've run out of time today to continue to look though.
Thanks very much for this really detailed analysis and sharing your insights.
I'll give the new query a try on Monday when I'm back at work. Do I also need
to recheck all other databases with this new query which didn't report anything
with the original query?
> Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
> "La vida es para el que se aventura"
You're located in the middle of the forest east of Freiburg im Breisgau in
Germany? 🤣
Cheers,
Paul
Re: Find out the version of the server
Thx. On Fri, Nov 29, 2024, 2:03 PM Tom Lane wrote: > Adrian Klaver writes: > > On 11/29/24 11:15 AM, Igor Korot wrote: > >> Are you saying that the current version can connect even to 9.6.1 > >> server? (I have a really old Mac with the 9.6.1 version installed). > > > Yes. > > Current libpq will probably work with servers back to around 7.0 > (whenever we introduced the version-3 wire protocol). We don't > routinely test it against anything older than 9.2, but I tried > current psql against 8.2 (the oldest functional server I have > on this machine) and it basically worked. > > regards, tom lane >
Re: Find out the version of the server
Reply to list also Ccing list On 11/29/24 11:15 AM, Igor Korot wrote: Hi, Adrian, On Fri, Nov 29, 2024 at 10:12 AM Adrian Klaver wrote: Reuse it for what? For building.. I got a linking errors of not finding sscanf() and sprntf() when building my code. Googling revealed that MSVC made those inline after MSVC 2010. Is it possible to find what version of the server was used for the build? Im looking at the Windows Explorer timestamp, which gives me 4 September 2017. At any point in time there are 5 supported versions of Postgres in play. Given that you could have also built against one of the unsupported versions a file timestamp will not help much. Though if you want to guess, version 10 was released October 5, 2017. It is plausible you built against a RC version in advance of the production release. Understood. I also tried to open the explorer and go to Properties of the DLL built That dialog shows for src/interfaces/libpq/Release/ for the Detail tab Type:Application Extension File Version: 9.6.2.17037 Product name: PostgreSQL Product version: 9.6.2 Can this info be trusted? libpq is backwards compatible, so why not try building against a current version of Postgres and see whats happens? Are you saying that the current version can connect even to 9.6.1 server? (I have a really old Mac with the 9.6.1 version installed). Yes. The CLI client psql is based on libpq and from here: https://www.postgresql.org/docs/current/app-psql.html "If you want to use psql to connect to several servers of different major versions, it is recommended that you use the newest version of psql. Alternatively, you can keep around a copy of psql from each major version and be sure to use the version that matches the respective server. But in practice, this additional complication should not be necessary." If you have a new version of psql available connect to the 9.6.1 instance to verify. Thank you. Thank you. P.s. please forgive for any typos as it's been written from the Android phone. -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: Find out the version of the server
Hi, Adrian, On Fri, Nov 29, 2024, 1:24 PM Adrian Klaver wrote: > Reply to list also > > Ccing list > > On 11/29/24 11:15 AM, Igor Korot wrote: > > Hi, Adrian, > > > > On Fri, Nov 29, 2024 at 10:12 AM Adrian Klaver > > wrote: > >> > > >> Reuse it for what? > > > > For building.. > > I got a linking errors of not finding sscanf() and sprntf() > > when building my code. > > > > Googling revealed that MSVC made those inline after > > MSVC 2010. > > > >> > >>> > >>> Is it possible to find what version of the server was used for the > build? > >>> > >>> Im looking at the Windows Explorer timestamp, which gives me 4 > September > >>> 2017. > >> > >> At any point in time there are 5 supported versions of Postgres in play. > >> Given that you could have also built against one of the unsupported > >> versions a file timestamp will not help much. Though if you want to > >> guess, version 10 was released October 5, 2017. It is plausible you > >> built against a RC version in advance of the production release. > > > > Understood. > > I also tried to open the explorer and go to Properties of the DLL built > > > > That dialog shows for src/interfaces/libpq/Release/ for the Detail tab > > > > Type:Application Extension > > File Version: 9.6.2.17037 > > Product name: PostgreSQL > > Product version: 9.6.2 > > > > Can this info be trusted? > >> > >> libpq is backwards compatible, so why not try building against a current > >> version of Postgres and see whats happens? > > > > Are you saying that the current version can connect even to 9.6.1 > > server? (I have a really old Mac with the 9.6.1 version installed). > > Yes. > > The CLI client psql is based on libpq and from here: > > https://www.postgresql.org/docs/current/app-psql.html > > "If you want to use psql to connect to several servers of different > major versions, it is recommended that you use the newest version of > psql. Alternatively, you can keep around a copy of psql from each major > version and be sure to use the version that matches the respective > server. But in practice, this additional complication should not be > necessary." > > If you have a new version of psql available connect to the 9.6.1 > instance to verify. > Thank you. Ill try to get the new version and build it. > > > > Thank you. > > > >> > >>> > >>> Thank you. > >>> > >>> P.s. please forgive for any typos as it's been written from the Android > >>> phone. > >>> > >> > >> -- > >> Adrian Klaver > >> adrian.kla...@aklaver.com > >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Find out the version of the server
Adrian Klaver writes: > On 11/29/24 11:15 AM, Igor Korot wrote: >> Are you saying that the current version can connect even to 9.6.1 >> server? (I have a really old Mac with the 9.6.1 version installed). > Yes. Current libpq will probably work with servers back to around 7.0 (whenever we introduced the version-3 wire protocol). We don't routinely test it against anything older than 9.2, but I tried current psql against 8.2 (the oldest functional server I have on this machine) and it basically worked. regards, tom lane
Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION
Hello, On 2024-Nov-29, Paul Foerster wrote: > > On 29 Nov 2024, at 18:15, Alvaro Herrera wrote: > > This reports case 2 as OK and case 1 as bogus, as should be. I tried > > adding more partitions and this seems to hold correctly. I was afraid > > though that this would fail if we create an FK in an intermediate level > > of the partition hierarchy ... but experimentation doesn't seem to give > > that result. I've run out of time today to continue to look though. > > Thanks very much for this really detailed analysis and sharing your > insights. I'll give the new query a try on Monday when I'm back at > work. Do I also need to recheck all other databases with this new > query which didn't report anything with the original query? Only if you have self-referencing FKs in partitioned tables. It would be an interesting data point to verify whether this reports anything else. Also, I'd be really curious if your databases include the case I'm suspicious about: a multi-level hierarchy containing an FK that points to an intermediate level of itself. > > Álvaro Herrera 48°01'N 7°57'E — > > https://www.EnterpriseDB.com/ > > "La vida es para el que se aventura" > > You're located in the middle of the forest east of Freiburg im > Breisgau in Germany? 🤣 I'm within fives minutes of longitude and latitude of that location, yes :-) I didn't want to give unnecessary precision there, but is somebody wants to chat sometime or whatever is welcome to ping me. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Estoy de acuerdo contigo en que la verdad absoluta no existe... El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)
Errors when restoring backup created by pg_dumpall
My HDD recently failed so I'm trying to restore my backup, but I'm running into some errors. I've been using a systemd service that periodically backs up my cluster with pg_dumpall, and I'm using this command to restore: sudo psql -f backup.sql postgres I'm getting this output: https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea This is my (redacted) database dump: https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
Re: Errors when restoring backup created by pg_dumpall
On Fri, Nov 29, 2024 at 8:35 PM PopeRigby wrote: > My HDD recently failed so I'm trying to restore my backup, but I'm > running into some errors. > > I've been using a systemd service that periodically backs up my cluster > with pg_dumpall, and I'm using this command to restore: > > sudo psql -f backup.sql postgres > > I'm getting this output: > https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea > > This is my (redacted) database dump: > https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49 > Have you installed whatever software provides extensions like cube, vector and earthdistance? -- Death to , and butter sauce. Don't boil me, I'm still alive. lobster!
Re: Errors when restoring backup created by pg_dumpall
On 11/29/24 17:34, PopeRigby wrote: My HDD recently failed so I'm trying to restore my backup, but I'm running into some errors. I've been using a systemd service that periodically backs up my cluster with pg_dumpall, and I'm using this command to restore: sudo psql -f backup.sql postgres I'm getting this output: https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea psql:all.sql:4104: ERROR: type "earth" does not exist LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth QUERY: SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth CONTEXT: SQL function "ll_to_earth" during inlining 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 ); Looks like an extension or extensions where not installed before the restore was done. This is my (redacted) database dump: https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49 -- Adrian Klaver adrian.kla...@aklaver.com
