Re: Is consistent (deterministic) ordering possible in our case?

PostgreSQL: Documentation: 13: 9.27. System Administration Functions I am not an expert here, but if you can make use of pg_try_advisory_lock to ensure the same rows in the same function are not modified by two

Is consistent (deterministic) ordering possible in our case?

Hi, Our story is as follows. We have a function called Foo() which internally will call many other sub-functions. These sub-functions have complicated if..else.. conditions. Calling function Foo() will start a transaction. Now the scenario is that when two processes call Foo() at the same time, th

Re: Internationalisation (i18n) with Postgres as backend

On 6/1/21 5:00 PM, Laura Smith wrote: What is your notion of "object".  I first assumed it was akin to "document" but then pages have objects. I think my terminology is a bit off. A document/page has object(s) on it. Or, perhaps better expressed, think of document/page as the template and obj

Re: Internationalisation (i18n) with Postgres as backend

Hi Steve, I didn't consider hstore, I did consider jsonb though. The thing that made me lean towards individual rows rather than consolidated was that I thought versioning would ultimately be easier/cleaner to achieve with individual rows (e.g. using tsrange & gist exclude).  But willing to be

Re: Internationalisation (i18n) with Postgres as backend

> What is your notion of "object".  I first assumed it was akin to > "document" but then pages have objects. I think my terminology is a bit off. A document/page has object(s) on it. Or, perhaps better expressed, think of document/page as the template and object(s) is what fills the gaps in t

Re: Internationalisation (i18n) with Postgres as backend

Hi Laura, Did you consider using hstore to store language and data as a kvp? For example: b2bc_owner@b2bcreditonline=# create table langtest(pageid text, objectid text, objectdata hstore, constraint langtest_pk primary key (pageid, objectid)); CREATE TABLE b2bc_owner@b2bcreditonline=# insert into

Re: Internationalisation (i18n) with Postgres as backend

On 6/1/21 2:09 PM, Laura Smith wrote: Hi, I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS. So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgr

Internationalisation (i18n) with Postgres as backend

Hi, I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS. So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgresql gurus. I am especially interested

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

ok, so Tom ran on pg14 it seems. :) On Wed, 2 Jun 2021 at 00:53, Thomas Munro wrote: > On Wed, Jun 2, 2021 at 7:15 AM Vijaykumar Jain > wrote: > > i only get workers to create mv, but refresh mv plan does not use > workers for the same conf params. > > Yeah, this changed in v14: > > > https:/

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

On Wed, Jun 2, 2021 at 7:15 AM Vijaykumar Jain wrote: > i only get workers to create mv, but refresh mv plan does not use workers for > the same conf params. Yeah, this changed in v14: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e7ccd9ef64d05e87ceb1985d459bef9031205c0

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

ok i see this. i may be wrong, but even when i force parallel cost to 0, i only get workers to create mv, but refresh mv plan does not use workers for the same conf params. *** postgres=# create table if not exists t( id int primary key, value int ); CREATE TABLE postgres=# insert

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

Philip Semanchuk writes: > I can confirm that it’s not waiting on a lock. In addition, through the AWS > CPU utilization monitor I can see that the REFRESH uses one CPU/worker > whereas the CREATE uses four. This is consistent with the EXPLAIN ANALYZE for > the CREATE which says it uses four wo

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

> On Jun 1, 2021, at 2:20 PM, Vijaykumar Jain > wrote: > > if you are not using it concurrently, can you confirm the there are *no > active* queries on the mv. > refresh requires AccessExclusiveLock and will wait, till it gets one. > just asking if you can rule out the extended time is not d

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

if you are not using it concurrently, can you confirm the there are *no active* queries on the mv. refresh requires AccessExclusiveLock and will wait, till it gets one. just asking if you can rule out the extended time is not due to waiting for lock. also, can you share the plans where you see th

CREATE/REFRESH MATERIALIZED VIEW planner difference?

Hi all, Should I expect a planner difference between CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW? We have a materialized view that uses 4 workers during CREATE but only one worker during REFRESH, and as a result the refresh takes much longer (~90 minutes vs. 30 minutes for the CREATE)

Re: Query on postgres_fdw extension

You are right, we added more than one coordinator nodes for high availability and to avoid single point of failure. Thanks Swathi On Tue, Jun 1, 2021, 3:54 PM Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > On Tue, Jun 1, 2021 at 3:31 PM Etsuro Fujita > wrote: > > > > Hi, >

Re: Query on postgres_fdw extension

On Tue, Jun 1, 2021 at 3:31 PM Etsuro Fujita wrote: > > Hi, > > On Fri, May 14, 2021 at 6:08 PM Swathi P wrote: > > In our sharding solution, we have multiple coodinator nodes. If we declare > > the table column as serial data type, we might end up having duplicate > > values for id column in t

Re: Query on postgres_fdw extension

Hi, On Fri, May 14, 2021 at 6:08 PM Swathi P wrote: > In our sharding solution, we have multiple coodinator nodes. If we declare > the table column as serial data type, we might end up having duplicate values > for id column in the table_a in host_b (data node) as cconnections come from > mult

RE: Framework for 0 downtime deploys

Confidential Thank you Nik That’s very useful and will be checking out the migration test tool! Z From: Nikolay Samokhvalov Sent: 22 May 2021 13:32 To: Zahir Lalani Cc: pgsql-general@lists.postgresql.org Subject: Re: Framework for 0 downtime deploys No silver bullets exist (yet). A couple of