Re: [GENERAL] Data corruption zero a file - help!!
On Mon, Mar 06, 2006 at 05:17:54PM +1100, Noel Faux wrote: dd bs=8k skip=115860 count=1 if=/usr/local/postgresql/postgresql-7.4.8/data/base/37958/111685332.68 | od -x Wrong block (115860) -- you used the number from my earlier message, which was based on the bad block being 902292. After noticing that the error message said the bad block was 9022921 I corrected both the file number and the block: I suggested looking in the .6 file based on block 902292; if the real bad block is 9022921 then I think it would be block 110025 in file .68 (again, check the math yourself). Try skip=110025. You can use pg_filedump to examine the block in an easier-to-read format; since you're running 7.4 you'd need pg_filedump 3.0. http://sources.redhat.com/rhdb/utilities.html Try running this command: pg_filedump -if -R 110024 110026 /path/111685332.68 That should show the bad block (110025) and the block before and after it. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query timeout
On Fri, 2006-03-03 at 21:14, Rick Gigger wrote: I assume that running the vacuumdb command is the same as running it through psql? Well, you either run it through psql, or not :-) Seriously, I understand that any client session is affected by the statement timeout settings, doesn't matter what the statement is. But autovacuum is built in starting with 8.1, so it is not affected by statement-timeout. The contrib autovacuum from versions 8.1 is in turn a regular client, so it is affected. Cheers, Csaba. ---(end of broadcast)--- TIP 1: 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
[GENERAL] problem with overloading the coalesce function
Hi, I am trying to overload the coalesce function to accept an integer and a string. Here it is : CREATE OR REPLACE FUNCTION coalesce(a int4, b varchar) RETURNS varchar AS $BODY$ begin if (a is null ) then return b; else return cast(a as varchar(15)); end if; end $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION coalesce(a int4, b varchar) OWNER TO postgres; I have added it to pg_catalog, but still I cant't use it, I get an error on the second parameter, apparently the function gets lost at some point. Any additional steps I need to complete? Thanks in advance, Emil __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: 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] problem with overloading the coalesce function
Emil Rachovsky wrote: Hi, I am trying to overload the coalesce function to accept an integer and a string. Here it is : CREATE OR REPLACE FUNCTION coalesce(a int4, b varchar) RETURNS varchar AS ... I have added it to pg_catalog, but still I cant't use it, I get an error on the second parameter What is the error? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] problem with overloading the coalesce function
--- Richard Huxton dev@archonet.com wrote: Emil Rachovsky wrote: Hi, I am trying to overload the coalesce function to accept an integer and a string. Here it is : CREATE OR REPLACE FUNCTION coalesce(a int4, b varchar) RETURNS varchar AS ... I have added it to pg_catalog, but still I cant't use it, I get an error on the second parameter What is the error? The error is : invalid input syntax for integer That is,it expects an integer as a second parameter, since the first is an integer. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] problem with overloading the coalesce function
Emil Rachovsky wrote: --- Richard Huxton dev@archonet.com wrote: Emil Rachovsky wrote: Hi, I am trying to overload the coalesce function to accept an integer and a string. Here it is : CREATE OR REPLACE FUNCTION coalesce(a int4, b varchar) RETURNS varchar AS ... I have added it to pg_catalog, but still I cant't use it, I get an error on the second parameter What is the error? The error is : invalid input syntax for integer That is,it expects an integer as a second parameter, since the first is an integer. Hmm - looking at the source (and \df in psql) it seems the basic problem is that COALESCE() isn't a function. It has its own code in the parser and its own expression-node. So - your function never gets called because the parser sees coalesce() and doesn't build a function - it builds a coalesce-expression. It should work fine if you rename your function of course. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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] How to determine the table a query or a views columns come from?
Quoting Tom Lane [EMAIL PROTECTED]: Can this info be obtained by querying the system tables, especially in the case of views? I am using 'scripting' languages and using C will be quite awkward. I have got to find if libpq's output is exposed in PHP or Ruby. Frank Church [EMAIL PROTECTED] writes: Is there way to determine the table a query or a view's columns come from? Yeah, there's some support for that in the protocol. libpq exposes it as PQftable() and PQftablecol(). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question about the contrib rpm ?
Hello, We are working with PostGreSQL since november 2005, and ours questions are automatically those of novice persons, that's why your answer suprises us. The aim of our question about the contrib rpm was to understand why this package exists, what it is used for, and how generate it. We know that we are building our own rpm, but by doing this, we also test PostGreSQL on an IA64 platform... Thank you for your help. Regards, Agnès Alexandra chris smith a écrit : On 3/3/06, Agnes Bocchino [EMAIL PROTECTED] wrote: Hello Tom, hello List, Sorry if we haven't been clear in our first mail. We don't really understand your answer. So, we try to clarify our general question and give more details : When we go on the web site to download PostgreSQL 8.1.2, we find not only the serveur rpm but also some others rpms. and we don't kow which of them we have to install together with our rpm make from the 8.1.2 targz. We are making our rpm on Novascale Ia64 We have used the postgresql-8.1.2.tar.gz file downloaded from the PostGreSQL web site. From that file, we have re-compiled PostGreSQL for IA64 on Red Hat Enterprise Linux 4 AS, with the icc Intel compiler. We would like to 'deliver' a more complete set as possible. and we don't know if we have to package some others packages.. For the langage python,perl,tcl ...we know that if we need them we have to use the --with option when we compile. It seems also to us that it is not necessary to have the lib rpm as the necessary librairies are include in the rpm when wecompile and package it. but ..we dont' know what doing with the *contrib *rpm available on the net, should we have to deliver it with our rpm. Why this question ? When we have extract files from the archive file, we have obtained these directories : [/BUILD/postgresql-8.1.2]$ ls -ltr total 1528 -rw-r--r-- 1 postdev pgsql445 Apr 23 2004 aclocal.m4 -rw-r--r-- 1 postdev pgsql 1375 Oct 1 2004 README -rw-r--r-- 1 postdev pgsql 1412 Oct 6 2004 Makefile -rw-r--r-- 1 postdev pgsql 1192 Dec 31 2004 COPYRIGHT -rw-r--r-- 1 postdev pgsql 3435 May 1 2005 GNUmakefile.in -rwxr-xr-x 1 postdev pgsql 689752 Jan 5 05:02 configure -rw-r--r-- 1 postdev pgsql 43596 Jan 5 05:02 configure.in -rw-r--r-- 1 postdev pgsql 387774 Jan 6 05:09 HISTORY -rw-r--r-- 1 postdev pgsql 44484 Jan 6 05:09 INSTALL drwxr-xr-x 2 postdev pgsql 4096 Jan 6 05:09 config drwxr-xr-x 35 postdev pgsql 4096 Jan 6 05:09 *contrib* -rw-r--r-- 1 postdev pgsql 3435 Feb 16 12:22 GNUmakefile -rwxr-xr-x 1 postdev pgsql 56658 Feb 16 12:22 config.status drwxr-xr-x 15 postdev pgsql 4096 Feb 16 12:22 src drwxr-xr-x 7 postdev pgsql 4096 Feb 16 12:22 doc -rw-r--r-- 1 postdev pgsql 278305 Feb 16 12:22 config.log Under the contrib repertory, we have among others things *start-scripts* directory which contains the linux file which allows to launch automatically PostGreSQL each time the machine reboots. [ contrib]# ls adddepend dblink intarray mSQL-interface pgstattupletablefunc btree_gist dbmirror isbn_issn oid2name pg_trgmtips chkpassearthdistance lo oracle README tsearch2 contrib-global.mk fulltextindex ltree pgbench seguserlock cube fuzzystrmatch macpg_buffercache spivacuumlo dbase intagg Makefile pgcrypto start-scripts xml2 And when we install our rpm, we don't have a contrib directory such as this obtains after having extracted the files from the tar.gz archive. Our question is how to add in the rpm that we have generated a contrib directory, in order to have scripts like linux ? If you're building your own rpm you'll need to do it yourself - that's getting beyond what this list can help you with. Or should we used the contrib rpm available in the web site No - because it's not built for your system or with your compiler, it may work but you may also run into strange bugs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Question about the contrib rpm ?
On Mon, Mar 06, 2006 at 02:17:16PM +0100, DANTE ALEXANDRA wrote: Hello, We are working with PostGreSQL since november 2005, and ours questions are automatically those of novice persons, that's why your answer suprises us. The aim of our question about the contrib rpm was to understand why this package exists, what it is used for, and how generate it. We know that we are building our own rpm, but by doing this, we also test PostGreSQL on an IA64 platform... Sure. The contrib rpm contains everything in the contrib directory. Look in there if you want to see what it is. They have README files. The reason nobody can help you here is because not a lot of people here know anything about rpms at all and thus don't know how to answer your question. If you compile the stuff in contrib and include it in your main package, fine. It's just seperated out because not everybody needs it. It's not part of the core postgresql. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [GENERAL] Question about the contrib rpm ?
Martijn van Oosterhout a écrit : On Mon, Mar 06, 2006 at 02:17:16PM +0100, DANTE ALEXANDRA wrote: Hello, We are working with PostGreSQL since november 2005, and ours questions are automatically those of novice persons, that's why your answer suprises us. The aim of our question about the contrib rpm was to understand why this package exists, what it is used for, and how generate it. We know that we are building our own rpm, but by doing this, we also test PostGreSQL on an IA64 platform... Sure. The contrib rpm contains everything in the contrib directory. Look in there if you want to see what it is. They have README files. The reason nobody can help you here is because not a lot of people here know anything about rpms at all and thus don't know how to answer your question. If you compile the stuff in contrib and include it in your main package, fine. It's just seperated out because not everybody needs it. It's not part of the core postgresql. Have a nice day, Thank you for your answer. We will try to generate a contrib rpm from the contrib directory on an IA64 platform with the Intel compiler icc. Regards, Agnès Alexandra ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Syntax error, but where?
Hey guys, I'm having a slight problem with this database that I'm trying to setup on PostgreSQL 8.1.3... What I've got is a stored procedure that refuses to get itself into the system, and I'm not sure why. It is throwing a syntax error on DECLARE, but I don't see it. I looked at the documentation, and as far as I can tell, my CREATE FUNCTION line looks just as it should in structure, as does the CREATE TYPE line that is immediately before it. Any ideas? The code that is failing is: CREATE TYPE app_global.city_list AS (zip_code CHAR(5), city_name VARCHAR(40), state_abbr CHAR(2), distance_miles NUMERIC(6,3)); -- -- Stored Procedures in app_global -- CREATE FUNCTION app_global.get_zip_codes_range(zip_code CHAR(5), range_miles NUMERIC(4,1)) RETURNS SETOF city_list AS $$FUNC_BODY$$ DECLARE generic_cursor REFCURSOR; record_returned RECORD; record_to_return city_list%rowtype; Any help would be greatly appreciated! Thanks, Mike ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ECPG and COPY
Michael Meskes wrote: Yes, it's still an open report. Sorry, about that and all the other open bug reports/feature requests. I do not have the time at the moment to even reproduce bugs. Hopefully this will become better in the near future. Should we add this to TODO? Anything else? Yes, please add it. I do have some more open bug reports/feature requests, but I'd like to reproduce things first before we add to the docs. Sometimes it's easier to fix it than to add it. :-) Added for ecpg TODO: o Add COPY TO STDIN / STDOUT handling I think we should document _every_ thing that ecpg needs because it helps existing users know what is missing, and encourages patches. Would you please supply a list? -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Syntax error in Execute statement
Hello, I created a function coalescec which behaves the same as coalesce, but uses an integer and a string. Now I'm getting an error in the folowing statements : query := 'select UID from S_Users_To_Connection where ConnID = ' || coalescec(conn_id,'null'); execute query into nUID; ERROR: syntax error at or near $2 at character 20 QUERY: SELECT $1 into $2 I have put UID in quotes because it seems to be a keyword. Here is the table script: create TEMP table S_Users_To_Connection( UID integer NOT NULL ,ConnID integer NOT NULL ,LoginID integer NOT NULL ); Any suggestions ? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Syntax error, but where?
Michael Trausch wrote: Hey guys, I'm having a slight problem with this database that I'm trying to setup on PostgreSQL 8.1.3... What I've got is a stored procedure that refuses to get itself into the system, and I'm not sure why. It is throwing a syntax error on DECLARE, but I don't see it. I looked at the documentation, and as far as I can tell, my CREATE FUNCTION line looks just as it should in structure, as does the CREATE TYPE line that is immediately before it. Any ideas? The code that is failing is: CREATE TYPE app_global.city_list AS (zip_code CHAR(5), city_name VARCHAR(40), state_abbr CHAR(2), distance_miles NUMERIC(6,3)); -- -- Stored Procedures in app_global -- CREATE FUNCTION app_global.get_zip_codes_range(zip_code CHAR(5), range_miles NUMERIC(4,1)) RETURNS SETOF city_list AS $$FUNC_BODY$$ Is this actually part of the function: $$FUNC_BODY$$ ? If so, try it as $FUNC_BODY$ (single dollar signs around identifier). ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [OFFTOPIC] Typo3 + Postgresql anyone?
Florian G. Pflug wrote: Hi My company wants to run Typo3, and I'd like it to run against postgresql instead of mysql (Don't want to have to administer a mysql database ;-). I've googled a bit now, and it seems that I need ADODB for php + some Typo3 extension. Does anyone know of a howto that explains what software I'll need for that, and where to get it? Does anyone here have experience with Typo3+Postgresql that he might want to share? Thanks for your answers, and sorry for the slightly off-topic post ;-) greetings, Florian Pflug Just for the archives, in the end I managed to install Typo3 3.8.1 including the DBAL extension, which should make it possible to use postgres instead of mysql. Installing DBAL was a pain, because it seems to be included in Typo3 4.0, and was therefore removed from the typo3 extension cvs on sourceforge. I ended up going through the changelog, and checking out a previous version (From somtime in december). Typo3 worked, but lots of extensions didn't, so in the end we'll have to use mysql :-( Maybe the upcoming 4.0 version will solve this... greetings, Florian Pflug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Using same database from XP and Linux in notebook
Here is an example I always used as a start point in this type of project. http://campbellcentral.org/rick/code/setup.html Generally, I would store the datafiles on an NTFS partition - it seems easier to reach NTFS from Linux then vice-versa. Bill, Your example mounts NTFS as read-only. Is it possible to mount NTFS as read-write ? Have you used Posgres on Linux writing to a Postgres/XP cluster residing on a NTFS file system ? Are Postgres Linux and XP database formats binary compatible ? My db cluster uses Windows specific locale setting. How Postgres on Linux interpets this setting ? Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Using same database from XP and Linux in notebook
As a heads-up, the database has some protection mechanisms that may not allow you to do this. I can't say for certain, though, so it's at least worth a shot. I'm interested to know how it turns out. On Sat, Mar 04, 2006 at 10:43:41AM -0700, William Penberthy wrote: Here is an example I always used as a start point in this type of project. http://campbellcentral.org/rick/code/setup.html Generally, I would store the datafiles on an NTFS partition - it seems easier to reach NTFS from Linux then vice-versa. I have seen this done successfully with virtually all Linux distributions. Bill Penberthy Scarpa Technology, Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrus Moor Sent: Saturday, March 04, 2006 8:52 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Using same database from XP and Linux in notebook I have notebook which can be booted into Windows XP/NTFS and into Linux. Notebook is not connected to any network. There are separate IDE partitions for both OS. I want same Postgres 8.1 database to be available in both modes. I think I must put Postgres database cluster into a NTFS partition which can be written from Postgres running on Linux or into Linux partition which can be written from Postgres running in XP. Any idea how to implement dual OS database ? Is there a ready to install Linux distro which supports this ? Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: 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 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Using same database from XP and Linux in notebook
On Mar 4, 2006, at 7:51 AM, Andrus Moor wrote: I have notebook which can be booted into Windows XP/NTFS and into Linux. Notebook is not connected to any network. There are separate IDE partitions for both OS. I want same Postgres 8.1 database to be available in both modes. I think I must put Postgres database cluster into a NTFS partition which can be written from Postgres running on Linux or into Linux partition which can be written from Postgres running in XP. Any idea how to implement dual OS database ? Is there a ready to install Linux distro which supports this ? I've done this in the past (for windows client development) by booting linux in a vmware partition and running the database there, then connecting to it from windows. It also avoids the whole rebooting issue altogether. Given a decent X server running natively on Windows (the cygwin X server is workable, if not great) it makes for a good blended development environment. Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using same database from XP and Linux in notebook
On 3/4/06, William Penberthy [EMAIL PROTECTED] wrote:
Here is an example I always used as a start point in this type of project.http://campbellcentral.org/rick/code/setup.htmlGenerally, I would store the datafiles on an NTFS partition - it seems
easier to reach NTFS from Linux then vice-versa.I have seen this done successfully with virtually all Linux distributions.Bill PenberthyScarpa Technology, Inc.Ext{2,3} driver for XP
http://www.fs-driver.org/Bogdan
Re: [GENERAL] Syntax error in Execute statement
On Mon, Mar 06, 2006 at 07:21:58AM -0800, Emil Rachovsky wrote: I created a function coalescec which behaves the same as coalesce, but uses an integer and a string. Is there a reason you can't use the standard COALESCE and cast the integer value to text/varchar? Now I'm getting an error in the folowing statements : query := 'select UID from S_Users_To_Connection where ConnID = ' || coalescec(conn_id,'null'); execute query into nUID; ERROR: syntax error at or near $2 at character 20 QUERY: SELECT $1 into $2 What version of PostgreSQL are you running? EXECUTE INTO is new in 8.1; earlier versions would print an error like the above. I have put UID in quotes because it seems to be a keyword. How did you determine that? UID isn't shown in the SQL Key Words appendix of the documentation and I don't see it in the backend or PL/pgSQL grammars. Using it unquoted works here: test= CREATE TABLE foo (uid integer); CREATE TABLE test= INSERT INTO foo (uid) VALUES (1); INSERT 0 1 test= SELECT uid FROM foo; uid - 1 (1 row) I'd guess that you created the column with an uppercase quoted identifier. See the documentation regarding case folding and quoted identifiers: http://www.postgresql.org/docs/8.1/interactive/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ERROR: xlog flush request not satisfied
I'm seeing the following error in 7.4.6. It first surfaced after remounting a SAN mount from one box to another and then running 'vacuum full': pg_dump: ERROR: xlog flush request 68/7D853080 is not satisfied --- flushed only to 0/15A90A8 CONTEXT: writing block 34 of relation 17156/1470533 pg_dump: SQL command to dump the contents of table patient_demographics failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: xlog flush request 68/7D853080 is not satisfied --- flushed only to 0/15A90A8 CONTEXT: writing block 34 of relation 17156/1470533 pg_dump: The command was: COPY public.customer... Here's my pg_controldata: $ pg_controldata pg_control version number:72 Catalog version number: 200310211 Database cluster state: shutting down pg_control last modified: Mon Mar 6 13:26:19 2006 Current log file ID: 0 Next log file segment:2 Latest checkpoint location: 0/151E9C8 Prior checkpoint location:0/14DD760 Latest checkpoint's REDO location:0/151E9C8 Latest checkpoint's UNDO location:0/0 Latest checkpoint's StartUpID:20 Latest checkpoint's NextXID: 2022907 Latest checkpoint's NextOID: 26923 Time of latest checkpoint:Mon Mar 6 13:05:11 2006 Database block size: 8192 Blocks per segment of large relation: 131072 Maximum length of identifiers:64 Maximum number of function arguments: 32 Date/time type storage: floating-point numbers Maximum length of locale name:128 LC_COLLATE: C LC_CTYPE: C Here's my pg_xlog directory: -rw--- 1 cudba cu16777216 Mar 6 08:25 0068007F -rw--- 1 cudba cu16777216 Mar 6 08:51 00680080 -rw--- 1 cudba cu16777216 Mar 6 09:09 00680081 -rw--- 1 cudba cu16777216 Mar 6 09:20 00680082 -rw--- 1 cudba cu16777216 Mar 6 09:30 00680083 -rw--- 1 cudba cu16777216 Mar 6 09:45 00680084 -rw--- 1 cudba cu16777216 Mar 6 10:00 00680085 -rw--- 1 cudba cu16777216 Mar 6 10:10 0068007E -rw--- 1 cudba cu16777216 Mar 6 13:21 0001 I have pg_filedump built. Any clues on how to tweak this file to restore full access while minimizing data loss? Thanks, Ed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] About when we should setup index?
Hello, I have some questions about index. For example, we have a table test(id varchar(7) not null primary key, name varchar(15) , sex varchar(1) , birth_date date, valid_in_table boolean not null, time_stamp timestamp not null default now() ); . id is the primary key, so a default unique index is generated automatically ? . if queries based on name are often, index should be setup for name? if there are 30,000 records, 29,000 records' names are different, will the index for name still be useful? . possible values for sex are F/M and null, should we setup index for sex? . How about index for date and timestamp? Basically, I'd like to know is there a percentage of the differences among data to decide whether index will help or not? For example, among 30,000 records, for a column, its value choices are less than A% and greater than B% so that we know index will help a lot? thanks a lot! Emi ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: xlog flush request not satisfied
On Monday March 6 2006 12:47 pm, Ed L. wrote: I'm seeing the following error in 7.4.6. It first surfaced after remounting a SAN mount from one box to another and then running 'vacuum full': pg_dump: ERROR: xlog flush request 68/7D853080 is not satisfied --- flushed only to 0/15A90A8 CONTEXT: writing block 34 of relation 17156/1470533 pg_dump: SQL command to dump the contents of table patient_demographics failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: xlog flush request 68/7D853080 is not satisfied --- flushed only to 0/15A90A8 CONTEXT: writing block 34 of relation 17156/1470533 pg_dump: The command was: COPY public.customer... Typo: should have been patient_demographics as above... Any clues? Thanks, Ed ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg-admin
I am a new user for Pg-admin and i just inatlled it on Linux and i need to connect it on server but their is error that comes up to me which is: Is server running on host and accepting TCP connection on the port 5432 Thanks, mary ---(end of broadcast)--- TIP 1: 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] pg-admin
Mary Adel schrieb: I am a new user for Pg-admin and i just inatlled it on Linux and i need to connect it on server but their is error that comes up to me which is: Is server running on host and accepting TCP connection on the port 5432 Which part of that message dont you understand? Make sure your postgres server binds to a tcp-socket (see postgresql.conf) and make sure you spell host correctly. Also have a look at your pg_hba.conf since this will be your next question (identd) Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] About when we should setup index?
On Mon, Mar 06, 2006 at 03:01:15PM -0500, Emi Lu wrote: . id is the primary key, so a default unique index is generated automatically ? Yes. When you issue the CREATE TABLE statement you should see a notice like the following: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test You can see the index if you look at the table's description, such as when doing \d test in psql. . if queries based on name are often, index should be setup for name? if there are 30,000 records, 29,000 records' names are different, will the index for name still be useful? If you have 29,000 unique names out of 30,000 rows then an index should definitely speed up queries by name. . possible values for sex are F/M and null, should we setup index for sex? Probably not, although 8.1 can make better use of indexes on low-cardinality columns than previous versions could. If you're using 8.1 then try running typical queries with and without such an index to see if it makes much difference. EXPLAIN ANALYZE will show whether the index is being used. Unless you see a significant improvement in query performance then don't bother indexing this column. . How about index for date and timestamp? Probably, if you regularly query on those columns. Basically, I'd like to know is there a percentage of the differences among data to decide whether index will help or not? For example, among 30,000 records, for a column, its value choices are less than A% and greater than B% so that we know index will help a lot? There's no absolute rule; among other things physical order on disk influences the planner's decision to use an index. If a table is clustered on a particular index then the planner might use that index to fetch 80% of the table's rows, but if the data is randomly scattered then the planner might prefer a sequential scan to fetch only 3% of the rows. If you regularly query a column that has more than a handful of distinct values then queries will probably benefit from an index on that column; in 8.1 even queries against low-cardinality columns might benefit from an index. If you perform a lot of queries for values that are close together, or if queries for a certain value tend to return a lot of rows, then consider clustering the table on that column's index (and be sure to run ANALYZE afterwards to update the table's statistics). Indexes have costs, however: they take up disk space and they can slow down other operations like inserts and updates because each index has to be updated as well. Create whatever indexes you need to realize a significant improvement in query performance, but don't overdo it. -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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
[GENERAL] CRLF problem going from Linux PostgreSQL server to WinXP client via ODBC.
I have some multi-line address fields in my Linux PostgreSQL that get populated as Linux lines with CR line terminators. This works fine until a Windows client connects to it over ODBC and then barks because the end of line is not in CRLF format. Is there a simple fix for this? Is there anything I can do on the server side or in the PostgreSQL ODBC client settings? Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [pgsql-general] Daily digest v1.5986 (24 messages)
Sylvain, All of the things you want to do can be done using Veil: http://veil.projects.postgresql.org/ Be warned though, it is not simple. If you want privileges at the column level, or based on a where clause, you will have to use techniques like Veil's secured views. It's better to avoid this sort of complexity if you can. Try reading the documentation though, it may give you some ideas. __ Marc On Sat, 2006-03-04 at 23:45 -0400, [EMAIL PROTECTED] wrote: Date: 4 Mar 2006 05:08:27 -0800 From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org Subject: Questions about privileges Message-ID: [EMAIL PROTECTED] Hello, For an exercise at university, I have several SQL queries to find to manage privileges on a database. I didn't found all the queries. So, this is my problem : I have got one table named books with several columns. * The first question is : - All users can find titles, codes and prices of books (which are columns of the table books) For the moment, I did this : grant select on table books to public; But with this solution, all users can find information about all the attributes of the table books and not only about titles, codes and prices. So, I would like if a solution existed for that (to allow all users to access only at the columns titles, codes and prices). * My second question is : - Mister X can create an index on the table books For the moment, I did this : grant create on tablespace books to X; Is that good ? * My third question is : - Mister X can update the structure of the table books For that, I don't know how I can do. Someone would have an idea to do that ? * My last question is : - Mister X can update the column quantity of the table books where the column codes is equal to 2 but He can't access at others datas of the table books. For the moment, I found how to limit the update at the column quantity of the table books with the following query : grant update(quantity) on books to X; But, I don't know how to limit the update of the column quantity only where column codes is equal to 2. Someone would have an idea to do that ? Thanks to help me. Sylvain. signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Syntax error, but where?
Bricklen Anderson wrote: Is this actually part of the function: $$FUNC_BODY$$ ? If so, try it as $FUNC_BODY$ (single dollar signs around identifier). Oh, jeez. What an oversight. Thank you... I can't believe that I missed that. Sometimes, all that really is needed is a fresh pair of eyes. Thanks! - Mike ---(end of broadcast)--- TIP 1: 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
[GENERAL] is it possible to get current_user inside security definer function ?
Is there any way to get the name of the current user inside a plpgsql function that is defined with security definer? I know that this question was discused earlier, and session_user should be the answer, but user's curren_user doesn't have to be the same as session_user before function call, because of 'set role'. regardsjustweasel
[GENERAL] Sequencial scan instead of using index issue again
There seems to be many posts on this issue but I not yet found an answer to the seq scan issue. I am having an issue with a joins. I am using 8.0.3 on FC4 Query: select * from ommemberrelation where srcobj='somevalue' and dstobj in (select objectid from omfilesysentry where name='dir15_file80'); Columns srcobj, dstobj name are all indexed. I ran test adding records to ommemberrelation and omfilesysentry up to 32K in each to simulate and measured query times. The graph is O(n²) like. i.e sequencial scan The columns in the where clauses are indexed, and yes I did VACUUM ANALYZE FULL. I even tried backup restore of the entire db. No difference. Turning sequencial scan off results in a O(n log n) like graph, Explain analyze confirms sequencial scan. A majority (70ms) of the 91ms query is as a result of - Seq Scan on ommemberrelation Timing is on. QUERY PLAN -- Nested Loop IN Join (cost=486.19..101533.99 rows=33989 width=177) (actual time=5.493..90.682 rows=1 loops=1) Join Filter: (outer.dstobj = inner.objectid) - Seq Scan on ommemberrelation (cost=0.00..2394.72 rows=33989 width=177) (actual time=0.078..70.887 rows=100 loops=1) Filter: (srcobj = '3197a4e6-abf1-11da-a0f9-000fb05ab829'::text) - Materialize (cost=486.19..487.48 rows=129 width=16) (actual time=0.004..0.101 rows=26 loops=100) - Append (cost=0.00..486.06 rows=129 width=16) (actual time=0.063..1.419 rows=26 loops=1) - Index Scan using omfilesysentry_name_idx on omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.019..0.019 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omfile_name_idx on omfile omfilesysentry (cost=0.00..393.85 rows=101 width=16) (actual time=0.033..0.291 rows=26 loops=1) Index Cond: (name = 'dir15_file80'::text) - Seq Scan on omdirectory omfilesysentry (cost=0.00..24.77 rows=11 width=16) (actual time=0.831..0.831 rows=0 loops=1) Filter: (name = 'dir15_file80'::text) - Index Scan using omfilesequence_name_idx on omfilesequence omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omclipfile_name_idx on omclipfile omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omimagefile_name_idx on omimagefile omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omcollection_name_idx on omcollection omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omhomedirectory_name_idx on omhomedirectory omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Seq Scan on omrootdirectory omfilesysentry (cost=0.00..1.05 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1) Filter: (name = 'dir15_file80'::text) - Index Scan using omwarehousedirectory_name_idx on omwarehousedirectory omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.007..0.007 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) - Index Scan using omtask_name_idx on omtask omfilesysentry (cost=0.00..8.30 rows=2 width=16) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: (name = 'dir15_file80'::text) Total runtime: 91.019 ms (29 rows) So why is the planner not using the index? Everything I have read indicates sequencial scanning should be left on and the planner should do the right thing. This is a quote from 1 web site: These options are pretty much only for use in query testing; frequently one sets enable_seqscan = false in order to determine if the planner is unnecessarily discarding an index, for example. However, it would require very unusual circumstances to change any of them to false in the .conf file. So how do I determine why the planner is unnecessarily discarding the index? Thanks ---(end of broadcast)--- TIP 1: if posting/reading through
[GENERAL] Is the ACCESS EXCLUSIVE lock for TRUNCATE really necessary?
Hi I know that TRUNCATE needs to acquire an ACCESS EXCLUSIVE lock, because it will remove the datafile on commit, and needs to ensure that noone will be using it anymore by then. For a lot of applications (at least mine) this is imposes problems. I'd like to use TRUNCATE in a few places, not only to clean out the table, but also to prevent others from inserting while I'm deleting everything (Think a cache table - if I clear the cache, I don't want concurrent transactions to be able to insert - but readers are not a problem, they still see the database in their old state, so seeing the cache in the old state is correct). Now, I was thinking if TRUNCATE couldn't just let relfilenode in pg_class point to a new datafile, and leave the old one in place. TRUNCATE would still need a lock that prevents any write-access to the table, but it wouldn't need to lock-out readers too. VACUUM could then remove datafiles when it purges a record from pg_class. I'm asking mainly out of curiosity - I though about this for a while now, and couldn't come up with a reason why this wouldn't be possible. greetings, Florian Pflug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Is the ACCESS EXCLUSIVE lock for TRUNCATE really
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: Now, I was thinking if TRUNCATE couldn't just let relfilenode in pg_class point to a new datafile, and leave the old one in place. Until when? How would you synchronize the switchover? Every snapshot would either contain the old, or the new version of the corresponding pg_class tuple. The ones using the old version couldn't possible be writer, only reader (TRUNCATE would still need to acquire a lock that ensures that). New transactions started after the commit of the truncate would see the new version, and use the new datafile. Read-Committed transactions started before the truncate commited would be able to read the file, and block when trying to write it until the truncate is committed. Upon commit, they'd need reread pg_class, and use the new datafile. A serializable transaction would always read the old datafile, and would generate a serialization error when trying to write to the table, I'd believe. The serializable case shows what I percieve to be the difference between delete and truncate from my point of view. Delete deletes all records visible to the deleting transaction, while truncate deletes all records. For me at least, this fits perfectly with the serialization error, because you cannot insert into a table that another transaction wants to clear completly. VACUUM could then remove datafiles when it purges a record from pg_class. And how would you prevent VACUUM from doing so incorrectly? The fact that pg_class contains a dead record is not ordinarily an indication that the relfilenode mentioned in the record is not needed anymore. But if a file is not referenced by relfilenode of any live tuple in pg_class, (live meaning not to be vacuumed) how can there be any transactions still accessing it? It wouldn't have known about the existance of the file in the first place, I'd believe Does it make more sense to you now? Greetings, Florian Pflug ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is the ACCESS EXCLUSIVE lock for TRUNCATE really necessary?
Florian G. Pflug [EMAIL PROTECTED] writes: Now, I was thinking if TRUNCATE couldn't just let relfilenode in pg_class point to a new datafile, and leave the old one in place. Until when? How would you synchronize the switchover? VACUUM could then remove datafiles when it purges a record from pg_class. And how would you prevent VACUUM from doing so incorrectly? The fact that pg_class contains a dead record is not ordinarily an indication that the relfilenode mentioned in the record is not needed anymore. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about the contrib rpm ?
DANTE ALEXANDRA wrote: Martijn van Oosterhout a écrit : On Mon, Mar 06, 2006 at 02:17:16PM +0100, DANTE ALEXANDRA wrote: Hello, We are working with PostGreSQL since november 2005, and ours questions are automatically those of novice persons, that's why your answer suprises us. The aim of our question about the contrib rpm was to understand why this package exists, what it is used for, and how generate it. We know that we are building our own rpm, but by doing this, we also test PostGreSQL on an IA64 platform... Sure. The contrib rpm contains everything in the contrib directory. Look in there if you want to see what it is. They have README files. The reason nobody can help you here is because not a lot of people here know anything about rpms at all and thus don't know how to answer your question. If you compile the stuff in contrib and include it in your main package, fine. It's just seperated out because not everybody needs it. It's not part of the core postgresql. Have a nice day, Thank you for your answer. We will try to generate a contrib rpm from the contrib directory on an IA64 platform with the Intel compiler icc. You could always take the .src.rpm and recompile it. It will create the same packages as postgresql does (so -libs, -contrib, -devel etc) but using your icc compiler. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to determine the table a query or a views columns
Frank Church wrote: Quoting Tom Lane [EMAIL PROTECTED]: Can this info be obtained by querying the system tables, especially in the case of views? I am using 'scripting' languages and using C will be quite awkward. I have got to find if libpq's output is exposed in PHP or Ruby. Frank Church [EMAIL PROTECTED] writes: Is there way to determine the table a query or a view's columns come from? Yeah, there's some support for that in the protocol. libpq exposes it as PQftable() and PQftablecol(). Do you need this? test=# \d x View public.x Column | Type | Modifiers +---+--- name | character varying(50) | View definition: SELECT questions.name FROM questions; If you need to see what query \d x is running, start psql with -E and it will show it. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problem with overloading the coalesce function
Richard Huxton dev@archonet.com writes: Hmm - looking at the source (and \df in psql) it seems the basic problem is that COALESCE() isn't a function. If it were an ordinary function, it couldn't satisfy the property of not evaluating unused arguments ... regards, tom lane ---(end of broadcast)--- TIP 1: 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] ERROR: xlog flush request not satisfied
Ed L. [EMAIL PROTECTED] writes: I'm seeing the following error in 7.4.6. It first surfaced after remounting a SAN mount from one box to another and then running 'vacuum full': pg_dump: ERROR: xlog flush request 68/7D853080 is not satisfied --- flushed only to 0/15A90A8 CONTEXT: writing block 34 of relation 17156/1470533 What this looks like to me is corrupt data in a page's LSN field (the first 8 bytes of the page). The LSN is evidently far larger than it should be. You might try dumping out the indicated page with pg_filedump (or even just dd/od) to see if anything obvious jumps out. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Is the ACCESS EXCLUSIVE lock for TRUNCATE really necessary?
Florian G. Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: Until when? How would you synchronize the switchover? Every snapshot would either contain the old, or the new version of the corresponding pg_class tuple. The ones using the old version couldn't possible be writer, only reader (TRUNCATE would still need to acquire a lock that ensures that). New transactions started after the commit of the truncate would see the new version, and use the new datafile. Wrong. *All* transactions read the system catalogs with SnapshotNow. There's been some discussion of using MVCC semantics on catalog accesses, but we are very far from being ready to do that; the implications will reach into every part of the backend. To give just one example of why this isn't necessarily a hot idea, suppose that transaction A adds a constraint to a table T and commits, and then transaction B (which started before A and hasn't previously touched T) comes along and inserts a tuple into T that violates the constraint. If B uses an old snapshot for looking up T's catalog info then it will not see the constraint that it must satisfy. Locking does not help since A released its locks on T before B would have tried to take any lock. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Data corruption zero a file - help!!
On Tue, Mar 07, 2006 at 01:41:44PM +1100, Noel Faux wrote: Here is the output from the pg_filedump; is there anything which looks suss and where would we re-zero the data, if that's the next step: [...] Block 110025 Header - Block Offset: 0x35b92000 Offsets: Lower 0 (0x) Block: Size0 Version 24Upper 2 (0x0002) LSN: logid 0 recoff 0x Special 0 (0x) Items:0 Free Space:2 Length (including item array): 24 Error: Invalid header information. : 0200 0010: 1800 af459a00.E.. Data -- Empty block - no items listed Special Section - Error: Invalid special section encountered. Error: Special section points off page. Unable to dump contents. Looks like we've successfully identified the bad block; contrast these header values and the hex dump with the good blocks and you can see at a glance that this one is different. It might be interesting to you (but probably not to us, so don't send the output) to see if the block's contents are recognizable, as though they came from some unrelated file (which might suggest an OS bug). Check your local documentation to see what od/hd/hexdump/whatever options will give you an ASCII dump and use dd to fetch the page and pipe it into that command. Try this (substitute the hd command with whatever works on your system): dd bs=8k skip=110025 count=1 if=/path/file | hd Even if you don't care about the block's current contents, you might want to redirect dd's output to a file to save a copy of the block in case you do ever want to examine it further. And it would be prudent to verify that the data shown by the above dd command matches the data in the pg_filedump output before doing anything destructive. When you're ready to zero the file, shut down the postmaster and run a command like the following (but keep reading before doing so): dd bs=8k seek=110025 conv=notrunc count=1 if=/dev/zero of=/path/file Before running that command I would strongly advise reading the dd manual page on your system to make sure the options are correct and that you understand them. I'd also suggest practicing on a test table: create a table, populate it with arbitrary data, pick a page to zero, identify the file and block, run a command like the above, and verify that the table is intact except for the missing block. Make *sure* you know what you're doing and that the above command works before running it -- if you botch it you might lose a 1G file instead of an 8K block. In one of his messages Tom Lane suggested vacuuming the table after zeroing the bad block to see if vacuum discovers any other bad blocks. During the vacuum you should see a message like this: WARNING: relation foo page 110025 is uninitialized --- fixing If you see any other errors or warnings then please post them. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] JSP pages don't work with database after postgres downgrade
I recently had to downgrade a version of postgres on a devleopment box from 7.3.2 to 7.2.2 to bring it in line with a production server. I reinstalled postgres, postgres-server and postgres-jdbc. Now when I try to run pages from a web application written in JSP using apache/tomcat they don't work. By don't work I specifically mean that the 1st page is one where the user's username and password are checked against a table in the database. This is coming back with a logon failed message even though I know that the username and password are correct. So I am assuming that the JSP pages are having trouble connecting to the database. Does anyone know how I can go about debugging this to find the root of the problem? Regards, Phill ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is it possible to get current_user inside security definer function ?
On Mon, Mar 06, 2006 at 06:27:06PM +0100, Boris Migo wrote:
Is there any way to get the name of the current user inside a
plpgsql function that is defined with security definer?
I know that this question was discused earlier, and session_user
should be the answer, but user's curren_user doesn't have to be the
same as session_user before function call, because of 'set role'.
Is this what you're after? I don't know if there's another way.
\c test user1
CREATE FUNCTION whoami(OUT curr_user text, OUT sess_user text, OUT role_user
text)
AS $$
BEGIN
curr_user := current_user;
sess_user := session_user;
role_user := current_setting('role');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
\c test user2
SELECT current_user, session_user, current_setting('role');
current_user | session_user | current_setting
--+--+-
user2| user2| none
(1 row)
SET ROLE user3;
SELECT current_user, session_user, current_setting('role');
current_user | session_user | current_setting
--+--+-
user3| user2| user3
(1 row)
SELECT * FROM whoami();
curr_user | sess_user | role_user
---+---+---
user1 | user2 | user3
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 1: 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] is it possible to get current_user inside security definer function ?
Michael Fuhr [EMAIL PROTECTED] writes:
Is this what you're after? I don't know if there's another way.
role_user := current_setting('role');
We currently define the spec's CURRENT_ROLE as equivalent to
CURRENT_USER, but I wonder if it shouldn't do what Boris is after.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] JSP pages don't work with database after postgres downgrade
Phill Edwards [EMAIL PROTECTED] writes: I recently had to downgrade a version of postgres on a devleopment box from 7.3.2 to 7.2.2 to bring it in line with a production server. If you are working for someone who is running 7.2.2 as a production server, my recommendation is to resign immediately. 7.3.2 is not a lot better, but 7.2.2? It is way past time to force-feed them a clue ... mere inability-to-login problems are *not* what I'm worried about. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] JSP pages don't work with database after postgres downgrade
Phill Edwards wrote: I recently had to downgrade a version of postgres on a devleopment box from 7.3.2 to 7.2.2 to bring it in line with a production server. I reinstalled postgres, postgres-server and postgres-jdbc. Now when I try to run pages from a web application written in JSP using apache/tomcat they don't work. By don't work I specifically mean that the 1st page is one where the user's username and password are checked against a table in the database. This is coming back with a logon failed message even though I know that the username and password are correct. So I am assuming that the JSP pages are having trouble connecting to the database. To help you, we would need to know a) if you can run a very simple jsp test to ensure that database connectivity is occuring (i.e. can you pull and display a record) and b) if you are using an cryptography (like MD5) in your password checks. Does anyone know how I can go about debugging this to find the root of the problem? You can set the debug level up on PostgreSQL. This is done by modifying the startup script. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-9974 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is it possible to get current_user inside security definer function ?
On Tue, Mar 07, 2006 at 12:58:29AM -0500, Tom Lane wrote:
Michael Fuhr [EMAIL PROTECTED] writes:
Is this what you're after? I don't know if there's another way.
role_user := current_setting('role');
We currently define the spec's CURRENT_ROLE as equivalent to
CURRENT_USER, but I wonder if it shouldn't do what Boris is after.
I wondered why there wasn't a SOME_THING that showed that. Does
the spec say anything about it? I just did a quick search and
didn't see anything but I might easily have overlooked it.
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Sequencial scan instead of using index issue again
Harry Hehl [EMAIL PROTECTED] writes: I am having an issue with a joins. I am using 8.0.3 on FC4 Query: select * from ommemberrelation where srcobj='somevalue' and dstobj in (select objectid from omfilesysentry where name='dir15_file80'); PLAN ... (29 rows) That plan doesn't correspond to the query you posted. Try sending the result of EXPLAIN ANALYZE from the actual query or else posting the actual query you're running. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] JSP pages don't work with database after postgres downgrade
To help you, we would need to know a) if you can run a very simple jsp test to ensure that database connectivity is occuring (i.e. can you pull and display a record) and b) if you are using an cryptography (like MD5) in your password checks. The user logon screen is a simple jsp script - all it does it check the username and password against some values in a table. There is no cryptography involved. Is there a simple test page somewhere for JSP/Postgres that I could use to try to track down the cause of this? Regards, Phill ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] JSP pages don't work with database after postgres downgrade
Phill Edwards wrote: To help you, we would need to know a) if you can run a very simple jsp test to ensure that database connectivity is occuring (i.e. can you pull and display a record) and b) if you are using an cryptography (like MD5) in your password checks. The user logon screen is a simple jsp script - all it does it check the username and password against some values in a table. There is no cryptography involved. Is there a simple test page somewhere for JSP/Postgres that I could use to try to track down the cause of this? Check your postgresql logs firstly. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] JSP pages don't work with database after postgres downgrade
Check your postgresql logs firstly. Nothing's showing up in the logs. I can see that postgres has successfully started in syslog when I restart it. I'm not getting anything at all being written to /var/log/postgresql even though debug_level = 4 in /var/lib/pgsql/data/postgresql.conf Does this mean that tomcat isn't even getting into the database? Regards, Phill ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
