Re: [GENERAL] pg/plsql question
that worked :) thanks for your input fred On Tue, 2005-03-15 at 18:00 +, Ragnar Hafstað wrote: On Tue, 2005-03-15 at 18:18 +0100, Fred Blaise wrote: While I have accomplished what I needed with the pgedit script given by John, I am still curious as to why mine is not working... Here is the latest version: /* */ create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_t varchar; begin v_user := ''user''; v_schema := ''public''; FOR v_t in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', t; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; Please note that all ticks above are single ticks. Here is what I do to execute it: excilan=# \i grant.sql CREATE FUNCTION excilan=# select fred_on_all(); ERROR: missing .. at end of SQL expression CONTEXT: compile of PL/pgSQL function fred_on_all near line 8 taken from http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING quote Note: The PL/pgSQL parser presently distinguishes the two kinds of FOR loops (integer or query result) by checking whether the target variable mentioned just after FOR has been declared as a record or row variable. If not, it's presumed to be an integer FOR loop. This can cause rather nonintuitive error messages when the true problem is, say, that one has misspelled the variable name after the FOR. Typically the complaint will be something like missing .. at end of SQL expression. /quote try (untested): create or replace function fred_on_all() RETURNS integer AS ' declare v_schema varchar; v_user varchar; v_rec RECORD; begin v_user := ''user''; v_schema := ''public''; FOR v_rec in select tablename from pg_catalog.pg_tables where schemaname = v_schema LOOP raise notice ''v_t is %'', v_REC.tablename; END LOOP; return 1; end; ' LANGUAGE 'plpgsql'; gnari signature.asc Description: This is a digitally signed message part
[GENERAL] 2 questions about types
1. i have a few funcions that depend on a type. i don't want to have to srop every function just so I can drop the type and recreat everything. Is there a better way to do this in Postgres? 2. The reason I had to create my own type was because record didn't ork for me when I was selecting data across multiple tables. I thought it should be dynamic but it only seems to work if i select all data in one table. I need 2-3 columns from multiple tables. Is there a better way to do this in Postgres? I am using Suse with Postgres 7.4.2 but am considering an upgrade to 8.0 Thank you, Jason Tesser ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Installation on XP - Permissions
On Mar 15, 2005, at 6:14 PM, Glenn Sullivan wrote: When running the installer on XP, I get to the Service Configuration panel. When I enter the password and click OK, I get: Failed to open local computer policy. Unable to determine user account rights(5). If I click OK to that, the install continues until it fails complaining about needing access to C:\WINDOWS\system32 to write libpq.dll . The first problem may be causing the second one. Anyone have an idea why It fails to open local computer policy? I have not seen this error, but I have only used an administrative account to install (I don't recall reading that this is a requirement, but I suspect it is since things are installed in the Windows directory). Can you manually configure services and user accounts? If not, try switching to the main administrator account for installation. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 02:50:06PM -0700, Michael Fuhr wrote: On Tue, Mar 15, 2005 at 03:41:37PM +, Ragnar Hafstað wrote: actually, perl scripts with \r\n line endings will run just fine in unix/linux. Indeed, and PL/Perl doesn't care. I just tested several PLs with PostgreSQL 8.0.1 on Solaris 9 and here are the results: PL/pgSQL CRLF ok PL/PerlCRLF ok PL/RubyCRLF ok PL/Tcl CRLF ok PL/Python CRLF fails PL/R CRLF fails Details: Thanks for the detailed test you ran. This is something I should have done before spouting off. So, really, AIUI, one of the selling points for each of the above-mentioned languages is their portability. It would appear to me that this newline incompatibility ought to be considered a major bug in python. (Maybe I'm spouting off half-cocked again, though). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] plpython function problem workaround
On Tue, Mar 15, 2005 at 03:41:37PM +, Ragnar Hafstað wrote: On Tue, 2005-03-15 at 07:33 -0600, David wrote: [about the line-termination problem in plpython] I'd like to insert one note here. While I'm not particularly familiar with either perl or python, when I encountered the referred-to thread regarding Windows/Unix newline incompatibilities in Python, I ran some tests on my Linux system. I wrote some very simple scripts in Perl and Python and tested them. They ran OK when I saved them in native Linux newlines, they ran, but if I saved them in DOS mode (vim), neither the Python nor the Perl scripts would run. actually, perl scripts with \r\n line endings will run just fine in unix/linux. Yes, I believe Michael Fuhr demonstrated that quite admirably in another reply to this message. what you might have been experiencing, is the fact that the unix shell is expecting \n lineendings, and the #! line could have been failing to run the perl executable. Yes, I'm now certain you are correct. On retesting, I caught something I didn't when I first ran them - the error message was identical for each language. The message was : bad interpreter: No such file or directory If the error had been internal to the language, I'm sure the messages from each would have been different. I guess this shows what happens when you shoot off your mouth when you don't know what you're talking about. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] 2 questions about types
Jason Tesser wrote: 1. i have a few funcions that depend on a type. i don't want to have to srop every function just so I can drop the type and recreat everything. Is there a better way to do this in Postgres? Not really - if you're redefining the type then the functions really have to be recreated. I try to keep related objects in the same file, so I can re-run them all together. 2. The reason I had to create my own type was because record didn't ork for me when I was selecting data across multiple tables. I thought it should be dynamic but it only seems to work if i select all data in one table. I need 2-3 columns from multiple tables. Is there a better way to do this in Postgres? Could you give more details of what you're trying? RECORD variables in functions should work fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] generating statistics
Hi, I have set the following variables in postgresql.conf to true: stats_start_collector,stats_command_string, stats_row_level, stats_reset_on_server_start. I am trying to get a list of all used indices and referenced tables in the database for a particular run. According to the doc. the following tables should contain this information: pg_stat_user_indexes, pg_stat_all_tables. But, these tables are not getting populated. Do I need to set something else to be able to get this information?? Regards, Vinita Bansal _ Get headhunted by 5000 tech recruiters. http://www.naukri.com/tieups/tieups.php?othersrcp=736 Post your CV on naukri.com. ---(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: [GENERAL] 2 questions about types
OK here is an example of a function where I had to create a type called login. How could I have written this function without having to create a type. CREATE OR REPLACE FUNCTION public.loginbyindidget (integer) RETURNS SETOF public.login AS' declare iindid alias for $1; returnRec RECORD; begin for returnRec in select tblindividual.indid, tblindividual.title, tblindividual.firstname, tblindividual.middlename, tblindividual.lastname, tblindividual.suffix, tblloginname.loginname, tblloginname.loginnameid, tblloginname.ad,tblloginname.current, tblloginname.email, tblloginname.note from tblindividual inner join tblloginname on (tblindividual.indid = tblloginname.indlink) where tblloginname.indlink = iindid order by tblindividual.lastname, tblindividual.firstname, tblindividual.middlename, tblloginname.loginname loop return next returnRec; end loop; return; end; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; On Wed, 2005-03-16 at 13:51 +, Richard Huxton wrote: Jason Tesser wrote: 1. i have a few funcions that depend on a type. i don't want to have to srop every function just so I can drop the type and recreat everything. Is there a better way to do this in Postgres? Not really - if you're redefining the type then the functions really have to be recreated. I try to keep related objects in the same file, so I can re-run them all together. 2. The reason I had to create my own type was because record didn't ork for me when I was selecting data across multiple tables. I thought it should be dynamic but it only seems to work if i select all data in one table. I need 2-3 columns from multiple tables. Is there a better way to do this in Postgres? Could you give more details of what you're trying? RECORD variables in functions should work fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] plPerl subroutine
Title: RE: [GENERAL] Convert Cursor to array
hi
is it
possible to create subroutines with plPerl ?
i
tried this :
CREATE
OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId int4, evo
int4, fileName text, fileRelativeDir text) RETURNS int4
AS$BODY$my $theClob='';my $params = 'select
adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;$params
= $params.')';
$theClob = spi_exec_query($params);
elog NOTICE, $theClob;
return 4;$BODY$ LANGUAGE 'plperl'
VOLATILE;
CREATE
OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4, evo int4, clob
text, indx int4) RETURNS text AS$BODY$my $t_clob =
$_[3].'totototototototototot';return
$t_clob;$BODY$ LANGUAGE 'plperl' VOLATILE;
but
the CREATE_XML_FILE doesn't display 'totototototototototot' but
HASH(0xf03fa4)
is it
possible with this solution or does i need to create a module (including
makefile, .pm, ...) ?
if i must create a module, can you explain
how to do ?
thanks
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Joshua D. Drake [EMAIL PROTECTED] writes: Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? It entirely depends on the database but not that the 32bit limit of 4GB is per CPU. So if you have 4 CPUs you can have 16GB of ram. It's actually per-process, not per-CPU. The x86 ISA only has 32-bit address registers, so a process can only see 4GB max. The PAE extensions that came in with the PPro allow for more address bits in the page tables, so each process sees a different subset of a larger pool of physical RAM. The implication of this for PostgreSQL on x86 is that each backend has a maximum of 4GB (actually, usually more like 3 to allow for kernel address space) that must include shared buffers, server code and data, and memory used for sorting etc. On 64-bit platforms, the 4GB address space limitation disappears, and a single backend could use 20GB for a sort, if the memory was available and the administrator allowed it. However, you should be running Opterons anyway. Yup. :) -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] New user: Windows, Postgresql, Python
On Wed, Mar 16, 2005 at 01:46:23PM +0100, Marco Colombo wrote: It seems python documentation is plain wrong, or I'm not able to read it at all: http://docs.python.org/ref/physical.html A physical line ends in whatever the current platform's convention is for terminating lines. On Unix, this is the ASCII LF (linefeed) character. On Windows, it is the ASCII sequence CR LF (return followed by linefeed). On Macintosh, it is the ASCII CR (return) character. Perhaps the Python documentation could use some clarification about when the platform's convention is required and when it isn't. The Embedding Python documentation shows embedded code with lines ending in \n and without saying anything about requiring the platform's convention: http://docs.python.org/ext/high-level-embedding.html This is the language _reference_ manual, btw. I'm very surprised to hear python on windows is so broken. Anyway, that makes life simpler for us. plpython programs are \n separated, no matter what platform the server runs on. That the behavior makes life simpler is an argument against it being broken (although it would be even less broken if it were more flexible about what line endings it allows). A detailed response would be getting off-topic for PostgreSQL, but I'll stand by what I said earlier: I would find it bizarre if embedded Python code had to use different line endings on different platforms. That would mean the programmer couldn't simply do this: PyRun_SimpleString(x = 1\n print x\n); Instead, the programmer would have to do a compile-time or run-time check and build the string in a platform-dependent manner. What problem would the language be solving by requiring that? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] psql file restore - problem with encoding
Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] psql file restore - problem with encoding
Just update - the system is Fedora Core 2, but the PostgreSQL is compiled from sources. Miroslav ulc wrote: Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(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: [GENERAL] plPerl subroutine
My understanding is that pl/perl functions are simply anonymous
coderefs, so they can't call each other. What is it that you REALLY
want to do? (The code looks a bit like a toy example.)
Sean
On Mar 16, 2005, at 9:17 AM, FERREIRA William (COFRAMI) wrote:
hi
is it possible to create subroutines with plPerl ?
i tried this :
CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId
int4, evo int4, fileName text, fileRelativeDir text)
RETURNS int4 AS
$BODY$
my $theClob='';
my $params = 'select
adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
$params = $params.')';
$theClob = spi_exec_query($params);
elog NOTICE, $theClob;
return 4;
$BODY$
LANGUAGE 'plperl' VOLATILE;
CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4,
evo int4, clob text, indx int4)
RETURNS text AS
$BODY$
my $t_clob = $_[3].'totototototototototot';
return $t_clob;
$BODY$
LANGUAGE 'plperl' VOLATILE;
but the CREATE_XML_FILE doesn't display 'totototototototototot' but
HASH(0xf03fa4)
is it possible with this solution or does i need to create a module
(including makefile, .pm, ...) ?
if i must create a module, can you explain how to do ?
thanks
---(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: [GENERAL] generating statistics
On Wed, Mar 16, 2005 at 02:08:49PM +, vinita bansal wrote: I have set the following variables in postgresql.conf to true: stats_start_collector,stats_command_string, stats_row_level, stats_reset_on_server_start. Did you uncomment the settings in addition to setting them to true? I am trying to get a list of all used indices and referenced tables in the database for a particular run. According to the doc. the following tables should contain this information: pg_stat_user_indexes, pg_stat_all_tables. But, these tables are not getting populated. Did you restart the database after making the configuration changes? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] New user: Windows, Postgresql, Python
On Wed, 16 Mar 2005, Michael Fuhr wrote: On Wed, Mar 16, 2005 at 01:46:23PM +0100, Marco Colombo wrote: It seems python documentation is plain wrong, or I'm not able to read it at all: http://docs.python.org/ref/physical.html A physical line ends in whatever the current platform's convention is for terminating lines. On Unix, this is the ASCII LF (linefeed) character. On Windows, it is the ASCII sequence CR LF (return followed by linefeed). On Macintosh, it is the ASCII CR (return) character. Perhaps the Python documentation could use some clarification about when the platform's convention is required and when it isn't. The Embedding Python documentation shows embedded code with lines ending in \n and without saying anything about requiring the platform's convention: http://docs.python.org/ext/high-level-embedding.html This is the language _reference_ manual, btw. I'm very surprised to hear python on windows is so broken. Anyway, that makes life simpler for us. plpython programs are \n separated, no matter what platform the server runs on. That the behavior makes life simpler is an argument against it being broken (although it would be even less broken if it were more flexible about what line endings it allows). broken == 'not conforming to the specifications or the documentation' The fact it helps us is just a side effect. A detailed response would be getting off-topic for PostgreSQL, but I'll stand by what I said earlier: I would find it bizarre if embedded Python code had to use different line endings on different platforms. That would mean the programmer couldn't simply do this: PyRun_SimpleString(x = 1\n print x\n); Instead, the programmer would have to do a compile-time or run-time check and build the string in a platform-dependent manner. What problem would the language be solving by requiring that? This one: aprogram = x = 1\nprint x\n; printf(aprogram); PyRun_SimpleString(aprogram); See? THIS program requires compile-time or run-time checks. You can't run it on Windows, or Mac: it'll write garbage to the screen (something that looks like garbage, that is). Make it more general: aprogram = get_program_from_somewhere(); PyRun_SimpleString(aprogram); write_program_to_somefile_possibly_stdout(aprogram); What if get_program_from_somewhere() reads user input? On Windows lines will be \r\n separated. Now, should this program make platform checks? Why not simply read a file (or stdin) in text mode, and pass the result to PyRun_SimpleString()? The same applies to output, of course. Now something strikes me... in his tests, Paul tried my program and the output looks identical to Linux. Now... I was expecting program1 (the one with just \n) do display badly under Windows. Am I missing something? Does C runtime support in Windows convert \n into \r\n automatically in printf()? If so, I'm on the wrong track. It may do the same with scanf() and other stdio functions. I must say I wasn't expecting my program to run just fine, with all those \n I used in it. Staring from printf( Initialized.\n); Paul can you please tell me which compiler you used under Windows to complile my program and if you used some weird compiling options? TIA. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] psql file restore - problem with encoding
John DeSoi wrote: On Mar 16, 2005, at 9:34 AM, Miroslav ulc wrote: I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: Maybe try setting the client side encoding explicitly at the top of the dump file: \encoding UNICODE It should also be possible to set this in the psql command line if you don't want to modify the file. The dump file already contains the encoding information as the first command: SET client_encoding = 'UNICODE'; so I think this is sufficient. What is strange to me is that the communication is UNICODE - UNICODE so for me it means no conversion, but the result is different. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL Miroslav begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] psql file restore - problem with encoding
On Mar 16, 2005, at 9:34 AM, Miroslav ulc wrote: I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: Maybe try setting the client side encoding explicitly at the top of the dump file: \encoding UNICODE It should also be possible to set this in the psql command line if you don't want to modify the file. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] generating statistics
vinita bansal [EMAIL PROTECTED] writes: I have set the following variables in postgresql.conf to true: stats_start_collector,stats_command_string, stats_row_level, stats_reset_on_server_start. Is the statistics collector actually starting? Look in ps auxww output to see if the postmaster has a pair of child processes that claim to be the stats collector and stats buffer processes. If not, the postmaster log should have some info about the problem. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 2 questions about types
Jason Tesser wrote:
OK here is an example of a function where I had to create a type called
login.
How could I have written this function without having to create a type.
CREATE OR REPLACE FUNCTION public.loginbyindidget (integer) RETURNS
SETOF public.login AS'
[snip]
There's an example in the manuals - chapter 7.2.1.4. Table Functions
SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
So basically, you need to supply the type definitions in your SELECT if
you aren't going to supply it in the function definition.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] plPerl subroutine
FERREIRA William (COFRAMI) [EMAIL PROTECTED] writes: but the CREATE_XML_FILE doesn't display 'totototototototototot' but HASH(0xf03fa4) I think what you get back from spi_exec_query is always going to be a hash, even if it contains only one field. So you need to pick out the field value. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] New user: Windows, Postgresql, Python
On Wed, Mar 16, 2005 at 04:17:51PM +0100, Marco Colombo wrote: aprogram = x = 1\nprint x\n; printf(aprogram); PyRun_SimpleString(aprogram); See? THIS program requires compile-time or run-time checks. You can't run it on Windows, or Mac: it'll write garbage to the screen (something that looks like garbage, that is). Are you sure about that? It's been forever since I programmed in a Microsoft environment, but as I recall, I/O streams opened in text mode do automatic translations between \n and \r\n. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccore98/HTML/_crt_fopen.2c_._wfopen.asp Also, in text mode, carriage return-linefeed combinations are translated into single linefeeds on input, and linefeed characters are translated to carriage return-linefeed combinations on output. I didn't look up Mac behavior but I'd be surprised if it didn't offer the same text mode and binary mode behaviors. It's annoying that these platforms use different line endings, but at least their implementations of standard C libraries offer a way to hide that difference from the programmer. Now something strikes me... in his tests, Paul tried my program and the output looks identical to Linux. Now... I was expecting program1 (the one with just \n) do display badly under Windows. Am I missing something? Does C runtime support in Windows convert \n into \r\n automatically in printf()? If so, I'm on the wrong track. It may do the same with scanf() and other stdio functions. I think that's exactly what happens with I/O streams in text mode. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] plPerl subroutine
In article [EMAIL PROTECTED],
FERREIRA William (COFRAMI) [EMAIL PROTECTED] writes:
CREATE OR REPLACE FUNCTION adoc.CREATE_XML_FILE(docId int4, eleId int4, evo
int4, fileName text, fileRelativeDir text)
RETURNS int4 AS
$BODY$
my $theClob='';
my $params = 'select
adoc.GET_XML_FRAG('.$_[0].','.$_[1].','.$_[2].',\''.$theClob.'\','.0;
$params = $params.')';
$theClob = spi_exec_query($params);
elog NOTICE, $theClob;
return 4;
$BODY$
LANGUAGE 'plperl' VOLATILE;
CREATE OR REPLACE FUNCTION adoc.GET_XML_FRAG(docId int4, eleId int4, evo int4,
clob text, indx int4)
RETURNS text AS
$BODY$
my $t_clob = $_[3].'totototototototototot';
return $t_clob;
$BODY$
LANGUAGE 'plperl' VOLATILE;
but the CREATE_XML_FILE doesn't display 'totototototototototot' but
HASH(0xf03fa4)
... and rightly so. As documented in The Fine Manual, spi_exec_query
returns a hash reference. Probably you want something like
my $rv = spi_exec_query($params);
$theClob = $rv-.{rows}[0]-{get_xml_frag};
---(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: [GENERAL] 2 questions about types
snip
There's an example in the manuals - chapter 7.2.1.4. Table Functions
SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
So basically, you need to supply the type definitions in your SELECT if
you aren't going to supply it in the function definition.
ok I tried to rewrite as follows but I get an error that says a column
definition list is required fro functions returning record
here is my function and call for it now
CREATE OR REPLACE FUNCTION public.loginbyindidgettest (integer)
RETURNS SETOF pg_catalog.record AS'
declare
iindid alias for $1;
returnRec RECORD;
begin
for returnRec in select t1.indid, t1.title, t1.firstname, t1.middlename,
t1.lastname, t1.suffix, t1.loginname, t1.loginnameid, t1.ad,t1.current,
t1.email, t1.note
from tblindividual inner join tblloginname on (tblindividual.indid =
tblloginname.indlink) as t1
where tblloginname.indlink = iindid
order by tblindividual.lastname, tblindividual.firstname,
tblindividual.middlename, tblloginname.loginname
loop
return next returnRec;
end loop;
return;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
select * from loginbyindidgettest(43650);
---(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: [GENERAL] 2 questions about types
Jason Tesser wrote:
snip
There's an example in the manuals - chapter 7.2.1.4. Table Functions
SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
So basically, you need to supply the type definitions in your SELECT if
you aren't going to supply it in the function definition.
ok I tried to rewrite as follows but I get an error that says a column
definition list is required fro functions returning record
Because you didn't supply the type definitions in your SELECT...
here is my function and call for it now
CREATE OR REPLACE FUNCTION public.loginbyindidgettest (integer)
RETURNS SETOF pg_catalog.record AS'
...
select * from loginbyindidgettest(43650);
This needs to be something like:
SELECT * FROM loginbyindidgettest(43650) AS myres(a int, b text, c
date, ...)
Obviously, the types need to match the results of your function.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] New user: Windows, Postgresql, Python
On Wed, 16 Mar 2005, Michael Fuhr wrote: On Wed, Mar 16, 2005 at 04:17:51PM +0100, Marco Colombo wrote: aprogram = x = 1\nprint x\n; printf(aprogram); PyRun_SimpleString(aprogram); See? THIS program requires compile-time or run-time checks. You can't run it on Windows, or Mac: it'll write garbage to the screen (something that looks like garbage, that is). Are you sure about that? It's been forever since I programmed in a Microsoft environment, but as I recall, I/O streams opened in text mode do automatic translations between \n and \r\n. No I wasn't sure and I actually was wrong. I've never programmed under Windows. I've just learned something. Apparently, as far as Python is concerned, the platform presents \n at C level, so it makes sense for PyRun_SimpleString() to expect \n as line terminator. Still I don't understand when the lexxer would use \r\n as pysical line ending on Windows, but I can live with it. :-) It seems that any client application under Windows is likely to use only \n-delimited text, as long as it uses stdio functions and text mode. Problems arise when it gets text from some other source. But since at C level text is expected to be \n-delimited, the application should take care of the conversion as soon as it receives the data. I think that if we want to be conservative, any input that is supposed to be treated (actively) as text by the server, should be \n-delimited. That includes any function source. I'm against to any on-the-fly conversion, now. I don't like the idea of PostgreSQL accepting input in one form (\r\n) and providing output in a different form (\n). Also think of a function definition with mixed \r\n and \n lines: we'd have no way to reconstruct the original input. I think we should just state that text used for function definitions is \n-delimited. Some languages may accept \r\n as well, but that's undocumented side effect, and bad practice. Now that I learned that C programs on Windows are expected to handle \n-delimited text, I can't think of any reason why an application should send \r\n-delimited text via libpq as a function definition, unless the programmer forgot to perform the standard \r\n to \n conversion somewhere. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] dataKiosk 0.6 released
Hello, I have released dataKiosk version 0.6. DataKiosk is a JuK-like database interface tool for generic SQL databases. What does that mean? Essentially, DataKiosk provides a series of wizards (anyone familiar with Qt Designer's database wizards will find them familiar) that allow you to build a custom Juk-like interface for any SQL database with a QtSQL driver. New features in this version include the ability to edit the database through a new configurable edit form with custom widgets depending upon the the fields data type. MS Access style combo boxes have been implemented for Relation combo editors. Here is a more complete change list: * Fixed saved searches so that they load after all datatables have been created. * Clear advanced searches properly and disable the searchwidget for reports. * DataTable will have two tabs, one for viewing and one for editing. * Subclassed QDataBrowser to provide an edit record form. The edit form uses a custom editorfactory to provide the right widgets for data relations. The main datatable does this too. You can now edit records, although the layout and look of the form is not very good at the moment. * DataKiosk now has flow form layout management for the editor form thanks to dfaure and Qt4! FormLayout is based on this new example by TT: http://doc.trolltech.com/4.0/layouts-flowlayout.html * Fix an annoying problem with sorting the DataFields. * Add more advanced kcombobox for relation editor widgets and get ready for more advanced editor widgets in general. * Make the formlayout draw every widget with the same width/height. The width is calculated after every selectionChange in the QDataTable. The editor with the largest content wins. * The editors in FormLayout are now drawn correctly and the scroll feature of QDataBrowser works. A green/red border is drawn around the form based on what the user is doing. Update works. * Change focus policy to try and get a handle on how the data entry can be made as fast and efficient as possible allowing to navigate with keyboard only. * Editors remember state so when they change the colorbox changes. If the editors are changed back to match the current database buffer, the colorbox goes green reflecting that nothing has changed requiring updating. * Add shortcuts for tab between the datatables. * Ok, so I've settled on the following for keyboard shortcuts Alt+Up Scrolls the listbox containing the tables... UP skipping all the reports. :):) Alt+DownSame as above, just DOWN. Alt+LeftToggles between the edit view and table view plus it keeps all of the datatables in sync. They are all toggled. Alt+Right Same as above, just RIGHT. HomeSeeks to the first record. End Seeks to the last record. PageUp Seeks to the previous record. PageDown Seeks to the next record. CTRL+s Commits the changes to the database. Everything appears to be working and these are all universal shortcuts for the app, except the Home/End shortcuts don't work when a an editor widget in the edit view has focus. * Enforce readonly and do not allow autoediting. * Make sure that the editForm syncs with the configure table wizard. Various fixes. * Imported and modified libkdepim's kdateedit and ktimeedit into datakiosk. They are some ugly parts, but they do the job and I didn't feel like rewriting these widgets. Created a DateTimeEdit part out of them and made some fixes so they now handle the Date, Time and DateTime variant editors. * Added support for nested foreign keys in the Relation Combo editor. This just about completes the work on the Relation editor. * Add a configurable RelationCombo that can do MS Access style Combobox's with a QTable dropdown widget. DataKiosk also has a new and updated homepage available at: http://extragear.kde.org/apps/datakiosk/ ...complete with screenshots of the new features. Flash demos of dataKiosk in action can be found here: http://web.mit.edu/~treat/Public/datakiosk.html and here: http://web.mit.edu/~treat/Public/datakiosk-editor.html Cheers, Adam Treat ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpython function problem workaround
[I've changed the Subject back to the thread that started this
discussion.]
On Wed, Mar 16, 2005 at 05:52:02PM +0100, Marco Colombo wrote:
I'm against to any on-the-fly conversion, now.
I don't like the idea of PostgreSQL accepting input in one form
(\r\n) and providing output in a different form (\n). Also think of
a function definition with mixed \r\n and \n lines: we'd have no way
to reconstruct the original input.
Yeah, that's a reasonable argument against modifying the function
source code before storing it in pg_proc. But I expect this problem
will come up again, and some people might not care about being able
to reconstruct the original input if it's just a matter of stripped
carriage returns, especially if the function logic doesn't use
literal carriage return characters that would be missed. For those
people, the validator hack might be an acceptable way to deal with
a client interface that inserts carriage returns that the programmer
didn't intend anyway. Not necessarily as part of the core PostgreSQL
code or even distributed with PostgreSQL, but as something they
could install if they wanted to.
I think we should just state that text used for function definitions
is \n-delimited. Some languages may accept \r\n as well, but that's
undocumented side effect, and bad practice.
Whether it's an undocumented side effect depends on the language,
and whether it's bad practice is a matter of opinion. In any case,
that's the language's concern and not something PostgreSQL should
judge or enforce. PostgreSQL shouldn't have to know or care about a
procedural language's syntax -- a function's source code should be an
opaque object that PostgreSQL stores and passes to the language's
handler without caring about its contents. Syntax enforcement should
be in the language's validator or handler according to the language's
own rules.
Speaking of code munging and syntax enforcement, have a look at this:
CREATE FUNCTION foo() RETURNS text AS $$
return line 1
line 2
line 3
$$ LANGUAGE plpythonu;
SELECT foo();
foo
--
line 1
line 2
line 3
(1 row)
Eh? Where'd those leading tabs come from? Why, they came from
PLy_procedure_munge_source() in src/pl/plpython/plpython.c:
mrc = PLy_malloc(mlen);
plen = snprintf(mrc, mlen, def %s():\n\t, name);
Assert(plen = 0 plen mlen);
sp = src;
mp = mrc + plen;
while (*sp != '\0')
{
if (*sp == '\n')
{
*mp++ = *sp++;
*mp++ = '\t';
}
else
*mp++ = *sp++;
}
*mp++ = '\n';
*mp++ = '\n';
*mp = '\0';
How about them apples? The PL/Python handler is already doing some
fixup behind the scenes (and potentially causing problems, as the
example illustrates).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] generating statistics
On Wed, Mar 16, 2005 at 06:27:43PM +, vinita bansal wrote: Did you uncomment the settings in addition to setting them to true? Did you restart the database after making the configuration changes? Yes, I did. FYI- I am using Postgres 8 Have you used SHOW to verify that the variables are indeed set? As Tom Lane suggested, if you're on a *nix system, did you use ps to see if the stats buffer process and stats collector process are running? (I'm not sure how to check that if you're on Windows.) Have you looked for errors in the postmaster logs? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] help with plpgsql function called by trigger
Hello-- I need to make sure that every time a row is inserted into a table called users rows are automatically inserted into two other tables: join_bd and behavior_demographics. The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called users_id). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Now I want to trigger this function whenever there is an insert on the users table, so I did this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(); The problem is that I need to be able to send the value of the users_id that was generated by the insert into users to insert_bd_join_bd(). The users id value is generated by a sequence. So I think I'd need something like this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(***USERS ID GENERATED BY SEQ FROM LAST USERS INSERT***); I'm new to writing plpgsql and to triggers, so I'm probably missing something, or doing this wrong, but I can't figure out from the docs how to send this value to the function. Can someone help point me to some docs that might help, or tell me why I'm on the wrong track? Thanks so much! Heather Johnson ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Data Recovery
I have a crashed database fileset, and I'm wondering if there is any way to recover the data from a specific table. I know which table got corrupted, and it's not the table I am trying to recover. I know this is a little vague, but I'm not really sure what information would be pertinent.. Any help would be greatly appreciated! Thanks very much, Alex Turner netEconomist ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data Recovery
On Wed, 16 Mar 2005 14:43:09 -0500, Alex Turner [EMAIL PROTECTED] wrote: I have a crashed database fileset, and I'm wondering if there is any way to recover the data from a specific table. I know which table got corrupted, and it's not the table I am trying to recover. I know this is a little vague, but I'm not really sure what information would be pertinent.. Crashed how exactly? If you can explain what led to the current state, we could likely assist in recovering. Which version of PostgreSQL are you running, and on which OS? I'm guessing that you don't have recent reliable backups of this data? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Data Recovery
It's postgresl 8.01 on AMD 64 Suse 9.2. The database didn't dump succesfully for several days in a row so the backup is corrupted also. The controller card crashed and we think caused data corruption. I rebooted the system the following day, and it came back up, but all was not well, pg_dump all failed that day, and the following day. Thanks, Alex Turner netEconomist On Wed, 16 Mar 2005 12:14:03 -0800, Lonni J Friedman [EMAIL PROTECTED] wrote: On Wed, 16 Mar 2005 14:43:09 -0500, Alex Turner [EMAIL PROTECTED] wrote: I have a crashed database fileset, and I'm wondering if there is any way to recover the data from a specific table. I know which table got corrupted, and it's not the table I am trying to recover. I know this is a little vague, but I'm not really sure what information would be pertinent.. Crashed how exactly? If you can explain what led to the current state, we could likely assist in recovering. Which version of PostgreSQL are you running, and on which OS? I'm guessing that you don't have recent reliable backups of this data? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Data Recovery
On Wed, 16 Mar 2005 15:46:16 -0500, Alex Turner [EMAIL PROTECTED] wrote: It's postgresl 8.01 on AMD 64 Suse 9.2. The database didn't dump succesfully for several days in a row so the backup is corrupted also. The controller card crashed and we think caused data corruption. I rebooted the system the following day, and it came back up, but all was not well, pg_dump all failed that day, and the following day. Failed how? What options are you using and what kind of output are you seeing? What makes you think you had data corruption? What kind of filesystem are you using? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data Recovery
Fsync was off - we are using XFS and the Microcontroller on the RAID card Crashed and took two filesystems offline at about 2 a.m. There were some error messages in the postgres log, something like Update failed - right part of branch is wrong (I'm guessing - I'm trying to find the exact error - but we do 10 hits/second and there are alot of logs). The pg_dumpall would get to a certain table and crap out - it would just not read anymore data. Thanks, Alex Turner netEconomist On Wed, 16 Mar 2005 12:51:21 -0800, Lonni J Friedman [EMAIL PROTECTED] wrote: On Wed, 16 Mar 2005 15:46:16 -0500, Alex Turner [EMAIL PROTECTED] wrote: It's postgresl 8.01 on AMD 64 Suse 9.2. The database didn't dump succesfully for several days in a row so the backup is corrupted also. The controller card crashed and we think caused data corruption. I rebooted the system the following day, and it came back up, but all was not well, pg_dump all failed that day, and the following day. Failed how? What options are you using and what kind of output are you seeing? What makes you think you had data corruption? What kind of filesystem are you using? -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data Recovery
On Wed, 16 Mar 2005 16:02:58 -0500, Alex Turner [EMAIL PROTECTED] wrote: Fsync was off - we are using XFS and the Microcontroller on the RAID card Crashed and took two filesystems offline at about 2 a.m. Did you run xfs_repair afterwards? There were some error messages in the postgres log, something like Update failed - right part of branch is wrong (I'm guessing - I'm trying to find the exact error - but we do 10 hits/second and there are alot of logs). Knowing/seeing those errors would be useful. The pg_dumpall would get to a certain table and crap out - it would just not read anymore data. crap out meaning what exactly? Is it hanging? Is there an error message? Seriously, you need to provide information here. Too much is better than the trickle that you've provided thus far. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data Recovery
Ok - I found the log messages:
ERROR: duplicate key violates unique constraint features_pkey
STATEMENT: insert into features
(propid,dtmodified,proptype,groupid,featid,group_desc,feat_desc)
values (449356005,'3/9/2005 12:03:59 AM',1,26,1,'Water','PublicWater')
PANIC: right sibling's left-link doesn't match
STATEMENT: insert into features
(propid,dtmodified,proptype,groupid,featid,group_desc,feat_desc)
values (449356005,'3/9/2005 12:04:00 AM',1,27,1,'Sewer','PublicSewer')
LOG: server process (PID 13129) was terminated by signal 6
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
STATEMENT: update features set dtmodified='9/16/2004 7:28:42
AM',proptype=3,group_desc='Primary Heating',feat_desc='GasHeat' where
propid=442448204 and groupid=15 and featid=2
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
STATEMENT: select count(C.agentcode) from propmain A,areacodes
B,members C,office D,type_xref E,prop_extra F where A.listprice35
and A.listprice10 and A.approx_age300 and A.approx_age0 and
A.areacode=B.areacode and A.listagent=C.agentcode and
C.officecode=D.officecode and A.type_of_prop=E.type_of_prop and
A.propid=F.propid and lower(B.group_name) in ('bucks') and
A.school_dist in ('quakertown comm','u perkiomen') and A.type_of_prop
in ('SNG')
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2005-03-12 01:36:41 EST
LOG: checkpoint record is at 2B/553CED30
LOG: redo record is at 2B/553CED30; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 118419066; next OID: 50155349
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 2B/553CED78
FATAL: the database system is starting up
FATAL: the database system is starting up
On Wed, 16 Mar 2005 16:02:58 -0500, Alex Turner [EMAIL PROTECTED] wrote:
Fsync was off - we are using XFS and the Microcontroller on the RAID
card Crashed and took two filesystems offline at about 2 a.m.
There were some error messages in the postgres log, something like
Update failed - right part of branch is wrong (I'm guessing - I'm
trying to find the exact error - but we do 10 hits/second and there
are alot of logs).
The pg_dumpall would get to a certain table and crap out - it would
just not read anymore data.
Thanks,
Alex Turner
netEconomist
On Wed, 16 Mar 2005 12:51:21 -0800, Lonni J Friedman [EMAIL PROTECTED]
wrote:
On Wed, 16 Mar 2005 15:46:16 -0500, Alex Turner [EMAIL PROTECTED] wrote:
It's postgresl 8.01 on AMD 64 Suse 9.2. The database didn't dump
succesfully for several days in a row so the backup is corrupted also.
The controller card crashed and we think caused data corruption. I
rebooted the system the following day, and it came back up, but all
was not well, pg_dump all failed that day, and the following day.
Failed how? What options are you using and what kind of output are
you seeing?
What makes you think you had data corruption? What kind of filesystem
are you using?
--
~
L. Friedman[EMAIL PROTECTED]
LlamaLand http://netllama.linux-sxs.org
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Data Recovery
This is the message I get when I try to start the database:
LOG: database system was interrupted while in recovery at 2005-03-16
16:07:58 EST
HINT: This probably means that some data is corrupted and you will
have to use the last backup for recovery.
LOG: checkpoint record is at 2B/553CED30
LOG: redo record is at 2B/553CED30; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 118419066; next OID: 50155349
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 2B/553CED78
LOG: record with zero length at 2B/62360AB8
LOG: redo done at 2B/62360A88
Thanks,
Alex Turner
netEconomist
On Wed, 16 Mar 2005 16:05:16 -0500, Alex Turner [EMAIL PROTECTED] wrote:
Ok - I found the log messages:
ERROR: duplicate key violates unique constraint features_pkey
STATEMENT: insert into features
(propid,dtmodified,proptype,groupid,featid,group_desc,feat_desc)
values (449356005,'3/9/2005 12:03:59 AM',1,26,1,'Water','PublicWater')
PANIC: right sibling's left-link doesn't match
STATEMENT: insert into features
(propid,dtmodified,proptype,groupid,featid,group_desc,feat_desc)
values (449356005,'3/9/2005 12:04:00 AM',1,27,1,'Sewer','PublicSewer')
LOG: server process (PID 13129) was terminated by signal 6
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
STATEMENT: update features set dtmodified='9/16/2004 7:28:42
AM',proptype=3,group_desc='Primary Heating',feat_desc='GasHeat' where
propid=442448204 and groupid=15 and featid=2
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
STATEMENT: select count(C.agentcode) from propmain A,areacodes
B,members C,office D,type_xref E,prop_extra F where A.listprice35
and A.listprice10 and A.approx_age300 and A.approx_age0 and
A.areacode=B.areacode and A.listagent=C.agentcode and
C.officecode=D.officecode and A.type_of_prop=E.type_of_prop and
A.propid=F.propid and lower(B.group_name) in ('bucks') and
A.school_dist in ('quakertown comm','u perkiomen') and A.type_of_prop
in ('SNG')
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted at 2005-03-12 01:36:41 EST
LOG: checkpoint record is at 2B/553CED30
LOG: redo record is at 2B/553CED30; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 118419066; next OID: 50155349
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 2B/553CED78
FATAL: the database system is starting up
FATAL: the database system is starting up
On Wed, 16 Mar 2005 16:02:58 -0500, Alex Turner [EMAIL PROTECTED] wrote:
Fsync was off - we are using XFS and the Microcontroller on the RAID
card Crashed and took two filesystems offline at about 2 a.m.
There were some error messages in the postgres log, something like
Update failed - right part of branch is wrong (I'm guessing - I'm
trying to find the exact error - but we do 10 hits/second and there
are alot of logs).
The pg_dumpall would get to a certain table and crap out - it would
just not read anymore data.
Thanks,
Alex Turner
netEconomist
On Wed, 16 Mar 2005 12:51:21 -0800, Lonni J Friedman [EMAIL PROTECTED]
wrote:
On Wed, 16 Mar 2005 15:46:16 -0500, Alex Turner [EMAIL PROTECTED] wrote:
It's postgresl 8.01 on AMD 64 Suse 9.2. The database didn't dump
succesfully for several days in a row so the backup is corrupted also.
The controller card crashed and we think caused data corruption. I
rebooted the system the following day, and it came back up, but all
was not well, pg_dump all failed that day, and the following day.
Failed how? What options are you using and what kind of output are
you seeing?
What makes you think you had data corruption? What kind of filesystem
are you using?
--
~
L. Friedman
Re: [GENERAL] Data Recovery
On Wed, 16 Mar 2005 16:05:16 -0500, Alex Turner [EMAIL PROTECTED] wrote: Ok - I found the log messages: ERROR: duplicate key violates unique constraint features_pkey STATEMENT: insert into features (propid,dtmodified,proptype,groupid,featid,group_desc,feat_desc) values (449356005,'3/9/2005 12:03:59 AM',1,26,1,'Water','PublicWater') PANIC: right sibling's left-link doesn't match See http://groups-beta.google.com/group/comp.databases.postgresql.hackers/browse_thread/thread/115e69a0e5a66bb5/ed3bf8b7de3a6cc0?q=%22right+sibling%27s+left-link+doesn%27t+match%22#ed3bf8b7de3a6cc0 In short, you need to drop and rebuild the index to address that error. But this assumes that you've already successfully run xfs_repair on the filesystem. If your FS is hosed, all the recovery in the world isn't going to help the DB. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] psql file restore - problem with encoding (solved)
We have solved the problem. Maybe someone will face the same problem so just to close this thread, the problem was in php.ini where a default output character set was set which caused the second recoding problem (just for display using php). Miroslav ulc wrote: Hi all, I have problem on one of our potentional client's linux server with data encoding. I have played with this for some time but with no success. Here is the problem: I have dump file created with pg_dump on my dev linux machine (PostgreSQL 8.0.1). When I upload the file to the client's server with psql, the data is uploaded but it seems the strings were encoded once again though file header states the data is in UNICODE and the database is in UNICODE too. The database was initialized with 'initdb --locale=cs_CZ.utf8 -D /var/lib/pgsql/data'. Here is a sample how the data look: Akzent Media spolenost s ruenm omezen and how they should look: Akzent Media spolenost s ruenm omezenm What is strange on the machine is that there are perhaps two versions of postgresql installed - version 8.0.1 and 7.4.2 so I use the full path to the 8.0.1. I think that the system is Fedora Core (but I'm not sure - unfortunatelly I cannot catch the admin now). We have no real control of the client's server so we cannot install, uninstall etc. I have also tried to dump some data from our php app - with the same result. phpinfo() says that php was configured against PostgreSQL 8.0.1 but it also says in PostgreSQL configuration info that there is PostgreSQL(libpq) Version 7.4.2. It seems to me that the problem might be in: 1) the mixture of the two versions of PostgreSQL in the system 2) some problem with locale 3) something different Can someone please help me to diagnose this problem so I can solve it and move forward? Thank you in advance. Miroslav ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] foreign key help
I am building a database that has about 20 fields in various tables that I would like to constrain to a small group (=5) of choices. Rather that refer to 20 seperate tables, I would like to refer to 1 table that contained category and choice fields. for example choices table category | choice - color| red color| green size | small size | big shape| circle shape| square item table item | shape - box | constrain to choices(choice) where category='shape' Is there an easy way to do this, or is it better to have many choices_category tables? Thanks, Dale ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] search_path, schemas and functions...
I have a large amount of data that I am managing using schemas. The previous owner of the DB wrote functions to automatically create new schemas on demand, and sets of funtions to interact with them. Rather than maintain a set of functions that are dynamically re-written with every new schema I wanted to just use the USER search path and update it with each new schema creation. The problem I am running into is I have a single process inserting data using database functions. When I create the new schema and update the search path, the inserter continues inserting into previous search path. I tried writing all of the functions making use of a dynamic string for the function contents and an EXECUTE. This worked, the inserter automatically started inserting data into the correct table. Unfortunately it caused a massive performance hit. For the moment, I have the inserter set to close its connection and re-establish it after a block of inserts. This is not really ideal either as the new schema creation happens only once a day and the batches complete in about 1-2 seconds. Is there a way to cause the functions to re-evaluate on demand to use the new search path without disconnecting? I even manually set the search path each pass, but it didn't work. (probably because the functions were already cached at that point) The only other thing I have come up with is to set a flag when I put in a new schema that would cause the inserter to disconnect, reconnect, clear the flag and continue until the flag changes back. Is there a better way to do this that I am missing? Thanks -- Andrew Lundgren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?
Hi. I'm writing backup guide and script. I will look up on-disk-filename should be backuped in a pg_class, like: SELECT oid,relfilenode,relname,relkind FROM pg_class; it return like: 17173 | 17173 | sql_packages| r 17182 | 17182 | pg_toast_17178_index| i 17180 | 17180 | pg_toast_17178 | t 376 | 0 | pg_xactlock | s I'll include backup set relkind='r' or 'i' or 'S' or 'v' or 't'. but PostgreSQL 8.0.1 Documentation 41.9. pg_class say c = composite type, s = special 1)relkind = 's' is always have not on-disk file? 2)when exist relkind='s' object except pg_xactlock? user(administrator) can create it? 3)relkind = 'c' is always have not on-disk file? (I create TYPE. but no file exist.) I will only include pg_type relation in backup set. is it OK ? 4)any other advice to backup? :-) Regards, -- Katsuhiko Okano k_okano _at_ po.ntts.co.jp NTT Software Corp. (division NBRO-PT6) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] New PostGreSQL Newsgroup - VOTE TO BEGIN
After all these months, the NAN Usenet team has lined up a vote taker for the comp.databases.postgresql new group proposal. Expect voting to begin within a few days. The PGSQL* hierarchy is now well distributed, and there is no need for a comp.* group. If anything, the useless comp.* group will confuse newbies into thinking that that is the best forum for PostGreSQL advice - instead of the PGSQL.* hierarchy. None of the developers and power users of these lists will be answering questions in the comp.* group, if created, so it would be better to not create the group at all. If created, comp.databases.postgresql will NOT be gated to any of the mailing lists. It it is in the best interest of the PostGreSQL community to VOTE AGAINST THIS PROPOSAL. Vern [EMAIL PROTECTED] (Automated UVV Post) wrote in news:[EMAIL PROTECTED]: The following proposal has been assigned to a votetaker, but the CFV has not yet appeared in news.announce.newgroups. PROPOSAL comp.databases.postgresql VOTE TAKER: UVV: Bill Aten [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)
Marc G. Fournier wrote in Msg [EMAIL PROTECTED]: it can't *hurt* to have the group ... I respectfully disagree with you, Marc. :) The PGSQL* hierarchy is now well distributed, and there is no need for a comp.* group. If anything, the ungated comp.* group will confuse newbies into thinking that that is the best forum for PostGreSQL advice ... instead of the PGSQL.* hierarchy. None of the developers and power users of these lists will be answering questions in the comp.* group, if created, so it would be better to not create the group at all. Vern ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] help with plpgsql function called by trigger
On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote:
CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
What version of PostgreSQL are you using? The opaque type has
been deprecated since 7.3; recent versions should use trigger.
And trigger functions aren't declared with arguments -- if the
function needs arguments, then use TG_ARGV. But I think your code
can use NEW instead of a function argument.
declare
r RECORD;
uid ALIAS FOR $1;
begin
INSERT INTO behavioral_demographics (users_id) VALUES (uid);
SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
end;
' LANGUAGE 'plpgsql';
Based on the rest of your description, I think you're looking for
something like this:
CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS '
DECLARE
bdid integer;
BEGIN
INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid);
bdid := currval(''behavioral_demographics_bdid_seq'');
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid);
RETURN NULL;
END;
' LANGUAGE plpgsql VOLATILE;
See the Trigger Procedures section of the PL/pgSQL chapter in the
documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and
see the Sequence Manipulation Functions section of the Functions
and Operators chapter to learn more about currval().
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [DOCS] Fast major-version upgrade (was: [GENERAL] postgresql 8.0
Jim C. Nasby wrote: Has anyone tried moving a database from one location to another on the HD? I'd like to use slony to minimize downtime, but I'd also like my data to end up in the same place it is right now when I'm done. I used a straight copy of the filesystem with running database (over the net in my case) and immediately after that, stop the db and rsync for the last changes. This took only 10 minutes (compared to 1.5h for the full filesystem copy) and I could start up the db in new location. this could work for you too. I hadn't thought about using rsync; that's a great idea! Is there somewhere this could be documented? In an FAQ maybe? Added to documentation, patch attached. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.56 diff -c -c -r2.56 backup.sgml *** doc/src/sgml/backup.sgml25 Feb 2005 04:56:01 - 2.56 --- doc/src/sgml/backup.sgml17 Mar 2005 05:01:54 - *** *** 374,379 --- 374,388 /para para +Another option is to use applicationrsync/ to perform a file +system backup. First, while the database server is running, +run applicationrsync/, then shut down the database +server and perform a second applicationrsync/, then +restart the database server. This allows a file system backup to be +performed with minimal downtime. + /para + + para Note that a file system backup will not necessarily be smaller than an SQL dump. On the contrary, it will most likely be larger. (applicationpg_dump/application does not need to dump ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Best practices: Handling Daylight-saving time
Don't use DST. Use GMT/UTC. That makes the issue go away. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://cbbrowne.com/info/slony.html Signs of a Klingon Programmer #2: You question the worthiness of my code? I should kill you where you stand! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?
Pg_xactlock is always there as a special relation. It has no footprint on disk. Transactions will keep a record in pg_xactlock at the beginning and remove the record at the end. Once any conflicting update happens, the latter transaction will use this relation to wait for the former transaction's result. AFAIK, since lock tag always needs a relationalId as a parameter, so we create this special relation for this usage. I am not sure about 'c'. Regards, Qingqing Katsuhiko Okano [EMAIL PROTECTED] writes Hi. I'm writing backup guide and script. I will look up on-disk-filename should be backuped in a pg_class, like: SELECT oid,relfilenode,relname,relkind FROM pg_class; it return like: 17173 | 17173 | sql_packages| r 17182 | 17182 | pg_toast_17178_index| i 17180 | 17180 | pg_toast_17178 | t 376 | 0 | pg_xactlock | s I'll include backup set relkind='r' or 'i' or 'S' or 'v' or 't'. but PostgreSQL 8.0.1 Documentation 41.9. pg_class say c = composite type, s = special 1)relkind = 's' is always have not on-disk file? 2)when exist relkind='s' object except pg_xactlock? user(administrator) can create it? 3)relkind = 'c' is always have not on-disk file? (I create TYPE. but no file exist.) I will only include pg_type relation in backup set. is it OK ? 4)any other advice to backup? :-) Regards, -- Katsuhiko Okano k_okano _at_ po.ntts.co.jp NTT Software Corp. (division NBRO-PT6) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] foreign key help
You may want to have a look at pg DOMAIN (also conforms to SQL standard). http://www.postgresql.org/docs/8.0/static/sql-createdomain.html Regards, Qingqing Dale Sykora [EMAIL PROTECTED] writes I am building a database that has about 20 fields in various tables that I would like to constrain to a small group (=5) of choices. Rather that refer to 20 seperate tables, I would like to refer to 1 table that contained category and choice fields. for example choices table category | choice - color| red color| green size | small size | big shape| circle shape| square item table item | shape - box | constrain to choices(choice) where category='shape' Is there an easy way to do this, or is it better to have many choices_category tables? Thanks, Dale ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?
Qingqing Zhou [EMAIL PROTECTED] writes: Pg_xactlock is always there as a special relation. pg_xactlock isn't really a relation. The way I think about it is that it's a dummy entry in pg_class that exists to reserve a relation OID for a specific purpose --- namely, we can lock transaction IDs by locking what would otherwise be a page of that relation. There was some talk recently about reorganizing the locktag design so that transaction lock tags would be clearly distinguishable from any lock associated with a relation. If we got that done, there'd be no need for the pg_xactlock entry at all. I am not sure about 'c'. 'c' entries in pg_class are for composite types. They don't have any associated disk storage either. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
