Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Tom Lane]

Paul Foerster  writes:
>> On 26 Nov 2024, at 17:56, Adrian Klaver  wrote:
>> When you run the query again do you get the same two statements?

> Yes. I can repeat the above 4 steps as much as I want. The result remains the 
> same. I would have expected to have an empty result doing the final repeated 
> select, but it shows exactly the same output.

I would have expected an empty result too.  Can you confirm that
p_ci_pipelines used to be a partition of something?  Can you show us
the full DDL (or psql \d+ output) for the partitioned table it
used to be part of, and for that matter also for p_ci_pipelines?
Did the FK used to reference the whole partitioned table, or just
this partition?

I'm suspicious that our repair recipe might not have accounted
for self-reference FKs fully, but that's just a gut feeling at
this point.

regards, tom lane

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Paul Foerster]

Hi Adrian,

> On 26 Nov 2024, at 17:56, Adrian Klaver  wrote:
> 
> Did you commit the statements?

Yes. I have autocommit on, the psql default.

> Are you using concurrent sessions to do this?

No. I do this in one session. 1. select, 2. drop, 3. add, 4. select.

> When you run the query again do you get the same two statements?

Yes. I can repeat the above 4 steps as much as I want. The result remains the 
same. I would have expected to have an empty result doing the final repeated 
select, but it shows exactly the same output.

Cheers,
Paul

Re: License question

[Adrian Klaver]

On 11/25/24 22:04, prashant sinha wrote:

Hi Christophe,
Thank you so much for the response. One of my client is also requesting 
details of licensed version of PostgreSQL. Could you please guide me 


They are all licensed, it comes down to what the license restrictions 
are. The community edition essentially has no restrictions and is free. 
Commercial forks will have restrictions and will cost.


cost of PostgreSQL license which they want to get installed on their 
(client’s) on premise server. Not sure how pricing works for enterprise 
license which have all associated support for enterprise. Also not sure 
if we can install on any supported license for on-Prem server or we have 
to buy specific server recommended by PostgreSQL. Will appreciate your 
guidance here.


PostgreSQL is not a company.  The PostgreSQL Global Development Group 
the body that oversees it is a non-profit.


Per my previous post companies that deal with Postgres can be found here:

https://www.postgresql.org/support/professional_support/

and here:

https://www.postgresql.org/support/professional_hosting/




Thanks,
Prashant


Sent from Yahoo Mail for iPhone 



On Tuesday, November 26, 2024, 1:29 AM, Christophe Pettus 
 wrote:




 > On Nov 21, 2024, at 21:40, prashant sinha
mailto:prashantss...@yahoo.co.in>> wrote:
 > Is there a free version of PostgreSQL available which I can use
without buying any licenses? In case I want to self install and
manage the database? Just need guidance if I can install the
available version from site for business purpose too or I must buy
licenses? Will appreciate an appropriate related answer.


PostgreSQL itself is an open source project distributed under a very
permissive license:

https://www.postgresql.org/about/licence/


No payment is required to download and run it.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: License question

[Bruce Momjian]

On Mon, Nov 25, 2024 at 10:12:09PM -0800, Christophe Pettus wrote:
> Hello,
>
> I think there is a fundamental misunderstanding here about how
> PostgreSQL's licensing works.
>
> You do not pay for the community version of PostgreSQL.  The license
> specifically states it is available without a fee.  It's free.  No one
> collects money for the community version of PostgreSQL.
>
> There's no "enterprise" version of PostgreSQL that is provided by the
> community.  It's just PostgreSQL.
>
> There are vendors which provide either commercial versions of
> PostgreSQL based on the community version, or which provide commercial
> support for PostgreSQL, but those are separate things from the
> PostgreSQL community version, and you would need to talk to the
> individual companies to find out what their arrangements are.

I have blogged about this confusion:

https://momjian.us/main/blogs/pgblog/2023.html#September_1_2023

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  When a patient asks the doctor, "Am I going to die?", he means 
  "Am I going to die soon?"

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Adrian Klaver]

On 11/26/24 01:59, Paul Foerster wrote:

Hi,

I have a question regarding the recent security update for PostgreSQL 15.

We have a gitlab database. It used to run on the PostgreSQL 15.8 software. I 
updated from 15.8 to 15.10 and executed the corrective actions as outlined in:

https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and-1221-released-2955/

I executed "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));" which gave the result below:


-[ RECORD 1 
]-+-
constrained table | p_ci_pipelines
constraint| fk_262d4c2d19_p
references| p_ci_pipelines
drop  | alter table p_ci_pipelines drop constraint fk_262d4c2d19_p;
add   | 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;

I then executed the two alter table statements without any problem. No error 
was reported and all seems ok.

Now, if I execute the query to find the constraints again, I would expect the 
result to be empty. But it is not.


Did you commit the statements?

Are you using concurrent sessions to do this?

When you run the query again do you get the same two statements?



Why is that and what am I supposed to do? Is the problem fixed now or is it 
still pending? Any ideas would be greatly appreciated.

Cheers
Paul



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Paul Foerster]

Hi Tom,

> On 26 Nov 2024, at 22:25, Tom Lane  wrote:
> 
> I would have expected an empty result too.  Can you confirm that
> p_ci_pipelines used to be a partition of something?  Can you show us
> the full DDL (or psql \d+ output) for the partitioned table it
> used to be part of, and for that matter also for p_ci_pipelines?
> Did the FK used to reference the whole partitioned table, or just
> this partition?
> 
> I'm suspicious that our repair recipe might not have accounted
> for self-reference FKs fully, but that's just a gut feeling at
> this point.

Of course, it contains no secret data. Please find the full log below. 
According to the add constraint statement, it is a self reference.

Thanks for looking into it.

Cheers,
Paul


gitxp1t=# \set
AUTOCOMMIT = 'on'
...
VERSION = 'PostgreSQL 15.10 on x86_64-pc-linux-gnu, compiled by gcc (SUSE 
Linux) 7.5.0, 64-bit'
...



gitxp1t=# SELECT conrelid::pg_catalog.regclass AS "constrained table",
gitxp1t-#conname AS constraint,
gitxp1t-#confrelid::pg_catalog.regclass AS "references",
gitxp1t-#pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
gitxp1t(#  conrelid::pg_catalog.regclass, conname) AS 
"drop",
gitxp1t-#pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
gitxp1t(#  conrelid::pg_catalog.regclass, conname,
gitxp1t(#  pg_catalog.pg_get_constraintdef(oid)) AS 
"add"
gitxp1t-# FROM pg_catalog.pg_constraint c
gitxp1t-# WHERE contype = 'f' AND conparentid = 0 AND
gitxp1t-#(SELECT count(*) FROM pg_catalog.pg_constraint c2
gitxp1t(# WHERE c2.conparentid = c.oid) <>
gitxp1t-#(SELECT count(*) FROM pg_catalog.pg_inherits i
gitxp1t(# WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
gitxp1t(#   EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
gitxp1t(#   WHERE partrelid = i.inhparent));
constrained table |   constraint|   references   |  
  drop |
 add
  
---+-++-+--
p_ci_pipelines| fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE 
p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | 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;
(1 row)
 gitxp1t=# ALTER TABLE p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p;
ALTER TABLE
gitxp1t=# 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;
ALTER TABLE
gitxp1t=# SELECT conrelid::pg_catalog.regclass AS "constrained table",
gitxp1t-#conname AS constraint,
gitxp1t-#confrelid::pg_catalog.regclass AS "references",
gitxp1t-#pg_catalog.format('ALTER TABLE %s DROP CONSTRAINT %I;',
gitxp1t(#  conrelid::pg_catalog.regclass, conname) AS 
"drop",
gitxp1t-#pg_catalog.format('ALTER TABLE %s ADD CONSTRAINT %I %s;',
gitxp1t(#  conrelid::pg_catalog.regclass, conname,
gitxp1t(#  pg_catalog.pg_get_constraintdef(oid)) AS 
"add"
gitxp1t-# FROM pg_catalog.pg_constraint c
gitxp1t-# WHERE contype = 'f' AND conparentid = 0 AND
gitxp1t-#(SELECT count(*) FROM pg_catalog.pg_constraint c2
gitxp1t(# WHERE c2.conparentid = c.oid) <>
gitxp1t-#(SELECT count(*) FROM pg_catalog.pg_inherits i
gitxp1t(# WHERE (i.inhparent = c.conrelid OR i.inhparent = c.confrelid) AND
gitxp1t(#   EXISTS (SELECT 1 FROM pg_catalog.pg_partitioned_table
gitxp1t(#   WHERE partrelid = i.inhparent));
constrained table |   constraint|   references   |  
  drop |
 add
  
---+-++-+--
 p_ci_pipelines| fk_262d4c2d19_p | p_ci_pipelines | ALTER TABLE 
p_ci_pipelines DROP CONSTRAINT fk_262d4c2d19_p; | ALTER TABLE p_ci_pipelines 
ADD CONSTRAINT fk_262d4c2d19_p FOREIGN KEY (auto_canceled_by_partition_id, 
auto

Re: Unique key constraint Issue

[shashidhar Reddy]

Thank you Tom!

The issue is with OS upgrade we could able replicate it.

On Mon, 25 Nov, 2024, 9:32 pm Tom Lane,  wrote:

> shashidhar Reddy  writes:
> > The issue is a unique key constraint with two columns one is character
> > another is integer. At some point the unique key did not work as I see
> > duplicate values with these two columns combination and it happened on
> > multiple servers on multiple databases on same table with same unique
> key.
>
> If the table has existed for some time (like, across updates of the
> underlying operating system) then your problem likely traces to
> changes in the OS' sorting rules for character strings:
>
> https://wiki.postgresql.org/wiki/Locale_data_changes
>
> Such a change causes the unique key's index to be out of sort order
> and thus effectively corrupt from PG's viewpoint: searches may or
> may not find an entry that is there.  Once that happens it's pretty
> easy for duplicate entries to get added.
>
> The fix is to REINDEX affected indexes.  But if you already have
> duplicate entries in the table, you'll need to correct them before
> REINDEX will succeed.
>
> regards, tom lane
>

Re: Unique key constraint Issue

[shashidhar Reddy]

Thank you Adrian.

We replicated the issue with OS upgrade.

On Tue, 26 Nov, 2024, 11:06 am Adrian Klaver, 
wrote:

> On 11/25/24 19:07, shashidhar Reddy wrote:
> > Hi Adrian,
> >
> > Thank you for your response.
> >
> > I don't think index corrupted as I can generate the ddl script from the
>
> What DDL script?
>
> > index and the index is still in use when we query.
>
> See Tom Lanes post for more detail. Short version the index will still
> 'work' except for the part where it lets in duplicate entries.
>
> >   To rebuild the index we are still removing duplicate entries. But
> > nothing found in logs.
> >
> > On Mon, 25 Nov, 2024, 9:30 pm Adrian Klaver,  > > wrote:
> >
> > On 11/25/24 00:44, shashidhar Reddy wrote:
> >  > Hi Ajit,
> >  >
> >  > Thank you for your reply!
> >  >
> >  > The result of the query is same across all servers and yes
> streaming
> >  > replication does not have any issue but the question is how did
> >  > duplicate values entered when there unique key in place.
> >
> > Corrupted index?
> >
> > Have you tried doing a REINDEX?
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: DB Switchover using repmgr--Error

[jayakumar s]

Hi Paul,

Thanks for your help. I have fixed that issue. Now I am getting one more
issue. Can you help to fix the below error?

[postgres@post1 bin]$ /usr/pgsql-16/bin/repmgr -f
/var/lib/pgsql/repmgr.conf cluster show
WARNING: node "standby" not found in "pg_stat_replication"
 ID | Name| Role| Status| Upstream  | Location | Priority |
Timeline | Connection string
+-+-+---+---+--+--+--+-
 1  | primary | primary | * running |   | default  | 100  | 8
 | host=192.168.29.193 user=repmgr dbname=repmgr connect_timeout=2
 2  | standby | standby |   running | ! primary | default  | 100  | 8
 | host=192.168.29.9 user=repmgr dbname=repmgr connect_timeout=2

WARNING: following issues were detected
  - node "standby" (ID: 2) is not attached to its upstream node "primary"
(ID: 1)

[postgres@post1 bin]$

On Tue, Nov 26, 2024 at 3:41 PM Paul Foerster 
wrote:

> Hi,
>
> > [postgres@post1 bin]$ ./repmgr -f /var/lib/pgsql/repmgr.conf primary
> register
> > ERROR: following errors were found in the configuration file:
> >   syntax error in file "/var/lib/pgsql/repmgr.conf" line 3, near token
> "data_directory"
> >   syntax error in file "/var/lib/pgsql/repmgr.conf" line 6, near token
> "log_file"
> > [postgres@post1 bin]$
> >
> > Conf file:
> >
> > [postgres@post1 data]$ cat /var/lib/pgsql/repmgr.conf
> > node_id=1
> > node_name=primary
> > conninfo='host=192.168.29.193 user=repmgr dbname=repmgr
> connect_timeout=2' data_directory='/application/pgsql/data'
> failover=automatic
> > promote_command='/usr/pgsql-16/bin/repmgr standby promote -f
> /var/lib/pgsql/repmgr.conf --log-to-file'
> > follow_command='/usr/pgsql-16/bin/repmgr standby follow -f
> /var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
> > pg_bindir='/usr/pgsql-16/bin' log_file='/usr/pgsql-16/repmgr.log'
> > [postgres@post1 data]$
>
> I'm not a repmgr guru but at first glance I would say that your config
> lacks two line breaks. It should probably look like this:
>
> node_id=1
> node_name=primary
> conninfo='host=192.168.29.193 user=repmgr dbname=repmgr connect_timeout=2'
> data_directory='/application/pgsql/data'
> failover=automatic
> promote_command='/usr/pgsql-16/bin/repmgr standby promote -f
> /var/lib/pgsql/repmgr.conf --log-to-file'
> follow_command='/usr/pgsql-16/bin/repmgr standby follow -f
> /var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
> pg_bindir='/usr/pgsql-16/bin' log_file='/usr/pgsql-16/repmgr.log'
>
> Cheers
> Paul

Re: DB Switchover using repmgr--Error

[Paul Foerster]

Hi,

> [postgres@post1 bin]$ ./repmgr -f /var/lib/pgsql/repmgr.conf primary register
> ERROR: following errors were found in the configuration file:
>   syntax error in file "/var/lib/pgsql/repmgr.conf" line 3, near token 
> "data_directory"
>   syntax error in file "/var/lib/pgsql/repmgr.conf" line 6, near token 
> "log_file"
> [postgres@post1 bin]$
> 
> Conf file:
> 
> [postgres@post1 data]$ cat /var/lib/pgsql/repmgr.conf
> node_id=1
> node_name=primary
> conninfo='host=192.168.29.193 user=repmgr dbname=repmgr connect_timeout=2' 
> data_directory='/application/pgsql/data' failover=automatic
> promote_command='/usr/pgsql-16/bin/repmgr standby promote -f 
> /var/lib/pgsql/repmgr.conf --log-to-file'
> follow_command='/usr/pgsql-16/bin/repmgr standby follow -f 
> /var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
> pg_bindir='/usr/pgsql-16/bin' log_file='/usr/pgsql-16/repmgr.log'
> [postgres@post1 data]$

I'm not a repmgr guru but at first glance I would say that your config lacks 
two line breaks. It should probably look like this:

node_id=1
node_name=primary
conninfo='host=192.168.29.193 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/application/pgsql/data'
failover=automatic
promote_command='/usr/pgsql-16/bin/repmgr standby promote -f 
/var/lib/pgsql/repmgr.conf --log-to-file'
follow_command='/usr/pgsql-16/bin/repmgr standby follow -f 
/var/lib/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
pg_bindir='/usr/pgsql-16/bin' log_file='/usr/pgsql-16/repmgr.log'

Cheers
Paul

PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Paul Foerster]

Hi,

I have a question regarding the recent security update for PostgreSQL 15.

We have a gitlab database. It used to run on the PostgreSQL 15.8 software. I 
updated from 15.8 to 15.10 and executed the corrective actions as outlined in:

https://www.postgresql.org/about/news/postgresql-171-165-159-1414-1317-and-1221-released-2955/

I executed "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));" which gave the result below:


-[ RECORD 1 
]-+-
constrained table | p_ci_pipelines
constraint| fk_262d4c2d19_p
references| p_ci_pipelines
drop  | alter table p_ci_pipelines drop constraint fk_262d4c2d19_p;
add   | 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;

I then executed the two alter table statements without any problem. No error 
was reported and all seems ok.

Now, if I execute the query to find the constraints again, I would expect the 
result to be empty. But it is not.

Why is that and what am I supposed to do? Is the problem fixed now or is it 
still pending? Any ideas would be greatly appreciated.

Cheers
Paul

Re: change JSON serialization for BIGINT?

[Victor Yegorov]

вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin :

> Is there a way to have Postgres serialize BIGINT as a string rather than
> number in JSON?  By default it does this:
>
>
> select row_to_json(row(500::bigint));
>  row_to_json
> -
>  {"f1":500}
>
> But I want it to do this (note that "500" is quoted):
>
> select row_to_json(row(500::bigint));
>  row_to_json
> -
>  {"f1":"500"}
>

Will this work?

select row_to_json(row(500::text));

-- 
Victor Yegorov

change JSON serialization for BIGINT?

[Tim McLaughlin]

Is there a way to have Postgres serialize BIGINT as a string rather than number 
in JSON?  By default it does this:


select row_to_json(row(500::bigint));
 row_to_json
-
 {"f1":500}

But I want it to do this (note that "500" is quoted):

select row_to_json(row(500::bigint));
 row_to_json
-
 {"f1":"500"}

I tried doing this, but it has no effect:

CREATE or replace FUNCTION bigintJson(bigint) RETURNS json
    AS 'select $1::text::json;'
    LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

create cast (bigint as json) with function bigintJson(bigint) as implicit;

Thanks for any guidance.

--
Tim McLaughlin

Re: change JSON serialization for BIGINT?

[Tim McLaughlin]

Thanks for the idea.  We could do that, but that would require code changes to 
our thousands of lines of SQL that generate JSON to add the explicit cast to 
text.  I am hoping not to have to do that.

There are numerous places where I would think this would be useful:
BIGINT or NUMERIC overflows Javascript Number Max Integer (my problem) with the 
default JSON parser in NodeJS
DECIMAL / NUMERIC value loses precision due to being ingest as a JSON Number 
which is annotated in high precision using a standard JSON parser

There are probably others, but the point is that there are cases where it would 
be great to override the default JSON serialization of Postgres scalars rather 
than have to go explicitly cast them all to text first.

--
Tim McLaughlin
On Nov 26, 2024 at 6:36 AM -0500, Victor Yegorov , wrote:
> вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin :
> > > Is there a way to have Postgres serialize BIGINT as a string rather than 
> > > number in JSON?  By default it does this:
> > >
> > >
> > > select row_to_json(row(500::bigint));
> > >  row_to_json
> > > -
> > >  {"f1":500}
> > >
> > > But I want it to do this (note that "500" is quoted):
> > >
> > > select row_to_json(row(500::bigint));
> > >  row_to_json
> > > -
> > >  {"f1":"500"}
>
> Will this work?
>
> select row_to_json(row(500::text));
>
> --
> Victor Yegorov