Performance degrade on insert on conflict do nothing

Hi insert into dictionary(lang,tid,sportid,brandid,translatedtext,objecttype,basetid) values ($1,$2,$3,$4,$5,$6,$7) on conflict do nothing *8vcpus and 32gb ram Number of calls per sec 1600 at this time 42% of cpu utilized Max in ms 33.62 per call Avg in ms

Re: Test mail for pgsql-general

Hi Adrian, > First match wins loses in this case. The entries are processed top to > bottom the first the one matches in this case: > > local all all peer > > Per This answers my question. Thanks for the help, -- Chris.

Re: Test mail for pgsql-general

Hi David, > The second line is pointless, the first three columns are compared against the > incoming connection host/user/dbname to find out how authentication should be > handled. The first match wins. So for every local connection peer, and only > peer, is going to be used since everything mat

Re: Removing duplicate rows in table

On 2024-09-10 18:38 +0200, Rich Shepard wrote: > P.S. Please reply to the mail list so I receive only one copy of your > message, not two. You can configure your list subscription to not receive an extra copy. That setting is under "Global configuration" on https://lists.postgresql.org/manage/. -

Re: Test mail for pgsql-general

On 9/10/24 16:21, Chris Miller wrote: Hi Folks, I am confused about authentication. I understand that in the local connection case, I have choices of “peer”, and “md5” (password). In pg_hba.conf, I have the lines: local all all peer local all all md5 I have an OS user “postgres”, and I

Re: Test mail for pgsql-general

On Tuesday, September 10, 2024, Chris Miller wrote: > Hi Folks, > > I am confused about authentication. I understand that in the local > connection case, I have choices of “peer”, and “md5” (password). > > > In pg_hba.conf, I have the lines: > > > local all all peer > > local all all md5 > > The

Re: Test mail for pgsql-general

Hi Folks, I am confused about authentication. I understand that in the local connection case, I have choices of “peer”, and “md5” (password). In pg_hba.conf, I have the lines: local all all peer local all all md5 I have an OS user “postgres”, and I can “su – postgres”, which brings me to

Re: Overlapping values (?) in multi-column partitioned tables

> On Sep 10, 2024, at 16:02, David Rowley wrote: > > (it seems strange to always have MINVALUE and MAXVALUE as the range. > I'm guessing that was just an example.) Correct.

Re: Overlapping values (?) in multi-column partitioned tables

> On Sep 10, 2024, at 15:57, Laurenz Albe wrote: > So the upper limit of the first partition is strictly bigger than the lower > end > of the second partition. > > "record" types have the same lexicographical sorting order as ORDER BY > clauses. Ah, OK, got it. I was thinking of them as a

Re: Overlapping values (?) in multi-column partitioned tables

On Wed, 11 Sept 2024 at 10:57, Laurenz Albe wrote: > You should specify the upper bound as ('2023-12-31 23:59:59.99', > MAXVALUE). Or maybe do multi-level partitioning. (it seems strange to always have MINVALUE and MAXVALUE as the range. I'm guessing that was just an example.) David

Re: Overlapping values (?) in multi-column partitioned tables

On Tue, 2024-09-10 at 15:13 -0700, Christophe Pettus wrote: > I am clearly not understanding something.  Consider: > > > xof=# create table t (pk bigint not null, ts timestamp not null) partition > > by range (ts, pk); > > CREATE TABLE > > xof=# create table t1 partition of t for values from > >

Re: Overlapping values (?) in multi-column partitioned tables

The comparisons for partitions are lower <= value < upper, so this works fine: > xof=# create table t (pk bigint not null, ts timestamp not null) partition by > range (ts); > CREATE TABLE > xof=# create table t1 partition of t for values from > ('2024-01-01'::timestamp) to ('2024-02-01'::timesta

Overlapping values (?) in multi-column partitioned tables

Hi, I am clearly not understanding something. Consider: > xof=# create table t (pk bigint not null, ts timestamp not null) partition by > range (ts, pk); > CREATE TABLE > xof=# create table t1 partition of t for values from > ('2024-01-01'::timestamp, minvalue) to ('2024-02-01'::timestamp, max

Re: Strange permission effect depending on DEFERRABILITY

Στις 10/9/24 20:22, ο/η Laurenz Albe έγραψε: On Tue, 2024-09-10 at 12:20 +0300, Achilleas Mantzios - cloud wrote: On 9/10/24 00:09, Laurenz Albe wrote: On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote: The below runs on PostgreSQL 16.4 We are trying to implement a certain o

Re: Strange permission effect depending on DEFERRABILITY

On Tue, 2024-09-10 at 12:20 +0300, Achilleas Mantzios - cloud wrote: > On 9/10/24 00:09, Laurenz Albe wrote: > > On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote: > > > The below runs on PostgreSQL 16.4 > > > > > > We are trying to implement a certain operation based on a securi

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Rich Shepard wrote: to see how big a problem it is. If it is only a few projects it could just a matter of manually deleting the extras. Not knowing how to do that is why I wrote. A web search (which I should have done before posting this thread) shows me how to do this

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Adrian Klaver wrote: You might want to do something like: select proj_nbr, count(*) as ct from projects group by proj_nbr; to see how big a problem it is. If it is only a few projects it could just a matter of manually deleting the extras. Adrian, It's a small table, n

Re: Removing duplicate rows in table

On 9/10/24 08:29, Rich Shepard wrote: On Tue, 10 Sep 2024, Adrian Klaver wrote: Is there a Primary Key or Unique index on this table? Adrian, No. It didn't occur to me to make the project number a PK as this table is not related to others in the database. But, yesterday it occurred to me to

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Andreas Kretschmer wrote: you can use the hidden ctid-column: postgres=# create table demo (id int, val text); CREATE TABLE postgres=# insert into demo values (1, 'test1'); INSERT 0 1 postgres=# insert into demo values (1, 'test1'); INSERT 0 1 postgres=# insert into demo va

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Christophe Pettus wrote: If you don't mind taking the time to swap tables, you can always do an INSERT ... SELECT DISTINCT into a new table, and then swap it with the existing table. Christophe, I'll make the proj_nbr table the PK then do as you recommend. Thank you, R

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Francisco Olarte wrote: Do you have any kid of corruption (i.e, unique index violation) or is it just a duplicate problem? Francisco, Only a duplicate problem because when I created this table I didn't make the proj_nbr column a PK. Also, if you do not have any uniquene

Re: Removing duplicate rows in table

On Tue, 10 Sep 2024, Adrian Klaver wrote: Is there a Primary Key or Unique index on this table? Adrian, No. It didn't occur to me to make the project number a PK as this table is not related to others in the database. But, yesterday it occurred to me to make the proj_nbr a PK to eliminate fu

Re: Removing duplicate rows in table

Am 10.09.24 um 17:07 schrieb Rich Shepard: I've no idea how I entered multiple, identical rows in a table but I want to delete all but one of these rows. Here's an example: bustrac=# select * from projects where proj_nbr = '4242.01';  proj_nbr |   proj_name    | start_date |  end_date  | de

Re: Removing duplicate rows in table

On 9/10/24 08:07, Rich Shepard wrote: I've no idea how I entered multiple, identical rows in a table but I want to delete all but one of these rows. Is there a Primary Key or Unique index on this table? Here's an example: bustrac=# select * from projects where proj_nbr = '4242.01';  proj_

Removing duplicate rows in table

I've no idea how I entered multiple, identical rows in a table but I want to delete all but one of these rows. Here's an example: bustrac=# select * from projects where proj_nbr = '4242.01'; proj_nbr | proj_name| start_date | end_date | description | notes --+

Re: infinite loop in an update statement

On 9/10/24 07:47, Fabrice Chapuis wrote: Reply to list also Ccing list no trigger on theses tables On Mon, Sep 9, 2024 at 5:00 PM Adrian Klaver > wrote: On 9/9/24 07:55, Fabrice Chapuis wrote: > Hi, > > table a and b are empty, this quer

Re: Database schema for "custom fields"

The relational purists will gave their concerns, but especially given what you described about your performance and volumetrics, there is a reason why JSON(b) is a thing. For type checking, and more, I've had success a multi-key approach so that one entry might comprise: - A "name" - A "type" - A

Re: Strange permission effect depending on DEFERRABILITY

On 9/10/24 00:09, Laurenz Albe wrote: On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote: The below runs on PostgreSQL 16.4 We are trying to implement a certain operation based on a security definer function : mariner_update_availability_date This is supposed to update a tab

Re: Database schema for "custom fields"

On 2024-09-10 12:08:14 +0500, Muhammad Usman Khan wrote: > There is not a properly defined solution but you can try the > Entity-Attribute-Value (EAV) Model. This is an alternative approach, where a > separate table is used to store custom fields as attributes for each record. > New fields can be a

Re: Database schema for "custom fields"

Hi, There is not a properly defined solution but you can try the Entity-Attribute-Value (EAV) Model. This is an alternative approach, where a separate table is used to store custom fields as attributes for each record. New fields can be added without altering the schema. There will be no need for D