how to check PEM version
Hi, I have installed pem in centos 7, now i want to check the version of PEM using sql query. Please let me know, how should i do that. My postgres version is 11.8 Regards, Atul
Re: how to check PEM version
On Thu, Sep 10, 2020 at 2:19 PM Atul Kumar wrote: > Hi, > > I have installed pem in centos 7, now i want to check the version of > PEM using sql query. > > Please let me know, how should i do that. PEM is EnterpriseDB proprietary product, please avoid asking questions about that on this list. Please send your queries to EnterpriseDB. -- Thanks, Ashesh Vashi > > > My postgres version is 11.8 > > > Regards, > Atul > > >
PKEY getting corrupted
Hi, We have seen several times a situation where a PKEY is compromised and duplicate values are created within a table. This has happened so far on PG 928 on Linux and Windows, and also on PG955 on AIX. We ran massive test on PG10.4 but it has not recreated the problem. Has anyone faced the same issue? Any known bug? Thanks Danny
Return value of CREATE TABLE
Is this possible? Basically I want to manage temp table status for use in a procedure. The procedure looks at pg_catalog to get information for processing. So basically I would like to be able to know what namespace a temp table is created in, so that I can constrain lookup. example CREATE TEMP TABLE tagdata (test int,test2 numeric(10,2)); SELECT relname,relpersistence ,relnamespace ,pa.atttypid,attname,attnum ,nspname FROM pg_catalog.pg_class pc JOIN pg_attribute pa ON pc.oid=pa.attrelid JOIN pg_namespace pn ON pn.oid=relnamespace WHERE relname = 'tagdata' AND attnum>0 Which returns (when its run for the second time in different tabs in pgadmin) relname persistence namespace typeid colname colnum schema "tagdata" "p" "2200" "23""fileid"1 "public" "tagdata" "p" "2200" "25""tagname" 2 "public" "tagdata" "p" "2200" "1009""tagvalue" 3 "public" "tagdata" "t" "483934""23""test" 1 "pg_temp_10" "tagdata" "t""538079" "23""test" 1 "pg_temp_13" "tagdata" "t""538079""1700" "test2"2 "pg_temp_13" So I would like some way of knowing exactly which schema the temp table has been created in, I cant see anything obvious thanks Mike
Re: Return value of CREATE TABLE
## Mike Martin (redt...@gmail.com):
> So basically I would like to be able to know what namespace a temp table is
> created in, so that I can constrain lookup.
pg_my_temp_schema() returns the OID of the session's temporary schema
("or 0 if none", according to the docs).
Regards,
Christoph
--
Spare Space
PG13 partitioned table logical replication
Dear community, I just made some test of upcoming version of porstgresql, especially the ability to publish partitioned table via root partition. I tried to setup PG13 to PG12 replication with different partitioning schemes. My first attempt was to replicate time partitioned table in PG13 cluster to a regular table in PG12 cluster, but after looking at the logs I saw the following error : 2020-09-09 12:21:33.964 CEST [22976] LOG: logical replication table synchronization worker for subscription "rep_part_sub", table "stock_sales" has started 2020-09-09 12:21:33.997 CEST [22976] ERROR: table "public.stock_sales" not found on publisher 2020-09-09 12:21:33.999 CEST [20346] LOG: background worker "logical replication worker" (PID 22976) exited with exit code 1 While not being aware of underlying mechanics of logical replication, I was expecting this use case to work given table names and column names/types are the same on both sides. Is this the intended behavior and simply out of the scope of the changes introduced by PG13 ? Thank you, Best regards, Nicolas SORNIN IT Team Lead This email message as well as all attachments are for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.
Re: Return value of CREATE TABLE
Thanks , exactly what I was looking for
On Thu, 10 Sep 2020 at 13:16, Christoph Moench-Tegeder
wrote:
> ## Mike Martin (redt...@gmail.com):
>
> > So basically I would like to be able to know what namespace a temp table
> is
> > created in, so that I can constrain lookup.
>
> pg_my_temp_schema() returns the OID of the session's temporary schema
> ("or 0 if none", according to the docs).
>
> Regards,
> Christoph
>
> --
> Spare Space
>
Re: PKEY getting corrupted
> On Sep 10, 2020, at 6:43 AM, Abraham, Danny wrote: > > Hi, > > We have seen several times a situation where a PKEY is compromised and > duplicate values are created within a table. > > This has happened so far on PG 928 on Linux and Windows, and also on PG955 on > AIX. > > We ran massive test on PG10.4 but it has not recreated the problem. > > Has anyone faced the same issue? Any known bug? In my experience, this is usually due to an application writing PK values rather than allowing a Postgres sequence to generate them, or due to the application misusing sequences. Postgres sequences are monotonically increasing, so even if you insert sequence values “out of order”, they won’t overlap. That’s not to say a bug in Postgres is impossible, but every time I’ve seen this problem, it’s been my application, not Postgres. Postgres 10 added new syntax for PK columns "GENERATED ALWAYS AS IDENTITY” (https://www.postgresql.org/docs/10/sql-createtable.html). Postgres will raise an exception if an application tries to insert a specific value into a column defined this way. When we upgraded from 9.6 to 11 we converted all of our PK columns to use this syntax which added a nice safety net for us. You’re probably already aware that 9.5.23 is the oldest supported version in the 9.x series, so the sooner you can upgrade those unsupported 9.x versions the better. Maybe you’re in the middle of an upgrade already which is why this came up. :-) Cheers Philip
Re: PKEY getting corrupted
On 9/10/20 3:43 AM, Abraham, Danny wrote: Hi, We have seen several times a situation where a PKEY is compromised and duplicate values are created within a table. Table definition? The situation is? Postgres log entries from the error? This has happened so far on PG 928 on Linux and Windows, and also on PG955 on AIX. We ran massive test on PG10.4 but it has not recreated the problem. The test would be? Has anyone faced the same issue? Any known bug? Thanks Danny -- Adrian Klaver adrian.kla...@aklaver.com
Re: PKEY getting corrupted
Danny, On 2020-Sep-10, Abraham, Danny wrote: > We have seen several times a situation where a PKEY is compromised and > duplicate values are created within a table. Yeah, this is not unheard of. > This has happened so far on PG 928 on Linux and Windows, and also on > PG955 on AIX. You do realize that 9.2.8 is not only missing the many bugfixes that happened until 9.2.24 was released in November 2017, but also any bugfixes that were done after that branch was abandoned? 9.5.5 sounds more interesting as a target, but it's still missing all updates until 9.5.23 released last month. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: PG13 partitioned table logical replication
On 2020-09-10 14:13, Nicolas Sornin wrote: I just made some test of upcoming version of porstgresql, especially the ability to publish partitioned table via root partition. I tried to setup PG13 to PG12 replication with different partitioning schemes. My first attempt was to replicate time partitioned table in PG13 cluster to a regular table in PG12 cluster, but after looking at the logs I saw the following error : 2020-09-09 12:21:33.964 CEST [22976] LOG: logical replication table synchronization worker for subscription "rep_part_sub", table "stock_sales" has started 2020-09-09 12:21:33.997 CEST [22976] ERROR: table "public.stock_sales" not found on publisher 2020-09-09 12:21:33.999 CEST [20346] LOG: background worker "logical replication worker" (PID 22976) exited with exit code 1 While not being aware of underlying mechanics of logical replication, I was expecting this use case to work given table names and column names/types are the same on both sides. Is this the intended behavior and simply out of the scope of the changes introduced by PG13 ? When the subscriber running on PG12 is trying to fetch information about the table from the publisher, it is looking for a real table, which it doesn't find, because it's a partitioned table. So this combination doesn't work. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: how to check PEM version
Hi Atul, copy from program is your friend ;P https://www.postgresql.org/docs/current/sql-copy.html On 2020-09-10 05:49, Atul Kumar wrote: Hi, I have installed pem in centos 7, now i want to check the version of PEM using sql query. Please let me know, how should i do that. My postgres version is 11.8 Regards, Atul .
RE: PG13 partitioned table logical replication
Hello Peter, That's what I thought. I'll find another way. Thank you for your time. Regards, Nicolas SORNIN IT Team Lead This email message as well as all attachments are for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you. -Message d'origine- De : Peter Eisentraut Envoyé : jeudi 10 septembre 2020 17:04 À : Nicolas Sornin ; pgsql-gene...@postgresql.org Objet : Re: PG13 partitioned table logical replication On 2020-09-10 14:13, Nicolas Sornin wrote: > I just made some test of upcoming version of porstgresql, especially > the ability to publish partitioned table via root partition. > > I tried to setup PG13 to PG12 replication with different partitioning > schemes. > > My first attempt was to replicate time partitioned table in PG13 > cluster to a regular table in PG12 cluster, but after looking at the > logs I saw the following error : > > 2020-09-09 12:21:33.964 CEST [22976] LOG: logical replication table > synchronization worker for subscription "rep_part_sub", table > "stock_sales" has started > > 2020-09-09 12:21:33.997 CEST [22976] ERROR: table "public.stock_sales" > not found on publisher > > 2020-09-09 12:21:33.999 CEST [20346] LOG: background worker "logical > replication worker" (PID 22976) exited with exit code 1 > > While not being aware of underlying mechanics of logical replication, > I was expecting this use case to work given table names and column > names/types are the same on both sides. > > Is this the intended behavior and simply out of the scope of the > changes introduced by PG13 ? When the subscriber running on PG12 is trying to fetch information about the table from the publisher, it is looking for a real table, which it doesn't find, because it's a partitioned table. So this combination doesn't work. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
