PostgreSQL update failures with r40245

Anthony_Messina 2012-09-19 10:27:29 UTC #1

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

-A

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Ralf_Becker_Stylite 2012-09-19 11:30:44 UTC #2

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

Ralf Becker
Director Software Development

Stylite AG

Morschheimer Strasse 15 | Tel. +49 6352 70629 0
D-67292 Kirchheimbolanden | Fax. +49 6352 70629 30

Email: rb@stylite.de

www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Live Security Virtual Conference
Exclusive live event will cover all the ways today’s security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2012-09-19 21:39:57 UTC #3

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are
specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

No worries, Ralf. I’ve been in the habit of doing a pg_dump, then applying
updates, followed by a pg_restore and unapplying updates if things don’t work.
That process became easier since I’ve figured a way to sort of git mirror the
content.

Thanks again! -A

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Ralf_Becker_Stylite 2012-09-20 12:12:05 UTC #4

Hi Anthony,

please try again, my PostgreSQL test-instance updates now.

Ralf

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are
specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

No worries, Ralf. I’ve been in the habit of doing a pg_dump, then applying
updates, followed by a pg_restore and unapplying updates if things don’t work.
That process became easier since I’ve figured a way to sort of git mirror the
content.

Thanks again! -A

Live Security Virtual Conference
Exclusive live event will cover all the ways today’s security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

–
Ralf Becker
Director Software Development

Stylite AG

Morschheimer Strasse 15 | Tel. +49 6352 70629 0
D-67292 Kirchheimbolanden | Fax. +49 6352 70629 30

Email: rb@stylite.de

www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://ad.doubleclick.net/clk;258768047;13503038;j?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2012-09-21 10:49:25 UTC #5

Hi Anthony,

please try again, my PostgreSQL test-instance updates now.

Ralf

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT
NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are
specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

No worries, Ralf. I’ve been in the habit of doing a pg_dump, then
applying
updates, followed by a pg_restore and unapplying updates if things don’t
work. That process became easier since I’ve figured a way to sort of git
mirror the content.

Thanks again! -A

Ok, Ralf, I gave it s shot and came up with only two issues. The first is
that there are some old recurring calendar entries that for some reason come
up with a NULL range start, and are therefore not able to be inserted, as the
range_start column is set to NOT NULL.

The PostgreSQL error:
ERROR: null value in column “range_start” violates not-null constraint
STATEMENT: UPDATE egw_cal SET range_start = (SELECT MIN(cal_start) FROM
egw_cal_dates WHERE egw_cal_dates.cal_id=egw_cal.cal_id)

Now, I’m not certain why some of these entries came up with null values for
range_start, except for the fact that they are some of my oldest calendar
entries (a few years old).

The second part of the diff is just a fix for a typo as there is an extra ')'
parens included in the SQL statement.

The diff below allows the upgrade to succeed.

diff --git a/calendar/setup/tables_update.inc.php
b/calendar/setup/tables_update.inc.php
index 7c4be53…325f5dc 100644
— a/calendar/setup/tables_update.inc.php
+++ b/calendar/setup/tables_update.inc.php
@@ -2173,7 +2173,7 @@ function calendar_upgrade1_9_006()
$GLOBALS[‘egw_setup’]->oProc-

AlterColumn(‘egw_cal’,‘range_start’,array(
‘type’ => ‘int’,
‘precision’ => ‘8’,

```
          'nullable' => False,
```

          'nullable' => True,
          'comment' => 'startdate (of range)'
  ));
  $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET range_start =

(SELECT MIN(cal_start) FROM egw_cal_dates WHERE
egw_cal_dates.cal_id=egw_cal.cal_id)’, LINE, FILE);
@@ -2192,7 +2192,7 @@ function calendar_upgrade1_9_006()
}
else // PostgreSQL, MsSQL, …
{

          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET

range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id)’, LINE, FILE);

          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET

range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id’, LINE, FILE);
}
$GLOBALS[‘egw_setup’]->oProc->DropColumn(‘egw_cal_repeats’,array(
‘fd’ => array(

I’m wondering then, whether it would be best to set the range_start to the
value of the ‘cal_created’ column, at least in my instance, to ensure future
upgrades succeed. Or would it be best to set them to ‘0’, or some other
value?

-A

–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E

Ralf_Becker_Stylite 2012-09-25 07:16:43 UTC #6

Hi Anthony,

thanks for testing

I improved update scripts again:

a) fixed that stray bracket

b) moved altering range_start to NOT NULL and dropping default at end

If you dont mind, please test it again.

Ralf

Hi Anthony,

please try again, my PostgreSQL test-instance updates now.

Ralf

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT
NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are
specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

No worries, Ralf. I’ve been in the habit of doing a pg_dump, then
applying
updates, followed by a pg_restore and unapplying updates if things don’t
work. That process became easier since I’ve figured a way to sort of git
mirror the content.

Thanks again! -A

Ok, Ralf, I gave it s shot and came up with only two issues. The first is
that there are some old recurring calendar entries that for some reason come
up with a NULL range start, and are therefore not able to be inserted, as the
range_start column is set to NOT NULL.

The PostgreSQL error:
ERROR: null value in column “range_start” violates not-null constraint
STATEMENT: UPDATE egw_cal SET range_start = (SELECT MIN(cal_start) FROM
egw_cal_dates WHERE egw_cal_dates.cal_id=egw_cal.cal_id)

Now, I’m not certain why some of these entries came up with null values for
range_start, except for the fact that they are some of my oldest calendar
entries (a few years old).

The second part of the diff is just a fix for a typo as there is an extra ')'
parens included in the SQL statement.

The diff below allows the upgrade to succeed.

diff --git a/calendar/setup/tables_update.inc.php
b/calendar/setup/tables_update.inc.php
index 7c4be53…325f5dc 100644
— a/calendar/setup/tables_update.inc.php
+++ b/calendar/setup/tables_update.inc.php
@@ -2173,7 +2173,7 @@ function calendar_upgrade1_9_006()
$GLOBALS[‘egw_setup’]->oProc-

AlterColumn(‘egw_cal’,‘range_start’,array(
‘type’ => ‘int’,
‘precision’ => ‘8’,
          'nullable' => False,
          'nullable' => True,
           'comment' => 'startdate (of range)'
   ));
   $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET range_start =
(SELECT MIN(cal_start) FROM egw_cal_dates WHERE
egw_cal_dates.cal_id=egw_cal.cal_id)’, LINE, FILE);
@@ -2192,7 +2192,7 @@ function calendar_upgrade1_9_006()
}
else // PostgreSQL, MsSQL, …
{
          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET
range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id)’, LINE, FILE);
          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET
range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id’, LINE, FILE);
}
$GLOBALS[‘egw_setup’]->oProc->DropColumn(‘egw_cal_repeats’,array(
‘fd’ => array(

I’m wondering then, whether it would be best to set the range_start to the
value of the ‘cal_created’ column, at least in my instance, to ensure future
upgrades succeed. Or would it be best to set them to ‘0’, or some other
value?

-A

Got visibility?
Most devs has no idea what their production app looks like.
Find out how fast your code is with AppDynamics Lite.
http://ad.doubleclick.net/clk;262219671;13503038;y?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

–
Ralf Becker
Director Software Development

Stylite AG

Morschheimer Strasse 15 | Tel. +49 6352 70629 0
D-67292 Kirchheimbolanden | Fax. +49 6352 70629 30

Email: rb@stylite.de

www.stylite.de | www.egroupware.org

Managing Directors: Andre Keller | Ralf Becker | Gudrun Mueller
Chairman of the supervisory board: Prof. Dr. Birger Leon Kropshofer

VAT DE214280951 | Registered HRB 31158 Kaiserslautern Germany

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers

Anthony_Messina 2012-09-25 09:34:03 UTC #7

Hi Anthony,

thanks for testing

I improved update scripts again:

a) fixed that stray bracket

b) moved altering range_start to NOT NULL and dropping default at end

If you dont mind, please test it again.

Ralf

This works well, Ralf. Thanks! -A

Hi Anthony,

please try again, my PostgreSQL test-instance updates now.

Ralf

Hi Anthony,

The updates to the calendar related tables fail with PostgreSQL 9.1:

ERROR: column “range_start” contains null values
STATEMENT: ALTER TABLE “egw_cal” ALTER COLUMN “range_start” SET NOT
NULL
ERROR: syntax error at or near “,” at character 15
STATEMENT: UPDATE egw_cal,egw_cal_repeats SET
egw_cal.range_end=egw_cal_repeats.recur_enddate WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id
ERROR: syntax error at or near “,” at character 73

Apparently, PostgreSQL doesn’t like the fact that two tables are
specified:

“UPDATE egw_cal,egw_cal_repeats SET …”

http://www.postgresql.org/docs/9.1/static/sql-update.html

Ok, need to check that. Probably not today …

Ralf

No worries, Ralf. I’ve been in the habit of doing a pg_dump, then
applying
updates, followed by a pg_restore and unapplying updates if things don’t
work. That process became easier since I’ve figured a way to sort of git
mirror the content.

Thanks again! -A

Ok, Ralf, I gave it s shot and came up with only two issues. The first is
that there are some old recurring calendar entries that for some reason
come up with a NULL range start, and are therefore not able to be
inserted, as the range_start column is set to NOT NULL.

The PostgreSQL error:
ERROR: null value in column “range_start” violates not-null constraint
STATEMENT: UPDATE egw_cal SET range_start = (SELECT MIN(cal_start) FROM
egw_cal_dates WHERE egw_cal_dates.cal_id=egw_cal.cal_id)

Now, I’m not certain why some of these entries came up with null values
for
range_start, except for the fact that they are some of my oldest calendar
entries (a few years old).

The second part of the diff is just a fix for a typo as there is an extra
’)’ parens included in the SQL statement.

The diff below allows the upgrade to succeed.

diff --git a/calendar/setup/tables_update.inc.php
b/calendar/setup/tables_update.inc.php
index 7c4be53…325f5dc 100644
— a/calendar/setup/tables_update.inc.php
+++ b/calendar/setup/tables_update.inc.php
@@ -2173,7 +2173,7 @@ function calendar_upgrade1_9_006()
     $GLOBALS['egw_setup']->oProc-
AlterColumn(‘egw_cal’,‘range_start’,array(
             'type' => 'int',
             'precision' => '8',
          'nullable' => False,
          'nullable' => True,

           'comment' => 'startdate (of range)'
   ));
   $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET range_start
=>
(SELECT MIN(cal_start) FROM egw_cal_dates WHERE
egw_cal_dates.cal_id=egw_cal.cal_id)’, LINE, FILE);
@@ -2192,7 +2192,7 @@ function calendar_upgrade1_9_006()
     }
     else    // PostgreSQL, MsSQL, ...
     {
          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET
range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id)’, LINE, FILE);
          $GLOBALS['egw_setup']->db->query('UPDATE egw_cal SET
range_end=recur_enddate FROM egw_cal_repeats WHERE
egw_cal.cal_id=egw_cal_repeats.cal_id’, LINE, FILE);
     }
     $GLOBALS['egw_setup']->oProc->DropColumn('egw_cal_repeats',array(
             'fd' => array(
I’m wondering then, whether it would be best to set the range_start to the
value of the ‘cal_created’ column, at least in my instance, to ensure
future upgrades succeed. Or would it be best to set them to ‘0’, or some
other value?

-A

---- Got visibility?
Most devs has no idea what their production app looks like.
Find out how fast your code is with AppDynamics Lite.
http://ad.doubleclick.net/clk;262219671;13503038;y?
http://info.appdynamics.com/FreeJavaPerformanceDownload.html

eGroupWare-developers mailing list
eGroupWare-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/egroupware-developers
–
Ralf Becker
Director Software Development
–
Anthony - http://messinet.com - http://messinet.com/~amessina/gallery
8F89 5E72 8DF0 BCF0 10BE 9967 92DC 35DC B001 4A4E