[SQL] Permissions + Views + Functions = relation not exist when not owner
HI! I got a View which calls function which uses table. It works fine when selecting from view by owner (postgres) of table, function and view. Problem is when another user is sellecting view. There is an error: ERROR: relation "XXX" does not exist CONTEXT: PL/pgSQL function "XXX" line 4 at select into variables User has rights to select from view, use schema AND function is declared as IMMUTABLE CALLED ON NULL INPUT SECURITY DEFINER It is strange it looks like problem with permission but error is not about permissions. Calling function directly also produces error. Functions and Views are in separate schema but tables are in "public". Any idea why this is happening? Using pg7.4.6 Martin Domanski ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Permissions + Views + Functions = relation not exist when not owner
"Adie" <[EMAIL PROTECTED]> writes: > I got a View which calls function which uses table. > It works fine when selecting from view by owner (postgres) of table, > function and view. > Problem is when another user is sellecting view. There is an error: > ERROR: relation "XXX" does not exist Perhaps the other user is running with a different search_path setting? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Sql select like question.
I use domain in my database Ex CREATE DOMAIN ud_id AS bpchar(3) DEFAULT ''::bpchar NOT NULL; And have table A CREATE TABLE A ( id ud_ma_nt NOT NULL, name ud_name, CONSTRAINT A_pkey PRIMARY KEY (id) ) I have a select query Select * from A where id like 'US%' In pgAdmin i have error : "ERROR: unsupported type: 136452". But if select is : Select * from A where id like 'US%'::ud_id, query work well. So can I use first query in postgre? I don't want to use second query because i don't know columns 's domain but i know it is char(xx). Thank in advance. Sorry for my English. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Duplicated records
Hi. Thanks for the article... But, I have read it and the query works very slow... My table have aprox. 180.000 records (correct) and in entire table it has aprox.360.000 records(duplicated)... I tried to execute a query to delete the duplicated records, but it worked very very slow... look: # select * from lanctos order by numos; numos | field1 | field2 | field3 |... 1 | test | T2-2 | 2 |... 1 | test | T2-2 | 2 |... 2 | Blabla | 0 | ABC|... 2 | Blabla | 0 | ABC|... 3 | Ll | Oo | Rr |... 3 | Ll | Oo | Rr |... ... The records is entire duplicated (with all fields having the same data), thinking the "numos" fields as primary key I have executed the query: # DELETE from lanctos where not oid=(select oid from lanctos as l2 where l2.numos=lanctos.numos limit 1); I have tested others querys with EXPLAIN command to examine the performance time, and this query was the best performance I got... but its is slow. Other query is: # DELETE from lanctos where not exists (select '1' from lanctos as l2 where l2.numos=lanctos.numos and not l2.oid=lanctos.oid); Is there a way to delete those duplicated records faster??? Remembering the table have aprox 360.000 records... Is better I create other table and copy those data??? How should I created??? Thanks. Quoting Andreas Kretschmer <[EMAIL PROTECTED]>: am 24.05.2005, um 17:59:31 -0300 mailte [EMAIL PROTECTED] folgendes: Hi. How can I delete the duplicated records with "DELETE FROM TABLE WHERE..." clause?? Please read http://www.gtsm.com/oscon2003/deletetid.html Its a very good article about this problem. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Duplicated records
On Wed, May 25, 2005 at 01:58:07PM -0300, [EMAIL PROTECTED] wrote: > The records is entire duplicated (with all fields having the same data), > thinking the "numos" fields as primary key I have executed the query: > > # DELETE from lanctos where not oid=(select oid from lanctos as l2 where > l2.numos=lanctos.numos limit 1); That's because you don't have an index on the Oid column. You may want to try using the ctid column instead; it's the physical row position in the table so it doesn't need to scan the whole table each time. HTH, -- Alvaro Herrera () "The only difference is that Saddam would kill you on private, where the Americans will kill you in public" (Mohammad Saleh, 39, a building contractor) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [despammed] Re: [SQL] Duplicated records
am 25.05.2005, um 13:58:07 -0300 mailte [EMAIL PROTECTED] folgendes: > Hi. > Thanks for the article... > But, I have read it and the query works very slow... > My table have aprox. 180.000 records (correct) and in entire table it has > aprox.360.000 records(duplicated)... How often is this necessary? > Is there a way to delete those duplicated records faster??? Remembering the > table have aprox 360.000 records... I dont know, but i think, you should prevent duplicated records in the future, and make the job (delete duplicates) now. Btw.: you wrote, there is a primary key on the first row. Real? ,[ sorry, messages in german language ] | test_db=# create table blub (id int primary key, name varchar); | HINWEIS: CREATE TABLE / PRIMARY KEY erstellt implizit einen Index >>blub_pkey<< für Tabelle >>blub<< | CREATE TABLE | test_db=# insert into blub values (1, 'x'); | INSERT 970706 1 | test_db=# insert into blub values (1, 'y'); | FEHLER: duplizierter Schlüssel verletzt Unique-Constraint >>blub_pkey<< ` In other words: if there a primary key on the first row, you cannot insert duplicates. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Duplicated records
Thanks CTAS (Create Table As Select) command works fine!!! With great performance. I think it is the best way to correct the data...(apparently) I didnt know about "select DISTINCT". I am going to read about it. Thank you. Quoting Bricklen Anderson <[EMAIL PROTECTED]>: Is there a way to delete those duplicated records faster??? Remembering the table have aprox 360.000 records... Is better I create other table and copy those data??? How should I created??? for 180k rows (which isn't many) just do a CTAS (Create Table As Select): create table lanctos_distinct as select distinct * from lanctos; truncate table lanctos; alter table lanctos_distinct rename to lanctos; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sql select like question.
"hatuan" <[EMAIL PROTECTED]> writes: > I have a select query Select * from A where id like 'US%' > In pgAdmin i have error : "ERROR: unsupported type: 136452". What PG version is this? There were some bugs of that ilk in 8.0..8.0.2, but I think they are all fixed in 8.0.3. Leastwise your example doesn't fail for me ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
