Community: PostgreSQL community pinboard

[dagwieers]

This has all moved to community.postgres collection repository.

[dagwieers]

cc @fessmage @Andersson007 @astorath @tcraxs @kostiantyn-nemchenko @antoinell @zikalino @frittentheke @jbscalia @lichensky @m4ci3k2 @Glandos @chamini2 @sebasmannem @nerzhul @tdtrask @pilou- @strk @archf @Dorn- @indreek @gshivani
cc @andytom @gothicx @jbscalia @kustodian @vfoucault @tedder @amenonsen @herrewig @michael-dev2rights @acasademont @wrouesnel @joshmoore @willthames @woqer @veger @aleksandr-vin @jirutka @simonalpha
cc @matburt

[dagwieers]

If you want to actively lead or are interested to be part of this Working Group, add your name to the Wiki page ! If we have a large enough group, we can start our own PostgreSQL Working Group.

I started labeling all the PostgreSQL related issues and PRs so we can more easily track them:

• https://github.com/ansible/ansible/pulls?q=is:pr+is:open+label:postgresql (20 open PRs)
• https://github.com/ansible/ansible/issues?q=is:issue+is:open+label:postgresql (22 open issues)

PS If you no longer want to receive any messages from this pinboard, feel free to unsubscribe from this issue ticket.

[Andersson007]

@dagwieers , thank you for this invatation! If nobody is reasonably against I'd like to actively lead this group. I hope everything works out.

[dagwieers]

@Andersson007 Great and thanks ! We do allow more than one leading a Working Group, as long as it helps moving forward and engages the community as a whole I support every new idea. There are no strict rules we try to learn as we go with this new concept of technology-related communities.

There's a lot of open PRs to review and open issues to verify, so everyone is welcome to make the upcoming Ansible v2.8 release the best one for PostgreSQL support. We have about one more month to get our act together!

[mattclay]

@dagwieers We do have test infrastructure for PostgreSQL. We have the postgresql integration tests which spin up their own instance for testing, which are run by CI.

[dagwieers]

@mattclay I stand corrected. However the current integration tests are lacking any tests for postgresql_ext, postgresql_idx, postgresql_lang and postgresql_schema modules. So I will add this to the PostgreSQL Wiki.

[Andersson007]

Who can explain me a couple of questions about writing and execution of integration tests?

[dagwieers]

@Andersson007 Integration tests live in our tree in test/integration/targets/. I wrote some information about integration tests at: https://github.com/ansible/community/wiki/Reviewing#integration-tests

My integration tests typically look like this: https://github.com/ansible/ansible/blob/devel/test/integration/targets/mso_tenant/tasks/main.yml

Running an integration test is done using:

$ ansible-test integration --allow-unsupported <target-name>

You can add --docker for running tests in a container, read ansible-test integration --help for more information.

[Andersson007]

@dagwieers, thank you for this useful info! I hope it will be useful for all. This is convenient to use docker for this. I tried to run - nothing difficult.
I'm going to try to write tests for all of my new recent modules
and, if success, maybe for existing modules without tests.
Would be better if other contributors from working group help to add this tests for them too.

[antoinell]

Thanks Andrey for working on this. I also started fiddling with docker today. I did a 11.5 miles run today and my energy was spent there.
I noticed all the useful comments you wrote on https://github.com/ansible/ansible/pull/50568 https://github.com/ansible/ansible/pull/50568 and I hope to get started implementing this slowly.
My current work does not use postgresql so I need to get to run postgresql in docker first to do some exploratory research such as finding which query gives
the options of a tablespace in case one is working on an existing tablespace and the code needs to check whether the current options match the options requested by the user.

Regards,

Antoine

On Feb 2, 2019, at 2:14 PM, Andrey Klychkov [e-mail] wrote:

@dagwieers https://github.com/dagwieers, thank you for this useful info! I hope it will be useful for all. This is convenient to use docker for this. I tried to run - nothing difficult.
I'm going to try to write tests for all of my new recent modules
and, if success, maybe for existing modules without tests.
Would be better if other contributors from working group help to add this tests for them too.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub https://github.com/ansible/community/issues/435#issuecomment-459991218, or mute the thread https://github.com/notifications/unsubscribe-auth/AAckbGC5JoiIsv85Ofjttn8ELlIB6VRaks5vJeN7gaJpZM4acdcw.

[Andersson007]

Any ideas what's going on on freebsd 11 ? https://github.com/ansible/ansible/pull/51875

04:57 The full traceback is:
04:57 Traceback (most recent call last):
04:57   File "/tmp/ansible_postgresql_set_payload_PtOEQV/__main__.py", line 308, in main
04:57     db_connection = psycopg2.connect(**kw)
04:57   File "/usr/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
04:57     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
04:57 OperationalError: fe_sendauth: no password supplied
04:57 
04:57 
04:57 fatal: [testhost]: FAILED! => {
04:57     "changed": false, 
04:57     "invocation": {
04:57         "module_args": {
04:57             "db": null, 
04:57             "login_host": "", 
04:57             "login_password": "", 
04:57             "login_unix_socket": "", 
04:57             "login_user": "postgres", 
04:57             "name": "work_mem", 
04:57             "port": 5432, 
04:57             "reset": null, 
04:57             "session_role": null, 
04:57             "ssl_mode": "prefer", 
04:57             "ssl_rootcert": null, 
04:57             "value": "12MB"
04:57         }
04:57     }, 
04:57     "msg": "unable to connect to database: fe_sendauth: no password supplied\n"
04:57 }
04:57 ...ignoring
04:57 
04:57 TASK [postgresql : assert] *****************************************************
04:57 task path: /root/.ansible/test/tmp/postgresql-Cl4cwA-ÅÑŚÌβŁÈ/test/integration/targets/postgresql/tasks/postgresql_set.yml:26
04:57 fatal: [testhost]: FAILED! => {
04:57     "msg": "The conditional check 'set_wm.name == 'work_mem'' failed. The error was: error while evaluating conditional (set_wm.name == 'work_mem'): 'dict object' has no attribute 'name'"
04:57 }

Seems it relates with default authentication freebsd11 but why another tests passed this successfully...

[frittentheke]

Great idea. I've been wondering if a collective push towards a better structure of the PostgreSQL integration would not be sensible.

[Andersson007]

Seems it relates with default authentication freebsd11 but why another tests passed this successfully...

I figured it out - on freebsd 11, if you intall PostgreSQL 9.5 (or lower), there is an unusual user for PostgreSQL called "pgsql" (for the system and db). You must use became_user: pgsql, login_user: pgsql, and db: postgres to run your playbook.

[dagwieers]

@frittentheke Proposals and ideas are welcome.

One of the first actions of the GitLab WG was to refactor all GitLab modules. IMO it is best we first implement integration tests for the existing code before we refactor these modules.

[Andersson007]

Three modules expect reviews (The last week I added session_role and CI tests for them, improved documentation):
postgresql_ping - https://github.com/ansible/ansible/pull/51477
postgresql_facts - https://github.com/ansible/ansible/pull/51164 (it was epic work, thanks @dagwieers for the review and notices)
postgresql_set - https://github.com/ansible/ansible/pull/51875
postgresql_table - https://github.com/ansible/ansible/pull/52077

[Andersson007]

https://github.com/ansible/ansible/pull/51477 needs to be tried manually. Can anybody run it locally and do shipit if success? (it needs installed psycopg2 as usual)

[Andersson007]

I wrote CI tests for existing module postgresql_idx, added new parameters, returned values, improved documentation and rewrite the code https://github.com/ansible/ansible/pull/52230

[Andersson007]

@alikins Hello, I remember you've suggested some help with it but I can't recall where ;) Could you send me a test message to aaklychkov at mail dot ru ? I'd like to ask you a couple of questions about general things.

[Andersson007]

And if anyone needs review or help for his/her postgresql related PRs, please, let me know (you can do it directly by my email written in the message above).

[Andersson007]

Hey, guys. This is the new module PR https://github.com/ansible/ansible/pull/39902.
If the author doesn't mind that somebody rewrite it, is here who want to do it ?
If yes, write it to me directly and here, please.
I'm going to wait a couple of days and start, but I prefer that anybody wants ;) because there are many unmerged new modules from me.
I can help with it.
Of course, the best way is that @jbscalia complete it by himself (I can try to help you if needed)

[sebasmannem]

@Andersson007 : I can have a look at
ansible/ansible#39902 if you like.

[Andersson007]

@sebasmannem , cool, I think if @jbscalia won't answer us before the end of this week, it would be great if you rewrite it ) Thanks!

[Andersson007]

@sebasmannem , also, would be great, if you look at several new modules that are waiting for review.
You can see it here https://github.com/ansible/community/wiki/PostgreSQL.
Waiting for your opinion, notices, shippit, etc )

[Andersson007]

Dear colleagues!
We should discuss pg_quote_identifier() function.
I noticed that it's used in for example,

query = "SELECT 1 FROM pg_roles WHERE rolname = '%s'" % pg_quote_identifier(rolname, "role")

vim lib/ansible/module_utils/database.py

114 def pg_quote_identifier(identifier, id_type):
115     identifier_fragments = _identifier_parse(identifier, quote_char='"')
116     if len(identifier_fragments) > _PG_IDENTIFIER_TO_DOT_LEVEL[id_type]:
117         raise SQLParseError('PostgreSQL does not support %s with more than %i dots' % (id_type, _PG_IDENTIFIER_TO_DOT_LEVEL[id_type]))
118     return '.'.join(identifier_fragments)

Seems it looks like we want to verify user's input validity... but I'm not really sure.

IMO, we don't need to check all possible user input mistakes (like that a name of a database object can't contain more than 63 symbols, etc.).
So, I suggest to abandon to use this function for more clear code and, respectively, any other functions that check user input for typos, mistakes, impossible symbols, etc.
In any case, seems nothing horrible will happen and the PostgreSQL server just will return an exception to psycopg2 and a user see it.

I've never broken the Postgres instance by doing any mistakes in object names ;)

Maybe I'm wrong. If yes, please, describe me why.
Any ideas?

[antoinell]

Taking a look at this pg_quote_identifier method, it seems that the method does the following \:

• split the identifiers by the . period character
• make sure that the number of tokens once split by the period is not more than a maximum depending upon the object type
• quote each token with a double quote and replace any embedded double quote by 2 double quotes

so for instance foo.bar.bl"a would be replaced by "foo"."bar"."bl""a".

It seems that pg_quote_identifier is also used by the mysql module.
I would keep using pg_quote_identifier in the postgresql module for now by conservatism, or humbleness.

[Andersson007]

@antoinell thanks for your answer!
I try to sort out it makes practical sense or not.
If you’re sure that it’s absolutely necessary, so, OK.
However, if it doesn’t, IMO, it should be abandoned as an useless thing to make our code cleaner.
The code should evolve.
In any case, it’s our own business to use it or not, but, of coarse, if there are important reasons, we must use it.
Do you have any ideas there are any important reasons (particular cases) to use pg_quote_identifier? (I didn’t use it in my modules, maybe I must add it).
(By the way, our module postgresql_tablespace is waiting for your review;)
Waiting for you feedback. Thank you!

[antoinell]

Concerning pg_quote_identifier It will make a difference if one uses an owner name or a table name with an embedded space in it. This link syntax - Quoting columns with spaces in PostgreSQL? - Database . is a hint that embedded spaces in identifiers of postgres objects are not recommended but are allowed in practice.

I just did this little exercise ::

create user "SOME USER" password 'bla';
create TABLESPACE AMSTRAMGRAM OWNER SOMEUSER LOCATION '/var/postgres/tablespace1' WITH (effective_io_concurrency = 0);
alter tablespace amstramgram  owner to "SOME USER";
SELECT r.rolname, t.spcoptions, pg_tablespace_location(t.oid)
                      FROM pg_catalog.pg_tablespace AS t 
                     JOIN pg_catalog.pg_roles AS r 
                    ON t.spcowner = r.oid; 
  rolname  |          spcoptions          |  pg_tablespace_location   
-----------+------------------------------+---------------------------
 postgres  |                              | 
 postgres  |                              | 
 SOME USER | {effective_io_concurrency=0} | /var/postgres/tablespace1
(3 rows)

My conclusion is we should keep pg_quote_identifier and use it in new modules, otherwise if there are ansible users who are dealing with identifiers with embedded spaces they will most likely file bug reports.
Looking at the log of lib/ansible/module_utils/database.py one sees that these identifiers functions were added with a clear intent by Toshio Kuratomi back in November 2014.

[antoinell]

I found this official documentation Lexical Structure which explains which characters are allowed inside postgres identifiers and why would one want to quote an identifier.

postgres=# create TABLESPACE SELECT  OWNER SOMEUSER LOCATION '/var/postgres/tablespace4' ;
espace4' ;
ERROR:  syntax error at or near "SELECT"
LINE 1: create TABLESPACE SELECT  OWNER SOMEUSER LOCATION '/var/post...
                          ^
postgres=# create TABLESPACE "SELECT"  OWNER SOMEUSER LOCATION '/var/postgres/tablespace4' ;
blespace4' ;
CREATE TABLESPACE

So it is possible to create a tablespace called SELECT if one refers to it as "SELECT".

I also found out that if one does not use quotes when creating a tablespace, the name of the tablespace is stored in pg_tablespace lowercase. If one uses quotes, whatever case was used when creating the tablespace is preserved. Postgres allows the creation of different tablespaces, whose name is different by the case only such as "foo" and "FOO".

postgres=# create TABLESPACE "foo"  OWNER SOMEUSER LOCATION '/var/postgres/tablespace6' ;
space6' ;
CREATE TABLESPACE
postgres=# create TABLESPACE "FOO"  OWNER SOMEUSER LOCATION '/var/postgres/tablespace7' ;
space7' ;

Also it seems that "FOO" is only usable on the psql prompt as "FOO". Unquoted versions foo or FOO refer internally to foo (even though this is an implementation detail).

The use of pg_quote_identifier allows to use identifiers which otherwise would not be allowed by postgres (when they have embedded spaces or are the same as keywords) and it also allows to create different versions differing only by case.
When we create a database called FOO using the ansible module postgres_db the database is created with an internal name of FOO and one has to refer to it as FOO.

[Andersson007]

@antoinell ,
Ok, I see now - better to keep using of this function in the future.
Thank you for detailed research, good job!
Hope you will continue to support the community.

[chamini2]

I agree, checking for those mistakes is not this layer's job. Also, if Postgres starts accepting some of these identifiers now considered _illegal_, we would have to update to accommodate for changes, this is a clear flag to me that is not our job to check for that.

EDIT: I had not refreshed the page before writing this! I see you guys sorted it out!

[Andersson007]

@chamini2 , thanks for your feedback. If you are going to use pg_quote_identifier, use it only with DDL queries like CREATE obj, DROP obj, ALTER obj, etc. To be short, pg_quote_identifier adds double quotes to the database object name (if user pass OBJECT_NAME, it changes it to \"OBJECT_NAME\" in the sql to prevent a collision with existing object_name, etc., because OBJECT_NAME without quotes will be transformed to lower case).

[antoinell]

I wrote a pull request for a postgresql_tablespace module because it is required for a process of bootstrapping a database whose tables, views, indices, ... are then setup with sqitch. It is a complex task to setup all the database objects of a postgresql database (or for that matter an oracle or db2 or ...).
Tools like liquibase or sqitch or flyway contain a lot of functionality to create all the objects of a database. Ansible does not need to create all objects of a database and might be better served to establish bridges with liquibase, sqitch or flyway.
When one wants to upgrade a table there might be a lot of objects connected to the table (indices, stored procedures, views, ...) which will need to be recompiled or dropped and recreated. Do we want to duplicate all this logic in ansible ?

[antoinell]

The pull request https://github.com/ansible/ansible/pull/50406 was not a duplicate after all. This pull request is about being able to pick a tablespace for a new database. Can it get reopened ?

[Andersson007]

Sorry, the idea is good, answered to the PR

[antoinell]

I will open a new PR for this.

[Andersson007]

!
2019-03-21 Beta 1 Feature freeze
No new functionality (including modules/plugins) to any code

We have roughly about a week to do as more as we can to add new modules in 2.8.
These modules below have CI tests, they're partially reviewed, etc.
Who wants to make postgresql support better, please, look at it:
postgresql_facts https://github.com/ansible/ansible/pull/51164
postgresql_set https://github.com/ansible/ansible/pull/51875
postgresql_table https://github.com/ansible/ansible/pull/51875

[Andersson007]

I've just added a new module postgresql_membership with the detailed description and CI tests. Needs reviews #53733

[Andersson007]

Hey! Seems the feature freeze will be a bit later (2019-03-28).
So, we can try to add 2 new modules before that
postgresql_table https://github.com/ansible/ansible/pull/52077
postgresql_membership (this is quite simple) https://github.com/ansible/ansible/pull/53733

[Andersson007]

if nobody has started writing missing CI tests for postgresql_lans module, I'll make them.

[Andersson007]

postgresql_facts has been renamed to postgresql_info because of #54280
(it was merged before, just FYI)

[tcraxs]

I will create a new module postgresql_sequence #55027 to create/drop/alter sequence in postgresql

[Andersson007]

Sounds good to me

[Andersson007]

@tcraxs , IMO, it should be able to allow to set sequence’s value for new and existing sequences.

[tcraxs]

@tcraxs , IMO, it should be able to allow to set sequence’s value for new and existing sequences.

Sounds good, I will also add INCREMENT, MINVALUE, MAXVALUE and START WITH options, and also possibility to select the data_type like described in the documentation.

[Andersson007]

To be sure that our changes won't break something after releases, we should

• recheck existing tests (all options covered, etc.)
• improve tests for all modules
• move tests from main.yml to separate files
• add tests for params related with TLS (ssl_mode, ca_cert)

If anybody wants to implement it, please, let us know by comment here and directly on my email aaklychkov at mail dot ru

[gundalow]

@Andersson007 @tcraxs Just wanted to say, amazing work on Postgres during the Ansible 2.8 release!

[Andersson007]

Thanks all! It was impossible with your (all) support

[Andersson007]

plus to my comment about the tests - maybe we'll find some bugs in the modules doing this

[Andersson007]

FYI: I've just started to check tests for postgresql_idx module

[Andersson007]

connection params and some checks where moved to module_utils/postgres.py by #55799

[Andersson007]

if someone needs to implement something not big and difficult (e.g., new option for a module) and doesn't know what to do, just ping me

[Andersson007]

guys, if someone is interested in a new info module for MySQL #55434 , welcome for review :)
Needs your suggestions!

[Andersson007]

FYI: I've just started to work on postgresql_copy module

[Andersson007]

New module postgresql_copy is available for review

[Andersson007]

#40424 needs any opinions

[Andersson007]

New module postgresql_copy has just been merged. Thanks @tcraxs , @sebasmannem , and @kostiantyn-nemchenko for reviewing, @willthames for merging.

We have another one by @tcraxs postgresql_sequence that are ready for review (I reviewed and shipit'ed it, LGTM)

[Andersson007]

FYI: I've just started a new module called postgresql_vacuum

[Andersson007]

New module postgresql_vacuum is ready for review

[Andersson007]

@AsgerPetersen hey! we have the community pinboard here to communicate and WG where you can find some ideas to implement, add your own, and find things that need reviewing/opinions. You can write yourself into a members table there by editing the wiki page and subscribe here if you like :)
Thank you for the issue reporting and welcome to the Ansible PostgreSQL community!

[Andersson007]

who is interested in not to go to servers when vacuum is needed, please, look at postgresql_vacuum ;)

[Andersson007]

Hello all!
if somebody consider that postgresql_vacuum is useful or useless, please, add your opinion to #57452

My opinion: the point is doing vacuum/analyze/vacuum full remotely by ansible specific module

- name: upload data
  postgresql_copy:
    db: mydb
    from: '/tmp/data.csv'
    dst: mytable
    columns:
    - id
    - name

- name: do vacuum analyze
  postgresql_vacuum:
    db: mydb
    table: mytable
    analyze: yes
    columns:
    - id
    - name

at least the example above looks more elegant and in ansible style than:

- name: do vacuum analyze
  shell: 'psql mydb -c "VACUUM ANALYZE mytable (id, name);"'

For example, 1. I uploaded the data by postgresql_copy module to a table then 2. I'd like to use a specific module for analyze, not shell like above

(Moreover, users don't need to know SQL at all to do it)

[Andersson007]

Hello all! There are three news / suggestions

• I closed postgresql_vacuum PR because there is no activity, thanks all who looked at it, especially @kostiantyn-nemchenko , @sebasmannem and @amenonsen If you want to see it merged, feel free to ping me, I'll reopen it.

• This is an old PR about new module postgresql_publication.
  It looks good and useful.
  I'd like to see it in 2.9 but unfortunately the author didn't reply for my questions.
  Does anyone want to rewrite it keeping @nerzhul as a co-author?
  If yes, please, add a comment here before Wednesday 03.07.2019 or I'll start to do it by myself (or maybe @nerzhul find some time to complete it).

[Andersson007]

• I added a new option called version by #58381 to postgresql_ext module to be able to create extensions of suitable versions (thanks @strk for the idea and the first PR). Would be cool to get the second review (shipit'ed once)

[Andersson007]

Ding Ding Ding, new module postgresql_publication is ready for review

[Andersson007]

Hello all! The Feature Freeze is comming soon, ti will be in less than a week, so it is the last chance to see postgresql_publication merged https://github.com/ansible/ansible/pull/59439 , could anybody please look at this

[Andersson007]

Hey, dear colleagues!
Are there any brave volunteers to look at https://github.com/ansible/ansible/issues/62432, give opinions / solve ? Would be really great if yes :)
waiting for somebody's feedback

[nerzhul]

@Andersson007 yes sorry i miss too many time, thanks for your rewrite. Don't hesitate to write the subscription module to go with it for next release

[Andersson007]

@nerzhul hey, no problem!
agree, the subsctiption and publication would be an exellent pair :)
i recently proposed to write it to another contributor for helping him to sort out all of things and he agreed, will see :)

[Andersson007]

tomorrow i'm starting postgresql_subscription module. Hope it will be done at the end of next week (25.10.2019)

[Andersson007]

postgresql_subscription is available for review https://github.com/ansible/ansible/pull/63661

[Andersson007]

small reminder, https://github.com/ansible/ansible/pull/63661 is ready for review
@tcraxs thanks!

[Andersson007]

hey, dear folks, and i congratulate you on 2.9 was released yesterday!
there are a lot of PRs now - #open prs , I think it does not make sense to create new PRs before the current are merged.
So, really need your help. I'd like to continue despite feeling exhausted sometimes, etc :)
This is our small contribution to not postgres only, i think, but mankind progress in general and the bright future.
Anyway, thank you much for your support, i believe you do all what you can

[Andersson007]

for people who missed something, now all postgres stuff lives in community.general collection

[Andersson007]

FYI: postgres related stuff has been copied to a separate collection called community.postgresql.
The modules will continue to be in community.general for some time, then will be redirected and deprecated.
To not miss anything, don't forget to subscribe to notifications in the new repo:)

[Andersson007]

all new issues/PRs need to be created in community.postgresql, then, if needed, be backported to community.general.

[Andersson007]

Hi all!
A small reminder: postgres-specific stuff has been recently moved to community.postgresql repo (and version 1.0.0 of the new collection has been also published on galaxy).
As i can see, there are only several folks subscribed to the new repo and nobody from our WG...
So, not to miss anything, please start watching the repository (there's a special button for that).
Thanks!

[Andersson007]

FYI:
1) this pinboard has just been created in the dedicated repository https://github.com/ansible-collections/community.postgresql/issues/30
2) PostgreSQL Wiki page has also been moved to that repository https://github.com/ansible-collections/community.postgresql/wiki/PostgreSQL-Working-Group

[Andersson007]

@gundalow feel free to close this issue

[gundalow]

@Andersson007 Thanks done. I've also changed the Ansible/community wiki to point to the new one.

[Andersson007]

@gundalow thanks!