Re: [ADMIN] connection problem (newbie question)
Hello Paul: What user are you using. Remember, you have a brand new install and haven't created any users yet, except for the superuser(postgres). Try connecting as the postgres(superuser). btw, are you trying to start postmaster as root? it seems that there's a bug in postgresql: it says it started successfully when you start it as root, but it doesn't start. start it as pgsql (freebsd) or postgres (linux) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Connection problem (newbie question)
Hello I would like to know the procedure to install PostGres on Windows NT machine. Also, if you could please also send me the Installation, how to download ? --- Paul Kavanagh <[EMAIL PROTECTED]> wrote: > Hi, > > I've just installed Postgresql 7.3 via Cygwin on > Win2K, started the > postmaster successfully, yet cannot connect using > psql. See below for trace: > > $ pg_ctl start -D /usr/share/postresql/data -l > serverlog > postmaster successfully started > > $ psql > psql: could not connect to server: No such file or > directory > Is the server running locally and accepting > connections on Unix domain socket > "/tmp/.s.PGSQL.5432"? > > I've attached my .conf. > > Any ideas ? > > Thanks in advance, > -Paul > > ATTACHMENT part 2 application/octet-stream name=postgresql.conf > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > = Take careMohd.Ghalib Akhtar(office)91-11-6152172,Ext-217 Fax : 91-11-6146217, 6149446 - - __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] test
---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Connection problem (newbie question)
Title: RE: [ADMIN] Connection problem (newbie question) Try these great walkthroughs & tips: http://www.systinet.com/doc/wasp_uddi/databaseInstallationGuide/InstallingDatabaseServers.html http://www.ejip.net/faq/postgresql_win_setup_faq.jsp http://php.weblogs.com/discuss/post_tips Other great links. Comercial PostgreSQL on Windows: http://www.dbexperts.net/postgresql Native PostgreSQL on Windows: http://hp.vector.co.jp/authors/VA023283/PostgreSQLe.html -Original Message- From: Mohd Ghalib Akhtar [mailto:[EMAIL PROTECTED]] Sent: Monday, 9 December 2002 4:38 PM To: Paul Kavanagh; [EMAIL PROTECTED] Subject: Re: [ADMIN] Connection problem (newbie question) Hello I would like to know the procedure to install PostGres on Windows NT machine. Also, if you could please also send me the Installation, how to download ? --- Paul Kavanagh <[EMAIL PROTECTED]> wrote: > Hi, > > I've just installed Postgresql 7.3 via Cygwin on > Win2K, started the > postmaster successfully, yet cannot connect using > psql. See below for trace: > > $ pg_ctl start -D /usr/share/postresql/data -l > serverlog > postmaster successfully started > > $ psql > psql: could not connect to server: No such file or > directory > Is the server running locally and accepting > connections on Unix domain socket > "/tmp/.s.PGSQL.5432"? > > I've attached my .conf. > > Any ideas ? > > Thanks in advance, > -Paul > > ATTACHMENT part 2 application/octet-stream name=postgresql.conf > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > = Take careMohd.Ghalib Akhtar(office)91-11-6152172,Ext-217 Fax : 91-11-6146217, 6149446 - - __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] auto creation of database and schema
hi geeks, i am a newbie to DBA and need to know how to create a script to automate the installation of postgres on linux and create a defined schema on the server.any directions is greatly appreciated. thanks kris _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] How to change table owner?
Greetings! Sort of a newbie-ish question, I haven't seen it in the archives so thought I'd ask. I've created and deployed a number of databases and am now getting around to adding some table maintenance (a vacuum script). But, the script can't run as the "postgres" user. It runs as a defined db user "admin".. Unfortunately, in my db schema the tables are owned by postgres and my script doesn't have permission to vacuum. ( I've since changed the schema so this won't happen in the future) I need to change owner of the tables from postgres to admin... Granting all to admin apparently isn't enough. Is there are simple way to change table owner short of dumping, editing the dump and restoring? redhat7.2 postgresql7.1/7.2 Thanks Anders ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How to change table owner?
Anders Bogdan <[EMAIL PROTECTED]> writes: > I've created and deployed a number of databases and am now getting > around to adding some table maintenance (a vacuum script). But, the > script can't run as the "postgres" user. It runs as a defined db user > "admin".. Unfortunately, in my db schema the tables are owned by > postgres and my script doesn't have permission to vacuum. Make "admin" a postgres superuser. (I think "ALTER USER admin CREATEUSER" is the approved way to do this.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] How to change table owner?
Thankyou! That worked, much easier than dumping the db.. thanks Anders On Monday, December 9, 2002, at 09:35 AM, Tom Lane wrote: Anders Bogdan <[EMAIL PROTECTED]> writes: I've created and deployed a number of databases and am now getting around to adding some table maintenance (a vacuum script). But, the script can't run as the "postgres" user. It runs as a defined db user "admin".. Unfortunately, in my db schema the tables are owned by postgres and my script doesn't have permission to vacuum. Make "admin" a postgres superuser. (I think "ALTER USER admin CREATEUSER" is the approved way to do this.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] WAL disk space
An empty database cluster takes up 20 MB, 16 of which are dedicated to a WAL file. This seems to be a lot for a small or low-traffic database. Is there any way to reduce the size of this WAL file, besides reverting to version 7.0 (where WAL isn't used)? Thanks -Erin ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] WAL disk space
On Mon, 9 Dec 2002, Erin Munro wrote: > An empty database cluster takes up 20 MB, 16 of which > are dedicated to a WAL file. This seems to be a lot > for a small or low-traffic database. Is there any way > to reduce the size of this WAL file, besides reverting > to version 7.0 (where WAL isn't used)? IIRC, you can change the segment size at compile time in xlog.h. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] WAL disk space
Erin Munro <[EMAIL PROTECTED]> writes: > An empty database cluster takes up 20 MB, 16 of which > are dedicated to a WAL file. This seems to be a lot > for a small or low-traffic database. Is there any way > to reduce the size of this WAL file, besides reverting > to version 7.0 (where WAL isn't used)? Reduce XLogSegSize in src/include/access/xlog.h. Recompile, re-initdb (or at least do pg_resetxlog), put up with more frequent checkpoints. With sufficiently low write activity this might be worthwhile, but I find it hard to think of any recently-manufactured hardware wherein 16MB of disk space is worth agonizing over. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] "dumpProcLangs(): handler procedure for language plpgsql not found"on 7.0.3
hi there, I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs. I recently added plpgsql as a language to one of my databases, and now when I try to do a pg_dump, I get: "dumpProcLangs(): handler procedure for language plpgsql not found" If I drop the language, pg_dump works fine, but if I add it back (and even if I restart postgres), I get the same error. That's on our production box. Strangely enough, If I do the same thing on my dev box (also RH 7.1 w/ 7.0.3), I don't get the error and the dump works fine, picking up my triggers and all. I've gone through and compared all rpm-installed files on the two boxes and they're identical... Any ideas on how to fix that error? thanks, brian -- Brian T. Fujito www.lightsource.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] "dumpProcLangs(): handler procedure for language plpgsql not found" on 7.0.3
Brian Fujito <[EMAIL PROTECTED]> writes: > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs. > I recently added plpgsql as a language to one of my databases, > and now when I try to do a pg_dump, I get: > "dumpProcLangs(): handler procedure for language plpgsql not found" > If I drop the language, pg_dump works fine, but if I add it back (and > even if I restart postgres), I get the same error. What exactly are you doing to drop and re-add the language? I should think CREATE LANGUAGE would fail if the handler proc isn't there. (Also, are you doing pg_dump or pg_dumpall? If the latter, maybe the failure is occurring in a different database than the one you are changing.) But having said that, 7.0.3 is ancient history ... you really are overdue for an upgrade. With my Red Hat fedora on, I'd say the same about your choice of OS version too. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Insuring consistant backups
Hi: A question regarding backups. Commercial databases I've administered have had some facility to freeze or redirect transactions when a backup is being taken from a database which is online, in order to insure that the backup snapshots a consistant database. Does postgresql have a similar facility, or do I have to script some solution, perhaps forcing all sessions to be dropped before doing a pg_dump ? Thanks, -- Pat - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Insuring consistant backups
pg_dump does a snapshot at backup start, so your backups will be consistent even if there is activity. --- [EMAIL PROTECTED] wrote: > > > Hi: > > A question regarding backups. Commercial databases I've administered have had > some facility to freeze or redirect transactions when a backup is being taken > from a database which is online, in order to insure that the backup snapshots a > consistant database. > > Does postgresql have a similar facility, or do I have to script some solution, > perhaps forcing all sessions to be dropped before doing a pg_dump ? > > Thanks, > -- Pat > > > - > This mail sent through IMP: http://horde.org/imp/ > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Insuring consistant backups
On 9 Dec 2002 at 21:33, [EMAIL PROTECTED] wrote: > Does postgresql have a similar facility, or do I have to script some > solution, perhaps forcing all sessions to be dropped before doing a > pg_dump ? >From man pg_dump and http://www.postgresql.org/idocs/index.php?app- pgdump.html: pg_dump makes consistent backups even if the database is being used concurrently. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Thanks for your input-- I've tried both ways: createlang/droplang from the command line as user postgres and: CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; I'm using pg_dump (not pg_dumpall) on the specific database on which I created the language. I realize 7.0.3 is ancient (same with 7.1)... I just don't have the time to deal with an upgrade quite yet. Soon enough :) In the mean time, a stop-gap solution would definitely be appreciated. Thank you, Brian On Mon, 2002-12-09 at 14:28, Tom Lane wrote: > Brian Fujito <[EMAIL PROTECTED]> writes: > > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs. > > I recently added plpgsql as a language to one of my databases, > > and now when I try to do a pg_dump, I get: > > > "dumpProcLangs(): handler procedure for language plpgsql not found" > > > If I drop the language, pg_dump works fine, but if I add it back (and > > even if I restart postgres), I get the same error. > > What exactly are you doing to drop and re-add the language? I should > think CREATE LANGUAGE would fail if the handler proc isn't there. > > (Also, are you doing pg_dump or pg_dumpall? If the latter, maybe the > failure is occurring in a different database than the one you are > changing.) > > But having said that, 7.0.3 is ancient history ... you really are > overdue for an upgrade. With my Red Hat fedora on, I'd say the same > about your choice of OS version too. > > regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Brian Fujito <[EMAIL PROTECTED]> writes: >> What exactly are you doing to drop and re-add the language? I should >> think CREATE LANGUAGE would fail if the handler proc isn't there. > I've tried both ways: > createlang/droplang from the command line as user postgres > and: > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > HANDLER plpgsql_call_handler > LANCOMPILER 'PL/pgSQL'; Hrmph. Looks perfectly standard from here; I don't see why pg_dump is failing to find the handler. It would help to see what the server-side view of the transaction is like. Would you run pg_dump after setting query logging on (from memory, I think export PGOPTIONS="-d2" will work in 7.0, but too tired to check it) and then show us the tail end of the postmaster log after pg_dump fails? regards, tom lane PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 billion; is it possible your pg_language OID for plpgsql is over 2G? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
On 10 Dec 2002 at 0:02, Tom Lane wrote: > Brian Fujito <[EMAIL PROTECTED]> writes: > >> What exactly are you doing to drop and re-add the language? I > >> should think CREATE LANGUAGE would fail if the handler proc isn't > >> there. > > > I've tried both ways: > > > createlang/droplang from the command line as user postgres > > > and: > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > > HANDLER plpgsql_call_handler > > LANCOMPILER 'PL/pgSQL'; > > Hrmph. Looks perfectly standard from here; I don't see why pg_dump is > failing to find the handler. It would help to see what the > server-side view of the transaction is like. Would you run pg_dump > after setting query logging on (from memory, I think export > PGOPTIONS="-d2" will work in 7.0, but too tired to check it) and then > show us the tail end of the postmaster log after pg_dump fails? > >regards, tom lane > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 > billion; is it possible your pg_language OID for plpgsql is over 2G? Followed by another wild guess. Could the path be the problem? Looking at my notes (http://www.freebsddiary.org/postgresql- pgsql.php) I see that at one time I supplied a pathname : CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; Please let us know. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Thanks again for your input. I think you're right about the OID's. Watching the query logs, the pg_dump failed just after: 021210.00:58:25.578 [25527] query: SELECT * FROM pg_language WHERE lanispl ORDER BY oid MYDB=> select * from pg_language where lanispl order by oid; lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler -+-+--+---+- plpgsql | t | t| -1983262688 | PL/pgSQL (1 row) MYDB=> select oid from pg_proc where proname = 'plpgsql_call_handler'; oid - -1983262688 (1 row) lifeaudioV2=> select * from pg_language where lanplcallfoid = -1983262688; lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler -+-+--+---+- (0 rows) MYDB=> So yes, it looks like the OID's got messed up. That negative number looks suspiciously like an overflow on a max int. Is there *any* way to fix this, or should I just deal with it and upgrade? BTW, does this mean that other data in the system could be corrupted? If an upgrade is the best way out, are there any particular versions that would a) fix the problem and b) allow for a clean transition? I'm using Perl's DBI::Pg, PHP's pgsql extensions, and JDBC for postgres... Just a bit weary of making transitions :-) I understand I'll have to perform a pg_dump for the transition to work, which I can do by turning off the triggers, for now. Thank you, Tom, for all your help. Brian On Tue, 2002-12-10 at 00:02, Tom Lane wrote: > Brian Fujito <[EMAIL PROTECTED]> writes: > >> What exactly are you doing to drop and re-add the language? I should > >> think CREATE LANGUAGE would fail if the handler proc isn't there. > > > I've tried both ways: > > > createlang/droplang from the command line as user postgres > > > and: > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > > HANDLER plpgsql_call_handler > > LANCOMPILER 'PL/pgSQL'; > > Hrmph. Looks perfectly standard from here; I don't see why pg_dump is > failing to find the handler. It would help to see what the server-side > view of the transaction is like. Would you run pg_dump after setting > query logging on (from memory, I think export PGOPTIONS="-d2" will work > in 7.0, but too tired to check it) and then show us the tail end of the > postmaster log after pg_dump fails? > > regards, tom lane > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 > billion; is it possible your pg_language OID for plpgsql is over 2G? > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Brian T. Fujito Chief Technical Officer www.christianity.com www.lightsource.com www.online-giving.com [EMAIL PROTECTED] (703) 548-8900 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Nope- checked the filename. The libs match up. Thanks for your help, though--I appreciate it. Please see my reply to Tom regarding OID's... any insight on how to fix that? thank you all, brian On Tue, 2002-12-10 at 00:26, Dan Langille wrote: > On 10 Dec 2002 at 0:02, Tom Lane wrote: > > > Brian Fujito <[EMAIL PROTECTED]> writes: > > >> What exactly are you doing to drop and re-add the language? I > > >> should think CREATE LANGUAGE would fail if the handler proc isn't > > >> there. > > > > > I've tried both ways: > > > > > createlang/droplang from the command line as user postgres > > > > > and: > > > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > > > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > > > HANDLER plpgsql_call_handler > > > LANCOMPILER 'PL/pgSQL'; > > > > Hrmph. Looks perfectly standard from here; I don't see why pg_dump is > > failing to find the handler. It would help to see what the > > server-side view of the transaction is like. Would you run pg_dump > > after setting query logging on (from memory, I think export > > PGOPTIONS="-d2" will work in 7.0, but too tired to check it) and then > > show us the tail end of the postmaster log after pg_dump fails? > > > >regards, tom lane > > > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 > > billion; is it possible your pg_language OID for plpgsql is over 2G? > > Followed by another wild guess. Could the path be the problem? > Looking at my notes (http://www.freebsddiary.org/postgresql- > pgsql.php) I see that at one time I supplied a pathname : > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; > > Please let us know. > -- > Dan Langille : http://www.langille.org/ > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly -- Brian T. Fujito Chief Technical Officer www.christianity.com www.lightsource.com www.online-giving.com [EMAIL PROTECTED] (703) 548-8900 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
Brian, No data in the system is corrupted, just most of the dump and output routines in order PostgreSQL versions assume that oid == int (signed int), which is not true since few releases... or maybe never was true. Note that if you reference anything by oid, you should change your queries from SELECT whatever FROM wherever WHERE oid = value; to SELECT whatever FROM wherever WHERE oid = oid(value); This has bitten me many times already :) I guess all your dump troubles are related to oid overflow. Daniel >>>Brian Fujito said: > Thanks again for your input. I think you're right about the OID's. > > Watching the query logs, the pg_dump failed just after: > 021210.00:58:25.578 [25527] query: SELECT * FROM pg_language WHERE > lanispl ORDER BY oid > > > > MYDB=> select * from pg_language where lanispl order by oid; > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > -+-+--+---+- > plpgsql | t | t| -1983262688 | PL/pgSQL > (1 row) > > MYDB=> select oid from pg_proc where proname = 'plpgsql_call_handler'; > oid > - > -1983262688 > (1 row) > > lifeaudioV2=> select * from pg_language where lanplcallfoid = > -1983262688; > lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler > -+-+--+---+- > (0 rows) > > MYDB=> > > > So yes, it looks like the OID's got messed up. That negative number > looks suspiciously like an overflow on a max int. Is there *any* way to > fix this, or should I just deal with it and upgrade? > > BTW, does this mean that other data in the system could be corrupted? > > If an upgrade is the best way out, are there any particular versions > that would a) fix the problem and b) allow for a clean transition? > > I'm using Perl's DBI::Pg, PHP's pgsql extensions, and JDBC for > postgres... Just a bit weary of making transitions :-) I understand > I'll have to perform a pg_dump for the transition to work, which I can > do by turning off the triggers, for now. > > > Thank you, Tom, for all your help. > Brian > > > On Tue, 2002-12-10 at 00:02, Tom Lane wrote: > > Brian Fujito <[EMAIL PROTECTED]> writes: > > >> What exactly are you doing to drop and re-add the language? I should > > >> think CREATE LANGUAGE would fail if the handler proc isn't there. > > > > > I've tried both ways: > > > > > createlang/droplang from the command line as user postgres > > > > > and: > > > > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > > > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > > > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > > > HANDLER plpgsql_call_handler > > > LANCOMPILER 'PL/pgSQL'; > > > > Hrmph. Looks perfectly standard from here; I don't see why pg_dump is > > failing to find the handler. It would help to see what the server-side > > view of the transaction is like. Would you run pg_dump after setting > > query logging on (from memory, I think export PGOPTIONS="-d2" will work > > in 7.0, but too tired to check it) and then show us the tail end of the > > postmaster log after pg_dump fails? > > > >regards, tom lane > > > > PS: a wild-*ss guess: 7.0 wasn't too clean in handling OIDs above 2 > > billion; is it possible your pg_language OID for plpgsql is over 2G? > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- > Brian T. Fujito > Chief Technical Officer > www.christianity.com > www.lightsource.com > www.online-giving.com > [EMAIL PROTECTED] (703) 548-8900 > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] [BUGS] 7.3 RMPS bugs ( pgdb.py and postgresql launch script )
On Sunday 08 December 2002 11:45, Gaetano Mendola wrote: > the file pgdb.py distribuited with Postgresql 7.3 RPM ( RedHat 8.0) > is affectd by an error that not permit anymore to use the > pgdb interface for python. Then the official 7.3 tarball is also buggy. > There is also in the script for launch postgres a couple of errors: > 1) the wrong check for the version ( already known ) And fixed. > 2) when postgres is launched the script do: > echo " [ OK ] " > but should be more correctly: > echo_success Hmmm. I thought that used to work right let me check. > I attach both patch ( I added also the stop_immediate ) Many thanks. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] [BUGS] 7.3 RMPS bugs ( pgdb.py and postgresql launch script
Lamar Owen wrote: >[...] > > 2) when postgres is launched the script do: > > echo " [ OK ] " > > but should be more correctly: > > echo_success > > Hmmm. I thought that used to work right let me check. It will work the same (if the format did not change), but I can modify the echo_* script to email me so I know when a service has been started, stoped or failed to start. JLL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] "dumpProcLangs(): handler procedure for language
I have had similar troubles, related to oid overflow. I had to modify pg_dump to properly cast queries that contain oids. This is against 7.1.3 source. The patch was hacked quickly, in order to get a corrupted database reloaded, and this while I was traveling in another country... so it is far from perfect but saved my database(s). It also fixes other oid-related troubles of pg_dump. See attached file. Daniel >>>Brian Fujito said: > Thanks for your input-- > > I've tried both ways: > > createlang/droplang from the command line as user postgres > > and: > > CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' > HANDLER plpgsql_call_handler > LANCOMPILER 'PL/pgSQL'; > > I'm using pg_dump (not pg_dumpall) on the specific database on which > I created the language. > > I realize 7.0.3 is ancient (same with 7.1)... I just don't have the > time to deal with an upgrade quite yet. Soon enough :) In the mean > time, a stop-gap solution would definitely be appreciated. > > Thank you, > Brian > > > On Mon, 2002-12-09 at 14:28, Tom Lane wrote: > > Brian Fujito <[EMAIL PROTECTED]> writes: > > > I'm running postgres-7.0.3 on RH7.1 using RedHat RPMs. > > > I recently added plpgsql as a language to one of my databases, > > > and now when I try to do a pg_dump, I get: > > > > > "dumpProcLangs(): handler procedure for language plpgsql not found" > > > > > If I drop the language, pg_dump works fine, but if I add it back (and > > > even if I restart postgres), I get the same error. > > > > What exactly are you doing to drop and re-add the language? I should > > think CREATE LANGUAGE would fail if the handler proc isn't there. > > > > (Also, are you doing pg_dump or pg_dumpall? If the latter, maybe the > > failure is occurring in a different database than the one you are > > changing.) > > > > But having said that, 7.0.3 is ancient history ... you really are > > overdue for an upgrade. With my Red Hat fedora on, I'd say the same > > about your choice of OS version too. > > > >regards, tom lane > > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly *** pg_dump.c.orig Mon Apr 15 09:45:58 2002 --- pg_dump.c Tue Jun 25 00:23:53 2002 *** *** 2006,2012 finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype)); finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0); finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs)); ! finfo[i].lang = atoi(PQgetvalue(res, i, i_prolang)); finfo[i].usename = strdup(PQgetvalue(res, i, i_usename)); finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t") == 0); finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0); --- 2006,2012 finfo[i].prorettype = strdup(PQgetvalue(res, i, i_prorettype)); finfo[i].retset = (strcmp(PQgetvalue(res, i, i_proretset), "t") == 0); finfo[i].nargs = atoi(PQgetvalue(res, i, i_pronargs)); ! finfo[i].lang = atooid(PQgetvalue(res, i, i_prolang)); finfo[i].usename = strdup(PQgetvalue(res, i, i_usename)); finfo[i].iscachable = (strcmp(PQgetvalue(res, i, i_iscachable), "t") == 0); finfo[i].isstrict = (strcmp(PQgetvalue(res, i, i_isstrict), "t") == 0); *** *** 2289,2295 resetPQExpBuffer(query); appendPQExpBuffer(query, ! "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = %s ", tblinfo[i].oid); res2 = PQexec(g_conn, query->data); if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK) --- 2289,2295 resetPQExpBuffer(query); appendPQExpBuffer(query, ! "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = oid(%s) ", tblinfo[i].oid); res2 = PQexec(g_conn, query->data); if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK) *** *** 2328,2340 appendPQExpBuffer(query, " SELECT c.relname " "FROM pg_index i, pg_class c
Re: [ADMIN] PostgreSQL 7.3 Installation on SCO
It should have worked, but edit Makefile.shlib and remove that offending export from the link line. That may fix it. --- Shibashish wrote: > Dear Sir, > > I use SCO Open Server 5.0.5 on an intel box. Although I have installed and > used PostgreSQL on Linux, setting it on SCO has not been successful :$ > I have downloaded the latest version ie "Postgresql-7.3" I have also tried > installing postgresql-7.1 and postgresql-7.2.3, but never succeeded. > > I also installed "ant" package for using java. I have "tcl8.0," "tk8.0," > "itclsh3.0" and "itkwish3.0" installed in my system. > "gcc version 2.7.2.1" > "Java 2 SDK, Standard Edition, v. 1.2.1" > "GNU Make 3.80" > > My "configure" command was as following ... > ./configure --prefix=/data/pgsql --with-perl --with-tcl > --with-tclconfig=/data/tcl/lib/ --with-tkconfig=/data/tk/lib/ > --with-java --without-readline --without-zlib > > The output has been attached as file "configoutput.txt" > > Then i give the "make" command. The compiling stops on an error and > exits after some time. The output has been attached as file > "makeoutput.txt" > > I'd be thankful to you if you can help me out sort the problem. I got your > mail-ids from the net and came to know that you are working on the similar > lines. > > Also, in the file doc/FAQ_SCO, does the section "Compiling PostgreSQL 7.1 > with the UDK" apply to release 7.3 of Postgresql ? I am not using UDK. > > Waiting for a quick response from your end. kindly inform me if you have > already solved the problem, or whether any patch is available. Any > documentation or url will be highly helpful. > > Thanking You in anticipation. > > with regards > Shibashish > > [EMAIL PROTECTED] > On Yahoo Messenger : shib_leo > > Software Engineer > IIT Bombay, India. Content-Description: [ Attachment, skipping... ] Content-Description: [ Attachment, skipping... ] Content-Description: [ Attachment, skipping... ] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
