[SQL] underscore problem
Is there anywhere in the docs that says we should not use an underscore in a table name? table FC_Client_Info exists with a record where jobno 1234 has info: select acode from FC_Client_Info where jobno = '1234'; fails with a warning that fc_client_info does not exist notice the lower case. If I quote the table select acode from "FC_Client_Info" where jobno = '1234'; the select works fine What's the problem here? This should be easy. Ted
Re: [SQL] underscore problem
From: "postgresql" <[EMAIL PROTECTED]> > Is there anywhere in the docs that says we should not use an > underscore in a table name? > > select acode from FC_Client_Info where jobno = '1234'; > fails with a warning that fc_client_info does not exist > notice the lower case. If I quote the table > > select acode from "FC_Client_Info" where jobno = '1234'; > the select works fine It's the case not the underscore. PG lower-cases table/field names both on creation and on queries. You can create/access mixed-case tables by quoting them, as you've discovered. This usually crops up converting from a different DB to Postgresql. - Richard Huxton
Re: [SQL] DLookup('field', 'table', ['condition'])
Mr. Ambos, > Is this supposed to give effectively the same result as the subquery > (select student.id where name='Bill Gates' limit 1)? I don't think > that subquery is supported in 7.0, but will be in 7.1 along with > EXECUTE for plpgsql which should let you build a query out of the > parts you give it. I think you'd possibly be able to do this in 7.0 > using pltcl but I don't know tcl so I can't help there. Based on an earlier discussion regarding dynamic queries and EXECUTE, what you want will be hard to do with an SQL or PL/pgSQL function (although possible) even in 7.1. According to one developer, with PL/Tcl it's easier to build dynamic queries; one could also use C or Perl. However, you may want to re-think why you're using a dynamic "DLookup" function rather than a more specific function, saved query, or view. Dynamic queries are always going to run slower, regardless of language, than saved DB structures. This is especially true of Dlookup in MS Access, let alone DSum or DCount. If the program role you're trying to serve is frequent (student look-up, for example) in your application, you would probably be better off in several regards constructing a specific function to fill that purpose (lf_lookup_student_id(VARCHAR)). Personally, I'm a function "junkie" an I've never had need to replicate the VBA Dlookup functionality. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] underscore problem
if your table is table name is case sensitive, you should double quota it, since postgres take lower case as default. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 27 Feb 2001, postgresql wrote: > Is there anywhere in the docs that says we should not use an > underscore in a table name? > > table FC_Client_Info exists with a record where jobno 1234 has > info: > > select acode from FC_Client_Info where jobno = '1234'; > fails with a warning that fc_client_info does not exist > notice the lower case. If I quote the table > > select acode from "FC_Client_Info" where jobno = '1234'; > the select works fine > > What's the problem here? This should be easy. > > Ted > >
[SQL] Debug messages in beta5
Maybe this will help in evaluating beta5. Here is some debug output from serverlog while the system was doing a restore to 7.1b5 from a pg_dump file created on a 7.0.3 box: DEBUG: MoveOfflineLogs: remove 003D DEBUG: MoveOfflineLogs: remove 003E DEBUG: MoveOfflineLogs: remove 003F DEBUG: MoveOfflineLogs: remove 0040 DEBUG: MoveOfflineLogs: remove 0041 DEBUG: MoveOfflineLogs: remove 0036 DEBUG: MoveOfflineLogs: remove 0037 DEBUG: MoveOfflineLogs: remove 0038 DEBUG: MoveOfflineLogs: remove 0039 DEBUG: MoveOfflineLogs: remove 003A DEBUG: MoveOfflineLogs: remove 003B DEBUG: MoveOfflineLogs: remove 003C DEBUG: copy: line 25300, XLogWrite: new log file created - try to increase WAL_FILES DEBUG: copy: line 57362, XLogWrite: new log file created - try to increase WAL_FILES DEBUG: copy: line 27109, XLogWrite: new log file created - try to increase WAL_FILES DEBUG: copy: line 2978, XLogWrite: new log file created - try to increase WAL_FILES DEBUG: MoveOfflineLogs: remove 0044 DEBUG: MoveOfflineLogs: remove 0045 DEBUG: MoveOfflineLogs: remove 0046 DEBUG: MoveOfflineLogs: remove 0042 DEBUG: MoveOfflineLogs: remove 0043 I'm not sure where/how to change WAL_FILES. Is that a compiled in default or a configuration setting? Is the MoveOfflineLogs anything to worry about? These were interesting too: I'm not sure what to make of them: NOTICE: Adding missing FROM-clause entry for table "cont_group_link" ERROR: Rule WHERE condition may not contain references to other relations NOTICE: Adding missing FROM-clause entry for table "cont_group_link" Here's what the original schema definition for cont_group_link looks like: create table cont_group_link ( - -- Primary Keys: org_id, cont_id, group_owner, group_name -- Foreign Keys: org_id, cont_id into cont_cont org_id int, cont_id int, crt_by int4, group_name varchar, unique (org_id, cont_id, crt_by, group_name), foreign key (org_id) references cont_org on update cascade on delete cascade, foreign key (org_id, cont_id) references cont_cont on update cascade on delete cascade, foreign key (crt_by, group_name) references cont_group on update cascade on delete cascade ); And here's a view definition that the messages are probably related to: drop view cont_group_v; drop view cont_group_link_v; --CV: create view cont_group_v as select *, oid as _oid from cont_group where not (access = 'none' and crt_by != getpguid()); create rule cont_group_v_insert as on insert to cont_group_v do instead insert into cont_group (crt_by, group_name, group_type, descr, access) values (getpguid(), new.group_name, new.group_type, new.descr, new.access); create rule cont_group_v_delete as on delete to cont_group_v do instead delete from cont_group where crt_by = old.crt_by and group_name = old.group_name; create rule cont_group_v_update as on update to cont_group_v do instead update cont_group set group_name = new.group_name, group_type = new.group_type, descr = new.descr, access = new.access where crt_by = old.crt_by and group_name = old.group_name; create view cont_group_link_v as select *, oid as _oid from cont_group_link where cont_group_priv(crt_by,group_name,'r'); create rule cont_group_link_v_innull as on insert to cont_group_link_v do instead nothing; create rule cont_group_link_v_insert as on insert to cont_group_link_v where cont_group_priv(crt_by,group_name,'w') do instead insert into cont_group_link (org_id, cont_id, crt_by, group_name) values (new.org_id, new.cont_id, getpguid(), new.group_name); create rule cont_group_v_link_denull as on delete to cont_group_link_v do instead nothing; create rule cont_group_v_link_delete as on delete to cont_group_link_v where cont_group_priv(crt_by,group_name,'w') do instead delete from cont_group_link where org_id = old.org_id and cont_id = old.cont_id and crt_by = old.crt_by and group_name = old.group_name; This seems to lack sufficient context to find exactly where they came from: ERROR: Rule WHERE condition may not contain references to other relations ERROR: select rule's target entry 27 has different type from attribute lquant However, based on the field name, I can tell the last one is coming from this view: create view mtr_reg_v_unpackaged as select m.*, (select coalesce(sum(l.quant),0) from pak_lnk l where m.ttype = l.ttype and m.ropnum = l.ropnum and m.inum = l.inum and m.minum = l.minum and m.bminum = l.bminum) as lquant, (select p.base
Re: [SQL] Debug messages in beta5
Kyle <[EMAIL PROTECTED]> writes: > These were interesting too: I'm not sure what to make of them: > NOTICE: Adding missing FROM-clause entry for table "cont_group_link" > ERROR: Rule WHERE condition may not contain references to other > relations > NOTICE: Adding missing FROM-clause entry for table "cont_group_link" These are fishy --- I would not have expected pg_dump output to trigger such messages. Could you redo the load with PGOPTIONS="-d2" so that the queries appear in the log along with the notices/errors? That way we could see just what's triggering the messages. regards, tom lane
Re: [SQL] Urgent help
I suspect you have a group with a dangling reference to a deleted user. What do you get from "select * from pg_user" and "select * from pg_group" ? regards, tom lane
[SQL] Error ??
Hello, i have an error : NOTICE: get_groname: group 0 not found getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. what can i do ?? help me please Nizomi -- PostgreSQL 6.5.3 on sparc-sun-solaris2.6, compiled by gcc 2.95.1
Re: [SQL] Error ??
Tubagus Nizomi <[EMAIL PROTECTED]> writes: > NOTICE: get_groname: group 0 not found > getTables(): SELECT failed. Explanation from backend: 'pqReadData() -- > backend closed the channel unexpectedly. I think you dropped a user that was still a member of a group, or a group still referred to in a table's permissions, or something like that. Look into pg_user and pg_group, and look at the permissions of the table you were trying to access. We have fixed a couple of bugs like this in current sources, but I'd like to know the details when you have them, just to make sure this case is covered now ... regards, tom lane
[SQL] Re: Compiling "C" Functions
On Thu, Dec 28, 2000 at 09:36:57AM -0500, Ron Peterson wrote: > Tulio Oliveira wrote: > > > > I appreciate any "C" Function complete samples, including de command > > line for > > the compiler. > > I've attached a generic GNU make snippet for compiling .so files. > Adjust to suite your tastes. Like my math textbooks used to say > "writing the C code is trivial, and is left as an excercise for the > reader." ;) > > CC = /usr/bin/gcc > TARGET = debug > #TARGET = final > DFLAGS = -Wall -g > FFLAGS = -Wall -O2 > SFLAGS = -fpic -shared > MYINCLUDES = -I/usr/local/include > -I/usr/local/src/postgresql/src/include -I/usr/local/postgresql/include > MYLIBS = -L/usr/local/lib -L/usr/local/postgresql/lib -lpq > > ifeq ($(TARGET),final) > MYCFLAGS = $(FFLAGS) > else > MYCFLAGS = $(DFLAGS) > endif > > %.so: > $(CC) $(MYCFLAGS) $(MYINCLUDES) $(MYLIBS) $(*F).c -c -o $(*F).o > $(CC) $(SFLAGS) $(*F).o -o $(*F).so > [ -d $(TARGET) ] || mkdir $(TARGET) > mv $(*F).so $(TARGET) > rm *.o Or using implicit rules, only type "make my_file.so": INCLUDES = -I /usr/include/postgresql CFLAGS = -g -Wall $(INCLUDES) -fPIC %.so: %.o ld -shared -soname $@ -o $@ $< $(LIBS) -- THERAMENE: Elle veut quelque temps douter de son malheur, Et ne connaissant plus ce héros qu'elle adore, Elle voit Hippolyte et le demande encore. (Phèdre, J-B Racine, acte 5, scène 6)
