Re: gen_random_uuid key collision

2021-09-02 Thread Mark Dilger
reset the seed for your random generator. I haven't looked specifically at your uuid generator, and I don't know if it gets nondeterministic randomness from /dev/random or similar, but deterministic random generators can be made to produce the same sequence again if the seed

Re: Determining if a table really changed in a trigger

2021-10-26 Thread Mark Dilger
ead nothing; CREATE RULE rules=# update my_table set j = '{"key":2}'::jsonb; UPDATE 0 rules=# update my_table set j = '{"key":3}'::jsonb; WARNING: [old.i=1, old.j={"key": 2}] => [new.i=1, new.j={"key": 3}] WARNING: [old.i=2, old.j={"key": 2}] => [new.i=2, new.j={"key": 3}] WARNING: [old.i=3, old.j={"key": 2}] => [new.i=3, new.j={"key": 3}] UPDATE 3 — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Determining if a table really changed in a trigger

2021-10-26 Thread Mark Dilger
> On Oct 26, 2021, at 1:34 PM, Mitar wrote: > > Hi! > > On Tue, Oct 26, 2021 at 10:17 PM Mark Dilger > wrote: >> I can't tell from your post if you want the trivial update to be performed, >> but if not, would it work to filter trivial updates as: >

Re: Determining if a table really changed in a trigger

2021-10-26 Thread Mark Dilger
> On Oct 26, 2021, at 3:39 PM, Mitar wrote: > > On Tue, Oct 26, 2021 at 10:55 PM Mark Dilger > wrote: >> Note that there is a performance cost to storing the old rows using the >> REFERENCING clause of the trigger > > Yea, by moving the trivial update check

Re: Determining if a table really changed in a trigger

2021-10-26 Thread Mark Dilger
and casting the json column to jsonb before comparing for equality. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

2021-11-09 Thread Mark Dilger
executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Re: Logical Replication - Should Destination Table Columns Be Defined With Default Value

2021-11-09 Thread Mark Dilger
the publisher, the subscriber might not bother creating indexes over that data, even if such indexes exist on the publisher. — Mark Dilger EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

Logical replication blocking alter/drop

2017-12-12 Thread Mark Fletcher
t there until I stopped the logical replication processes (I did not have to drop the logical replication slots). Is this expected or are we perhaps doing something wrong? Thanks, Mark

Looking for workaround to avoid deadlock when using exclusion constraint

2018-02-04 Thread Mark Scheffer
e dead-lock is predicted, but i.m.o underestimated. Regards, Mark

Postgres 12.21?

2024-11-05 Thread Mark Hill
Will there be a minor version update to Postgres 12 this Thursday, 11/7/24, going to version 12.21? Thanks, Mark

create policy statement USING clause

2024-11-12 Thread Mark Phillips
er AS PERMISSIVE FOR SELECT TO prm_staff USING (coalesce(deadfiled,false)=false); So my question is specifically about the USING clause, but also more broadly about this attempted application of RLS. Links and advice accepted with gratitude. Mark

Re: create policy statement USING clause

2024-11-13 Thread Mark Phillips
the column, and it now works fine. The better form of the USING clause certainly helped. I am happy to share my notes if someone would like to see them. As for pg 12, an update to the current stable release is on the project roadmap. Cheers, - Mark > On Nov 12, 2024, at 12:48 AM, Laurenz Alb

Row level security policy

2024-11-13 Thread Mark Phillips
Given a database table with one policy statement FOR SELECT applied, it is necessary to apply additional policy statements for insert, update, and delete operations? My testing indicates that this is case but I haven’t found an explanation of this requirement in the documentation. - Mark

Re: Row level security policy

2024-11-13 Thread Mark Phillips
Thank you. I will revisit my test cases to be sure I have the use cases covered. - Mark, out and about.On Nov 13, 2024, at 5:36 PM, David G. Johnston wrote:On Wednesday, November 13, 2024, Mark Phillips <mphill...@mophilly.com> wrote:Given a database table with one policy statement FOR

Re: Row level security policy

2024-11-13 Thread Mark Phillips
are greatly appreciated. - Mark Here are two tests I ran using pg 12 (upgrade on the schedule). Given a table “customer” with a column “deadfiled” with a default of false. If deadfile is true, then exclude row from queries executed by role “staff”. Test 1 CREATE POLICY

Re: Row level security policy

2024-11-14 Thread Mark Phillips
Thank you for clarifying this. I missed that even though it is there in the second paragraph. - Mark, out and about. > On Nov 14, 2024, at 1:57 AM, Laurenz Albe wrote: > > On Wed, 2024-11-13 at 17:33 -0800, Mark Phillips wrote: >> Given a database table with one policy state

Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

2024-12-20 Thread mark bradley
I'm getting a strange error message when I try to insert a date using the view/edit grid in pgadmin. See below. I've tried quotes, no quotes and various formats. The column type is clearly "date." [cid:4ff69cfe-2efa-4636-8dde-6230512706f7] Mark Brady, Ph.D. Deputy Chie

Re: Duplicate Key Values

2025-03-15 Thread mark bradley
Adrian & Ron, Thank you for your assistance. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Thursday, March 13, 2025 1:03 PM To: mark bradley ; Ron Johnson ; pgsql-general S

Re: Duplicate Key Values

2025-03-07 Thread mark bradley
Here are some of the references from Copilot https://dba.stackexchange.com/questions/62675/why-does-my-table-hold-duplicate-primary-keys https://stackoverflow.com/questions/1461/having-duplicate-rows-on-a-primary-key-and-unique-constraints-in-postgres Best regards, Mark Brady amazon.com

Re: Duplicate Key Values

2025-03-07 Thread mark bradley
I hesitate to just delete my tables and start over because this error will reoccur. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Thursday, March 6, 2025 3:34 PM To: mark bradley Cc:

Re: Duplicate Key Values

2025-03-07 Thread mark bradley
Wouldn't that be nice 🙂 Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Friday, March 7, 2025 10:55 AM To: mark bradley Cc: pgsql-general Subject: Re: Duplicate Key Values On 3

Re: Duplicate Key Values

2025-03-09 Thread mark bradley
I tried adding a dummy column with different values and then deleting one, as you suggested. Got the same message. Mark Brady, Ph.D. Deputy Chief Data Officer, TRMC amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: mark bradley

Re: Duplicate Key Values

2025-03-06 Thread mark bradley
Here is the table definition: [cid:ecac8e92-826b-45c8-95a8-aaf0e55c4f9c] And here is the error message I get when I try to delete a duplicate: [cid:a0f5f298-984d-4f89-abd2-475c02e65b9d] Mark Brady, amazon.com/author/markjbrady<https://amazon.com/author/markjbr

Re: Duplicate Key Values

2025-03-11 Thread mark bradley
tables. I will reenter the data, make a backup, and then try your further extended suggestions. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Friday, March 7, 2025 3:25 PM To: mark br

Re: Duplicate Key Values

2025-03-11 Thread mark bradley
The rows that were preserved in the nodes table were the ones that were not dups originally. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Tuesday, March 11, 2025 10:56 AM To: mark b

Re: Duplicate Key Values

2025-03-11 Thread mark bradley
b REINDEX TABLE node; Also reindexed table with node_id as a foreign key in the same way. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Tuesday, March 11, 2025 11:12 AM To: mark b

Re: Duplicate Key Values

2025-03-11 Thread mark bradley
dataset and processing_node tables. Is there anything in Postgres log at the time you did the above that showed it did more then a REINDEX? Not that I can tell. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> ___

Re: Duplicate Key Values

2025-03-11 Thread mark bradley
to create a simple database from scratch and see if I can duplicate this behavior. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Tuesday, March 11, 2025 3:37 PM To: mark bradley C

Re: Duplicate Key Values

2025-03-13 Thread mark bradley
> I'm assuming that by 'simple version' you mean no inheritance. Anyway, inheritance can be undone via ALTER TABLE dataset NO INHERIT node; Now, there are no dups and hopefully it will stay that way. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.

Re: Duplicate Key Values

2025-03-13 Thread mark bradley
74] Where node_id is a foreign key in dataset, and node_type is not. Best regards, Mark Brady amazon.com/author/markjbrady<https://amazon.com/author/markjbrady> From: Adrian Klaver Sent: Thursday, March 13, 2025 12:05 PM To: mark bradley ; Ron Johnson ; pg

Duplicate Key Values

2025-03-05 Thread mark bradley
Although I did not enter them, somehow duplicate primary key values have appeared in one of my tables. I can't remove the offending rows because the key is a foreign key in another table. What to do? Mark Brady, amazon.com/author/markjbrady<https://amazon.com/author/markjbrady>

<    1   2