Reaching the limit: 1600 columns (event analytics temp tables)

Hi devs,

so, it was difficult, but finally we got there. When we run the analytics, we are getting the following exception

ERROR: tables can have at most 1600 columns; nested exception is org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

and, yes, we have programs containing above 1600 dataelements,

I’m just writing this email to know if someone here may have a solution for this, although I’m sure this is a restriction of postgresQL (and in fact, google says, the only way to change that limit is hacking the core of postgres…)

Thanks guys

Jose

1 Like

wow!

···

On Monday, 1 February 2016, Jose Garcia Muñoz josemp10@gmail.com wrote:

Hi devs,

so, it was difficult, but finally we got there. When we run the analytics, we are getting the following exception

ERROR: tables can have at most 1600 columns; nested exception is org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

and, yes, we have programs containing above 1600 dataelements,

I’m just writing this email to know if someone here may have a solution for this, although I’m sure this is a restriction of postgresQL (and in fact, google says, the only way to change that limit is hacking the core of postgres…)

Thanks guys

Jose


Pierre Dane

Jembi Health Systems

Software Development Manager

tel: +27 (0)21 701 0939

cel: +27 (0)83 680 8274

email: pierre@jembi.org

web: www.jembi.org

Hi Jose,

sorry yes that will be an issue. Postgres has a max limit of 1600 cols, and for program / event analytics we “crosstabulate” and have one column per data element.

Are all these data elements relevant for analytics? The only solution that comes to mind immediately is to introduce a new property for program data elements for skipping analytics.

regards,

Lars

···

On Mon, Feb 1, 2016 at 9:15 PM, Pierre Dane pierre@jembi.org wrote:

wow!

On Monday, 1 February 2016, Jose Garcia Muñoz josemp10@gmail.com wrote:

Hi devs,

so, it was difficult, but finally we got there. When we run the analytics, we are getting the following exception

ERROR: tables can have at most 1600 columns; nested exception is org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

and, yes, we have programs containing above 1600 dataelements,

I’m just writing this email to know if someone here may have a solution for this, although I’m sure this is a restriction of postgresQL (and in fact, google says, the only way to change that limit is hacking the core of postgres…)

Thanks guys

Jose

Pierre Dane

Jembi Health Systems

Software Development Manager

tel: +27 (0)21 701 0939

cel: +27 (0)83 680 8274

email: pierre@jembi.org

web: www.jembi.org


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Thanks Lars,

I think we are going to remove some dataelements from the programs (dont think we need them). However, I really like your idea. mmm… DataElements belonging to ProgramIndicators need to be part of the analytics tables, right?

···

On Mon, Feb 1, 2016 at 9:53 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Jose,

sorry yes that will be an issue. Postgres has a max limit of 1600 cols, and for program / event analytics we “crosstabulate” and have one column per data element.

Are all these data elements relevant for analytics? The only solution that comes to mind immediately is to introduce a new property for program data elements for skipping analytics.

regards,

Lars

On Mon, Feb 1, 2016 at 9:15 PM, Pierre Dane pierre@jembi.org wrote:

wow!

On Monday, 1 February 2016, Jose Garcia Muñoz josemp10@gmail.com wrote:

Hi devs,

so, it was difficult, but finally we got there. When we run the analytics, we are getting the following exception

ERROR: tables can have at most 1600 columns; nested exception is org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

and, yes, we have programs containing above 1600 dataelements,

I’m just writing this email to know if someone here may have a solution for this, although I’m sure this is a restriction of postgresQL (and in fact, google says, the only way to change that limit is hacking the core of postgres…)

Thanks guys

Jose

Pierre Dane

Jembi Health Systems

Software Development Manager

tel: +27 (0)21 701 0939

cel: +27 (0)83 680 8274

email: pierre@jembi.org

web: www.jembi.org


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

http://www.dhis2.org

Thanks Lars,

I think we are going to remove some dataelements from the programs (dont
think we need them). However, I really like your idea. mmm... DataElements
belonging to ProgramIndicators need to be part of the analytics tables,
right?

Yes that is correct.

···

On Mon, Feb 1, 2016 at 10:37 PM, Jose Garcia Muñoz <josemp10@gmail.com> wrote:

On Mon, Feb 1, 2016 at 9:53 PM, Lars Helge Øverland <larshelge@gmail.com> > wrote:

Hi Jose,

sorry yes that will be an issue. Postgres has a max limit of 1600 cols,
and for program / event analytics we "crosstabulate" and have one column
per data element.

Are all these data elements relevant for analytics? The only solution
that comes to mind immediately is to introduce a new property for program
data elements for skipping analytics.

regards,

Lars

On Mon, Feb 1, 2016 at 9:15 PM, Pierre Dane <pierre@jembi.org> wrote:

wow!

On Monday, 1 February 2016, Jose Garcia Muñoz <josemp10@gmail.com> >>> wrote:

Hi devs,

so, it was difficult, but finally we got there. When we run the
analytics, we are getting the following exception

ERROR: tables can have at most 1600 columns; nested exception is
org.postgresql.util.PSQLException: ERROR: tables can have at most 1600
columns

and, yes, we have programs containing above 1600 dataelements,

I'm just writing this email to know if someone here may have a solution
for this, although I'm sure this is a restriction of postgresQL (and in
fact, google says, the only way to change that limit is hacking the core of
postgres...)

Thanks guys
Jose

--
*Pierre Dane*

Jembi Health Systems
Software Development Manager
tel: +27 (0)21 701 0939
cel: +27 (0)83 680 8274
email: pierre@jembi.org
web: www.jembi.org

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

--
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
http://www.dhis2.org <https://www.dhis2.org>

--
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
http://www.dhis2.org <https://www.dhis2.org>