[GENERAL] Pg_catalog reference
Dear all, i'm trying to create tables using pg_user (pg_authid) as a foreign key for my table. I need to log and control that only registered users can modify data and i want to control data changes via logging triggers. I need to know who exactly was modifying data. To be more exact i want to create a trigger that can log user information into the a table. I want to have a way to make rollbacks of high level data (documents...whatever). But when i'm trying to create a constraint referencing pg_catalog tables - i'm getting an error. What you consider to be a solution in thi case? Any help appriciated. Thanks. Best regards, Anton Nikiforov ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] writes: is there any function that can translate INT to INET type? Nothing built-in, and given the fact that inet no longer means IPv4, it's unlikely we'd add one in the future. But there's nothing stopping you from adding one of your own. For example regression=# create or replace function int2inet(int) returns inet as $$ regression$# declare oct1 int; regression$# oct2 int; regression$# oct3 int; regression$# oct4 int; regression$# begin regression$# oct1 := ((($1 24) % 256) + 256) % 256; regression$# oct2 := ((($1 16) % 256) + 256) % 256; regression$# oct3 := ((($1 8) % 256) + 256) % 256; regression$# oct4 := ((($1 ) % 256) + 256) % 256; regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; regression$# end$$ language plpgsql strict immutable; CREATE FUNCTION regression=# select int2inet(-1062726656); int2inet -- 192.168.20.0 (1 row) There's probably a better way to do the shifting-and-masking, but that was the first thing that came to mind. (Actually, if you are planning to push a whole lot of data through this, it might be worth your time to write something in C. But for a one-shot data conversion task this is probably plenty good enough.) regards, tom lane ---(end of broadcast)--- TIP 1: 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 Thanks alot, Mr. Lane i was just thinking that there was something inside postgres to convert this types. But now will try to write this functions :) Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
Tom Lane wrote: Anton Nikiforov [EMAIL PROTECTED] writes: is there any function that can translate INT to INET type? Nothing built-in, and given the fact that inet no longer means IPv4, it's unlikely we'd add one in the future. But there's nothing stopping you from adding one of your own. For example regression=# create or replace function int2inet(int) returns inet as $$ regression$# declare oct1 int; regression$# oct2 int; regression$# oct3 int; regression$# oct4 int; regression$# begin regression$# oct1 := ((($1 24) % 256) + 256) % 256; regression$# oct2 := ((($1 16) % 256) + 256) % 256; regression$# oct3 := ((($1 8) % 256) + 256) % 256; regression$# oct4 := ((($1 ) % 256) + 256) % 256; regression$# return oct1 || '.' || oct2 || '.' || oct3 || '.' || oct4; regression$# end$$ language plpgsql strict immutable; CREATE FUNCTION regression=# select int2inet(-1062726656); int2inet -- 192.168.20.0 (1 row) There's probably a better way to do the shifting-and-masking, but that was the first thing that came to mind. (Actually, if you are planning to push a whole lot of data through this, it might be worth your time to write something in C. But for a one-shot data conversion task this is probably plenty good enough.) regards, tom lane ---(end of broadcast)--- TIP 1: 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 Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
Martijn van Oosterhout wrote: On Sun, Dec 04, 2005 at 02:09:53PM +0300, Anton Nikiforov wrote: Sorry for my stupidity but, maybe there is a function that converts mask stored in int format to a numer of bits? ;) Your function easyly convert this mask to dot decimal notation, but how to count the number of 1 in it? No, but you can write one the same way like so: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function. Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] int to inet conversion
Martijn van Oosterhout wrote: On Sun, Dec 04, 2005 at 03:21:47PM +0300, Anton Nikiforov wrote: Martijn van Oosterhout wrote: Let i be your input. Calculate t = -i. If i is in the right format, t will have exactly one bit set. Test this with t 0 and (t i) == t If that's ok, then your answer is 32 - log2(t) Have a nice day, Sorry, did not quite catch. t in this case is int, and there is no log2(int) function. But there is a log(x,y) function, so log(2,t) would work also. Note that 255.255.255.0 stored as integer is -256. Have a nice day, Thanks alot! Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] int to inet conversion
Dear All. is there any function that can translate INT to INET type? the table contains ip and mask in different fields (int fields): ip | integer | default 0 mask| integer | default -1 db= select ip, mask from ips limit 2; ip | mask -+-- -1062726656 | -256 -1062724096 | -256 But i need inet or sidr records :) Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Merry Christmas
Andreas Kretschmer wrote: begin Raymond O'Donnell [EMAIL PROTECTED] wrote: Happy Christmas from the west of Ireland! - Nollaig shona dobh. Happy Christmass and New Year from Russia -- (Best regads), (Anton Nikiforov) smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] PGCluster in production
Dear All! I'm currently looking for multy-master PostgreSQL cluster software and found one named PGCluster (version 1.0.7...something) But looks like nobody use this software in production because in this maillist i have found only questions and refferences to the PGCluster, but no success story or discussion or something. And on the developer's site (http://www.csra.co.jp/~mitani/jpug/pgcluster/en/index.html) there is no success story also. I understand that maybe i'm looking in the wrong list, but could someone supply me with a link or direction or [un]success story? My preffered platform is i386 My preffered OS is FreeBSD My preffered DBMS is PostgreSQL :) The number of nodes required 2-3-4 (according to a single node database load, responce time and network topography) The questions: Is it stable/reliable enough for production? How do remote servers replicates (for example the main cluster is 3 pcs and one database is far away from the main cluster and have an internet connection but should have the same data online)? How do updates to the same table (even the same field) will be handled? Does someone did a sequence replication tests (i do have a database consistency based on sequences). Best regards, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] LOG table changes ...
[EMAIL PROTECTED] wrote: hi, I want to track every change made to several tables...and was wondering what is the best way... What are my thoughts at the moment are to make a table like this : id, table_name, field_name, old_value, new_value, timestamp and triggers to do the logging. but this will probably generate alot of traffic (not so worrying at the moment) As I side effect of this I want to be able to make a snapshot of the TABLE at specified time.. any ideas on this too... tia. P.S. Other way would be to make a duplicate table and transffer the whole record, but dont think this is a good solution Hello! I did almost the same by writing a trigger on PL/Ruby that is logging all table changes + user + other information into the log table in XML format (the format of the table on which updation/insertion trigger is fiered is absolutley free and not required to be defind at compile time). So you still can view all data changes and build the database but my way is cappable to view who when changed what, but to build a database actual at exact moment is difficult enough but still possible. Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Getting user who fired a trigger
Dear All, I have made a trigger procedure that logs all table's changes to the log_table in XML format, but i do need to log a user who did that and not succsessful with this. The documentation (including 37.10. Trigger Procedures) give me nothing. Could somene supply me with a solution or documentation part that i should read? My trigger function is written in PL/Ruby because i found no way to make a table's structure independent function with PL/pgsql. And one more question - is there any special type to store XML instead of text? This question arised from the problem, that i'm planning (and i do need this) to store all the database updation history and some small procedures like adding a user will produce from 1 to 10 records in the log table of text type. So updating of 1 byte will produce kilobytes of text. So, maybe there is some compact type for XML storing? (i know that i could pack it and store in zipped or other format, but i would like to have a search possibility); -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Getting user who fired a trigger
Thanks for the replay, Richard. Richard Huxton wrote: Anton Nikiforov wrote: Dear All, I have made a trigger procedure that logs all table's changes to the log_table in XML format, but i do need to log a user who did that and not succsessful with this. The documentation (including 37.10. Trigger Procedures) give me nothing. Could somene supply me with a solution or documentation part that i should read? Can't remember which part it is, but you want one of: SELECT CURRENT_USER; SELECT SESSION_USER; test=# SELECT CURRENT_USER; current_user -- anton (1 row) test=# SELECT SESSION_USER; session_user -- anton (1 row) This gives the same output. Looks like i should check this with the real operatins to understand the difference or RT*M more. My trigger function is written in PL/Ruby because i found no way to make a table's structure independent function with PL/pgsql. And one more question - is there any special type to store XML instead of text? This question arised from the problem, that i'm planning (and i do need this) to store all the database updation history and some small procedures like adding a user will produce from 1 to 10 records in the log table of text type. So updating of 1 byte will produce kilobytes of text. So, maybe there is some compact type for XML storing? (i know that i could pack it and store in zipped or other format, but i would like to have a search possibility); Sorry, I don't think I understand. Do you want to store multiple versions of the same XML document? Or are you logging changes to non-XML data as XML text (and if so why)? I'm storing XML prepared text in the database in the column of type text. And the question is - is there some more wise way to do that? Like column type XML maybe? :) -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PL/Ruby
Hello David, thanks, changing to Ruby18 solved my problem after some manual passages with woodoo around the computer :) Best regards, Anton Nikiforov David Garamond wrote: Anton Nikiforov wrote: Thanks, David, but i did try this way already and in the case i build plruby like this i have the following in the psql: template1=# create function plruby_call_handler () returns opaque as '/usr/local/lib/ruby/1.6/i386-freebsd5/plruby.so' language 'C'; ERROR: could not load library /usr/local/lib/ruby/1.6/i386-freebsd5/plruby.so: dlopen '/usr/local/lib/ruby/1.6/i386-freebsd5/plruby.so' failed. (/usr/local/lib/ruby/1.6/i386-freebsd5/plruby.so: Undefined symbol rb_hash_delete) template1=# Hm, I'm pretty clueless myself about the above, but a couple of observation: 1) the path seems strange, why does it install to /usr/local/lib/ruby/1.6/ instead of the more usual /usr/local/lib/ruby/site_ruby/1.6/ ... 2) what does 'ldd /usr/local/lib/ruby/1.6/i386-freebsd5/plruby.so' say? Does it include the line similar to: libruby.so.1.8 = /usr/lib/libruby.so.1.8 (0x40037000) (*.so.1.6 in your case). 3) have you tried upgrading your Ruby installation to 1.8? ---(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: [GENERAL] Partial index question
Paul Thomas : On 29/04/2004 09:53 Anton Nikiforov wrote: looks like after a few tests that i've done i'm confused more than before. I did create a table and inserted 20 random records of two different types of data into it. Did you analyze the table afterwards? Hello and thanks for the comment. Yes. I did analyze. Also thanks to Bruno Wolff III for his comments, i have found it easyear to use his suggestion. But while i was making my tests i found a way how to use partial or complex indexes, but they make my selects slower than without indexes at all :( (I was selecting * not a ma()). But i'll continue my tests. -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Insert Or update
Dear All, I have a database which stores traffic data and to update the traffic for the particular IP i have to select this ip from the table for this period and if it is already in the database i should run an update statement, but if it is not presented - i should insert the data. It was OK when i have had 2-3 millions of records, but now it is 15 millions and i'm not complaining that it is too slow to select - no, but it takes some time anyway. I know that this will be helpful to write a function that will do this for me, but it will run the same time as my insertion tool that is written in c or even slower. So my question is: is it possible to have UPDATE OR INSERT statement in the PostgreSQL like it is possible in Oracle (as i heard but did not check it by myself yet)? Best regards, Anton smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Huge number of raws
Francisco Reyes ?: On Thu, 18 Mar 2004, Anton Nikiforov wrote: But i'm worry about mentioned centeral database that should store 240 millions of records daily and should collect this data for years. I have not worked with anything even remotely so big. A few thougths.. I think this is more of a hardware issue than a PostgreSQL issue. I think a good disk subsystem will be a must. Last time I was looking for my ex employer at large disk subsystems I think the one we were leaning towards was an IBM disk subsystem. I think it was in the $100,000 range. Regardless of architecture (ie PC, SUN, etc..) SMP may be of help if you have concurrent users. Lots and lots of memory will help too. And the data migration problem is still an opened issue for me - how to make data migration from fast devices (RAID ARRAY) to slower devices (MO Library or something like this) still having access to this data? Don't follow you there. You mean backup? You can make a pg_dump of the data while the DB is running and then back that up. Or were you talking about something else like storing different data in different media speeds? (Like Hierarchical Storage Management) I do not exactly know how to deal wth such a huge amount of data. The disk subsytem is the must and i do undrstand this. SMP architecture is the must also. I was asking is there any way that data will migrate from fast disk subsystem to slower but relyible automaticaly. Like in Nivell Netware (i used to work with it 7-8 years ago) you could ask the system if the file is untached for a month - then move it from one disk to magnetic-optical or tape but if this file is requested OS could move it back to the operational volume. Anton smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] Huge number of raws
Dear All! I have a question about how the PostgreSQL will manage a huge number of raws. I have a projet where each half an hour 10 millions of records will be added to the database and they should be calculated, summarized and managed. I'm planning to have a few servers that will receive something like a million records per server and then they will store this data into the centeral server in report-ready format. I know that one million records could be managed by postgres (i have a database with 25 millions of records and it is working just fine) But i'm worry about mentioned centeral database that should store 240 millions of records daily and should collect this data for years. I cannot even imagine the needed hardware to collect monthly statistics. And my question is - is this task is for postgres, or i should think about Oracle or DB2? I'm also thinking about replication of data between two servers for redundancy, what you could suggst for this? And the data migration problem is still an opened issue for me - how to make data migration from fast devices (RAID ARRAY) to slower devices (MO Library or something like this) still having access to this data? -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Huge number of raws
Anton Nikiforov : Dear All! I have a question about how the PostgreSQL will manage a huge number of raws. I have a projet where each half an hour 10 millions of records will be added to the database and they should be calculated, summarized and managed. I'm planning to have a few servers that will receive something like a million records per server and then they will store this data into the centeral server in report-ready format. I know that one million records could be managed by postgres (i have a database with 25 millions of records and it is working just fine) But i'm worry about mentioned centeral database that should store 240 millions of records daily and should collect this data for years. I cannot even imagine the needed hardware to collect monthly statistics. And my question is - is this task is for postgres, or i should think about Oracle or DB2? I'm also thinking about replication of data between two servers for redundancy, what you could suggst for this? And the data migration problem is still an opened issue for me - how to make data migration from fast devices (RAID ARRAY) to slower devices (MO Library or something like this) still having access to this data? And one more question - is there in postgress something like table partitioning in Oracle to store data according to the some rules, like a group of data source (IP network or something)? -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] PL/Ruby
Dear sirs, Does someone made PL/Ruby working on 4.7.1? I tried alot of things. Manual install, port install and nothing happend. Please give me the idea how to fix the problem with plruby.c: In function `pl_tuple_put': plruby.c:498: error: too few arguments to function `tuplestore_begin_heap' plruby.c: In function `pl_func_handler': plruby.c:1450: error: too few arguments to function `tuplestore_begin_heap' *** Error code 1 Stop in /usr/ports/databases/postgresql-plruby/work/plruby-0.3.8. *** Error code 1 Stop in /usr/ports/databases/postgresql-plruby. ---(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: [GENERAL] Postgress and MYSQL
Hello Bob! Everybody knows that PostgreSQL is better than MySQL and supports more features etc. But remember - the main issue of database systems now is web that is being build mainly by students that do not even know what database engines are made for. At least here (My second job is Instructor in Unix/Linux/etc.) and i know that the main thing that is required by students is Linux with apache and MySQL. And the strange thing - students are always starting learning from Network Administration or Linux in Enterprise course, but real administrators who is working with systems for 10-15 years are starting from Introduction into unix systems. Here in Russia almost all web design companies using MySQL, on all hosting systems owners asking to install MySQL for their users etc. MySQL is everywhere. So, who will work with PG? Only people registered here :)) Maybe a few more. So it is normal that MySQL beats PG on the market. I have Oracle, PostgreSQL servers, used to work with DB2 on AS/400 and personaly i know that PG is better than MySQL. But who will tell it to students who saw a computer twice and already think that they are administrators? : Please do not kill me for this post - i like PG and working with PG and developing for PG, i was talking just about what happening around. To make PG known there should be more and more products that relay on PG. And this should be not Banking or other mission critical projects. It should be a simple forums, picture bases i do not know what but the things that should be installable for 3 minutes and working for years. Otherwise if PG is positioning itself as a DB system for huge and mission critical tasks - nobody here should think about MySQL that was simple and will be simple when PG is being installed for tasks where MySQL will never work and nobody will ever think it will. Regards, Anton BP To whom it may concern: BP I find the recent articles in various trade publications a little BP disturbing due to the lack of PostgrSQL mention. I continue to see BP articles about how IBM may be considering MYSQL for development an BP open_source web database. BP Why isn't PostgreSQL being considered or talked about by major industry BP giants? As a DBA I know that Postgres is far superior to MYSQL but if BP the industry directs it's energies towards open-source database this BP coming year I think somehow PostgreSQL needs to be represented better. BP Bob Powell BP ---(end of broadcast)--- BP TIP 3: if posting/reading through Usenet, please send an appropriate BP subscribe-nomail command to [EMAIL PROTECTED] so that your BP message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Hierarchical queries
; temp.parent = temp_id; record_id = temp.parent; record_parent = temp.id; UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = record_id AND parent = record_parent AND blocked = FALSE; ELSE INSERT INTO tempidtable (id) VALUES (temp.id); UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = temp.id AND parent = temp.parent AND blocked = FALSE; END IF; END LOOP; DELETE FROM tempidtable WHERE id=buf_record.id; END LOOP; -- Here we are checking if something left in the buffer -- If nothing - just exit this loop SELECT INTO temp * FROM tempidtable LIMIT 1; IF NOT FOUND THEN EXIT; END IF; END LOOP; -- Now lets print the path from start to the end SELECT INTO path * from pathtable; buf = $1; LOOP path.id = buf; RETURN NEXT path; IF i = 0 THEN i=1; SELECT INTO temp * from temptable where id=buf; ELSE SELECT INTO temp * from temptable where id=buf AND blocked = TRUE; END IF; UPDATE temptable SET blocked = FALSE WHERE id = temp.id AND parent = temp.parent AND blocked = TRUE; IF FOUND THEN buf = temp.parent; ELSE EXIT; END IF; END LOOP; -- How we do not need temp tables anymore DROP TABLE tempidtable; DROP TABLE temptable; -- And lets finish procedure output :) RETURN NULL; END; ' LANGUAGE 'plpgsql'; == Now select from the function like this: SELECT id, treetable.data FROM gettree(8,5) where id=treetable.id; And you should get a path (treetable.data added only for visualization) id | data + 8 | Ch1Ch1Ch2 2 | Chield1Chield1 1 | Chield1 0 | root 3 | Chield2 4 | Chield2Chield2 5 | Ch2Ch2Ch1 (7 rows) SELECT id, treetable.data FROM gettree(6,7) where id=treetable.id; id | data + 6 | Ch2Ch2Ch2 4 | Chield2Chield2 3 | Chield2 0 | root 1 | Chield1 2 | Chield1Chield1 7 | Ch1Ch1Ch1 (7 rows) ANlr Hello everybody! ANlr Does someone know how to build hierarchical queries to the postgresql? ANlr I have a table with tree in it (id, parent) ANlr and need to find a way from any point of the tree to any other point. ANlr And i would like to have a list of all steps from point A to point B ANlr to make some changes on each step (this is required by the algorythm). ANlr Here is an example: ANlr treetable (where tree is stored): ANlr idparent data ANlr int4 int4 varchar(255) ANlr 0 0root ANlr 1 0root's chield 1 ANlr 2 0root's chield 2 ANlr 3 1root's chield 1 chield 1 ANlr 4 1root's chield 1 chield 2 ANlr 5 2root's chield 2 chield 1 ANlr 6 2root's chield 2 chield 2 ANlr And i want to get something like this: ANlr start point root's chield 2 chield 2 ANlr finish root's chield 1 chield 1 ANlr And the result i need: ANlr idparent data ANlr 6 2root's chield 2 chield 2 ANlr 2 0root's chield 2 ANlr 0 0root ANlr 1 0root's chield 1 ANlr 4 1root's chield 1 chield 2 ANlr i know that it is possible in Oracle but what about postgres? ANlr Best regards, ANlr Anton Nikiforov ANlr ---(end of broadcast)--- ANlr TIP 7: don't forget to increase your free space map settings , IT .: +7 095 7814200 : +7 095 7814201 Mail: [EMAIL PROTECTED] Web: www.loteco.ru ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Hierarchical queries
Thanks Graeme! MG http://marc.theaimsgroup.com/?l=postgresql-sqlm=106739176106877w=2 But this function is still returning only a subtree and in addition it have a bug when calling it like SELECT * FROM crawl_tree(0,0); You will always get ERROR: out of memory But this function is clear enough to write some additional code :) regards, Anton ---(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: [GENERAL] Hierarchical queries
Thanks Joe, But this function is not giving a path from one element to other, it is just truncating the tree beginning from the start element, but it is not rotating the whole tree making starting element a tree's root. JC See contrib/tablefunc for a function called connectby(). Regards, Anton ---(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
[GENERAL] insertion with trigger failed unexpectedly
Dear all, i have a problem with insertion data and running post insert trigger on it. Preambula: there is a table named raw: ipsrc | cidr ipdst | cidr bytes | bigint time | timestamp Triggers: daily_trigger AFTER INSERT OR UPDATE ON raw FOR EACH ROW EXECUTE PROCEDURE daily_func() and the table daily: ip| cidr bytesin | bigint bytesout | bigint local_traffic | boolean time | date The matter of this code is to store raw traffic counters that i'm getting from my cisco or FreeBSD routers and calculate daily traffic in daily table dividing it into internal and external by internal ip. The problem: From the very beginning everything was fine and all records that i was getting from routers were calculated just right. I spent a weeks monitoring and testing my software. Now i have 10.000.000 records in raw table and when i'm inserting data alot of records are missing in raw and daily. Sometimes i got UPDATE failed errors, sometimes INSERT failed, but in general i'm getting this messages twice a day but not only two records are missing - hundreds of them. Currently i have no idea where to go and what to check. I did my tests mainly on FreeBSD platform and now i did tests on RedHat Linux and the result is the same - some records just did not reach the database (trigger has logger that is saying that everything was inserted, but it is not true, the tool that inerting records has logger too). When i'm doing tests - everything is going well, but in production when multiple records being inserted losses happend. The insertion tool is very simple C program that (looks like) have no place to store bugs :) The trigger is very simple too. If you need the code - i could post it here, but i do not think that it will be useful. Could you give me an idea where to go and what to check in this case? Regards, Anton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Hierarchical queries
Hello and thanks for the links, but http://www.brasileiro.net/postgres/cookbook. this site is still down or at least do not accsepting requests, and RH http://gppl.terminal.ru/index.eng.html this patch is not working with my 7.4 release, i tried hier-0.3, but cannot compile my postgres with it installed. RH I could have sworn there was something in contrib/ too, but I can't see it RH now. Yes it is gone. :) One more URL: http://www.sai.msu.su/~megera/postgres/gist/ltree I read all but did not get how to get a tree sorted starting not from root, but from required started point of the tree getting a full path to the required finish. Best regards, Anton Nikiforov. ---(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: [GENERAL] Hierarchical queries
Hello Oleg and thanks for the link, but i could not understand how to get path from one point of the tree to another? Anyway thanks :) Best regards, Anton OB Look at contrib/ltree OB http://www.sai.msu.su/~megera/postgres/gist/ltree OB Oleg OB On Fri, 9 Jan 2004 [EMAIL PROTECTED] wrote: Hello everybody! Does someone know how to build hierarchical queries to the postgresql? I have a table with tree in it (id, parent) and need to find a way from any point of the tree to any other point. And i would like to have a list of all steps from point A to point B to make some changes on each step (this is required by the algorythm). Here is an example: treetable (where tree is stored): idparent data int4 int4 varchar(255) 0 0root 1 0root's chield 1 2 0root's chield 2 3 1root's chield 1 chield 1 4 1root's chield 1 chield 2 5 2root's chield 2 chield 1 6 2root's chield 2 chield 2 And i want to get something like this: start point root's chield 2 chield 2 finish root's chield 1 chield 1 And the result i need: idparent data 6 2root's chield 2 chield 2 2 0root's chield 2 0 0root 1 0root's chield 1 4 1root's chield 1 chield 2 i know that it is possible in Oracle but what about postgres? Best regards, Anton Nikiforov ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings OB Regards, OB Oleg OB _ OB Oleg Bartunov, sci.researcher, hostmaster of AstroNet, OB Sternberg Astronomical Institute, Moscow University (Russia) OB Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ OB phone: +007(095)939-16-83, +007(095)939-23-83 OB ---(end of broadcast)--- OB TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Hierarchical queries
Hello Oleg! There is no data yet, i'm just planning to start a new project :) Text labels are just fine and i red the documentation from the top to the very end a few times and found the way to use your module, but using it will not as beautiful as i was planning mathematicaly. You know i have (planning to have) a tree like: idparent data int4 int4 varchar(255) 0 0root 1 0root's chield 1 2 0root's chield 2 3 1root's chield 1 chield 1 4 1root's chield 1 chield 2 5 2root's chield 2 chield 1 6 2root's chield 2 chield 2 And to find a way from the record with id #6 to the record with id #3 WITH YOUR MODULE: I have to find Lowest Common Ancestor (lca) Then to find a path from id #6 to lca Then to find a path from lca to id#3 Then combine this pathes (remember that i need all steps from id #6 to id #3) And then run a special code to update all needed data (create records in different tables) IN MY BRAINS: I just need to have function that will rotate a tree and make id #6 the root element and then select a path from root (id#6) to desired id #3. As i think somebody did this already. And i'm not the first who is trying to find out the code. If i'm too stupid to understand the ability of your module - just give me a direction (i did installed your module and currently playing with it, so maybe my stupidity will become wiser and wiser in the nearest feature) : Best regards, Anton OB On Sat, 10 Jan 2004 [EMAIL PROTECTED] wrote: Hello Oleg and thanks for the link, but i could not understand how to get path from one point of the tree to another? OB have you read documentation ? Get all childrens - ltree @ ltree, OB for example: OB ltreetest=# select path from test where path @ 'Top.Science'; OB path OB OB Top.Science OB Top.Science.Astronomy OB Top.Science.Astronomy.Astrophysics OB Top.Science.Astronomy.Cosmology OB (4 rows) OB You should provide us example of your data and query, so we could help you. Anyway thanks :) Best regards, Anton OB Look at contrib/ltree OB http://www.sai.msu.su/~megera/postgres/gist/ltree OB Oleg OB On Fri, 9 Jan 2004 [EMAIL PROTECTED] wrote: Hello everybody! Does someone know how to build hierarchical queries to the postgresql? I have a table with tree in it (id, parent) and need to find a way from any point of the tree to any other point. And i would like to have a list of all steps from point A to point B to make some changes on each step (this is required by the algorythm). Here is an example: treetable (where tree is stored): idparent data int4 int4 varchar(255) 0 0root 1 0root's chield 1 2 0root's chield 2 3 1root's chield 1 chield 1 4 1root's chield 1 chield 2 5 2root's chield 2 chield 1 6 2root's chield 2 chield 2 And i want to get something like this: start point root's chield 2 chield 2 finish root's chield 1 chield 1 And the result i need: idparent data 6 2root's chield 2 chield 2 2 0root's chield 2 0 0root 1 0root's chield 1 4 1root's chield 1 chield 2 i know that it is possible in Oracle but what about postgres? Best regards, Anton Nikiforov ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings OB Regards, OB Oleg OB _ OB Oleg Bartunov, sci.researcher, hostmaster of AstroNet, OB Sternberg Astronomical Institute, Moscow University (Russia) OB Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ OB phone: +007(095)939-16-83, +007(095)939-23-83 OB ---(end of broadcast)--- OB TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster OB Regards, OB Oleg OB _ OB Oleg Bartunov, sci.researcher, hostmaster of AstroNet, OB Sternberg Astronomical Institute, Moscow University (Russia) OB Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ OB phone: +007(095)939-16-83, +007(095)939-23-83 , IT .: +7 095 7814200 : +7 095 7814201 Mail: [EMAIL PROTECTED] Web: www.loteco.ru ---(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
[GENERAL] Hierarchical queries
Hello everybody! Does someone know how to build hierarchical queries to the postgresql? I have a table with tree in it (id, parent) and need to find a way from any point of the tree to any other point. And i would like to have a list of all steps from point A to point B to make some changes on each step (this is required by the algorythm). Here is an example: treetable (where tree is stored): idparent data int4 int4 varchar(255) 0 0root 1 0root's chield 1 2 0root's chield 2 3 1root's chield 1 chield 1 4 1root's chield 1 chield 2 5 2root's chield 2 chield 1 6 2root's chield 2 chield 2 And i want to get something like this: start point root's chield 2 chield 2 finish root's chield 1 chield 1 And the result i need: idparent data 6 2root's chield 2 chield 2 2 0root's chield 2 0 0root 1 0root's chield 1 4 1root's chield 1 chield 2 i know that it is possible in Oracle but what about postgres? Best regards, Anton Nikiforov ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
