Re: [HACKERS] Is there any method to keep table in memory at startup
Andrew Dunstan wrote: Vinay Jain wrote: You mean that I should create a static table in C program itself and use it...if i am not wrong Ya for the time being i am doing this thing but actually table is bigger (around 5000 rows with 6 columns) bigger than what? also this table is also created and destroyed in each indchar_lt call which is called many times in order by clause uh ... maybe you need to look in your C manual about the effect of a static declaration. The object will be created once. yup I know the effect of Static but this is also fact that when program terminates and restarts object will be created again...(not persistant).. one thing strikes in my mind is that indchar_lt is function in shared object file indchar.so.if this file is not unloaded during Order by call than this static defination can work for me.. Not sure but I think this file is loaded each time indchar_lt is called in order by clause... one more thing i want it generalized so that I can include other indian languages also..without changing code.. If there is not any method to get results fast using database table i will have to opt this option only.. Not for what you want - you are pursuing a chimera, IMNSHO. cheers andrew regards Vinay jain ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Subtle pg_dump problem...
I have a table with a tsearch2 index on it. Now, I have all the tsearch2 stuff installed into a 'contrib' schema. I have had to change the default database schema to include the contrib schema as behind-the-scenes, tsearch2 looks for its tables, and cannot find them even if the function itself is schema-qualfified. This might well be a tsearc2 bug. Anyway, this means the table is dumped like this: SET SESSION AUTHORIZATION 'auadmin'; SET search_path = public, pg_catalog; COPY ... Which give this error upon restoring: ERROR: relation pg_ts_cfg does not exist CONTEXT: COPY food_categories, line 1: 79 102 Vegetables, Salads Legumes\N 'legum':3 'salad':2 'veget':1 It's because the search_path needs to be like this for it to work: SET search_path = public, contrib, pg_catalog; Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Subtle pg_dump problem...
Thanks Christopher, we'll look into the issue. Oleg On Fri, 7 May 2004, Christopher Kings-Lynne wrote: I have a table with a tsearch2 index on it. Now, I have all the tsearch2 stuff installed into a 'contrib' schema. I have had to change the default database schema to include the contrib schema as behind-the-scenes, tsearch2 looks for its tables, and cannot find them even if the function itself is schema-qualfified. This might well be a tsearc2 bug. Anyway, this means the table is dumped like this: SET SESSION AUTHORIZATION 'auadmin'; SET search_path = public, pg_catalog; COPY ... Which give this error upon restoring: ERROR: relation pg_ts_cfg does not exist CONTEXT: COPY food_categories, line 1: 79 102 Vegetables, Salads Legumes\N 'legum':3 'salad':2 'veget':1 It's because the search_path needs to be like this for it to work: SET search_path = public, contrib, pg_catalog; Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE TODO items
Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: What about rules/views/functions and who knows what else (domains?) might be dependant on the current type definition? Yeah, I was just thinking about that this morning. We probably ought to look for dependencies on the table rowtype as well as the individual column. But on the other side of the coin, should we actually reject the ALTER if we see a function that uses the rowtype as a parameter or result type? Without looking inside the function, we can't really tell if the ALTER will break the function or not. With looking, you can't necessarily. What if I'm building a query with EXECUTE or for that matter, what if I've written it in C? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Christopher Kings-Lynne wrote: Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that Exist a postgres irc server? If yes may I know the server and port ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Constraint not shown on \d ?
Hi all,
today I was tryng to delete an index but I had the following
error:
ERROR: cannot drop index activation_code_code_key because constraint activation_code_code_key on
table activation_code requires it
HINT: You may drop constraint activation_code_code_key on table activation_code instead.
however this is what \d show:
# \d activation_code
Table public.activation_code
Column | Type |Modifiers
+---+-
id_activation_code | integer | not null default
nextval('public.activation_code_id_activation_code_seq'::text)
code | character varying(64) | not null
id_code_pool | integer | not null
Indexes:
activation_code_pkey primary key, btree (id_activation_code)
activation_code_code_key unique, btree (code, id_code_pool)
Check constraints:
activation_code_code CHECK (char_length(code::text) = 5)
Foreign-key constraints:
$1 FOREIGN KEY (id_code_pool) REFERENCES code_pool(id_code_pool) ON UPDATE CASCADE ON DELETE
CASCADE
basically what is the difference between:
CREATE UNIQUE INDEX activation_code_code_key ON public.activation_code
USING btree (code, id_code_pool);
or
ALTER TABLE activation_code ADD UNIQUE ( code, id_code_pool );
with \d command there is no difference but is different because the first command
create an index deleteable with a drop index.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER TABLE TODO items
FireBird: ALTER COLUMN column TYPE type DB2: ALTER COLUMN column SET DATA TYPE type. Oracle: MODIFY column type MSSQL:ALTER COLUMN column type constraints MySQL:Both Oracle and MSSQL Sap: MODIFY column type Spec: Nothing (obvious) on changing column types MODIFY is horrible. It seems to drop all constraints, defaults, etc that are not specified in the second definition. It is essentially a replacement of the column. In Oracle MODIFY leaves omitted parts unchanged, syntax is actually ALTER TABLE table MODIFY (column type default constraint) I think the parentheses are optional if only one column is modified. Andreas ---(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: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain said: Andrew Dunstan wrote: uh ... maybe you need to look in your C manual about the effect of a static declaration. The object will be created once. yup I know the effect of Static but this is also fact that when program terminates and restarts object will be created again...(not persistant).. you can have it preloaded and persistent to the end of the server run, see http://www.postgresql.org/docs/current/static/runtime-config.html#RUNTIME- CONFIG-RESOURCE one thing strikes in my mind is that indchar_lt is function in shared object file indchar.so.if this file is not unloaded during Order by call than this static defination can work for me.. Not sure but I think this file is loaded each time indchar_lt is called in order by clause... If course it is not loaded each time. That would be insane. If not preloaded it is loaded when first called in each process, and then kept. It is never unloaded (except by the termination of the process that loaded it). It seems you have been laboring under a misapprehension. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Is there any method to keep table in memory at startup
Andrew Dunstan wrote: Vinay Jain said: Andrew Dunstan wrote: uh ... maybe you need to look in your C manual about the effect of a static declaration. The object will be created once. yup I know the effect of Static but this is also fact that when program terminates and restarts object will be created again...(not persistant).. you can have it preloaded and persistent to the end of the server run, see http://www.postgresql.org/docs/current/static/runtime-config.html#RUNTIME- CONFIG-RESOURCE one thing strikes in my mind is that indchar_lt is function in shared object file indchar.so.if this file is not unloaded during Order by call than this static defination can work for me.. Not sure but I think this file is loaded each time indchar_lt is called in order by clause... If course it is not loaded each time. That would be insane. If not preloaded it is loaded when first called in each process, and then kept. It is never unloaded (except by the termination of the process that loaded it). It seems you have been laboring under a misapprehension. If this is the case than I can make connection to data base in starting of indchar.so file and close connection at end of it and it should work bingo!! it would solve my problem cheers andrew thanx Vinay Jain ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Gaetano Mendola said: Christopher Kings-Lynne wrote: Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that Exist a postgres irc server? If yes may I know the server and port ? irc://irc.freenode.net/postgresql (One of the things I put on pgfoundry's home page is a list of what I think are useful links for developers, including this link, Google archives, current docs, CVSweb, and so on) cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there any method to keep table in memory at startup
Vinay Jain said: Andrew Dunstan wrote: If course it is not loaded each time. That would be insane. If not preloaded it is loaded when first called in each process, and then kept. It is never unloaded (except by the termination of the process that loaded it). It seems you have been laboring under a misapprehension. If this is the case than I can make connection to data base in starting of indchar.so file and close connection at end of it and it should work bingo!! it would solve my problem *sigh* You are not getting it. An immutable function MUST NOT DEPEND ON DATA IN THE DATABASE. Sorry to shout but you really need to understand this. Use static C data, not database tables. In addition to it being pure, it will also be enormously faster than getting data from the database. Yes it means that if you want to change the lookup data you need to recompile your C function library, and to redo any indexes etc. that depend on the function. That's just the way it is, I'm afraid. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Is there any method to keep table in memory at startup
Andrew Dunstan wrote: Vinay Jain said: Andrew Dunstan wrote: If course it is not loaded each time. That would be insane. If not preloaded it is loaded when first called in each process, and then kept. It is never unloaded (except by the termination of the process that loaded it). It seems you have been laboring under a misapprehension. If this is the case than I can make connection to data base in starting of indchar.so file and close connection at end of it and it should work bingo!! it would solve my problem *sigh* You are not getting it. An immutable function MUST NOT DEPEND ON DATA IN THE DATABASE. Sorry to shout but you really need to understand this. Use static C data, not database tables. In addition to it being pure, it will also be enormously faster than getting data from the database. Yes it means that if you want to change the lookup data you need to recompile your C function library, and to redo any indexes etc. that depend on the function. That's just the way it is, I'm afraid. cheers andrew Hi ya you are right but i wanted to escape from compilation and stuff I will think on declaring function stable rather than immutable and test performance for the time being i will follow your advice... thanks regards Vinay ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY command - CSV files
Alvaro Herrera wrote: On Fri, May 07, 2004 at 12:31:51AM +0200, Umberto Zappi wrote: Thanks to everybody has reply to my email. Stop immediatly my work in progress. Some days ago I've downloaded version 7.4.3 of postgresql and I've begin to work over without know other jobs of other developers :-o You should really get the CVS code if you want to hack on Postgres ... there are a lot of changes since 7.4.3. 7.4.3 is not out yet, even I don't find the TAG on CVS. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Joshua D. Drake wrote: Personally, plpgSQL is only useful to those who are coming from Oracle. People are more likely to be comfortable with plPython or plPerl than plpgSQL. Well that was not true for my, I started using postgres and plpgsql not because I knew Oracle ( I don't know it ). plpgsql is more close to postgres then plPython or plPerl, and after all is nearest SQL then plPtyhton or plPerl so a DBA find it more confortable then others languages. my two cents. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
plpgsql is more close to postgres then plPython or plPerl, and after all is nearest SQL then plPtyhton or plPerl so a DBA find it more confortable then others languages. DBA probably... programmer? Doubtful. The majority of people that I run into that are using PostgreSQL are not DBA's. They are programmers trying to do it a better way. Providing plPerl or plPython etc... allows them to stay in a native and productive environment. Sincerely, Joshua D. Drake my two cents. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Usability, MySQL, Postgresql.org, gborg, contrib,
Joshua D. Drake wrote: plpgsql is more close to postgres then plPython or plPerl, and after all is nearest SQL then plPtyhton or plPerl so a DBA find it more confortable then others languages. DBA probably... programmer? Doubtful. The majority of people that I run into that are using PostgreSQL are not DBA's. They are programmers trying to do it a better way. Providing plPerl or plPython etc... allows them to stay in a native and productive environment. True, but a good DBA have to check each single store procedure and view that other programmers write, see tuning techics that programmers are not aware of ( see select max(id) from foo; optimized with a order by + limit 1 ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] COPY command - CSV files
On Fri, May 07, 2004 at 03:09:58PM +0200, Gaetano Mendola wrote: Alvaro Herrera wrote: On Fri, May 07, 2004 at 12:31:51AM +0200, Umberto Zappi wrote: Thanks to everybody has reply to my email. Stop immediatly my work in progress. Some days ago I've downloaded version 7.4.3 of postgresql and I've begin to work over without know other jobs of other developers :-o You should really get the CVS code if you want to hack on Postgres ... there are a lot of changes since 7.4.3. 7.4.3 is not out yet, even I don't find the TAG on CVS. Duh, isn't it? I haven't been paying attention to releases :-) But I meant what's the current CVS tip for the 7.4 branch. Anyway there's a lot of changes from there to the current CVS HEAD tip. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Aprender sin pensar es inútil; pensar sin aprender, peligroso (Confucio) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Weird prepared stmt behavior
Tom Lane wrote: Actually, no, I'd prefer not to make such a distinction; I'd be happy with SQL-level PREPARE being nontransactional. I'd be willing to put up with that distinction if someone shows it's needed, but so far there's not been a really good argument advanced for it, has there? Has anyone reviewed the standard with regards to embedded SQL PREPARE? It would be pretty weird if that behaved differently from the direct SQL PREPARE. (The brief summary is that is does not roll back, but there may be subtleties if have not found.) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: Just a note for the hackers, Jonathan (I think :) ) talked to me about this on the irc channel - we couldn't figure this one out. Seems that pg_dump produces NATURAL CROSS JOIN in the dump of a view, but the pgsql grammar does not appear to allow it. Hm. The syntax NATURAL CROSS JOIN is specifically disallowed by SQL99 and our parser (see attached SQL99 excerpt). If pg_dump produces that in a view dump then that's a bug, but this test case doesn't let me see it happen, because the parser rejects the given view definition. Do you happen to have the original input that created the view? regards, tom lane [snip excerpt from gram.y] During the irc discussion I discovered that. But the CREATE TABLE page in the docs appears to suggest that it is legal. That should be fixed. cheers andrew ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Multiple selects returned from a single stored procedure
Shachar Shemesh wrote: Just out of curiosity, how do I manually destroy the cursor when it's no longer needed? Just do close refcursor? Yup: http://www.postgresql.org/docs/current/static/sql-close.html Also, does this copy take place when the table is changed, or as soon as the transaction ends? If the former, it may not matter. IIRC it is the latter. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Aclitem high level description
Fabien COELHO wrote: Please find attached as somehow requested a plpgsql implementation for a high-level description (by that, I understand relationnal, not functionnal) of acl in postgres. That doesn't tell me anything. The functionality of the ACL system is to answer questions like does user X have privilege Y on object Z. It seems that this question can be answered using existing facilities. Additionally we have information schema views that list existing privileges, and those views are defined using existing facilities. It appears that your tables mostly duplicate that. Can you say in words what information you are missing? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] psql 7.3.4 disagrees with NATURAL CROSS JOIN
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: During the irc discussion I discovered that. But the CREATE TABLE page in the docs appears to suggest that it is legal. That should be fixed. Where exactly? I see For the INNER and OUTER join types, a join condition must be specified, namely exactly one of NATURAL, ON join_condition, or USING (join_column [, ...]). See below for the meaning. For CROSS JOIN, none of these clauses may appear. Dammit, I meant SELECT, from which you took that quote. And you're right. I missed that. Sorry. I saw from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ] and then join_type One of * [ INNER ] JOIN * LEFT [ OUTER ] JOIN * RIGHT [ OUTER ] JOIN * FULL [ OUTER ] JOIN * CROSS JOIN I should have read further cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] write a new built in type
Hi all, I with to write a new builtin type, I seen that what I have to do is: 1) ad on pgsql/src/include/utils/builtins.h the signature for my function that will manipulate my type 2) ad on pgsql/src/backend/utils/adt a new file my_type.c with the implementation of all function inserted in the file at point 1 and what else ? For example how may I define the name for my new type ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL pre-fork speedup
Hi Bruce, Sorry for the confusion because Rod asked a question and I answered too quickly. This is what I mean. 15x Slower: --- Client --TCP-- PgPool --UNIX-- PostgreSQL Client --TCP-- PgPool --TCP-- PostgreSQL 5x Faster: -- Client --UNIX-- PgPool --UNIX-- PostgreSQL Client --UNIX-- PgPool --TCP-- PostgreSQL Hope this helps! Pgpool speeds up connection time by 5x with UNIX socket due to pre-fork and connection pooling. However, pgpool slows down by 15x under TCP socket for some unknown reason. It appeared that the cause of TCP socket slowness was in reading the startup packet which is performed by read_startup_packet(). I did some measurement for the function and it showed huge difference between UNIX and TCP sockets. Times (in micro sec) for 100 call to read_startup_packet() are: UNIX socket: 623 TCP socket: 6086 As you can see TCP is nearly 10 times slower than UNIX socket. In the function there are 2 read()s to process the startup packet. I think I could enhance pool_read() so that it reduces the call to read() as little as possible... -- Tatsuo Ishii ---(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
