Re: [GENERAL] "invalid page header in block 597621 of relation..."error
On 24/11/05 5:27 pm, "Adam Witney" <[EMAIL PROTECTED]> wrote: > On 24/11/05 5:28 pm, "Tom Lane" <[EMAIL PROTECTED]> wrote: > >> Adam Witney <[EMAIL PROTECTED]> writes: >>> Does this help identifying what went wrong? >> >> At this point I think there's no question that your filesystem is >> dropping blocks :-(. Might want to check for available kernel updates, >> or contemplate changing to a different filesystem. Ok I think this problem is not entirely fixed, my backups are failing now with this: pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value 245334402 pg_dump: SQL command to dump the contents of table "measured_bioassay_base" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: unexpected chunk number 5153 (expected 21) for toast value 245334402 measured_bioassay_base is always inserted at the same time as mba_data_base (the table where I had the problem before) and it has a text field which is very large.. I guess this is where the TOAST comes in. Any ideas what is going on here? Thanks again for any help Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] GRANT question
Hi,
As a superuser I created a normal user:
CREATE USER probauser NOCREATEDB NOCREATEUSER;
ALTER GROUP eden_users ADD USER probauser;
a function:
CREATE OR REPLACE FUNCTION edenproc_usesysid(text)
RETURNS int4 AS .
-- Note that the owner of this function is the superuser.
I added the EXECUTE grant for a function to the probauser:
GRANT EXECUTE ON FUNCTION edenproc_usesysid(text) TO probauser;
And then tried to run the function:
# select edenproc_usesysid('probauser');
ERROR: function edenproc_usesysid(text) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
Certainly as the superuser I can run the function... What's missing more???
Thank you,
-- Csaba Együd
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] "invalid page header in block 597621
Could it be faulty hardware? Run memtest86? Test your drives? At 10:49 AM 11/26/2005 +, Adam Witney wrote: Any ideas what is going on here? Thanks again for any help Adam ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "invalid page header in block 597621 of relation..."error
Adam Witney <[EMAIL PROTECTED]> writes: > pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value > 245334402 > measured_bioassay_base is always inserted at the same time as mba_data_base > (the table where I had the problem before) and it has a text field which is > very large.. How large is "very large" ... on the order of 10Mb? If so I'd say this is the same problem as we saw in your table --- a lot of consecutive rows have gone missing. You could examine the toast table to confirm or deny this. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] GRANT question
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes:
> # select edenproc_usesysid('probauser');
> ERROR: function edenproc_usesysid(text) does not exist
^^
Read the error message: this is not a lack-of-permissions problem.
I'd wonder whether the two users have the same schema search path...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "invalid page header in block 597621 of relation..."error
On 26/11/05 4:14 pm, "Tom Lane" <[EMAIL PROTECTED]> wrote: > Adam Witney <[EMAIL PROTECTED]> writes: >> pg_dump: ERROR: unexpected chunk number 5153 (expected 21) for toast value >> 245334402 > >> measured_bioassay_base is always inserted at the same time as mba_data_base >> (the table where I had the problem before) and it has a text field which is >> very large.. > > How large is "very large" ... on the order of 10Mb? If so I'd say this > is the same problem as we saw in your table --- a lot of consecutive > rows have gone missing. You could examine the toast table to confirm > or deny this. Yes, around 10Mb is about right. I deleted the two datasets in mba_data_base that were affected by the empty pages, I also deleted the relevant two rows in measured_bioassay_base... But maybe it didn't do the right thing with the toast table for these two rows? Where would I need to look to see what the problem is? Thanks again for the help. Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] "invalid page header in block 597621 of relation..."error
Adam Witney <[EMAIL PROTECTED]> writes: > I deleted the two datasets in mba_data_base that were affected by the empty > pages, I also deleted the relevant two rows in measured_bioassay_base... But > maybe it didn't do the right thing with the toast table for these two rows? Evidently the missing data in the toast table is associated with yet a different dataset. I'd suggest first looking into the toast table to see if you can confirm that the missing data corresponds to a swath of zeroed-out pages. If that's the case then it gives even more urgency to the need to find out what's going wrong with your filesystem (or possibly your disk drive, but my gut feel is that this is a kernel filesystem problem). The other thing you'd need to do is figure out which dataset you have to reload. A tedious way to do this is something like select sum(length(bigfield)) from maintable where dataset = 'xxx'; for various values of xxx until you see the error. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Syntax Error in COPY - worked around
Hi, it seems to be some kind of windows/pgadmin error (?), because running the dump on the linux server it was ok. I was able to work around the problem, but... Thank you Jaime. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jaime Casanova Sent: Friday, November 25, 2005 6:24 PM To: Együd Csaba Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Syntax Error in COPY On 11/25/05, Együd Csaba <[EMAIL PROTECTED]> wrote: > But the data was dumped with a windows version pg_dump. Or do know > wrong that pgadmin3 uses a boundled version of pg_dump (installed on the client)? > can you run a pg_dump (in FC4) and try with that dump? maybe it's some kind of problem with pg_dump of pgadmin3 (i don't know, just guessing :)... until someone get with a better idea, it's worth to try ;) -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] GRANT question
Hi Tom,
thank yor answer.
>Read the error message: this is not a lack-of-permissions problem.
You are right, I just meant that perhaps it needs some other settings, e.g.
set search_path or similar ... Sorry for that...
Regarding the search_path it is the same in both cases:
# show search_path;
search_path
--
$user,public
(1 row)
What else could cause this problem?
-- csaba
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 26, 2005 5:29 PM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes:
> # select edenproc_usesysid('probauser');
> ERROR: function edenproc_usesysid(text) does not exist
^^
I'd wonder whether the two users have the same schema search path...
regards, tom lane
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] GRANT question
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > Regarding the search_path it is the same in both cases: > # show search_path; > search_path > -- > $user,public > (1 row) That's not "the same" if the superuser has a self-named schema ... 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] GRANT question
On Sat, Nov 26, 2005 at 06:13:21PM +0100, Egyd Csaba wrote: > Regarding the search_path it is the same in both cases: > > # show search_path; > search_path > -- > $user,public > (1 row) For different users, the above represents *different* search paths because $user is expanded to the user name if a schema exists with that name. What does "\df edenproc_usesysid" show in psql? If it shows nothing then try wildcarding the name, like "\df *eden*". If it still shows nothing, are you sure you're connecting to the same database? What does "SELECT current_database();" show for each user? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Resend: Question about PostgreSQL, pgpool, and Postfix
Take a loot at postfix's proxymap feature - I believe it works for MySQL and Postgres, and may solve your problem. http://www.postfix.org/proxymap.8.html From the docs "To consolidate the number of open lookup tables by sharing one open table among multiple processes. For example, making mysql connections from every Postfix daemon process results in "too many connec- tions" errors. The solution: virtual_alias_maps = proxy:mysql:/etc/postfix/virtual_alias.cf The total number of connections is limited by the number of proxymap server processes." John Gregory Youngblood wrote: [I don't know if this message made it out before or not. If it did, please accept my apologies for the duplicate message. Thanks.] I'm running postfix 2.0.18 with a postgresql 8.0.3 database backend. I'm also using courier imap/pop servers connected to postgresql as well. All email users are stored in tables, with views providing lookup information to courier and postfix. It works very well, with one exception. Postfix likes to hang on to idle connections to the database, even if there are not that many postfix processes running. For example, with postfix: 18338 ?Ss 0:00 /usr/lib/postfix/master 18339 ?S 0:00 \_ pickup -l -t fifo -u 18340 ?S 0:00 \_ qmgr -l -t fifo -u 18344 ?S 0:00 \_ trivial-rewrite -n rewrite -t unix -u 18358 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18360 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18361 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18362 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18363 ?S 0:00 \_ cleanup -z -t unix -u 18370 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18371 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18372 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18373 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18386 ?S 0:00 \_ cleanup -z -t unix -u 18390 ?S 0:00 \_ cleanup -z -t unix -u 18397 ?S 0:00 \_ cleanup -z -t unix -u 18401 ?S 0:00 \_ cleanup -z -t unix -u 18402 ?S 0:00 \_ cleanup -z -t unix -u 18403 ?S 0:00 \_ cleanup -z -t unix -u 18427 ?S 0:00 \_ cleanup -z -t unix -u 18440 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18441 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 18557 ?S 0:00 \_ cleanup -z -t unix -u 18558 ?S 0:00 \_ virtual -t unix This is what postgresql looks like: 17610 pts/1S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data 17612 pts/1S 0:00 \_ postgres: writer process 17613 pts/1S 0:00 \_ postgres: stats buffer process 17614 pts/1S 0:00 | \_ postgres: stats collector process 17916 pts/1S 0:00 \_ postgres: courier netio 127.0.0.1(3037) idle 18345 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27587) idle 18346 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27588) idle 18347 pts/1S 0:00 \_ postgres: domains netio 127.0.0.1(27589) idle 18364 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27590) idle 18365 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27591) idle 18366 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27592) idle 18367 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27593) idle 18377 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27596) idle 18378 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27597) idle 18379 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27598) idle 18387 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27601) idle 18388 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27602) idle 18389 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27603) idle 18394 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27604) idle 18395 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27605) idle 18396 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27606) idle 18398 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27607) idle 18399 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27608) idle 18400 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27609) idle 18404 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27610) idle 18408 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27611) idle 18409 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27612) idle 18410 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27613) idle 18411 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27614) idle 18412 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27615) idle 18413 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27616) idle 18420 pts/1S 0:00 \_ postgres: transport netio 127.0.0.1(27618) idle 18
Re: [GENERAL] Logging prepared statements in 8.1?
Bj?rn T Johansen wrote: > I am upgrading from 7.4 to 8.1 and there seems to have been a change > in how postgresql log prepared sql statements.. Before I could see the > actual statements (i.e. after every parameters had been changed with > real values) but now I see the sql statements before the change (i.e > with $1, $2 as parameters).. > > Is there a way to see the actual statements in 8.1? Uh, not sure. Perhaps the interface wasn't using real prepared statements, but now it is. I am not aware we ever output the query with the parameters in place. 8.1 logs prepared statements while in previous releases it didn't properly. -- 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Resend: Question about PostgreSQL, pgpool, and
Of course, RTFM. :) Thanks for the pointer. I'll pursue that path and see how it goes. Thanks, Greg On Sat, 2005-11-26 at 17:41 +, John Sidney-Woollett wrote: Take a loot at postfix's proxymap feature - I believe it works for MySQL and Postgres, and may solve your problem. http://www.postfix.org/proxymap.8.html From the docs "To consolidate the number of open lookup tables by sharing one open table among multiple processes. For example, making mysql connections from every Postfix daemon process results in "too many connec- tions" errors. The solution: virtual_alias_maps = proxy:mysql:/etc/postfix/virtual_alias.cf The total number of connections is limited by the number of proxymap server processes." John Gregory Youngblood wrote: > [I don't know if this message made it out before or not. If it did, > please accept my apologies for the duplicate message. Thanks.] > > I'm running postfix 2.0.18 with a postgresql 8.0.3 database backend. I'm > also using courier imap/pop servers connected to postgresql as well. All > email users are stored in tables, with views providing lookup > information to courier and postfix. It works very well, with one > exception. > > Postfix likes to hang on to idle connections to the database, even if > there are not that many postfix processes running. For example, with > postfix: > > 18338 ?Ss 0:00 /usr/lib/postfix/master > 18339 ?S 0:00 \_ pickup -l -t fifo -u > 18340 ?S 0:00 \_ qmgr -l -t fifo -u > 18344 ?S 0:00 \_ trivial-rewrite -n rewrite -t unix -u > 18358 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18360 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18361 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18362 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18363 ?S 0:00 \_ cleanup -z -t unix -u > 18370 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18371 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18372 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18373 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18386 ?S 0:00 \_ cleanup -z -t unix -u > 18390 ?S 0:00 \_ cleanup -z -t unix -u > 18397 ?S 0:00 \_ cleanup -z -t unix -u > 18401 ?S 0:00 \_ cleanup -z -t unix -u > 18402 ?S 0:00 \_ cleanup -z -t unix -u > 18403 ?S 0:00 \_ cleanup -z -t unix -u > 18427 ?S 0:00 \_ cleanup -z -t unix -u > 18440 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18441 ?S 0:00 \_ smtpd -n smtp -t inet -u -s 2 > 18557 ?S 0:00 \_ cleanup -z -t unix -u > 18558 ?S 0:00 \_ virtual -t unix > > This is what postgresql looks like: > 17610 pts/1S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data > 17612 pts/1S 0:00 \_ postgres: writer process > 17613 pts/1S 0:00 \_ postgres: stats buffer process > 17614 pts/1S 0:00 | \_ postgres: stats collector process > 17916 pts/1S 0:00 \_ postgres: courier netio 127.0.0.1(3037) > idle > 18345 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27587) idle > 18346 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27588) > idle > 18347 pts/1S 0:00 \_ postgres: domains netio 127.0.0.1(27589) > idle > 18364 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27590) idle > 18365 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27591) > idle > 18366 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27592) > idle > 18367 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27593) idle > 18377 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27596) idle > 18378 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27597) > idle > 18379 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27598) > idle > 18387 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27601) idle > 18388 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27602) > idle > 18389 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27603) > idle > 18394 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27604) idle > 18395 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27605) > idle > 18396 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27606) > idle > 18398 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27607) idle > 18399 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27608) > idle > 18400 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27609) > idle > 18404 pts/1S 0:00 \_ postgres: mailbox netio 127.0.0.1(27610) > idle > 18408 pts/1S 0:00 \_ postgres: transport netio > 127.0.0.1(27611) idle > 18409 pts/1S 0:00 \_ postgres: virtual netio 127.0.0.1(27612) > idle > 18410 pts/1S 0:00 \_ postgres:
Re: [GENERAL] Postgres as embedded db for GUI
on 11/24/05 9:55 AM, [EMAIL PROTECTED] purportedly said: > Hi Jaime. I currently work with psycopg with web apps. My question is > more a matter of whether postgres can be installed in a way that keeps > it contained to the program files for the app (as opposed to the > regular type of install where postgres becomes available as a server > available anywhere on the operating system. I don't want the > installation used for tables other than app tables (or available to > anything other than the app). Although the short answer is that Postgres is not designed to be embedded, you can emulate this condition with a custom install that does not include the client apps and keeping all authentication private to the app (especially keeping app authentication separate from db authentication). The the only issue you have to watch out for is multiple installations, which can be avoided by using a specific IP address (or domain socket if Win supports them). So essentially, you're keeping people out simply because they can't authenticate, which, by and large, is the only way that most, if not all, "embeddable" systems keep foreign apps out. Best, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] GRANT question
Tom, I see your point and you are right.
Michael, it seems if no functions were in my database...
When I created the moson.gdb database I didn't create any additional
schemas. I simply reloaded the dump - I think into the public. Thats all.
In PgAdmin the (all my) functions can be seen in the public schema (logging
in as any user). I don't really understand. What do I do wrong?
[EMAIL PROTECTED]:~> psql -U eden_admin moson.gdb
Password:
moson.gdb=# select edenproc_usesysid('probauser'::text) as usesyid;
usesyid
-
118
(1 row)
moson.gdb=# show search_path;
search_path
--
$user,public
(1 row)
moson.gdb=# \df edenproc_usesysid
List of functions
Schema | Name| Result data type | Argument data types
+---+--+-
public | edenproc_usesysid | integer | text
(1 row)
moson.gdb=# SELECT current_database();
current_database
--
moson.gdb
(1 row)
moson.gdb=# \q
[EMAIL PROTECTED]:~> psql -U probauser moson.gdb
moson.gdb=> select edenproc_usesysid('probauser'::text) as usesyid;
ERROR: function edenproc_usesysid(text) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
moson.gdb=> \df edenproc_usesysid
List of functions
Schema | Name | Result data type | Argument data types
+--+--+-
(0 rows)
moson.gdb=> \df eden*
List of functions
Schema | Name | Result data type | Argument data types
+--+--+-
(0 rows)
moson.gdb=> SELECT current_database();
current_database
--
moson.gdb
(1 row)
-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 26, 2005 6:28 PM
To: Együd Csaba
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question
On Sat, Nov 26, 2005 at 06:13:21PM +0100, Egyd Csaba wrote:
> Regarding the search_path it is the same in both cases:
>
> # show search_path;
> search_path
> --
> $user,public
> (1 row)
For different users, the above represents *different* search paths because
$user is expanded to the user name if a schema exists with that name.
What does "\df edenproc_usesysid" show in psql? If it shows nothing then
try wildcarding the name, like "\df *eden*". If it still shows nothing, are
you sure you're connecting to the same database?
What does "SELECT current_database();" show for each user?
--
Michael Fuhr
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GRANT question
On Sat, Nov 26, 2005 at 09:10:40PM +0100, Egyd Csaba wrote: > moson.gdb=> \df eden* >List of functions > Schema | Name | Result data type | Argument data types > +--+--+- > (0 rows) Oops, I forgot that \df uses pg_function_is_visible(), so it won't show functions that aren't in your search path. Try this: SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE '%eden%'; or SELECT n.nspname, p.proname, oidvectortypes(p.proargtypes) FROM pg_proc AS p LEFT OUTER JOIN pg_namespace AS n ON n.oid = p.pronamespace WHERE p.proname ILIKE '%eden%'; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
FW: [GENERAL] GRANT question
-Original Message- From: Együd Csaba (Freemail) [mailto:[EMAIL PROTECTED] Sent: Saturday, November 26, 2005 9:36 PM To: 'Michael Fuhr' Subject: RE: [GENERAL] GRANT question OK, here you are. --- moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE '%eden%'; oid public.triggerfunc_eden_anamnesis_after_insert() public.triggerfunc_eden_anamnesis_before_update() public.triggerfunc_eden_cal_att_after_update() public.triggerfunc_eden_common_before_update() public.triggerfunc_eden_countries_before_delete() public.triggerfunc_eden_countries_before_update() public.triggerfunc_eden_insurers_before_delete() public.triggerfunc_eden_insurers_before_update() public.triggerfunc_eden_partners_before_delete() public.triggerfunc_eden_partners_before_update() public.triggerfunc_eden_patientes_after_insert() public.triggerfunc_eden_patientes_before_delete() public.triggerfunc_eden_patientes_before_insert() public.triggerfunc_eden_patientes_before_update() public.triggerfunc_eden_positions_before_delete() public.triggerfunc_eden_positions_before_update() public.triggerfunc_eden_treatments_before_update() public.triggerfunc_eden_user_before_delete() public.triggerfunc_eden_user_before_update() public.triggerfunc_eden_worksheets_before_insert() public.triggerfunc_eden_worksheets_before_update() public.triggerfunc_eden_xrays_before_insert() public.triggerfunc_eden_xrays_before_update() public.edenproc_revoke_grants(integer) public.edenproc_admin(integer) public.edenproc_admin(text) public.edenproc_admin_only(integer) public.edenproc_admin_only(text) public.edenproc_permission_matrix() public.edenproc_revoke_grants(text) public.edenproc_set_grants(integer) public.edenproc_set_grants(text) public.edenproc_set_all_grants() public.edenproc_superuser(integer) public.edenproc_superuser(text) public.edenproc_usename(integer) public.edenproc_user_enabled(integer) public.edenproc_user_enabled(text) public.edenproc_usesysid(text) (39 rows) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Saturday, November 26, 2005 9:23 PM To: Együd Csaba Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: Re: [GENERAL] GRANT question On Sat, Nov 26, 2005 at 09:10:40PM +0100, Egyd Csaba wrote: > moson.gdb=> \df eden* >List of functions > Schema | Name | Result data type | Argument data types > +--+--+- > (0 rows) Oops, I forgot that \df uses pg_function_is_visible(), so it won't show functions that aren't in your search path. Try this: SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE '%eden%'; or SELECT n.nspname, p.proname, oidvectortypes(p.proargtypes) FROM pg_proc AS p LEFT OUTER JOIN pg_namespace AS n ON n.oid = p.pronamespace WHERE p.proname ILIKE '%eden%'; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25. -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25. ---(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: FW: [GENERAL] GRANT question
On Sat, Nov 26, 2005 at 09:37:00PM +0100, Egyd Csaba (Freemail) wrote:
> moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE
> '%eden%';
[...]
> public.edenproc_usesysid(text)
Hmmm...so the function indeed exists in the public schema. What
happens if you call the schema-qualified function? What are the
permissions on the schema itself?
SELECT public.edenproc_usesysid('probauser');
SELECT * FROM pg_namespace WHERE nspname = 'public';
If the first query fails with "permission denied for schema public"
then you probably need to grant at least USAGE on the public schema
to certain users. That would imply that somebody has changed the
default permissions on public, either in this database or in the
template datbase from which it was created (template1 by default).
--
Michael Fuhr
---(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] GRANT question
Michael Fuhr <[EMAIL PROTECTED]> writes: > Oops, I forgot that \df uses pg_function_is_visible(), so it won't > show functions that aren't in your search path. Try this: Or try \df *.edenproc_usesysid I note the lack of "show search_path" for the non-admin user in your example ... it might also be interesting to try select current_schemas(true); regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] GRANT question
Hi Tom,
in case of the admin it results: {pg_catalog,public}. In case of the
non-admin user: {pg_catalog}!!!
Again, seeing it from the PgAdmin (as both users) I can see all the 5 well
known schemas.
-- csaba
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 27, 2005 12:07 AM
To: Michael Fuhr
Cc: Együd Csaba; pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question
Michael Fuhr <[EMAIL PROTECTED]> writes:
> Oops, I forgot that \df uses pg_function_is_visible(), so it won't
> show functions that aren't in your search path. Try this:
Or try
\df *.edenproc_usesysid
I note the lack of "show search_path" for the non-admin user in your example
... it might also be interesting to try
select current_schemas(true);
regards, tom lane
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
---(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] GRANT question
=?iso-8859-2?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes:
>> ... it might also be interesting to try
>> select current_schemas(true);
> in case of the admin it results: {pg_catalog,public}. In case of the
> non-admin user: {pg_catalog}!!!
I think that confirms Michael's suggestion that you've removed public
USAGE access on the public schema. Not a very good move if you want
to share objects across users.
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: FW: [GENERAL] GRANT question
Dear Michael, Tom,
thak you very much! Now it works well.
moson.gdb=> SELECT public.edenproc_usesysid('probauser');
ERROR: permission denied for schema public
moson.gdb=> select current_schemas(true);
current_schemas
-
{pg_catalog}
(1 row)
moson.gdb=# GRANT USAGE ON SCHEMA PUBLIC TO probauser;
GRANT
moson.gdb=# select current_schemas(true);
current_schemas
-
{pg_catalog,public}
(1 row)
moson.gdb=# SELECT edenproc_usesysid('probauser');
edenproc_usesysid
---
118
(1 row)
Best Regards,
-- Csaba Együd
-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 26, 2005 10:16 PM
To: Együd Csaba (Freemail)
Cc: pgsql-general@postgresql.org
Subject: Re: FW: [GENERAL] GRANT question
On Sat, Nov 26, 2005 at 09:37:00PM +0100, Egyd Csaba (Freemail) wrote:
> moson.gdb=> SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE
> '%eden%';
[...]
> public.edenproc_usesysid(text)
Hmmm...so the function indeed exists in the public schema. What happens if
you call the schema-qualified function? What are the permissions on the
schema itself?
SELECT public.edenproc_usesysid('probauser');
SELECT * FROM pg_namespace WHERE nspname = 'public';
If the first query fails with "permission denied for schema public"
then you probably need to grant at least USAGE on the public schema to
certain users. That would imply that somebody has changed the default
permissions on public, either in this database or in the template datbase
from which it was created (template1 by default).
--
Michael Fuhr
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
