Re: pg_dump insert column GENERATED
Got it. > > >You are going need to provide more information about the above: > >1) Define regular results. > >2) The query used. > >3) The outcome vs what you expected. > > I will continue to experiment. The question is which option is better (this would reduce the number of options): pd_dump is the source server; pg_restore - receiver server; or pd_dump - receiver server; pg_restore - server-receiver;
Re: pg_dump insert column GENERATED
=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= writes: > Uchet=# select attname, attgenerated from pg_attribute > Uchet-# where attrelid = 'bpd.object'::regclass and attnum > 0; > attname| attgenerated > ---+-- > id| > id_class | > id_position | > pg.dropped.4 | > pg.dropped.5 | > pg.dropped.6 | > bquantity | > pg.dropped.8 | > pg.dropped.9 | > id_position_root | > id_conception | > barcode_unit | > id_unit_conversion_rule | > pg.dropped.14 | > timestamp | > on_freeze | > timestamp_class | > name | > id_class_root | > id_group | > id_group_root | > id_object_carrier | > desc | > pg.dropped.24 | > pg.dropped.25 | > pg.dropped.26 | > id_class_prop_object_carrier | > id_pos_temp_prop | > pg.dropped.29 | > mc| > is_inside | s Hah ... that gave me the clue I needed. If there are dropped column(s) before the GENERATED one, pg_dump gets it wrong --- but only in --inserts mode, not the default COPY mode, which no doubt explains why nobody noticed. There is code in there to inject DEFAULT, but it must be indexing the flag array wrong. Will fix, thanks for the report! regards, tom lane
Doubt regarding Postgres replication issue
Hi Team, Greetings! I have a doubt regarding Postgres replication. I am able to create replication setup. But when the network is down for 60 seconds, the replication was broken. Communication is broken between Master and slave. Slave is trying to become master and it is not able to sync with master and both went down. How to over come this scenario. When the network is up, the replication should start working automatically. Can you suggest a way to achieve this functionality. Currently we are using 9.6 and trying to migrate to Postgres 14.Thanks in advance. Thanks,Palanivel M
Re: pg_dump insert column GENERATED
Рад помочь! -- С уважением, Дмитрий! пн, 22 нояб. 2021 г. в 19:55, Tom Lane : > =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= > writes: > > Uchet=# select attname, attgenerated from pg_attribute > > Uchet-# where attrelid = 'bpd.object'::regclass and attnum > 0; > > attname| attgenerated > > ---+-- > > id| > > id_class | > > id_position | > > pg.dropped.4 | > > pg.dropped.5 | > > pg.dropped.6 | > > bquantity | > > pg.dropped.8 | > > pg.dropped.9 | > > id_position_root | > > id_conception | > > barcode_unit | > > id_unit_conversion_rule | > > pg.dropped.14 | > > timestamp | > > on_freeze | > > timestamp_class | > > name | > > id_class_root | > > id_group | > > id_group_root | > > id_object_carrier | > > desc | > > pg.dropped.24 | > > pg.dropped.25 | > > pg.dropped.26 | > > id_class_prop_object_carrier | > > id_pos_temp_prop | > > pg.dropped.29 | > > mc| > > is_inside | s > > Hah ... that gave me the clue I needed. If there are dropped > column(s) before the GENERATED one, pg_dump gets it wrong --- > but only in --inserts mode, not the default COPY mode, which > no doubt explains why nobody noticed. There is code in there > to inject DEFAULT, but it must be indexing the flag array wrong. > > Will fix, thanks for the report! > > regards, tom lane >
Re: insert column monetary type ver 2
Ron writes: > On 11/21/21 9:35 PM, Rob Sargent wrote: >> Some of us like it as an easy formatter trick for reports > to_char() can do the same, no? to_char doesn't have adequate logic for locale-specific monetary formatting. You can get it to emit a locale-specific currency symbol, but it has no clue whether that should go before or after the value. It knows nothing of other locale-specific details, such as possibly using parens in place of a minus sign. Also, the POSIX API allows monetary decimal point and thousands separators to be different from the numeric ones that to_char knows about. (I have no idea which locales use that, but I doubt they'd have put in that complication without need.) regards, tom lane
Re: pg_dump insert column GENERATED
It seems to me that you are right all round (I gave up COPY because of
problems with the MONEY type):
sudo /usr/lib/postgresql/14/bin/pg_dump --file
"/home/dismay/uchet/object.sql" --host "server" --port "5999" --username
"back" --no-password --verbose --format=p --quote-all-identifiers
--encoding="UTF8" --table "bpd".object --dbname "Uchet"
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.9
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)
-- Started on 2021-11-22 20:21:59 +05
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = "heap";
--
-- TOC entry 353 (class 1259 OID 94786)
-- Name: object; Type: TABLE; Schema: bpd; Owner: IvanovDU
--
CREATE TABLE "bpd"."object" (
"id" bigint DEFAULT
"nextval"('"bpd"."object_general_id_seq"'::"regclass") NOT NULL,
"id_class" bigint NOT NULL,
"id_position" bigint DEFAULT '-1'::integer NOT NULL,
"bquantity" numeric NOT NULL,
"id_position_root" bigint NOT NULL,
"id_conception" bigint NOT NULL,
"barcode_unit" bigint DEFAULT 0 NOT NULL,
"id_unit_conversion_rule" integer NOT NULL,
"timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
"on_freeze" boolean DEFAULT false NOT NULL,
"timestamp_class" timestamp without time zone DEFAULT LOCALTIMESTAMP(3)
NOT NULL,
"name" character varying(255) NOT NULL,
"id_class_root" bigint NOT NULL,
"id_group" bigint NOT NULL,
"id_group_root" bigint NOT NULL,
"id_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
"desc" character varying(2044) DEFAULT 'н/д'::character varying NOT
NULL,
"id_class_prop_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
"id_pos_temp_prop" bigint DEFAULT '-1'::integer NOT NULL,
"mc" numeric DEFAULT 0 NOT NULL,
"is_inside" boolean GENERATED ALWAYS AS ((("id_object_carrier" > 0) OR
("id_pos_temp_prop" >= 0))) STORED,
CONSTRAINT "check_self_integration" CHECK (("id" <>
"id_object_carrier"))
);
ALTER TABLE "bpd"."object" OWNER TO "IvanovDU";
--
-- TOC entry 5225 (class 0 OID 94786)
-- Dependencies: 353
-- Data for Name: object; Type: TABLE DATA; Schema: bpd; Owner: IvanovDU
--
COPY "bpd"."object" ("id", "id_class", "id_position", "bquantity",
"id_position_root", "id_conception", "barcode_unit",
"id_unit_conversion_rule", "timestamp", "on_freeze", "timestamp_class",
"name", "id_class_root", "id_group", "id_group_root", "id_object_carrier",
"desc", "id_class_prop_object_carrier", "id_pos_temp_prop", "mc") FROM
stdin;
51253 1015 461 1 461 84 202512530 14 2021-11-14 08:40:31.381 f
2021-02-19 11:01:28.402 NFC метка самоклеющаяся 1013 138 138 -1 -1 -1
1
*
sudo /usr/lib/postgresql/14/bin/pg_dump --file
"/home/dismay/uchet/object2.sql" --host "server" --port "5999" --username
"back" --no-password --verbose --format=p --quote-all-identifiers
--column-inserts --inserts --encoding="UTF8" --table "bpd".object --dbname
"Uchet"
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.9
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)
-- Started on 2021-11-22 20:24:31 +05
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = "heap";
--
-- TOC entry 353 (class 1259 OID 94786)
-- Name: object; Type: TABLE; Schema: bpd; Owner: IvanovDU
--
CREATE TABLE "bpd"."object" (
"id" bigint DEFAULT
"nextval"('"bpd"."object_general_id_seq"'::"regclass") NOT NULL,
"id_class" bigint NOT NULL,
"id_position" bigint DEFAULT '-1'::integer NOT NULL,
"bquantity" numeric NOT NULL,
"id_position_root" bigint NOT NULL,
"id_conception" bigint NOT NULL,
"barcode_unit" bigint DEFAULT 0 NOT NULL,
"id_unit_conversion_rule" integer NOT NULL,
"timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
"on_freeze" boolean DEFAULT false NOT NULL,
"timestamp_class" timestamp without time zone DEFAULT LOCALTIMESTAMP(3)
NOT NULL,
"name" character varying(255) NOT NULL,
"id_class_root" bigint NOT NULL,
"id_group" bigint NOT NULL,
"id_group_root" bigint NOT NULL,
"id_object_carrier" bigint DEFAULT '-1'::integer NOT NULL,
"desc" character
Re: insert column monetary type ver 2
About the locale, I wanted to add a couple of lines. I noticed a strange behavior of the currency symbol. The EDB installer, when specifying the Russian locale, sets the following locale parameters: (PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit) (Encoding UTF8) lc_messages = 'Russian_Russia.1251' # locale for system error message # strings lc_monetary = 'Russian_Russia.1251' # locale for monetary formatting lc_numeric = 'Russian_Russia.1251' # locale for number formatting lc_time = 'Russian_Russia.1251' # locale for time formatting In this case, the data of the financial type is not correctly displayed: 1 238,23 ? If you set the locale: lc_messages = 'ru_RU.UTF-8' # locale for system error message # strings lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting lc_numeric = 'ru_RU.UTF-8' # locale for number formatting lc_time = 'ru_RU.UTF-8' # locale for time formatting The data of the financial type is displayed correctly: 1 238,23 ₽ -- Regards, Dmitry! пн, 22 нояб. 2021 г. в 20:17, Tom Lane : > Ron writes: > > On 11/21/21 9:35 PM, Rob Sargent wrote: > >> Some of us like it as an easy formatter trick for reports > > > to_char() can do the same, no? > > to_char doesn't have adequate logic for locale-specific monetary > formatting. > You can get it to emit a locale-specific currency symbol, but it has no > clue whether that should go before or after the value. It knows nothing > of other locale-specific details, such as possibly using parens in place > of a minus sign. Also, the POSIX API allows monetary decimal point and > thousands separators to be different from the numeric ones that to_char > knows about. (I have no idea which locales use that, but I doubt they'd > have put in that complication without need.) > > regards, tom lane > > >
Re: pg_dump insert column GENERATED
On 11/22/21 03:32, Дмитрий Иванов wrote: Got it. >You are going need to provide more information about the above: >1) Define regular results. >2) The query used. >3) The outcome vs what you expected. I will continue to experiment. The question is which option is better (this would reduce the number of options): pd_dump is the source server; pg_restore - receiver server; or pd_dump - receiver server; pg_restore - server-receiver; pg_dump is backwards compatible not forwards. In the explanations below Postgres versioning(major/minor) is determined as: Pre-version 10: X.x.x 10+: X.x Where X is major and x is minor. This means if are moving forwards in Postgres major version then: 1) Use pg_dump from newer version of Postgres to dump from older version of Postgres. In your case pg_dump(v14) dump Postgres server v12. 2) To restore use the version of pg_restore for the Postgres version you are restoring to. In your case pg_restore(v14). Staying on the same version: 1) Use the pg_dump/pg_restore for the version your are on. 2) If you are moving from one minor release to another then it would be better to use the latest minor release version to get any bug fixes. Going backwards from newer version to older version: This is not supported. -- Adrian Klaver adrian.kla...@aklaver.com
Re: insert column monetary type ver 2
On 11/21/21 19:21, Ron wrote: Which means *that* is broken. Especially since money is a type we advise people avoid anyway. We can just add this to the list of reasons why. Why then does the money type exist? At the very least, why isn't it deprecated? It was at one point: https://www.postgresql.org/docs/8.2/datatype-money.html Not entirely sure why it was revived. You can search the mailing list archive for discussions on this. Bottom line is if you don't want surprises use numeric and format the output on the client side. -- Angular momentum makes the world go 'round. -- Adrian Klaver adrian.kla...@aklaver.com
Re: pg_dump insert column GENERATED
Thank you, there is a clear logic to it. -- Regards, Dmitry! пн, 22 нояб. 2021 г. в 21:11, Adrian Klaver : > On 11/22/21 03:32, Дмитрий Иванов wrote: > > Got it. > > > > > > >You are going need to provide more information about the above: > > >1) Define regular results. > > >2) The query used. > > >3) The outcome vs what you expected. > > > > I will continue to experiment. The question is which option is better > > (this would reduce the number of options): > > pd_dump is the source server; > > pg_restore - receiver server; > > or > > pd_dump - receiver server; > > pg_restore - server-receiver; > > pg_dump is backwards compatible not forwards. > > In the explanations below Postgres versioning(major/minor) is determined > as: > > Pre-version 10: > > X.x.x > > 10+: > > X.x > > Where X is major and x is minor. > > This means if are moving forwards in Postgres major version then: > > 1) Use pg_dump from newer version of Postgres to dump from older version > of Postgres. In your case pg_dump(v14) dump Postgres server v12. > > 2) To restore use the version of pg_restore for the Postgres version you > are restoring to. In your case pg_restore(v14). > > Staying on the same version: > > 1) Use the pg_dump/pg_restore for the version your are on. > > 2) If you are moving from one minor release to another then it would be > better to use the latest minor release version to get any bug fixes. > > Going backwards from newer version to older version: > > This is not supported. > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: insert column monetary type ver 2
Adrian Klaver writes: > On 11/21/21 19:21, Ron wrote: >> Why then does the money type exist? At the very least, why isn't it >> deprecated? > It was at one point: > https://www.postgresql.org/docs/8.2/datatype-money.html > Not entirely sure why it was revived. You can search the mailing list > archive for discussions on this. I think we concluded there was no appetite for actually removing it. 8.3 fixed the very worst problem that had led to deprecation, namely making it an 8-byte integer not 4-byte, so we concluded that it had at least some excuse to live. As already noted, if it doesn't do what you want, don't use it. regards, tom lane
[no subject]
Postgres 9.6. We're attempting to delete some old users from a DB. Log into DB as masteruser. Run this block of commands (the_schema=public, and it's the only schema in this particular DB): REVOKE ALL PRIVILEGES ON DATABASE the_database FROM old_role; REVOKE USAGE ON SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA the_schema FROM old_role; Here we actually had to create a loop to revoke one function at a time while avoiding system functions on which this single command fails: REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA the_schema FROM old_role; REVOKE ALL PRIVILEGES ON SCHEMA the_schema FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON SEQUENCES FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON TABLES FROM old_role; ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON FUNCTIONS FROM old_role; REASSIGN OWNED BY old_role TO masteruser; DROP OWNED BY old_role; DROP USER old_role; The consensus online is that these are the steps to be taken to successfully remove roles, yet we get this error below: ERROR: role "old_role" cannot be dropped because some objects depend on it DETAIL: privileges for database the_database 96 objects in database the_database SQL state: 2BP01 What is it that we could be missing here? Thanks for any help!
Re:
AC Gomez writes: > REASSIGN OWNED BY old_role TO masteruser; > DROP OWNED BY old_role; > DROP USER old_role; > The consensus online is that these are the steps to be taken to > successfully remove roles, yet we get this error below: > ERROR: role "old_role" cannot be dropped because some objects depend on it > DETAIL: privileges for database the_database 96 objects in database > the_database SQL state: 2BP01 > What is it that we could be missing here? You need to do the REASSIGN OWNED and DROP OWNED steps in each database of the cluster that old_role has any objects/privileges in. You might need a manual revoke on "the_database" too; I'm not sure if DROP OWNED does anything about DB-level privileges. regards, tom lane
Why swirling circles in pgAdmin?
Why there are swirling circles in pgAdmin and no expansion to view details? Regards, David
Re: Why swirling circles in pgAdmin?
On 11/22/21 18:14, Shaozhong SHI wrote: Why there are swirling circles in pgAdmin and no expansion to view details? Huh? This is going to need more detail in order to get an answer. In particular: 1) What action is causing swirling circles? 2) Expansion of what? Regards, David -- Adrian Klaver adrian.kla...@aklaver.com
Max connections reached without max connections reached
Hi, This is a bit of an odd one - I'm on PostgreSQL 11.9 with a single streaming replica. The system handles a lot of connections - we have a max_connections of 600. Most are long lived JDBC, but there are a lot of ETL / ad-hoc jobs etc. Connections normally sit at 300ish, with 70 active at the most. The machines have 32 CPU cores . PgBouncer is sadly not an option hereas we are using many long lived connections which make use of prepared statements. Sometimes a strange condition occurs. The number of connections is well under 600 (and dropping), but new connections are not being allowed into the database, I can see this message in the logs: (0:53300)FATAL: remaining connection slots are reserved for non-replication superuser connections >From ps I can see a lot of processes like this: postgres: accessor monitoring 10.10.7.54[41655] startup The number of these grows, and no new connections are allowed in. These startup connections do not appear in pg_stat_activity so I can't find what they are waiting on. Removing some long lived connections sometimes appears to help clear the startup processes and return the system to normal - but I have not been able to find a correlation. It's more blind luck at the moment. Any pointers on where I could start prodding? Cheers, James -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
