Re: Duplicate key error

[Peter Geoghegan]

On Mon, Nov 11, 2024 at 12:41 AM yudhi s  wrote:
> So it means it will ensure no duplication happens for ID values, but still we 
> are seeing "duplicate key" error. So what is the possible reason here or are 
> we encountering any buggy behaviour here?

MERGE doesn't actually make any promises about not getting unique
violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
make such a promise. That's the main reason why Postgres supports
both.

-- 
Peter Geoghegan

Re: Advice on cluster architecture for two related, but distinct, use cases

[Greg Sabino Mullane]

Some of those requirements are vague, but yes, Patroni should probably be
the first approach you look at. If the second datacenter is just for
redundancy, then a simple setup would be:

DCA (data center A):
Postgres server 1
Postgres server 2

DCB:
Postgres server 3 (set no_failover: true)

You will also need a DCS system of some sort (e.g. etcd on all three
nodes), as well as a backup system (e.g. pgBackRest). Will also need to
decide how automated you want things to be (for example, cross datacenter
failover in the above would be manually done). It should definitely be able
to handle your RPO/RTO requirements easily enough.

[Patroni] However it seems relatively complex to set up and operate


Setting things up can be a little complex, yes, but once done it just
works, so very little operation resources are needed.

We can not assume eg a load balancer.


Possible via the application: see
https://www.postgresql.org/docs/current/libpq-connect.html (esp.
target_session_attrs)

Cheers,
Greg

Question About Native Support for SQL:2011 Temporal Tables in PostgreSQL

[David Lynam]

Hi Postgres Community,

I wanted to ask about support for temporal tables as defined in the SQL:2011 
standard. Right now, I know that we can get similar functionality using 
extensions like pg_temporal, which has been really useful. But for those of us 
running PostgreSQL on managed platforms like AWS RDS (where adding custom 
extensions isn’t an option), it creates a bit of a challenge.
Here are a few questions I’d love your thoughts on:

  1.
Are there any plans or discussions about adding native support for SQL:2011 
temporal tables, so we don’t need extensions?
  2.
What are the main reasons behind needing an extension for this feature? Is it 
tough to build directly into PostgreSQL’s core?
  3.
For those who can’t use extensions (like on AWS RDS), is it practical to build 
temporal table features using only RAW SQL. I'm aware of the Nearform trigger 
solution but I'd really love the syntactical sugar "with SYSTEM VERISON" gives? 
If so, are there any best practices or tips for recreating some of that 
extension-like functionality?

Having native temporal table support would be a huge help for users needing 
built-in tools for audits, historical tracking, and meeting data compliance 
needs. I think adding these features natively could make PostgreSQL even more 
powerful and flexible for different use cases.
I've been looking into https://github.com/xocolatl/periods but I can't tell if 
its an extension or part of the regular deployment.
Thanks so much for taking the time to read this and share your insights. 
Looking forward to hearing what you all think!

Dave

Re: Duplicate key error

[Achilleas Mantzios - cloud]

On 11/11/24 07:40, yudhi s wrote:

We have a merge query as below for a partition table which is range 
partitioned on a truncated date column 'part_date'. And the only 
unique key in this table is a composite primary key on (id, 
part_date). And this merge queries ON condition is based on one of the 
columns i.e ID which is the leading column of the PK.So it means it 
will ensure no duplication happens for ID values, but still we are 
seeing "duplicate key" error. So what is the possible reason here or 
are we encountering any buggy behaviour here?


WITH source_data (col1, col2, col3.col29) AS (VALUES ($1, 
$2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, 
$14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, 
$27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS 
target USING source_data AS source ON target.ID = source.ID WHEN 
MATCHED THEN UPDATE SET ) WHEN NOT MATCHED THEN INSERT (...) 
VALUES ();


Error:
ERROR: duplicate key value violates unique constraint "TAB1_pkey"
Detail: Key (ID, part_date)=(X, 2024-11-04) already exists.


You have to use the whole composite unique key (including part_date) 
when matching against source_data .


If you had uniqueness on "ID" and then added "part_name" to the key, 
you'd still had uniqueness.


BUT, reversely, if you have uniqueness on the pair (ID, part_date) there 
is no guaranteed uniqueness on ID alone, hence your ERROR.




Regards
Yudhi

CREATE SCHEMA ... CREATE M.V. support

[Kirill Reshke]

Hi!
I was exploring the PostgreSQL parser and discovered a very
interesting feature. Users can create schema along with schema objects
in single SQL.

So, for example
```
db1=# create schema shshshsh create table t1(i int) create view v1 as
select * from t1;
CREATE SCHEMA
```
works.

But `create schema shshshsh create materialized view v1 as select `
will not work.
I was trying to search for relevant discussions, but failed. It
appears that the feature has been available from 7.3 versions[1].
Support for materialized views began in 9.3. Perhaps, then, this is
simply something that was overlooked for support?

It appears that supporting this is as simple as changing this parser's
non-terminal [2]. However, perhaps there are justifications for why we
forbid this?

P.S. is this the correct place to make this question?

[1] 
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/parser/gram.y?h=REL7_3_STABLE#n802
[2] 
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/parser/gram.y?h=14e87ffa5c543b5f30ead7413084c25f7735039f#n1580
-- 
Best regards,
Kirill Reshke

Re: CREATE SCHEMA ... CREATE M.V. support

[Alvaro Herrera]

Hello Kirill

On 2024-Nov-11, Kirill Reshke wrote:

> I was exploring the PostgreSQL parser and discovered a very
> interesting feature. Users can create schema along with schema objects
> in single SQL.

Yeah, it's pretty cool.

> Support for materialized views began in 9.3. Perhaps, then, this is
> simply something that was overlooked for support?

Yeah, I don't know why but people seem generally uninterested in
expanding support of commands under CREATE SCHEMA, which I think is a
pity.  However, keep in mind that the set of commands allowed is
dictated by the SQL standard, which says

 ::=
CREATE SCHEMA 
[  ]
[ ... ]

 ::=

| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 
| 

Materialized views are not in the SQL standard, so if we do decide to
support them under CREATE SCHEMA, it would be an extension to the
standard.  (IMO it's quite a natural one at that.)

> It appears that supporting this is as simple as changing this parser's
> non-terminal [2]. However, perhaps there are justifications for why we
> forbid this?
> 
> [2] 
> https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/parser/gram.y?h=14e87ffa5c543b5f30ead7413084c25f7735039f#n1580

I don't think so, or at least I'm not aware of them.

Looking at the object list in the standard document, it looks like we're
missing quite some stuff there.

> P.S. is this the correct place to make this question?

No, this is more a pgsql-hackers question.

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)

Re: Duplicate key error

[yudhi s]

On Mon, Nov 11, 2024 at 1:57 PM Achilleas Mantzios - cloud <
a.mantz...@cloud.gatewaynet.com> wrote:

>
> > WITH source_data (col1, col2, col3.col29) AS (VALUES ($1,
> > $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
> > $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26,
> > $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS
> > target USING source_data AS source ON target.ID = source.ID WHEN
> > MATCHED THEN UPDATE SET ) WHEN NOT MATCHED THEN INSERT (...)
> > VALUES ();
> >
> > Error:
> > ERROR: duplicate key value violates unique constraint "TAB1_pkey"
> > Detail: Key (ID, part_date)=(X, 2024-11-04) already exists.
>
> You have to use the whole composite unique key (including part_date)
> when matching against source_data .
>
> If you had uniqueness on "ID" and then added "part_name" to the key,
> you'd still had uniqueness.
>
> BUT, reversely, if you have uniqueness on the pair (ID, part_date) there
> is no guaranteed uniqueness on ID alone, hence your ERROR.
>
>
>
Actually the table is partitioned on column part_date which is why the
unique key is composite i.e. on ID and part_date. So in that case even we
merge on one of the column i.e. ID which is unique in itself, if any
duplicate value ID tries to be merged it should satisfy the MATCH criteria
and thus it should do a UPDATE on table TAB1 rather than going for NOMATCH
or INSERT. So in that case it should not error out with "duplicate key
violation". Is my understanding correct here?

Re: Advice on cluster architecture for two related, but distinct, use cases

[sunyuc...@gmail.com]

CC

On Mon, Nov 11, 2024 at 09:28 Greg Sabino Mullane 
wrote:

> Some of those requirements are vague, but yes, Patroni should probably be
> the first approach you look at. If the second datacenter is just for
> redundancy, then a simple setup would be:
>
> DCA (data center A):
> Postgres server 1
> Postgres server 2
>
> DCB:
> Postgres server 3 (set no_failover: true)
>
> You will also need a DCS system of some sort (e.g. etcd on all three
> nodes), as well as a backup system (e.g. pgBackRest). Will also need to
> decide how automated you want things to be (for example, cross datacenter
> failover in the above would be manually done). It should definitely be able
> to handle your RPO/RTO requirements easily enough.
>
> [Patroni] However it seems relatively complex to set up and operate
>
>
> Setting things up can be a little complex, yes, but once done it just
> works, so very little operation resources are needed.
>
> We can not assume eg a load balancer.
>
>
> Possible via the application: see
> https://www.postgresql.org/docs/current/libpq-connect.html (esp.
> target_session_attrs)
>
> Cheers,
> Greg
>
>

Re: postgresql-17.0-1 Application - silent installation Issue

[Adrian Klaver]

On 11/10/24 07:11, JOLAPARA Urvi (SAFRAN) wrote:

C2 - Confidential


Hello Team,

I am from Application Packaging team. we have created the package of 
PostgreSQL 17.0-1 Application.


You are going to need to provide more detail on the package creation 
process.




We have used the command line parameter for installation is this : 
*“postgresql-17.0-1-windows-x64.exe--mode unattended --unattendedmodeui 
none --optionfile C:\Temp\Install”*


This is working in English language machine but it is failing in French 
language machine and throwing below error:


You should provide error messages as text not as image, so folks can run 
them through a translator if need be.


erreur d'ecriture du fichier -> file write error

according to Google.




we tried giving permission to this folder, then we have used 


Which directory(folder) would that be?

*“--installer-launage fr”* parameter *“--locale fr”* parameters but 


Should that not be --installer-language?

Also shouldn't this:

--locale fr

be?:

--locale fr-FR


still not working.

Please assist to resolve the issue asap.

Thanks & Regards,

Urvi Jolapara

urvi.jolap...@safrangroup.com 

Image

*www.safran-group.com* 

#
" Ce courriel et les documents qui lui sont joints peuvent contenir des 
informations confidentielles, être soumis aux règlementations relatives 
au contrôle des exportations ou ayant un caractère privé. S'ils ne vous 
sont pas destinés, nous vous signalons qu'il est strictement interdit de 
les divulguer, de les reproduire ou d'en utiliser de quelque manière que 
ce soit le contenu. Toute exportation ou réexportation non autorisée est 
interdite Si ce message vous a été transmis par erreur, merci d'en 
informer l'expéditeur et de supprimer immédiatement de votre système 
informatique ce courriel ainsi que tous les documents qui y sont attachés."

**
" This e-mail and any attached documents may contain confidential or 
proprietary information and may be subject to export control laws and 
regulations. If you are not the intended recipient, you are notified 
that any dissemination, copying of this e-mail and any attachments 
thereto or use of their contents by any means whatsoever is strictly 
prohibited. Unauthorized export or re-export is prohibited. If you have 
received this e-mail in error, please advise the sender immediately and 
delete this e-mail and all attached documents from your computer system."

#


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

postgresql-17.0-1 Application - silent installation Issue

[JOLAPARA Urvi (SAFRAN)]

C2 - Confidential

Hello Team,

I am from Application Packaging team. we have created the package of PostgreSQL 
17.0-1 Application.
We have used the command line parameter for installation is this :  
"postgresql-17.0-1-windows-x64.exe--mode unattended --unattendedmodeui none 
--optionfile C:\Temp\Install"
This is working in English language machine but it is failing in French 
language machine and throwing below error:

[cid:image001.jpg@01DB33B0.FA1B10A0]

we tried giving permission to this folder, then we have used 
"--installer-launage fr" parameter "--locale fr" parameters but still not 
working.
Please assist to resolve the issue asap.

Thanks & Regards,
Urvi Jolapara
urvi.jolap...@safrangroup.com
[Image]
www.safran-group.com

#
" Ce courriel et les documents qui lui sont joints peuvent contenir des 
informations confidentielles, être soumis aux règlementations relatives au 
contrôle des exportations ou ayant un caractère privé. S'ils ne vous sont pas 
destinés, nous vous signalons qu'il est strictement interdit de les divulguer, 
de les reproduire ou d'en utiliser de quelque manière que ce soit le contenu. 
Toute exportation ou réexportation non autorisée est interdite Si ce message 
vous a été transmis par erreur, merci d'en informer l'expéditeur et de 
supprimer immédiatement de votre système informatique ce courriel ainsi que 
tous les documents qui y sont attachés."
**
" This e-mail and any attached documents may contain confidential or 
proprietary information and may be subject to export control laws and 
regulations. If you are not the intended recipient, you are notified that any 
dissemination, copying of this e-mail and any attachments thereto or use of 
their contents by any means whatsoever is strictly prohibited. Unauthorized 
export or re-export is prohibited. If you have received this e-mail in error, 
please advise the sender immediately and delete this e-mail and all attached 
documents from your computer system."
#