Postgres, bytea and my ruined evening

RT Developers
1

After extensive discussions with the current developer of DBD::Pg
(Thanks, David!), I’ve come to the conclusion that re-engineering our
database independence layer to support bytea is a medium/big fix that
has scary performance implications. Thankfully, we never got around to
storing raw binary data in the bytea fields that we use. So, it’s back
to “text” columns instead of “bytea”. (At the same time, I added a
field “LastUpdated” to the in-database session cache, which will make a
lot of people happy a month or three from now.)

Can someone from the Postgres camp grab

http://fsck.com/aegis/aegis.cgi/rt.3.0.C26.tar.gz?file@aetar+project@rt.3.0+change@26

and tell me if it deals better?

-j

Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.

2

At 09:28 PM 19/03/2003 -0500, Jesse Vincent wrote:

http://fsck.com/aegis/aegis.cgi/rt.3.0.C26.tar.gz?file@aetar+project@rt.3.0+change@26

For sessions:

 LastUpdated not null default current_timestamp

needs to read:

 LastUpdated TIMESTAMP not null default current_timestamp

I’ve done a make upgrade, and it still seems to work…

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

3

Thanks. applied.On Thu, Mar 20, 2003 at 03:13:23PM +1100, Philip Warner wrote:

At 09:28 PM 19/03/2003 -0500, Jesse Vincent wrote:

http://fsck.com/aegis/aegis.cgi/rt.3.0.C26.tar.gz?file@aetar+project@rt.3.0+change@26

For sessions:

LastUpdated not null default current_timestamp

needs to read:

LastUpdated TIMESTAMP not null default current_timestamp

I’ve done a make upgrade, and it still seems to work…

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

4

At 03:13 PM 20/03/2003 +1100, Philip Warner wrote:

LastUpdated TIMESTAMP not null default current_timestamp

…that should really be ‘TIMESTAMP WITHOUT TIME ZONE’ to be consistent.

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

5

-----BEGIN PGP SIGNED MESSAGE-----

On 20-Mar-2003, Jesse Vincent wrote message “[rt-devel] Postgres, bytea and my
ruined evening.”

After extensive discussions with the current developer of DBD::Pg
(Thanks, David!), I’ve come to the conclusion that re-engineering our
database independence layer to support bytea is a medium/big fix that
has scary performance implications. Thankfully, we never got around to
storing raw binary data in the bytea fields that we use. So, it’s back
to “text” columns instead of “bytea”. (At the same time, I added a
field “LastUpdated” to the in-database session cache, which will make a
lot of people happy a month or three from now.)

Can someone from the Postgres camp grab

http://fsck.com/aegis/aegis.cgi/rt.3.0.C26.tar.gz?file@aetar+project@rt.3.0+ch
ange@26

and tell me if it deals better?

  -j

–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Nope, no go.

make initialize-database

/usr/bin/perl //opt/rt3/sbin/rt-setup-database --action init --dba pg72

  • –prompt-for-dba-password
    In order to create a new database and grant RT access to that database,
    this script needs to connect to your Pg instance on localhost as pg72.
    Please specify that user’s database password below. If the user has no database
    password, just press return.

Password: Now creating a database for RT.
Creating Pg database rt3.
DBD::Pg::db do failed: ERROR: Multi-byte support is not enabled at
//opt/rt3/sbin/rt-setup-database line 182, line 1.
ERROR: Multi-byte support is not enabled at //opt/rt3/sbin/rt-setup-database
line 182, line 1.
*** Error code 255

Stop in /usr/download/rt/rt-3-0-0rc3.

Just to backtrack, I extracted a fresh copy of rc3. Grabbed the aegis url you
listed, extracted the file from that url from within the extracted rc3 dir.
Had to give execute permission to ./configure and then I used:

./configure --with-db-type=Pg --with-db-port=65432 --with-db-dba=pg72

  • –with-db-rt-user=rt --with-rt-pass=xxxxxx

Andy Harrison
Great Works Internet
System Operations
ajharrison@gwi.net
RSA 1024 pgp key: http://www.nachoz.com/andy.pub

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQCVAwUBPnnDcFPEkLgodAWVAQHCqAQAsw2WNdo036mtgYsG6NTvUO5HVk+3MPUT
7Aw4lCFtx6uwmliHdqQ2uhrkE9D4J7GNNbPDUkNeKS+OsSxhav0vC4APTiVcKe69
QZlmfX6itTXTd//mWTndKiO1Xb2WdiyLhvbISziZ8mLdVmXDmGo+sXPaWqK6nBih
B1U7+VFBFeA=
=Y0TY
-----END PGP SIGNATURE-----

6

Ah. the unicode fix is apparently a postgres 7.3 addition. I’ll upload a
new change within the next few minutes that should resolve this, whicle
still not penalizing folks using pg 7.3

Nope, no go.

make initialize-database

/usr/bin/perl //opt/rt3/sbin/rt-setup-database --action init --dba pg72

  • –prompt-for-dba-password
    In order to create a new database and grant RT access to that database,
    this script needs to connect to your Pg instance on localhost as pg72.
    Please specify that user’s database password below. If the user has no database
    password, just press return.

Password: Now creating a database for RT.
Creating Pg database rt3.
DBD::Pg::db do failed: ERROR: Multi-byte support is not enabled at
//opt/rt3/sbin/rt-setup-database line 182, line 1.
ERROR: Multi-byte support is not enabled at //opt/rt3/sbin/rt-setup-database
line 182, line 1.
*** Error code 255

Stop in /usr/download/rt/rt-3-0-0rc3.

Just to backtrack, I extracted a fresh copy of rc3. Grabbed the aegis url you
listed, extracted the file from that url from within the extracted rc3 dir.
Had to give execute permission to ./configure and then I used:

./configure --with-db-type=Pg --with-db-port=65432 --with-db-dba=pg72

  • –with-db-rt-user=rt --with-rt-pass=xxxxxx

~~
Andy Harrison
Great Works Internet
System Operations
ajharrison@gwi.net
RSA 1024 pgp key: http://www.nachoz.com/andy.pub

-----BEGIN PGP SIGNATURE-----
Version: PGP 6.5.8

iQCVAwUBPnnDcFPEkLgodAWVAQHCqAQAsw2WNdo036mtgYsG6NTvUO5HVk+3MPUT
7Aw4lCFtx6uwmliHdqQ2uhrkE9D4J7GNNbPDUkNeKS+OsSxhav0vC4APTiVcKe69
QZlmfX6itTXTd//mWTndKiO1Xb2WdiyLhvbISziZ8mLdVmXDmGo+sXPaWqK6nBih
B1U7+VFBFeA=
=Y0TY
-----END PGP SIGNATURE-----

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

7

Thanks. Applied.On Thu, Mar 20, 2003 at 11:05:35PM +1100, Philip Warner wrote:

At 03:13 PM 20/03/2003 +1100, Philip Warner wrote:

LastUpdated TIMESTAMP not null default current_timestamp

…that should really be ‘TIMESTAMP WITHOUT TIME ZONE’ to be consistent.

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

8

At 03:13 PM 20/03/2003 +1100, Philip Warner wrote:

LastUpdated TIMESTAMP not null default current_timestamp

…that should really be ‘TIMESTAMP WITHOUT TIME ZONE’ to be consistent.

Thanks. Applied.

Except my postgres 7.3.2 doesn’t like the syntax and my reading of the
docs seems to claim that ‘WITHOUT TIME ZONE’ is the default.
Anway, this table isn’t going to be touched using the RT API, only using
a reaper to clear out old sessions. I’m going to back out this change.

-j

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

9

Can folks who’ve been having Postgres issues give this snapshot a shot?

http://fsck.com/aegis/aegis.cgi/rt.3.0.C28.tar.gz?file@aetar+project@rt.3.0+change@28

Also, for the upgrade instructions, what’s the recommended postgres
procedure for “dump all data, but not the schema from this database to a file on disk and then reload it”?On Thu, Mar 20, 2003 at 03:03:45PM -0500, Jesse Vincent wrote:

At 03:13 PM 20/03/2003 +1100, Philip Warner wrote:

LastUpdated TIMESTAMP not null default current_timestamp

…that should really be ‘TIMESTAMP WITHOUT TIME ZONE’ to be consistent.

Thanks. Applied.

Except my postgres 7.3.2 doesn’t like the syntax and my reading of the
docs seems to claim that ‘WITHOUT TIME ZONE’ is the default.
Anway, this table isn’t going to be touched using the RT API, only using
a reaper to clear out old sessions. I’m going to back out this change.

-j

–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

10

“JV” == Jesse Vincent jesse@bestpractical.com writes:

JV> Also, for the upgrade instructions, what’s the recommended
JV> postgres procedure for “dump all data, but not the schema from
JV> this database to a file on disk and then reload it”?

Well, to survive reordering of columns, the only real way to do it is
with “pg_dump --attribute-inserts --data-only” then loading the result
via psql after the new schema has been created.

This will dump full SQL insert clauses, and include necessary trigger
disabling so that foreign keys won’t be tested per insert.

11

At 04:17 PM 20/03/2003 -0500, Vivek Khera wrote:

Well, to survive reordering of columns, the only real way to do it is
with “pg_dump --attribute-inserts --data-only” then loading the result
via psql after the new schema has been created.

Since at least 7.3, but maybe 7.2 PG_DUMP will use the extended COPY format
which knows about column names & order. It was a surprise to me too…

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

12

At 03:25 PM 20/03/2003 -0500, Jesse Vincent wrote:

Also, for the upgrade instructions, what’s the recommended postgres
procedure for “dump all data, but not the schema from this database to a
file on disk and then reload it”?

For 7.2 or less (as well as 7.3 if you want to):

 pg_dump -D -a rt-database-name > dump.sql

(-a is a data-only flag).
(-D says to use column-inserts in case column order changes).

For 7.3:

 pg_dump -a rt-database-name > dump.sql

the only advantage of this version is that it is faster for large volumes
of data – but because columns may have changed order (or new columns may
have been added), it will only work in 7.3+

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

13

rather than doing a full dump and restore, does this sequence of
commands make sense for updating postgres’ schema for RC4?

ALTER TABLE Attachments RENAME COLUMN Content to OldContent;
ALTER TABLE Attachments ADD COLUMN Content text NULL;
UPDATE Attachments SET Content = OldContent;
ALTER TABLE Attachments RENAME COLUMN Headers to OldHeaders;
ALTER TABLE Attachments ADD COLUMN Headers text NULL;
UPDATE Attachments SET Headers = OldHeaders;
ALTER TABLE Attachments DROP Column OldContent;
ALTER TABLE Attachments DROP Column OldHeaders;
ALTER TABLE sessions ADD COLUMN LastUpdated TIMESTAMP not null default current_timestamp;On Fri, Mar 21, 2003 at 10:29:28AM +1100, Philip Warner wrote:

At 03:25 PM 20/03/2003 -0500, Jesse Vincent wrote:

Also, for the upgrade instructions, what’s the recommended postgres
procedure for “dump all data, but not the schema from this database to a
file on disk and then reload it”?

For 7.2 or less (as well as 7.3 if you want to):

pg_dump -D -a rt-database-name > dump.sql

(-a is a data-only flag).
(-D says to use column-inserts in case column order changes).

For 7.3:

pg_dump -a rt-database-name > dump.sql

the only advantage of this version is that it is faster for large volumes
of data – but because columns may have changed order (or new columns may
have been added), it will only work in 7.3+

Philip Warner | —___
Albatross Consulting Pty. Ltd. |----/ -
(A.B.N. 75 008 659 498) | /(@) —
Tel: (+61) 0500 83 82 81 | _________
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / |
| –___–
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.

14

“JV” == Jesse Vincent jesse@bestpractical.com writes:

JV> rather than doing a full dump and restore, does this sequence of
JV> commands make sense for updating postgres’ schema for RC4?

by eyeball it looks fine, but the drop column is only in PG 7.3+

15

“JV” == Jesse Vincent jesse@bestpractical.com writes:

JV> rather than doing a full dump and restore, does this sequence of
JV> commands make sense for updating postgres’ schema for RC4?

by eyeball it looks fine, but the drop column is only in PG 7.3+

nod thanks.

rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.