Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/22/25 20:23, Ray O'Donnell wrote:

On 22/05/2025 18:52, Achilleas Mantzios wrote:

Hi people

I'd like to know if people here know of or use any integrated 
solution for all or some of the above. It would be nice if it 
supported LDAP / OAuth 2.0 , integrate with plain vanilla git (not 
github / gitlab) and be open, and active as a project.


I've used gitea:

    https://about.gitea.com
It does code reviews which is great, maybe the development part of 
things should be independent of the management part . Thank you.


I don't know whether it does everything you want, but it's quite nice, 
and worth a look.


Ray.

Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/22/25 20:33, Albrecht Dreß wrote:


Am 22.05.25 21:04 schrieb(en) Adrian Klaver:

On 5/22/25 10:52, Achilleas Mantzios wrote:

Hi people

I'd like to know if people here know of or use any integrated solution
for all or some of the above. It would be nice if it supported LDAP /
OAuth 2.0 , integrate with plain vanilla git (not github / gitlab) and
be open, and active as a project.

I use Redmine:

https://www.redmine.org/

+1 for Redmine!

Completely FOSS, written in Ruby.  I use it at work, with Postgres as backend, 
for hundreds of projects, and ~200 users and groups.  LDAP auth works ootb, for 
sync'ing LDAP users and groups a plugin is needed (search for “redmine ldap 
sync”, which is quite old and might need minor fixes or a forked version for 
the latest Redmine 6).  Never tried OAuth 2, but iirc a plugin is available.

You'll find a plethora of plugins (see ), both 
FOSS and closed-source, and it's pretty easy to write your own extensions (in Ruby, 
though, which is not my favourite language, but not too difficult).

Thank you, redmine is on the radar !


Hth, Albrecht.

Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/23/25 00:23, Justin Clift wrote:


On 2025-05-23 03:52, Achilleas Mantzios wrote:

Hi people

I'd like to know if people here know of or use any integrated 
solution for all or some of the above. It would be nice if it 
supported LDAP / OAuth 2.0 , integrate with plain vanilla git (not 
github / gitlab) and be open, and active as a project.


We are at a phase our business is expanding, the projects also are 
increasing in number and size, several of those are interconnected, 
either depending or prerequisite or even inter-meshed .


I'd like to have a tool to manage all this, but also a tool to show 
to the stakeholders the actual picture of our system.


I'd be grateful for any hints !


Maybe Corteza?

  https://cortezaproject.org

Hi I tend to think Corteza as an opensource ERP platform just like SAP  
or saleforce, this is a different thing, please enlighten if I am mistaken.


I just want something to fully manage my programmers, make reports to 
the ppl above me, and give me my time to do PostgreSQL things, this is 
the idea!



We do stuff with it at my work, and it's pretty decent.

Takes some time and effort to set up though, but is very capable
once that's done.

Regards and best wishes,

Justin Clift

Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/23/25 09:58, Wim Bertels wrote:

Justin Clift schreef op vr 23-05-2025 om 09:23 [+1000]:

On 2025-05-23 03:52, Achilleas Mantzios wrote:

Hi people

I'd like to know if people here know of or use any integrated
solution
for all or some of the above. It would be nice if it supported LDAP
/
OAuth 2.0 , integrate with plain vanilla git (not github / gitlab)
and
be open, and active as a project.


https://forgejo.org/
https://www.redmine.org/

Thank you!! forgejo.org looks cool!

Re: issue/bug management, project management, people management, product management all in one, preferably open source software ?

[Achilleas Mantzios]

On 5/22/25 20:04, Adrian Klaver wrote:

On 5/22/25 10:52, Achilleas Mantzios wrote:

Hi people

I'd like to know if people here know of or use any integrated 
solution for all or some of the above. It would be nice if it 
supported LDAP / OAuth 2.0 , integrate with plain vanilla git (not 
github / gitlab) and be open, and active as a project.


I use Redmine:

https://www.redmine.org/

My git integration is browse only per:

https://www.redmine.org/projects/redmine/wiki/RedmineRepositories#Git-repository 



If you need more than that you will need to some searching.
Thank you, I've used redmine in the past in a previous job and it was 
nice (better than youtrack in many aspects). I'll give it a shot!




We are at a phase our business is expanding, the projects also are 
increasing in number and size, several of those are interconnected, 
either depending or prerequisite or even inter-meshed .


I'd like to have a tool to manage all this, but also a tool to show 
to the stakeholders the actual picture of our system.


I'd be grateful for any hints !

Re: Changing a varchar(7) domain into text directly in pg_type

[Richard Zetterberg]

Thanks for the reply, Adrian!

I must have explained my problem poorly, sorry for that.

Let me present an imaginary scenario and hopefully that clears things up.

It's 2015 and you want to start a birdwatching club and want to keep track
of sightings in a postgres database.

To make sure that users in your birdwatching club all enter the specie of
the observed bird correctly, you decide to create a table that will contain
all distinct bird species. Since there are over >10 000 bird species you
decide to download an existing taxonomy of bird species. On
birdtaxonomies.com you download edition 5 of the bird species taxonomy as a
CSV-file. Each row of this file represents a bird specie and it has two
columns: unique ID and name.

You observe that the IDs in the downloaded file have at most 6 characters.
To make room for additional bird species in coming editions of the
taxonomy, you decide to use `varchar(7)` as the type of the ID column. To
improve readability, you create a type alias:

```
CREATE DOMAIN species_id AS varchar(7);
```

Then you create the table that contains the bird species taxonomy:

```
CREATE TABLE species (
  id   species_id PRIMARY KEY,
  name text   NOT NULL
);
```

Once all the bird species are imported from the taxonomy file into the
`species` table, nothing is changed in this file.

To guarantee that people enter the specie correctly, you now add a foreign
key to all of your observation tables referencing the `species` table.
Here's an example of what one of those tables look like:

```
CREATE TABLE sightings (
  id  serial PRIMARY KEY,
  specie_id   species_id NOT NULL REFERENCES species (id),
  coordinates geography(POINT, 4326) NOT NULL,
  datetimestamptzNOT NULL
);
```

Each year, birdtaxonomies.com release a new edition of the bird species
taxonomy, that you update your species table with. But this year,
birdtaxonomies.com comes decides they want a more fine grained way of
classifying bird species, which results in latest edition of bird species
taxonomy to have more than 1 million species and IDs that are as long as 9
characters.

After these 10 years your club has more than 1 million members worldwide,
more than 20 different kinds of observations tables, procedures and views,
billions of rows of different kinds of observations and a total size of
over 100 GB.

In order to import the latest taxonomy, you need to change the type of the
domain `species_id` from `varchar(7)` to `text`. But postgres doesn't allow
you to change the type of a domain with a single command. Instead what you
need to do is:

1. Rename current domain: `ALTER DOMAIN species_id RENAME TO
species_id_depricated;`
2. Create a new domain: `CREATE DOMAIN species_id AS text;`
3. Change all >20 tables, procedures and views to use the new domain
4. Drop the old domain

You had done a similar but smaller change a couple of years ago, and that
meant days of downtime. Also, you are a lazy person and it's tedious to
write the SQL code to change all tables, procedures and views. So, you
decide to manipulate `pg_type` directly, since you read somewhere online
that `varchar` and `text` are basically the same under the hood.

End of story.

Basically, I was hoping that my shortcut would save time and that this
small `pg_type` manipulation can be done without any side effects. I don't
have any knowledge of postgres internals, so I don't know if postgres would
accept this change and carry on like nothing happened. Or, if it will break
my indices or how queries are executed.

In my head, this change would be instant and postgres would carry on like
nothing happened and that the domain always had the type `text`. Is this a
fools errand? Or could it actually make sense in this situation?

Thanks
Richard

On Tue, May 27, 2025 at 8:10 PM Adrian Klaver 
wrote:

>
>
> On 5/27/25 7:27 AM, Richard Zetterberg wrote:
> > Hello,
> >
> > I have a read-only table that contains a set of never changing
> > categories. Each category has a unique alpha numerical ID and a
> > description. The purpose of this table is so that other tables can
> > reference the ID of this table, to make sure that they don't contain
> > invalid/unknown categories and so that users can lookup the description
> > of each category.
> Define 'read-only'. In other words can you temporarily make it not
> read-only and change the type to text(or just varchar (no length
> specifier)?
> This would be the easiest fix.
>
> >
> > This category table has the following type on the ID column:
> > "varchar(7)" (yes, I should have used text). In order to avoid having to
> > type "varchar(7)" in all the tables that references the category table,
> > I created this domain that I used as type for all referencing columns:
> > "CREATE DOMAIN cat.id  AS varchar(7);".
> >
> > During some data archeology, I found a bunch of new categories that
> > haven't been imported into the database yet, and they have IDs lo

Re: Changing a varchar(7) domain into text directly in pg_type

[Ron Johnson]

Foreign key "code tables" are ubiquitous in good database design; another
example is zip/postal codes and cities.  Statuses are another standard
usage.  Thus, what you're doing is absolutely bog-standard.  (In your case,
I would add "taxonomy_edition" to the species table.  You get more
information, and don't have to update the species table; just add more to
it.  New sightings can use the new taxonomy.)

As to whether it would work... I absolutely _would not_ go mucking around
in the catalog.  Bite the bullet and ALTER the twenty-odd columns to TEXT.

(Why TEXT and not a DOMAIN?  Simplicity.)

On Wed, May 28, 2025 at 8:33 AM Richard Zetterberg <
richard.zetterb...@googlemail.com> wrote:

> Thanks for the reply, Adrian!
>
> I must have explained my problem poorly, sorry for that.
>
> Let me present an imaginary scenario and hopefully that clears things up.
>
> It's 2015 and you want to start a birdwatching club and want to keep track
> of sightings in a postgres database.
>
> To make sure that users in your birdwatching club all enter the specie of
> the observed bird correctly, you decide to create a table that will contain
> all distinct bird species. Since there are over >10 000 bird species you
> decide to download an existing taxonomy of bird species. On
> birdtaxonomies.com you download edition 5 of the bird species taxonomy as
> a CSV-file. Each row of this file represents a bird specie and it has two
> columns: unique ID and name.
>
> You observe that the IDs in the downloaded file have at most 6 characters.
> To make room for additional bird species in coming editions of the
> taxonomy, you decide to use `varchar(7)` as the type of the ID column. To
> improve readability, you create a type alias:
>
> ```
> CREATE DOMAIN species_id AS varchar(7);
> ```
>
> Then you create the table that contains the bird species taxonomy:
>
> ```
> CREATE TABLE species (
>   id   species_id PRIMARY KEY,
>   name text   NOT NULL
> );
> ```
>
> Once all the bird species are imported from the taxonomy file into the
> `species` table, nothing is changed in this file.
>
> To guarantee that people enter the specie correctly, you now add a foreign
> key to all of your observation tables referencing the `species` table.
> Here's an example of what one of those tables look like:
>
> ```
> CREATE TABLE sightings (
>   id  serial PRIMARY KEY,
>   specie_id   species_id NOT NULL REFERENCES species (id),
>   coordinates geography(POINT, 4326) NOT NULL,
>   datetimestamptzNOT NULL
> );
> ```
>
> Each year, birdtaxonomies.com release a new edition of the bird species
> taxonomy, that you update your species table with. But this year,
> birdtaxonomies.com comes decides they want a more fine grained way of
> classifying bird species, which results in latest edition of bird species
> taxonomy to have more than 1 million species and IDs that are as long as 9
> characters.
>
> After these 10 years your club has more than 1 million members worldwide,
> more than 20 different kinds of observations tables, procedures and views,
> billions of rows of different kinds of observations and a total size of
> over 100 GB.
>
> In order to import the latest taxonomy, you need to change the type of the
> domain `species_id` from `varchar(7)` to `text`. But postgres doesn't allow
> you to change the type of a domain with a single command. Instead what you
> need to do is:
>
> 1. Rename current domain: `ALTER DOMAIN species_id RENAME TO
> species_id_depricated;`
> 2. Create a new domain: `CREATE DOMAIN species_id AS text;`
> 3. Change all >20 tables, procedures and views to use the new domain
> 4. Drop the old domain
>
> You had done a similar but smaller change a couple of years ago, and that
> meant days of downtime. Also, you are a lazy person and it's tedious to
> write the SQL code to change all tables, procedures and views. So, you
> decide to manipulate `pg_type` directly, since you read somewhere online
> that `varchar` and `text` are basically the same under the hood.
>
> End of story.
>
> Basically, I was hoping that my shortcut would save time and that this
> small `pg_type` manipulation can be done without any side effects. I don't
> have any knowledge of postgres internals, so I don't know if postgres would
> accept this change and carry on like nothing happened. Or, if it will break
> my indices or how queries are executed.
>
> In my head, this change would be instant and postgres would carry on like
> nothing happened and that the domain always had the type `text`. Is this a
> fools errand? Or could it actually make sense in this situation?
>
> Thanks
> Richard
>
> On Tue, May 27, 2025 at 8:10 PM Adrian Klaver 
> wrote:
>
>>
>>
>> On 5/27/25 7:27 AM, Richard Zetterberg wrote:
>> > Hello,
>> >
>> > I have a read-only table that contains a set of never changing
>> > categories. Each category has a unique alpha numerical ID and a
>> > description. The purpose of this table is so

Re: Changing a varchar(7) domain into text directly in pg_type

[Tom Lane]

Richard Zetterberg  writes:
> In my head, this change would be instant and postgres would carry on like
> nothing happened and that the domain always had the type `text`. Is this a
> fools errand? Or could it actually make sense in this situation?

Yeah, I think you can probably get away with this (especially since
you've already tested the effects in a throwaway database, IIUC).

The main thing people tend to forget when doing this sort of manual
catalog surgery is updating pg_depend and/or pg_shdepend.  But here
there's nothing to do.  Since both varchar and text are undroppable
built-in types, we don't bother to make dependencies on them.  And
you're not changing the domain's schema or owner, so its other
dependencies don't need to change.

regards, tom lane