Обсуждение: Trouble using plpgsql after 8.0 install
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've just upgraded to 8.01 on a Fedora 3 machine by doing the following yum remove postgresql-* and then (having previously downloaded these packages from my local FTP mirror) postgresql-8.0.1-2PGDG.i686.rpm postgresql-contrib-8.0.1-2PGDG.i686.rpm postgresql-devel-8.0.1-2PGDG.i686.rpm postgresql-docs-8.0.1-2PGDG.i686.rpm postgresql-jdbc-8.0.1-2PGDG.i686.rpm postgresql-libs-8.0.1-2PGDG.i686.rpm postgresql-pl-8.0.1-2PGDG.i686.rpm postgresql-server-8.0.1-2PGDG.i686.rpm postgresql-test-8.0.1-2PGDG.i686.rpm yum install postgresql-* After removing the devel package from the group (this gave me a dependency issue) I was able to successfully(?) install the new postgres. I then separately installed the devel package. However, when I: su - postgres psql some_database select get_booked_show(1, '20050101'); I get this result: ERROR: could not load library "/usr/lib/pgsql/plpgsql.so": /usr/lib/pgsql/plpgsql.so: undefined symbol: PG_exception_stack The get_booked_show function is defined thusly: CREATE FUNCTION get_booked_show(INTEGER, TIMESTAMP) RETURNS INTEGER AS ' ~ DECLARE ~ evt_id ALIAS FOR $1; ~ showtime ALIAS FOR $2; ~ r_return RECORD; ~ BEGIN ~ SELECT INTO r_return SUM(seats) AS seat_count ~ FROM reservation ~ WHERE xdb_reservation_id In( ~ SELECT reservation FROM event_reservation WHERE event = evt_id) ~ AND reserve_datetime = showtime; ~ RETURN r_return.seat_count; ~ END;' LANGUAGE 'plpgsql'; I search on the postgres site for 'undefined symbol: PG_exception_stack' didn't turn up anything interesting, and a search on Google groups turned up a thread indicating that perhaps I'm trying to call a pre 8.0 .so from the 8.0 front-end. However, wouldn't all of the 7.4 stuff have been removed by my yum remove call? Any insight is greatly appreciated. - -- Aaron Craig mercutio@pobox.com =========================================================== Get Firefox! http://www.spreadfirefox.com/?q=affiliates&id=0&t=1 =========================================================== -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCIlIeoyEQRcU83p4RAuo3AJ95tA2YZ8u1ug6pztry32f7LkaFNACcDciv POAIjxzb6yg8ODBbqv8hGUI= =EAC6 -----END PGP SIGNATURE-----
Aaron Craig <mercutio@pobox.com> writes:
> I get this result:
> ERROR: could not load library "/usr/lib/pgsql/plpgsql.so":
> /usr/lib/pgsql/plpgsql.so: undefined symbol: PG_exception_stack
This sounds like you are trying to load a newer plpgsql.so into an older
backend. In the context of the update process you describe, I can't
help wondering if you remembered to stop the old postmaster and start a
new one. You could still be talking to the running old postmaster
despite having deleted all its executable files from underneath it.
(Recent RPMs include an auto postmaster stop when uninstalling the server
package, but that wasn't always there.)
Note that another important step missing from your description was
dumping the old database so you could load it into the new postmaster.
It'd be a good idea to run pg_dumpall before trying to shut down the
old postmaster --- I'm not 100% sure that will work, but if it does,
it will save you having to reinstall the old RPMs in order to dump your
data.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Embarassingly, I realize that I should have actually shut down and restarted the server before complaining that the install didn't work. Having shut down the machine for the night, I discovered that the problem disappeared when I restarted the machine the next day. Shouldn't the rpm handle restarting the service after install though? Aaron Craig wrote: - -=-=- There is nothing either good or bad but thinking makes it so - -=-=- | I've just upgraded to 8.01 on a Fedora 3 machine by doing the following | | yum remove postgresql-* | | and then (having previously downloaded these packages from my local FTP | mirror) | | postgresql-8.0.1-2PGDG.i686.rpm | postgresql-contrib-8.0.1-2PGDG.i686.rpm | postgresql-devel-8.0.1-2PGDG.i686.rpm | postgresql-docs-8.0.1-2PGDG.i686.rpm | postgresql-jdbc-8.0.1-2PGDG.i686.rpm | postgresql-libs-8.0.1-2PGDG.i686.rpm | postgresql-pl-8.0.1-2PGDG.i686.rpm | postgresql-server-8.0.1-2PGDG.i686.rpm | postgresql-test-8.0.1-2PGDG.i686.rpm | | yum install postgresql-* | | After removing the devel package from the group (this gave me a | dependency issue) I was able to successfully(?) install the new | postgres. I then separately installed the devel package. | | However, when I: | su - postgres | psql some_database | select get_booked_show(1, '20050101'); | I get this result: | | ERROR: could not load library "/usr/lib/pgsql/plpgsql.so": | /usr/lib/pgsql/plpgsql.so: undefined symbol: PG_exception_stack | | The get_booked_show function is defined thusly: | CREATE FUNCTION get_booked_show(INTEGER, TIMESTAMP) RETURNS INTEGER AS ' | ~ DECLARE | ~ evt_id ALIAS FOR $1; | ~ showtime ALIAS FOR $2; | ~ r_return RECORD; | ~ BEGIN | ~ SELECT INTO r_return SUM(seats) AS seat_count | ~ FROM reservation | ~ WHERE xdb_reservation_id In( | ~ SELECT reservation FROM event_reservation WHERE event = evt_id) | ~ AND reserve_datetime = showtime; | ~ RETURN r_return.seat_count; | ~ END;' LANGUAGE 'plpgsql'; | | I search on the postgres site for 'undefined symbol: PG_exception_stack' | didn't turn up anything interesting, and a search on Google groups | turned up a thread indicating that perhaps I'm trying to call a pre 8.0 | .so from the 8.0 front-end. However, wouldn't all of the 7.4 stuff have | been removed by my yum remove call? | | Any insight is greatly appreciated. | - ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings - -- Aaron Craig mercutio@pobox.com =========================================================== Get Firefox! http://www.spreadfirefox.com/?q=affiliates&id=0&t=1 =========================================================== -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3-nr1 (Windows 2000) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCJFEkoyEQRcU83p4RAq6xAJ90GnRuLQccQpx5DuFCqKU1EpfZMACgjfPZ RnLTHEFBx4vkuSDVy3phdHg= =z/WP -----END PGP SIGNATURE-----