Re: Doubt in pgbouncer

[David G. Johnston]

On Thursday, October 1, 2020, Fabio Pardi  wrote:

> Hi Rama,
>
> On 02/10/2020 01:42, Rama Krishnan wrote:
>
> Hi Friends,
>
> By using pg bouncer can we split read and queries
>
>
> pgbouncer is just a connection pooler.
>
> The logic where to send the reads and where the writes, should be in our
> application.
>

Seems as if pgPool is at least worth considering...

David J.

Re: Doubt in pgbouncer

[Achilleas Mantzios]

On 2/10/20 9:50 π.μ., Fabio Pardi wrote:

Hi Rama,

On 02/10/2020 01:42, Rama Krishnan wrote:

Hi Friends,

By using pg bouncer can we split read and queries



pgbouncer is just a connection pooler.

The logic where to send the reads and where the writes, should be in our 
application.
yeah reading back some comments over the github on various issues, the pgbouncer hackers were of the philosophy that pgbouncer should remain as query agnostic as possible, in the sense that it should 
not investigate the content of the queries. Those had to do with classic problems like search_path in transaction mode (some PR's were rejected because of that).

You might find this interesting : https://github.com/awslabs/pgbouncer-rr-patch


regards,

fabio pardi



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

ERROR: terminating logical replication worker due to timeout

[FOUTE K . Jaurès]

Hello Everyone,

How can I handle this error. Any idea ?

I am usin PostgreSQL 12 on both Server. The error appear on the slave.
My OS is Ubuntu Server 18.04

-- 
Jaurès FOUTE

Re: Rows removed on child table when updating parent partitioned table.

[Eduard Català]

On Thu, Oct 1, 2020 at 8:02 PM David G. Johnston 
wrote:

> The convention on these lists is to inline or bottom-post, please do not
> top-post.
>
> On Thu, Oct 1, 2020 at 10:41 AM Jonathan Strong 
> wrote:
>
>> I've been away from coding for several years, but dusting off my chops
>> and getting back up to speed with PostgreSQL (love it!). So please forgive
>> me if my early answers here come off as naive. But my understanding of this
>> suggests that you shouldn't be using "update" on a serial field.
>>
>
> Yes Jonathan, your present understanding is flawed.  The OP has provided
> a self-contained simple test case for the problem at hand - which even if
> not "best practice" is indeed valid to do and demonstrates the problem
> quite clearly.  Without actually testing it out I would say that this is
> likely indeed an oversight in the partition row movement feature - it
> didn't take into account the ON UPDATE/ON DELETE clause.
>
> Adding Robert Hass who committed the row movement feature [1].
>
> We document on the UPDATE reference page that such an update is performed
> as a DELETE + INSERT.  Given that implementation detail, the observed
> behavior is what one would expect if no special consideration has been
> given to make row movement between partitions preserve (via deferred
> evaluation), or recreate the foreign key relationship.
>
> For now I would say you should consider the two features incompatible; and
> we need to update the documentation to reflect that reality more directly,
> barring a solution being proposed, and hopefully back-patched, instead.  I
> concur with the observation that one would expect these two features to
> interact better with each other and think it could possibly be done as a
> bug fix for the POLA violation.
>
> David J.
>
> [1]
> https://github.com/postgres/postgres/commit/2f178441044be430f6b4d626e4dae68a9a6f6cec
>
>
>
Regardless of how postgres implement the updates:

*Don’t think it’s a bug that executing an update, you are ending up with
fewer rows than you initially had? *
Is the perfect silent row-killer
Even worse, the deleted rows are from another table without realizing it.

If no one else gives an opinion I will open a bug for at least, force an
update of the documentation.

Re: Rows removed on child table when updating parent partitioned table.

[David G. Johnston]

On Fri, Oct 2, 2020 at 9:11 AM Eduard Català 
wrote:

> If no one else gives an opinion I will open a bug for at least, force an
> update of the documentation.
>

It's been seen and begun to be discussed over on -hackers [1].

[1]
https://www.postgresql.org/message-id/flat/CA%2BHiwqFvkBCmfwkQX_yBqv2Wz8ugUGiBDxum8%3DWvVbfU1TXaNg%40mail.gmail.com

David J.

Profile Creation

[Brajendra Pratap Singh]

Hi All,

How can we create a user profile in open postgresql db?

Thanks,
Singh

Re: Detecting which columns a query will modify in a function called by a trigger

[David G. Johnston]

On Tue, Mar 3, 2020 at 4:19 PM David G. Johnston 
wrote:

> On Tue, Mar 3, 2020 at 4:11 PM Adrian Klaver 
> wrote:
>
>> On 3/3/20 3:06 PM, David G. Johnston wrote:
>> > On Tue, Mar 3, 2020 at 3:48 PM Adrian Klaver > > > wrote:
>> >
>> > The link was for automatically updateable views. If you want to do
>> > something more involved then see:
>> >
>> >
>> https://www.postgresql.org/docs/12/rules-views.html#RULES-VIEWS-UPDATE
>> >
>> >
>> > CREATE TRIGGER works with views; I usually see recommendations to start
>> > there and avoid rules if at all possible.
>>
>> The above suggests triggers then rules.
>>
>
> Yeah, I see the buried in there.  The link itself and the page itself is
> something like 95% rules coverage so it still seems worth pointing out even
> in hindsight.
>
> Maybe add a link to the CREATE TRIGGER section in there...
>

Minor doc patch for this attached for consideration.

David J.


v1-notify-doc-fixup.patch
Description: Binary data

Re: Profile Creation

[Adrian Klaver]

On 10/2/20 1:40 PM, Brajendra Pratap Singh wrote:

Hi All,

How can we create a user profile in open postgresql db?


You are going to need to be more specific about what you consider a user 
profile to be.




Thanks,
Singh



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

Re: Profile Creation

[David G. Johnston]

On Fri, Oct 2, 2020 at 1:43 PM Brajendra Pratap Singh <
singh.bpratap...@gmail.com> wrote:

> How can we create a user profile in open postgresql db?
>
?

CREATE TABLE user_profile (...);
INSERT INTO user_profile VALUES (...);

David J.

Re: Profile Creation

[Rob Sargent]

On 10/2/20 2:45 PM, David G. Johnston wrote:
On Fri, Oct 2, 2020 at 1:43 PM Brajendra Pratap Singh 
mailto:singh.bpratap...@gmail.com>> wrote:


How can we create a user profile in open postgresql db?

?

CREATE TABLE user_profile (...);
INSERT INTO user_profile VALUES (...);

David J.


I'm betting on
CREATE ROLE rolename WITH option

https://www.postgresql.org/docs/13/sql-createrole.html

Re: Profile Creation

[Adrian Klaver]

On 10/2/20 1:56 PM, Brajendra Pratap Singh wrote:

Hi Adrian,

Here the user profile means which contains the following functionality 
same as in  EDB profile contains like password_verify_function, 
password_life_time, password_lock_time etc.


The below then?:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/9.5/Database_Compatibility_for_Oracle_Developers_Guide.1.073.html

If that is the case, there is no built in mechanism for that in the 
community edition. It would need to be built by you.




Thanks,
Singh

On Sat, 3 Oct, 2020, 2:14 AM Adrian Klaver, > wrote:


On 10/2/20 1:40 PM, Brajendra Pratap Singh wrote:
 > Hi All,
 >
 > How can we create a user profile in open postgresql db?

You are going to need to be more specific about what you consider a
user
profile to be.

 >
 > Thanks,
 > Singh


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

UUID generation problem

[James B. Byrne]

I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
platform for Idempiere.  When creating a new client in Idempiere I get this
error message:

** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
not exist Hint: No function matches the given name and argument types. You
might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
line 3 at RETURN

This usually means that the uuid-ossp extension is not active in the database. 
However, I previously installed this and psql shows that it is present:

sudo -u postgres psql --dbname=idempiere
psql (11.8)
Type "help" for help.

idempiere=# \dx
List of installed extensions
   Name| Version |   Schema   |   Description
---+-++-
 plpgsql   | 1.0 | pg_catalog | PL/pgSQL procedural language
 uuid-ossp | 1.1 | public | generate universally unique identifiers
(UUIDs)

The owner of the idempiere database is idempiere_dbadmin and this is the user
that is attempting to generate the UUID.

idempiere=# \l
  List of databases
Name |   Owner   | Encoding | Collate | Ctype |  
Access privileges
-+---+--+-+---+---
 idempiere   | idempiere_dbadmin | UTF8 | C   | C |

What is the problem?


-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: UUID generation problem

[Adrian Klaver]

On 10/2/20 3:08 PM, James B. Byrne wrote:

I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
platform for Idempiere.  When creating a new client in Idempiere I get this
error message:

** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
not exist Hint: No function matches the given name and argument types. You
might need to add explicit type casts. Where: PL/pgSQL function generate_uuid()
line 3 at RETURN

This usually means that the uuid-ossp extension is not active in the database.
However, I previously installed this and psql shows that it is present:

sudo -u postgres psql --dbname=idempiere
psql (11.8)
Type "help" for help.

idempiere=# \dx
 List of installed extensions
Name| Version |   Schema   |   Description
---+-++-
  plpgsql   | 1.0 | pg_catalog | PL/pgSQL procedural language
  uuid-ossp | 1.1 | public | generate universally unique identifiers
(UUIDs)

The owner of the idempiere database is idempiere_dbadmin and this is the user
that is attempting to generate the UUID.

idempiere=# \l
   List of databases
 Name |   Owner   | Encoding | Collate | Ctype |
Access privileges
-+---+--+-+---+---
  idempiere   | idempiere_dbadmin | UTF8 | C   | C |

What is the problem?


Per comment over at SO, have you run uuid_generate_v4()/generate_uuid(). 
in psql to see if it is there and works?








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

Re: UUID generation problem

[Tom Lane]

"James B. Byrne"  writes:
> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
> platform for Idempiere.  When creating a new client in Idempiere I get this
> error message:

> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
> not exist Hint: No function matches the given name and argument types. You
> might need to add explicit type casts. Where: PL/pgSQL function 
> generate_uuid()
> line 3 at RETURN

> This usually means that the uuid-ossp extension is not active in the 
> database. 
> However, I previously installed this and psql shows that it is present:

The two likely possibilities are that Idempiere isn't connecting to the
same database as you are doing manually, or that it is using a search_path
setting that doesn't include the "public" schema.

I think you could also get this if the "public" schema is not readable
by Idempiere's userid, but that doesn't seem terribly likely.

regards, tom lane

Re: UUID generation problem

[James B. Byrne]

On Fri, October 2, 2020 18:14, Adrian Klaver wrote:

>
> Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
> in psql to see if it is there and works?
>

[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.

idempiere=# select uuid_generate_v4();
ERROR:  function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
   ^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select uuid_generate();
ERROR:  function uuid_generate() does not exist
LINE 1: select uuid_generate();
   ^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=#


-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: UUID generation problem

[James B. Byrne]

On Fri, October 2, 2020 18:46, Tom Lane wrote:
> "James B. Byrne"  writes:
>> I have a FreeBSD-12.1p10 system running postgreSQL-11.8. This is a test
>> platform for Idempiere.  When creating a new client in Idempiere I get this
>> error message:
>
>> ** org.postgresql.util.PSQLException: ERROR: function uuid_generate_v4() does
>> not exist Hint: No function matches the given name and argument types. You
>> might need to add explicit type casts. Where: PL/pgSQL function
>> generate_uuid()
>> line 3 at RETURN
>
>> This usually means that the uuid-ossp extension is not active in the
>> database.
>> However, I previously installed this and psql shows that it is present:
>
> The two likely possibilities are that Idempiere isn't connecting to the
> same database as you are doing manually, or that it is using a search_path
> setting that doesn't include the "public" schema.
>
> I think you could also get this if the "public" schema is not readable
> by Idempiere's userid, but that doesn't seem terribly likely.
>
>   regards, tom lane
>

idempiere=# \dn
List of schemas
   Name|   Owner
---+---
 adempiere | adempiere
 public| postgres


-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: UUID generation problem

[Adrian Klaver]

On 10/2/20 5:08 PM, James B. Byrne wrote:



On Fri, October 2, 2020 18:14, Adrian Klaver wrote:



Per comment over at SO, have you run uuid_generate_v4()/generate_uuid().
in psql to see if it is there and works?



[root@accounting-2 ~ (master)]# sudo -u postgres psql --dbname=idempiere
--username=idempiere_dbadmin
Password for user idempiere_dbadmin:
psql (11.8)
Type "help" for help.

idempiere=# select uuid_generate_v4();
ERROR:  function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=# select uuid_generate();
ERROR:  function uuid_generate() does not exist
LINE 1: select uuid_generate();
^
HINT:  No function matches the given name and argument types. You might need to
add explicit type casts.
idempiere=#




So per Tom's post:

What does:

show search_path;

return?

Also what does:

select public.uuid_generate_v4();

do?



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

Re: UUID generation problem

[Tom Lane]

"James B. Byrne"  writes:
> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>> The two likely possibilities are that Idempiere isn't connecting to the
>> same database as you are doing manually, or that it is using a search_path
>> setting that doesn't include the "public" schema.
>> I think you could also get this if the "public" schema is not readable
>> by Idempiere's userid, but that doesn't seem terribly likely.

> idempiere=# \dn
> List of schemas
>Name|   Owner
> ---+---
>  adempiere | adempiere
>  public| postgres

Uh ... that rules out exactly none of those three possibilities.
"\dn" says what schemas exist, but it tells you nothing about
either search_path or privileges.

You could try "select current_schemas(true)" to narrow things
down a little bit, as that would show the active search path
in your session.

regards, tom lane

Re: UUID generation problem

[James B. Byrne]

On Fri, October 2, 2020 21:13, Tom Lane wrote:
> "James B. Byrne"  writes:
>> On Fri, October 2, 2020 18:46, Tom Lane wrote:
>>> The two likely possibilities are that Idempiere isn't connecting to the
>>> same database as you are doing manually, or that it is using a search_path
>>> setting that doesn't include the "public" schema.
>>> I think you could also get this if the "public" schema is not readable
>>> by Idempiere's userid, but that doesn't seem terribly likely.
>
>> idempiere=# \dn
>> List of schemas
>>Name|   Owner
>> ---+---
>>  adempiere | adempiere
>>  public| postgres
>
> Uh ... that rules out exactly none of those three possibilities.
> "\dn" says what schemas exist, but it tells you nothing about
> either search_path or privileges.
>
> You could try "select current_schemas(true)" to narrow things
> down a little bit, as that would show the active search path
> in your session.
>
>   regards, tom lane
>

Sorry about that.  Still finding my way.  Anyway, I believe that I may have
found the problem.  When I created the test database I was following the
software installation guide. The user the guide refers to is 'adempiere' and
not 'idempiere_dbadmin'.  At some point I came at the task from a different pov
and created another user to be the database owner.  Cannot say why but it is
evident that is what I did.

I will resolve the conflict either by granting 'idempiere_dbadmin' the
necessary privileges or by changing the connection to use the 'adempiere' user
instead.

Thanks for the pointers.

Regards,

P.S.  If it turns out to be something else then I will return with more details.

-- 
***  e-Mail is NOT a SECURE channel  ***
Do NOT transmit sensitive data via e-Mail
   Unencrypted messages have no legal claim to privacy
 Do NOT open attachments nor follow links sent by e-Mail

James B. Byrnemailto:byrn...@harte-lyne.ca
Harte & Lyne Limited  http://www.harte-lyne.ca
9 Brockley Drive  vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada  L8E 3C3

Re: UUID generation problem

[Adrian Klaver]

On 10/2/20 7:17 PM, James B. Byrne wrote:



On Fri, October 2, 2020 21:13, Tom Lane wrote:

"James B. Byrne"  writes:

On Fri, October 2, 2020 18:46, Tom Lane wrote:

The two likely possibilities are that Idempiere isn't connecting to the
same database as you are doing manually, or that it is using a search_path
setting that doesn't include the "public" schema.
I think you could also get this if the "public" schema is not readable
by Idempiere's userid, but that doesn't seem terribly likely.



idempiere=# \dn
 List of schemas
Name|   Owner
---+---
  adempiere | adempiere
  public| postgres


Uh ... that rules out exactly none of those three possibilities.
"\dn" says what schemas exist, but it tells you nothing about
either search_path or privileges.

You could try "select current_schemas(true)" to narrow things
down a little bit, as that would show the active search path
in your session.

regards, tom lane



Sorry about that.  Still finding my way.  Anyway, I believe that I may have
found the problem.  When I created the test database I was following the
software installation guide. The user the guide refers to is 'adempiere' and
not 'idempiere_dbadmin'.  At some point I came at the task from a different pov
and created another user to be the database owner.  Cannot say why but it is
evident that is what I did.

I will resolve the conflict either by granting 'idempiere_dbadmin' the
necessary privileges or by changing the connection to use the 'adempiere' user
instead.


I'm not sure that is going to help. You are not, AFAICT, getting any 
permission denied messages.


What does:

\df+ uuid_generate_v4

show under Access privileges?

What does:

select current_schemas(true);

show?




Thanks for the pointers.

Regards,

P.S.  If it turns out to be something else then I will return with more details.




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