[GENERAL] Hash function constant in the Hash join algorithm
Hi, As part part of my project, I had to compare the time taken by the Hashjoin algorithm to that of Nested loop algorithm for the inner join and Natural join queries, under two cases. I used six tables to join each other each containing 5000 rows each approximately. The two cases are given below. 1)When the hash function of the of hash join algorithm is not made to return a constant value, In this case the time taken by the hashjoin algorithm for the inner join is 1.45 secs and for the natural join is 4.58 secs and the time taken by the nested loop algorithm for inner join is 11.49 secs, and for natural join 46.94 secs. 2) when the hash function of the hash join algorithm is made to return a constant value. Here, in the program hashfunc.c, I made all the hash functions return a constant value. In this case the time taken by the hashjoin algorithm for inner join is 8.44 secs and for natural join is 29.18 secs, and the time taken by the Nested loop algorithm for inner join is 11.66 secs and for natural join is 46.9 secs. According to my understanding when a hashfunction is made to return a constant, all the tuples are hashed into a single bucket, and therefore the hashjoin algorithm is converted into a nestedloop algorithm. From the above two cases, I am understanding that even even when the hash function is made to return a constant, The hashjoin agorithm is taking significantly lower time compared to nested loop. Could anyone explain why does the hashjoin algorithm takes significantly lower time compared to nested loop when the hash function is made to return a constant value or have I done any mistake at any part of the code? Note - I am using the postgresql9.4 version for coding. I know the post is long, There might be some grammar mistakes, I regret if I caused any convenience to anyone. Thank you. -- Regards, K.Ravikiran ᐧ
[GENERAL] Error in the connection to the server
Hi, I have installed postgresql-9.4.0 version. I have started the server from eclipse indigo version using Run configurations. There is a table in my database whose name is b. whenever I give a query which is related to this table I get the error *The connection to the server was lost. Attempting reset: Failed. *Even for the drop command it is giving the same error. But it is working fine for the rest of the queries which are not related to the table b. Please help me in resolving this error. Thank you. -- Regards, K.Ravikiran
[GENERAL] Policy.c program
Hi, I am using the postgresql-9.4.0 version. There is a program policy.c which is there in the previous versions. The following link shows the program. http://doxygen.postgresql.org/policy_8c.html But in the version 9.4.0, I cannot find it. I want to know whether this program being removed from this version? Thank you -- Regards, K.Ravikiran
[GENERAL] Relation name stored in Postgres
Hi, I want to know how the relation name is stored in postgres, In which part of the postgres source code could I find the relation name being stored. Thank you -- Regards, K.Ravikiran ᐧ
Re: [GENERAL] Relation name stored in Postgres
Hi, Thank you Sir. Also, could you tell me during which stage(whether parser,optimizer or executor) does the table name gets stored, and if possible could you tell me which program specifically does that. Thank you. ᐧ On Thu, Apr 2, 2015 at 7:32 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi it is in system catalog - table pg_class, column relname Regards Pavel Stehule 2015-04-02 15:52 GMT+02:00 Ravi Kiran ravi.kolanp...@gmail.com: Hi, I want to know how the relation name is stored in postgres, In which part of the postgres source code could I find the relation name being stored. Thank you -- Regards, K.Ravikiran -- Regards, K.Ravikiran
Re: [GENERAL] Relation name stored in Postgres
Hi, Also, could you tell me during which stage(whether parser,optimizer or executor) does the table name gets stored, and if possible could you tell me which program specifically does that. ᐧ On Thu, Apr 2, 2015 at 7:56 PM, Ravi Kiran ravi.kolanp...@gmail.com wrote: Hi, Thank you Sir. Also, could you tell me during which stage(whether parser,optimizer or executor) does the table name gets stored, and if possible could you tell me which program specifically does that. Thank you. On Thu, Apr 2, 2015 at 7:32 PM, Pavel Stehule pavel.steh...@gmail.com wrote: Hi it is in system catalog - table pg_class, column relname Regards Pavel Stehule 2015-04-02 15:52 GMT+02:00 Ravi Kiran ravi.kolanp...@gmail.com: Hi, I want to know how the relation name is stored in postgres, In which part of the postgres source code could I find the relation name being stored. Thank you -- Regards, K.Ravikiran -- Regards, K.Ravikiran -- Regards, K.Ravikiran
[GENERAL] hash function in Postgres
hi, I want to know what kind of hash function postgresql uses while joining. I was debugging through gdb, I found out that it is not using bob jenkins hash function but a different hash function *hash_uint32() and hash_any() *functions if the joining attribute is an integer, and a different kind of hash function for a different type of joining attribute. I want to know whether the hash functions will change if the number of tuples in the table are very large or very low, and if it changes, please tell me what hash function it uses if the tuples are very large and what hash function it uses if the number is very low, also I came to know that the hash function will change depending on the type of the attribute on which the join takes place, but will it always remains the same for the integer type of joining attribute or will it it change. thanks
[GENERAL] Hash Function
Hi, I want to know what kind of hash function postgres is using currently, can someone please explain the algorithm postgres is using for the hash function in the hash join algorithm. Thanks, K.Ravikiran
[GENERAL] NODE
hi, I am going through the hashjoin algorithm in postgres. I find a function ExecHashjoin , which is called each time a new tuple is required by the hash join *Node.* could someone explain what exactly node mean in postgres. Thanks
[GENERAL] attaching a process in eclipse
hi, I am working with postgresql 9.4.0 source using eclipse(indigo version) in ubuntu 14.04. I am facing a problem of attaching a client process to postgresql server. I am following the steps given in this link https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_PostgreSQL_from_Eclipse In the debug configuration , I have given the following information in the fields. C/C++ Application :- src/backend/postgres Project :- postgresql-9.4.0 I have found out the process id of the client process using select pg_backend_pid() and used it to attach the server. But I am getting the following error whenever I do this Could not attach to process. If your uid matches the uid of the target process, check the setting of /proc/sys/kernel/yama/ptrace_scope, or try again as the root user. For more details, see /etc/sysctl.d/10-ptrace.conf how do we rectify this error? Thank you Regards K.Ravikiran
[GENERAL] mysql with postgres
hi all, Is postgres source code compatible with mysql database?? If it is, could someone could give me some links so that I can do that. I want to hack into the postgres source code, but as I am comfortable with mysql, I want to use the mysql database not postgres. any references would be fine. thank you
[GENERAL] debugging with gdb
Hi, I am facing some problem with gdb for the past few days. I want to debug nestloop.c in postgresql , so I followed the below steps. I followed the steps given in the below for collecting the stack trace. https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD 1) I started Postgresql using eclipse using run. 2) I started one client using the following command in bash 3)psql test(Where test is the database and it has the tables student and marks) 4)Then using the command pg_backend_pid(); , I got the pid of the client. 5)In another terminal I started gdb using the command sudo gdb -p pid 6)set pagination off 7)set detach-on-fork off 8)set schedule-multiple on 9)handle SIGUSR1 noprint nostop handle SIGUSR2 noprint nostop 10)python gdb.events.exited.connect(lambda x: [gdb.execute('inferior 1'), gdb.post_event(lambda: gdb.execute('continue'))]) 11)b ExecNestLoop(which is the function in nodenestloop.c) 12)In the client I did a join query, select * from student inner join marks on student.id=marks.id; 13)n But the problem is that the code in nodenestloop.c is not getting executed. could anyone help me with that. Thank you
[GENERAL] gdb debugging with postgres
hi , I am trying to attach a postgres process to gdb, Since I am using only 1 client , I am using the script give by Tom Lane posted in the mailing list. http://www.postgresql.org/message-id/bd6a35510707221030p694cd515kfeb529078557b...@mail.gmail.com I started running the client by following command *psql test *where test is my database. But When I am running that script I am getting two client processes. ravi 13368 13366 0 15:14 ?00:00:00 postgres: checkpointer process ravi 13379 13366 0 15:14 ?00:00:00 postgres: ravi test [local] idle Could any one tell me what is checkpointer process and is there any way of running the script without that process. Thank you, K.Ravikiran
[GENERAL] debugging with gdb in postgres
hi, I am trying to learn how postgresql implements the join algorithms. So I am trying to learn about the source code of the executor precisely the file nodenestloop.c . In the executor file I have nodenestloop.o but no binary executor file. I am using helios eclipse to edit the source code. I want to use gdb for debugging that file, but for that gdb needs a executor file. I want to Debug that file using gdb and attach the postgress process file. Could anyone help me with how to debug that file using gdb and attach a process, I know that we can find out about the pid of the process by using this command in psql, *select pg_backend_pid* , but how exactly are we going to give this pid to gdb. any help or related links wwould be grateful. Thank you
[GENERAL] breakpoints in eclipse using postgresql
hi, I am using helios Eclipse for debugging my code in postgresql. My aim is to know how postgresql uses join algorithms during the join query, so I started to debug*nodenestloop.c* which is in the Executor folder. I gave break points in that file, But whenever I try to debug that file, the control goes to main.c and never comes back,How do I constraint the control only to that particular file(nodenestloop.c) Below are the following fields which I gave in Debug configurations of Helios Eclipse. *C/C++ Application - src/backend/postgres* and *project - pgsql* I followed the steps given in the following link for running the program. https://wiki.postgresql.org/wiki/Working_with_Eclipse# I even uncheked the field *Start on Start up=main* , but When I do that, The *step in and Step over*buttons are not activated and the following problem has popped up. *Could not save master table to file '/home/ravi/workspace/.metadata/.plugins/org.eclipse.core.resources/.safetable/org.eclipse.core.resources'. /home/ravi/workspace/.metadata/.plugins/org.eclipse.core.resources/.safetable/org.eclipse.core.resources (Permission denied)* So I started eclipse using sudo, but this time the following error has come in the console of eclipse. *root execution of the PostgreSQL server is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server.* Could any one help me with this. Thank you
[GENERAL] debugging with child process
hi, can any one help me with the 4th and 5th steps of the following link which is to debug with a child process in postgres. *https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_with_child_processes https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_with_child_processes* when I give the following fields in the debug configuration *c/c++ Application : postgres* *Project - psql* *Build Configuration - Use Active* I am getting the following error- * No source available for __kernel_vsyscall() at 0xb726a424 * Is there any better tool other than eclipse to use for editing or knowing about source code in postgres. Thank you
[GENERAL] Executing nodenestloop.c
hi, I am using the environment Eclipse for the execution of the programs in the executor, whenever I give break points to specific program in eclipse , the control goes to main.c and finally the process never comes back to the actual program. Is there any way that the process be constrained only to my specific program. Thank you
[GENERAL] eclipse-gdb
hi, How do we debug specific functions in gdb of eclipse. Whenever I give break points in nodenestloop.c, it goes to main.c and finally the process ends there, The control never comes back to nodenestloop, how can we the proces constrained only to nodenestloop. Thank you
[GENERAL] eclipse-postgres
hi, How to compile and run the files the source code of postgres in eclipse, how to compile a c program in the backend directory of postgres?? I followed the steps in https://wiki.postgresql.org/wiki/Working_with_Eclipse , but when I build the program nodeNestloop.c in the backend directory, I am getting errors. thank you
[GENERAL] Postgress Doubts
Hello, I am Ravikiran, pursuing my third year BITS Pilani, India, I am doing my thesis in postgress technology, The project is about implementing new join algorithms in postgres, Since I am new to postgress, could you help me in which part of source code of postgres should be looking in postgress so that I can progress in this project, also are there any tools for postgress which helps me look into the code of postgress easily. Thank you.
[GENERAL] Multi Lingual problem
I am using the psqlodbc driver to connect to postgresql... But this is not supporting all the languages that UTF should support.. Database is of the correct encoding that is UTF... can you please give a solution so that i can psqlodbc supports multiple languages. Right now there is support for russian characters from my application.. thanks in advance for any replies
[GENERAL] Returning Cursor
Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT * FROM SAM1; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. I cant access any of the values that the select statement in the function should retrieve.When we executed the above function from VC we only got the cursor name. We've been trying to access the values for the past one week. Can you please help me by sending me a sample code as to how to get the values in a recordset using this refcursor. Please do reply. This is very urgent. Thanks and regards Ravi Kiran L
[GENERAL] Fwd: Returning Cursor
Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT * FROM SAM1; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. I cant access any of the values that the select statement in the function should retrieve.When we executed the above function from VC we only got the cursor name. We've been trying to access the values for the past one week. Can you please help me by sending me a sample code as to how to get the values in a recordset using this refcursor. Please do reply. This is very urgent. Thanks and regards Ravi Kiran L
[GENERAL] Fwd: Returning Cursor
Hello, I am a developer working on postgres. I just wrote a function which ll return a refcurosor as shown below. CREATE OR REPLACE FUNCTION reffunc(refcursor) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT * FROM SAM1; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; i have problems accessing this function from my middle tier i.e VC++. I wrote a VC statement to retrieve values from this refcursor using a record set. I cant access any of the values that the select statement in the function should retrieve.When we executed the above function from VC we only got the cursor name. We've been trying to access the values for the past one week. Can you please help me by sending me a sample code as to how to get the values in a recordset using this refcursor. Please do reply. This is very urgent. Thanks and regards Ravi Kiran L