Re: Internationalisation (i18n) with Postgres as backend

[Laura Smith]

On Wednesday, 2 June 2021 00:42, Rob Sargent  wrote:

> 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 
> > object(s) is what fills the gaps in the template.
>
> Then I take it this template (layout?) is to be re-used across disparate 
> content.  So /instances/ of template applied to content (of vice versa?) are 
> documents.  Two separate domains to be managed, no?  Is this an authoring 
> system?  Hard copy or digital presentation?

Yes, you are thinking along the right lines.

The templates themselves are out-of-scope for the database, they are defined 
and managed in front-end  as local filesystem files for the relevant parser to 
render since frontend tech is generally better suited to this role than 
databases.

The role of the database is (when given a document ID) :
- Tell the frontend which template to use
- Provide content to fill template placeholders

The content may take two forms:
(a) Pure "fill the gaps" content for template placeholders (i.e. text for here, 
image for there etc.)
(b) A list result set which then gets rendered by frontend (i.e think  change 
lists, news items, lists of people's bios etc.)

Versioning is a requirement of the system, particularly at content level.

The internationalisation requirement comes in at content level.  The templates 
themselves don't have any translatable elements.

So, I guess TL;DR is yes, an authoring system.  Currently only spec'd for 
digital (browser and mobile access), no print.

Hope this helps clarify !

Re: Internationalisation (i18n) with Postgres as backend

[Laurenz Albe]

On Tue, 2021-06-01 at 23:08 +, Laura Smith wrote:
> 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 
> proven wrong.

Your intuition is good.  Don't use JSON for that, and don't use the outdated
hstore extension in particular.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Internationalisation (i18n) with Postgres as backend

[Laurenz Albe]

On Tue, 2021-06-01 at 20:09 +, Laura Smith wrote:
> I'm creating a Postgres backend for an internal tool which is essentially a 
> very simple implementation of multi-lingual CMS.
> 
> I am especially interested in feedback and suggestions in relation to the 
> following questions:
> 
> (a) Is this going to work as expected (i.e. have I missed some obvious 
> foot-guns ?)
> 
> (b) Is this manner of doing things reasonably efficient or are there better 
> ways I should be thinking of ?
>  (bear in mind the schema is not set in stone, so completely out of the box 
> suggestions welcome !).
> 
> The basic design concept (oversimplified) is:  For each page, you have one or 
> more objects and those objects may have content in one or more languages.
> 
> create table langtest(
> pageid text not null,
> objectid text not null ,
> objectlang text not null,
> objectdata text not null);
>
> create unique index on (pageid,objectid,objectlang);
> 
> select distinct on(objectid)objectid,objectlang,pageid,objectdata
>  from langTest where pageid='zzz' and objectLang = any('{de,en}'::text[])
>  order by objectid,array_position('{de,en}'::text[],objectLang);

That looks ok, except you should remove "objectid" from the index.
That column makes that the index cannot be used for "objectlang" effectively.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

[Philip Semanchuk]

> On Jun 1, 2021, at 3:23 PM, 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://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e7ccd9ef64d05e87ceb1985d459bef9031205c0


Thanks, all! It’s great to have a clear explanation. I looked at the change 
notes for 12 & 13 before I posted. I didn’t occur to me to look at 14. :-) 

Cheers
Philip

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

[gerry gan]

Hi,

Thanks for the comments! I have a naive question related to the use of
advisory lock. Based on my current reasoning about our scenario, consistent
ordering of commands in two transactions might not help to solve the
deadlock situation. If advisory lock is used, it can return false in case
it cannot get lock on certain row. This, however, might occur in both
transactions. Then it seems to be hard to continue from application side
since the operation is, by any means, required by application logic. In
other words, I guess this might cause 'deadlock' in applications. Do you
have any suggestions to solve this situation? And I'm sorry if my question
is naive. Thank you!

--
Best regards,
Gerry

Vijaykumar Jain  于2021年6月2日周三 下午2:49写道：

> 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 different processes.
>
> Basically, try to get an advisory lock for the rows you want to make
> change, but if not able to get  a lock, return.
>
>
> postgres=# begin;
> BEGIN
> postgres=*# SELECT pg_try_advisory_lock(id) from t where id < 10;
>  pg_try_advisory_lock
> --
>  f
>  f
>  f
>  f
>  f
>  f
>  f
>  f
>  f
> (9 rows)
>
> postgres=*#
>
>
>
>
> 
> postgres=# begin
> postgres-# ;
> BEGIN
> postgres=*# SELECT pg_try_advisory_lock(id) from t where id < 10;
>  pg_try_advisory_lock
> --
>  t
>  t
>  t
>  t
>  t
>  t
>  t
>  t
>  t
> (9 rows)
>
>
>
> On Wed, 2 Jun 2021 at 11:52, gerry gan  wrote:
>
>> 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, the
>> two transactions will deadlock. I have extracted some log below.
>>
>> ---Log 1---
>> 2021-05-11 12:03:03 UTC testdb postgres ERROR:  deadlock detected
>> 2021-05-11 12:03:03 UTC testdb postgres DETAIL:  Process 3390 waits for
>> ShareLock on transaction 18569288; blocked by process 29031.
>> Process 29031 waits for ShareLock on transaction 18569301; blocked by
>> process 3390.
>> Process 3390: delete from records where id = '759476540'
>> Process 29031: update records set content='foo bar' where id = 121496691
>> and type = 'xyz'
>> ---End of Log 1---
>>
>> ---Log 2---
>> 2021-05-11 19:22:05 UTC testdb postgres ERROR:  deadlock detected
>> 2021-05-11 19:22:05 UTC testdb postgres DETAIL:  Process 21865 waits for
>> ShareLock on transaction 18574374; blocked by process 21873.
>> Process 21873 waits for ShareLock on transaction 18574373; blocked by
>> process 21865.
>> Process 21865: update records set content='foo abc' where id = 759698419
>> and type = 'xyz'
>> Process 21873: update records set content='foo def' where id = 686728333
>> and type = 'xyz'
>> ---End of Log 2---
>>
>> Based on the log, the deadlock happens to the table records. I have read
>> some articles about this kind of deadlock. Mostly, the suggested solution
>> is to make a consistent (deterministic) ordering of the commands in the
>> transaction so that they will not block each other. I just wonder whether
>> this can be applied in our case. As shown in above two logs, our function
>> actually goes to different branches in the function based on user input
>> data. The deadlock then occurs at different command operations(Log 1
>> blocked at delete/update operations while log 2 blocked at two updates). My
>> question is whether it is feasible to make consistent command ordering in
>> our case based on the superficial phenomenon shown in the above two logs?
>> Is explicit table lock more applicable in this case? For example, just lock
>> table records at the beginning of the transaction so that deadlock can be
>> avoided. But performance might be hurt with the explicit table lock. Any
>> insight about this issue is highly appreciated and thank you in advance!
>>
>> --
>> Best regards,
>> Gerry
>>
>
>
> --
> Thanks,
> Vijay
> Mumbai, India
>

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

[Tom Lane]

gerry gan  writes:
> Thanks for the comments! I have a naive question related to the use of
> advisory lock. Based on my current reasoning about our scenario, consistent
> ordering of commands in two transactions might not help to solve the
> deadlock situation. If advisory lock is used, it can return false in case
> it cannot get lock on certain row. This, however, might occur in both
> transactions. Then it seems to be hard to continue from application side
> since the operation is, by any means, required by application logic. In
> other words, I guess this might cause 'deadlock' in applications. Do you
> have any suggestions to solve this situation? And I'm sorry if my question
> is naive. Thank you!

A common answer to this sort of problem is to be willing to retry
transactions after deadlock failures.  If the deadlocks are common,
maybe this won't be workable from a performance standpoint.  But if
they're rare, or you can tweak things to make them so, think about it.
Retries can be a lot simpler, more robust, and even more performant
than trying to get to a provably-deadlock-free implementation.

regards, tom lane