Re: [HACKERS] Tablespaces
Christopher Kings-Lynne wrote: I'm interested if anyone is using tablespaces? Do we have any actual reports of people actually using them, to advantage, in the field?? Maybe the next postgresql.org survey could be on tablespace usage? Chris I have seen that tablespaces are widely used and highly appreciated. I have not seen people complaining about the current implementation. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Source Code Help Needed
Tom, Thanks a ton again, and, here's another problem that has me really puzzled-I'm starting with a fresh install of pgsql-8.0.1, and make 3 changes-1.) src/include/nodes/relation.h, Add a new Variable, hutz_idx_benefit to IndexOptInfo
typedef struct IndexOptInfo{../* Per IndexScan benefit, More than 1 indexscan maybe used for 1 tablescan ex. w/ OR */Costhutz_idx_benefit;..} IndexOptInfo;
2.) src/backend/optimizer/path/costsize.c, cost_index(), assign value to index-hutz_idx_benefitrun_cost += indexTotalCost - indexStartupCost;index-hutz_idx_benefit = run_cost;
elog(NOTICE,cost_index():index-indexoid=%u
index-hutz_idx_benefit=%.2f, index-indexoid,
index-hutz_idx_benefit);3.)
src/backend/optimizer/path/orindxpath.c, best_or_subclause_indexes(),
Read the value(s) of index-indexoid and index-hutz_idx_benefit/* Gather info for each OR subclause */foreach(slist, subclauses){...infos = lappend(infos, best_indexinfo);...}
/* DEBUG */ListCell *l;
int count=0;foreach(l, infos){
IndexOptInfo *index = (IndexOptInfo *) lfirst(l);elog(NOTICE,best_or_subclause_indexes():infos
c=%i: indexoid=%u hutz_idx_benefit=%.2f, count, index-indexoid,
index-hutz_idx_benefit);count++;}...
pathnode-indexinfo = infos; /* indexinfo' is a list of IndexOptInfo nodes, one per scan to be performed */
So, basically I have added a new variable alongside indexoid which
is the run_cost of one of the index scans if there are multiple index
scans such as in the case of OR subclauses for 1 table.
Now, I do a complete build and run two queries with OR subclauses as follows-
tpcd=# select s_suppkey from supplier where (s_suppkey125 and
s_suppkey128) or (s_suppkey175 and s_suppkey185) or
(s_suppkey200 and s_suppkey215);
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.02
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.06
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970 hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=1: indexoid=186970 hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=2: indexoid=186970 hutz_idx_benefit=2.09
On the second occasion, I change the order of the OR subclauses...
tpcd=# select s_suppkey from supplier where (s_suppkey200 and
s_suppkey215) or (s_suppkey175 and s_suppkey185) or
(s_suppkey125 and s_suppkey128);
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.09
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.06
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970 hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=1: indexoid=186970 hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=2: indexoid=186970 hutz_idx_benefit=2.02
>From the output, it can be seen that when I try to read the value(s),
the last value is stored in all the positions of the List infos which
is later assigned to (IndexPath) pathnode-indexinfo which is a
List of IndexOptInfo nodes, one per scan to be performed. Actually,
it seems all the pointers in the List indexinfo or infos are
pointing to the same object.
So,
Ques 1) Is my assumption correct that IndexPath-indexinfo should
contain all distinct IndexOptInfo structs with one for each of the
scans to be performed? If not, then why do we have multiple pointers to
the same object?
(Ques 2) How can this be fixed? Is this a bug or something else?
(Ques 3) Is this a problem in other areas as well, for example the following query doesn't give the expected values as well-
select s_suppkey, c_custkey from supplier, customer where
s_suppkey125 and s_suppkey128 and c_custkey125 and
c_custkey135 and c_custkey=s_suppkey;
I appreciate all the help of this group,
Thanks,
On 5/25/05, Tom Lane [EMAIL PROTECTED] wrote: Vikram Kalsi [EMAIL PROTECTED] writes: So, I suppose that during the query planning and optimization stage,
the value of the original variables in the plan are somehow copied to the plan which is finally returned inside pg_plan_query(). Look in createplan.c --- there are a couple places in there you need to
fix.
regards, tom lane
Re: [HACKERS] Tablespaces
On Fri, 2005-06-03 at 08:41 +0200, Hans-Jrgen Schnig wrote: Christopher Kings-Lynne wrote: I'm interested if anyone is using tablespaces? Do we have any actual reports of people actually using them, to advantage, in the field?? Maybe the next postgresql.org survey could be on tablespace usage? I have seen that tablespaces are widely used and highly appreciated. I have not seen people complaining about the current implementation. My recent experience is that it is mostly the new Windows users who are using 8.0. Yes, there are people using Tablespaces on those. The only complaint is why can't you move pg_xlog easily also? The migration to 8.0 for a many users appears very slow, with many PostgreSQL users still planning to enter production on 7.3 and 7.4. This has much to do with supported versions of integrated products, rather than any lack of interest in 8.0. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Tablespaces
On Fri, 2005-06-03 at 11:17 +0800, Christopher Kings-Lynne wrote: Maybe the next postgresql.org survey could be on tablespace usage? Could we plan a more comprehensive survey, with more than one question? Judging by the number of people who fill out surveys, we would still get thousands of replies if we asked them 10 questions instead of 1. That would allow us to cross-correlate the answers to gain an even better picture of what is happening and what is wanted. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Code for PostgreSQL?
Dear all, I completed my Computer Science MSc thesis in Athens University of Economics and Business, A.U.E.B (under the supervision of Ass.Prof. V.Vassalos). Its subject was DAIMON: Data Integration for a Mobile Network, i.e. mobile peers integrating data from other peers. We used PostgreSQL v7.4.2 as our base and wrote several lines of backend code that basically do the following: All peers share the same schema but actually hold data for different tables (Local As View paradigm). The code parses a submitted query (Query structure), locates all references to remotely stored data (currently distinct tables) and produces a Query* for each remote peer (or more to avoid Cartesian products) which is then sent to it (in string format,nodeToString) using an SRF and dblink. Returned tuples are stored into local tables (later used as cache) and a residual query (created during initial parsing) is executed on these tables. We thought that some parts of our code may be useful for others and if so we would like to contribute to the source code tree. More specifically we are thinking of isolating and contributing the following pieces of code: 1) A complete SRF example. Though a lot of information is available for this, I could not find (at the time of programming) any complete example (as some others too, I think) and had to write my own. The code is largely based on dblink related code. 2) The extended dblink mechanism. Our base for communicating with other peers was dblink but we also used libevent to enforce certain policies regarding the availability of mobile nodes. I've read the following posts on libevent but I would like to ask anyway. http://archives.postgresql.org/pgsql-hackers/2003-07/msg00481.php http://archives.postgresql.org/pgsql-hackers/2004-03/msg00393.php 3) Any part of the above described mechanism that seems interesting to you. We know this is an academic project and may not straightforwardly apply to common PostgreSQL's applications but we thing you should get informed. We would be pleased to provide any additional information. Best regards, Ntinos Katsaros ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] pg_stats not getting updated....
wht i wanted to do is... identify the tables which are getting used simultaneously... so that i can move them to different tablespaces for that i tried to do sampling of "pg_statio_user_tables" for top 20 tables...(in terms of usage)... so that i know how much io is being done... for different tables and when now the problem is... pg_statio_user_tables is not getting updated... at least wht i am able to make out of documentation is they should be updated regularly at each commit... but i am doing lots of commits in my test application also docs state that withing each transaction block postgres tries to give the same stats forget abt transaction blocks.. i even tried.. disconnecting and then reconnecting my sampling application every two mins... but no use... each time i am getting same stats...(only 4 updates in 30mins). one more thing that i noted is each time i run analyze pg_statio_user_tables is updated plz note that all pg_stat* tables are not getting updated not just pg_statio* i posted in general mailing list but no satisfying reply so i thought maybe u all can tell whts happening.. thx Himanshu Discover Yahoo! Find restaurants, movies, travel & more fun for the weekend. Check it out!
Re: [HACKERS] Google's Summer of Code ...
On Thursday 02 June 2005 18:52, Vikram Kalsi wrote:
I am a MSEE student at Penn State (University Park), for the past few
months I have been working on modifying parts of PostgreSQL for my
research work. I doubt if my current work would serve any purpose for
pgsql since it is experimental and research oriented, but all the
same, I have gained familiarity with parts of pgsql. I'm interested in
Google's Summer of Code, but I would definitely need help, starting
with selection of an idea to work on. So, if anybody from PostgreSQL
would like to support this, then please get in touch with me as early
as possible.
I think the easyist thing is to look over the TODO list and see if there are 1
or more items that you might be interested in working on, and then either
mentioning them here or submitting them to google.
By the way, I didn't see PostgreSQL in the list of Participating
Organizations on http://code.google.com/summerofcode.html?
Marc has contacted google with intentions of joining that list, I would guess
the more people who submit postgresql related proposals, the more likely it
would be that we end up joining.
Thanks and Regards,
-Vikram Kalsi
MSEE PennState
vzk101 at psu dot edu
www.personal.psu.edu/vzk101
On 5/25/05, Tom Lane [EMAIL PROTECTED] wrote:
Vikram Kalsi [EMAIL PROTECTED] writes:
So, I suppose that during the query planning and optimization stage,
the value of the original variables in the plan are somehow copied to
the plan which is finally returned inside pg_plan_query().
Look in createplan.c --- there are a couple places in there you need to
fix.
regards, tom lane
---(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
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] pg_stats not getting updated....
i just noted one more thing... pg_stat_get_db_blocks_fetched/hit is getting updated but pg_stat_get_blocks_fetched/hit are not getting updated. why is this happening.. Regards Himanshu --- Himanshu Baweja [EMAIL PROTECTED] wrote: wht i wanted to do is... identify the tables which are getting used simultaneously... so that i can move them to different tablespaces for that i tried to do sampling of pg_statio_user_tables for top 20 tables...(in terms of usage)... so that i know how much io is being done... for different tables and when now the problem is... pg_statio_user_tables is not getting updated... at least wht i am able to make out of documentation is they should be updated regularly at each commit... but i am doing lots of commits in my test application also docs state that withing each transaction block postgres tries to give the same stats forget abt transaction blocks.. i even tried.. disconnecting and then reconnecting my sampling application every two mins... but no use... each time i am getting same stats...(only 4 updates in 30mins). one more thing that i noted is each time i run analyze pg_statio_user_tables is updated plz note that all pg_stat* tables are not getting updated not just pg_statio* i posted in general mailing list but no satisfying reply so i thought maybe u all can tell whts happening.. thx Himanshu - Discover Yahoo! Find restaurants, movies, travel more fun for the weekend. Check it out! __ Discover Yahoo! Get on-the-go sports scores, stock quotes, news and more. Check it out! http://discover.yahoo.com/mobile.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] need help
hi, i want to know how CREATE table (creating a relation) works in Postgres.where can i find this in source code. also i want to know how postgres parser the input(create table) and how is this connected to the create table source code files. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Source Code Help Needed
Vikram Kalsi [EMAIL PROTECTED] writes: ... tpcd=# select s_suppkey from supplier where (s_suppkey125 and s_suppkey128) or (s_suppkey175 and s_suppkey185) or (s_suppkey200 and s_suppkey215); ... Actually, it seems all the pointers in the List indexinfo or infos are pointing to the same object. Given that query, it's not too surprising that the only relevant index for all the OR clauses would be the one on s_suppkey ... if you want to look at *all* the indexes on the relation, you need to scan the parent RelOptInfo's indexlist. You didn't say what it was you hoped to accomplish, but perhaps the technique requires a more complicated query to be of any use? BTW, best_or_subclause_indexes (and indeed most of orindxpath.c) is gone in CVS tip; all that code has been rewritten for 8.1. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_stats not getting updated....
Himanshu Baweja [EMAIL PROTECTED] writes: i just noted one more thing... pg_stat_get_db_blocks_fetched/hit is getting updated but pg_stat_get_blocks_fetched/hit are not getting updated. That's pretty difficult to credit after looking at the pgstat.c code: every incoming blocks_fetched count is added to both per-table and per-database stats. I wonder if you are looking at the wrong per-table entries? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_stats not getting updated....
--- Tom Lane [EMAIL PROTECTED] wrote: That's pretty difficult to credit after looking at the pgstat.c code: every incoming blocks_fetched count is added to both per-table and per-database stats. I wonder if you are looking at the wrong per-table entries? i am 100% sure SELECT pg_stat_get_db_blocks_fetched(764755937), pg_stat_get_db_blocks_hit(764755937); gives be constantly increasing stats and SELECT relname,heap_blks_read from pg_statio_user_tables order by heap_blks_read DESC LIMIT 15; is still showing me all zero 4 mins into the test until i first vacuum analyze is done just think abt this if we get these stats how easily we can decide the division of tables in tablespaces just write a simple program which will collect the data every t mins... analyze it and move them to diff tablespaces... is there any other way of finding table usage??? thx a lot tom Himanshu __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_stats not getting updated....
Himanshu Baweja [EMAIL PROTECTED] writes: SELECT pg_stat_get_db_blocks_fetched(764755937), pg_stat_get_db_blocks_hit(764755937); gives be constantly increasing stats and SELECT relname,heap_blks_read from pg_statio_user_tables order by heap_blks_read DESC LIMIT 15; is still showing me all zero 4 mins into the test until i first vacuum analyze is done Um, looking at the view definition, heap_blks_read is the *difference* between blocks_fetched and blocks_hit ... is it possible your test is testing a 100%-cached situation, such that those two numbers increase in lockstep? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Developer Network
Dear All, PGDN has a new treeview face. I would like to know your opinion on the current state of the website. If there are people who have ideas about different topics please dont hesitate to let me know. The current development site is: http://www.truesoftware.net:8081/pgdn Regards, Gevik.
Re: [HACKERS] NOLOGGING option, or ?
Alon Goldshuv wrote: I've been following this thread, and I'm a little confused. Could you possibly clarify what you mean, by providing a couple of lines of input as it would be formatted with escape processing turned off - containing a text field with an embedded newline and tab and a null field. yeah, this is all a bit confusing, but I *hope* I can clarify things here as I think I got a better understanding now. The basic problem with this thread is that it started with _conclusions_ (we need a LOAD DATA command, escapes are broken), and not with statements of fact (we need another way of specifying escapes, we have performance improvements). Any discussion that starts with conclusions instead of facts is bound to have this problem, and it often happens when a group discusses among themselves, outside the community, and appears with the conclusions, thinking they are helping us by not going into the details. As you can see, lack of facts actually hampers the discussion. What has me particularly concerned is someone saying that loading C:\TMP must be broken, and not understanding that the doubling of escapes is a major requirement to have data loaded reliably. Now, you can argue that a different escape should be possible, or that some other escape syntax could be used, but the existing mechanism is clearly 100% reliable when used properly and not broken. A quick email asking why C:\TMP doesn't load in properly would have yielded a much clearer conversation about why escaping is required in our current system, and the other options that should be explored. Saying escapes are broken and here is the fix really didn't get very far. I recommend you just start a new thread, with a new topic, and head in the _facts_ direction. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Developer Network
On Fri, Jun 03, 2005 at 05:57:32PM +0200, Gevik Babakhani wrote: Dear All, PGDN has a new treeview face. I would like to know your opinion on the current state of the website. It's empty. Could you tell us a little bit about what you want to put there? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] NOLOGGING option, or ?
Bruce, Point taken. Now, you can argue that a different escape should be possible, or that some other escape syntax could be used, but the existing mechanism is clearly 100% reliable when used properly and not broken. I think that having an option for another escape syntax (such as using ESCAPE AS clause in delimited format (non-csv) COPY) with a default of '\\' is a good compromise that will allow users to escape their data (like COPY currently is), or by specifying another escape character allow all of their backslashes to be treated as data. I'll start a new discussion about it on a new thread soon. Thx, Alon. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_stats not getting updated....
--- Tom Lane [EMAIL PROTECTED] wrote: Himanshu Baweja [EMAIL PROTECTED] writes: SELECT pg_stat_get_db_blocks_fetched(764755937), pg_stat_get_db_blocks_hit(764755937); gives be constantly increasing stats and SELECT relname,heap_blks_read from pg_statio_user_tables order by heap_blks_read DESC LIMIT 15; is still showing me all zero 4 mins into the test until i first vacuum analyze is done Um, looking at the view definition, heap_blks_read is the *difference* between blocks_fetched and blocks_hit ... is it possible your test is testing a 100%-cached situation, such that those two numbers increase in lockstep? regards, tom lane both blocks fetched and block reads are zero... had already checked for that = block hit is also zero... any ideas now... thx Himanshu __ Discover Yahoo! Have fun online with music videos, cool games, IM and more. Check it out! http://discover.yahoo.com/online.html ---(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] PostgreSQL Developer Network
The idea behind the PGDN is to provide a knowledgebase for pg developers. I was thinking about gathering howto's, articles, questions and answers and Of cource for those soles who are just starting programming pg to provide a getting started portal... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Fetter Sent: Friday, June 03, 2005 6:31 PM To: Gevik Babakhani Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] PostgreSQL Developer Network On Fri, Jun 03, 2005 at 05:57:32PM +0200, Gevik Babakhani wrote: Dear All, PGDN has a new treeview face. I would like to know your opinion on the current state of the website. It's empty. Could you tell us a little bit about what you want to put there? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] NOLOGGING option, or ?
Alon Goldshuv wrote: Bruce, Point taken. Now, you can argue that a different escape should be possible, or that some other escape syntax could be used, but the existing mechanism is clearly 100% reliable when used properly and not broken. I think that having an option for another escape syntax (such as using ESCAPE AS clause in delimited format (non-csv) COPY) with a default of '\\' is a good compromise that will allow users to escape their data (like COPY currently is), or by specifying another escape character allow all of their backslashes to be treated as data. I'll start a new discussion about it on a new thread soon. Yep, great. Right now we only support single-byte escapes (and delimiters), so there should be a discussion if multiple byte values are useful too. Also, there was discussion of what the default should be. I don't think there is any agreement to change the existing defaults. You might argue for a new mode that sets certain defaults to be easier to load, but I am afraid of a new format that isn't 100% reliable, because it assumes that some sequence of bytes will never appear in the data. Anyway, these are ideas you can consider when making a proposal. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[HACKERS] executing OS programs from pg
Dear people, Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Does such functionality exist or do I have to write my own trigger function in C. Reagrds, Gevik.
Re: [HACKERS] NOLOGGING option, or ?
Bruce, Is there a good source of multi-byte copy data test cases? What is currently done to test the trans-coding support? (where client and server encodings are different) I notice that the regression data in the CVS version of postgres does not seem to include cases other than the ASCII data, is there another source of data/cases we're missing? Also - Alon's looking into this, but it would appear that the presumption on EOL for two-byte encodings is 0x0a+0xNN, where 0x0a is followed by any byte. Similar for other current control characters (escape, delimiter). Is there a definition of format and semantics for COPY with 2-byte encodings we should look at? I've looked at the code and the docs like sql-copy.html and the question is relevant because of the following case: if newline were defined as 0x0a+0x00 as opposed to 0x0a+0xNN where N is arbitrary, we could parse using 16-bit logic. however if newline were defined as 0x0a+0xNN, we must use byte-wise parsing TIA - Luke ---(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] executing OS programs from pg
Gevik babakhani [EMAIL PROTECTED] writes: Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Use any of the untrusted PLs to execute system() or the like. Whether this is a good idea or not is a different question --- there are excellent reasons why it is a *bad* idea to execute outside-the-database actions from within a trigger. Mainly that the actions won't be undone if the transaction later rolls back, and now your database state is inconsistent with outside-the-database state. See the list archives for many past discussions of this point and safer ways to design your application. (BTW, this is hardly material for -hackers.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] executing OS programs from pg
On Fri, Jun 03, 2005 at 20:56:44 +0200, Gevik babakhani [EMAIL PROTECTED] wrote: Dear people, Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Does such functionality exist or do I have to write my own trigger function in C. You would have to write your own trigger, though you could use other languages than C (e.g. untrusted perl). Another option is to communicate with an external program using notify. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL Developer Network
It's great that you want to help in this area, but wouldn't be a better idea to revamp techdocs.postgresql.org? Gevik babakhani wrote: The idea behind the PGDN is to provide a knowledgebase for pg developers. I was thinking about gathering howto's, articles, questions and answers and Of cource for those soles who are just starting programming pg to provide a getting started portal... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Fetter Sent: Friday, June 03, 2005 6:31 PM To: Gevik Babakhani Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] PostgreSQL Developer Network On Fri, Jun 03, 2005 at 05:57:32PM +0200, Gevik Babakhani wrote: Dear All, PGDN has a new treeview face. I would like to know your opinion on the current state of the website. It's empty. Could you tell us a little bit about what you want to put there? Cheers, D ---(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] NOLOGGING option, or ?
Luke Lonergan wrote: Bruce, Is there a good source of multi-byte copy data test cases? What is currently done to test the trans-coding support? (where client and server encodings are different) I notice that the regression data in the CVS version of postgres does not seem to include cases other than the ASCII data, is there another source of data/cases we're missing? Also - Alon's looking into this, but it would appear that the presumption on EOL for two-byte encodings is 0x0a+0xNN, where 0x0a is followed by any byte. Similar for other current control characters (escape, delimiter). Is there a definition of format and semantics for COPY with 2-byte encodings we should look at? I've looked at the code and the docs like sql-copy.html and the question is relevant because of the following case: if newline were defined as 0x0a+0x00 as opposed to 0x0a+0xNN where N is arbitrary, we could parse using 16-bit logic. however if newline were defined as 0x0a+0xNN, we must use byte-wise parsing We have two and three-byte encodings, so 16-bit seems like it wouldn't work. I am not aware of any specs except the C code itself. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] NOLOGGING option, or ?
Bruce, We have two and three-byte encodings, so 16-bit seems like it wouldn't work. I am not aware of any specs except the C code itself. Ok - no problem. How about test data and cases? I see the SQL encoding examples used in src/test/regress/sql for testing encoding in SQL, but are there regressions for QA/test of multi-byte encoding support? If not, that's OK, but it would save us time if some were already written. WRT the COPY command, I'd like to have regressions that test the input of matched client/server encodings with different (standardized) multi-byte control characters. The current code seems to allow for an arbitrary second byte in control characters, so if we made a statement about control character support, I think it would be ctl-byteany-byte...mblen...any-byte is allowed for specification of control characters (newline, delimiter). Luke ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] NOLOGGING option, or ?
Luke Lonergan wrote: Bruce, We have two and three-byte encodings, so 16-bit seems like it wouldn't work. I am not aware of any specs except the C code itself. Ok - no problem. How about test data and cases? I see the SQL encoding examples used in src/test/regress/sql for testing encoding in SQL, but are there regressions for QA/test of multi-byte encoding support? If not, that's OK, but it would save us time if some were already written. No, I don't think so, but the good news is that the existing code has always worked flawlessly. WRT the COPY command, I'd like to have regressions that test the input of matched client/server encodings with different (standardized) multi-byte control characters. Makes sense, but how do we know what encodings the client supports? We would need some tests for that. The current code seems to allow for an arbitrary second byte in control characters, so if we made a statement about control character support, I think it would be ctl-byteany-byte...mblen...any-byte is allowed for specification of control characters (newline, delimiter). I have no idea what you are talking about. Again, give me facts about what we currently don't do and what you want to do. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] NOLOGGING option, or ?
Bruce, I have no idea what you are talking about. Again, give me facts about what we currently don't do and what you want to do. Currently: - No statement of multi-byte control character format - No tests to define or prove works flawlessly or identify when something breaks the current operational state Desired: - Clear statement of multi-byte control character format - Tests that define what works flawlessly means - Luke ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] executing OS programs from pg
Look at peter eisentraut's procedural language PL/sh It's on pgfoundry. ... John -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Saturday, June 04, 2005 5:16 AM To: Gevik babakhani Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] executing OS programs from pg Gevik babakhani [EMAIL PROTECTED] writes: Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Use any of the untrusted PLs to execute system() or the like. Whether this is a good idea or not is a different question --- there are excellent reasons why it is a *bad* idea to execute outside-the-database actions from within a trigger. Mainly that the actions won't be undone if the transaction later rolls back, and now your database state is inconsistent with outside-the-database state. See the list archives for many past discussions of this point and safer ways to design your application. (BTW, this is hardly material for -hackers.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Precedence of %
Does anyone understand why the precedence of % is strange: test= select -25 % -10; ?column? -- -35 (1 row) test= select -25 % (-10); ?column? -- -5 (1 row) Is it treating the first as -25 - 10? Why? Why are parens necessary to get the right answer? I see this in gram.y: %left '+' '-' %left '*' '/' '%' Look at this: test= select -25 + -10; ?column? -- -35 (1 row) test= select -25 * -10; ?column? -- 250 (1 row) test= select -25 / -10; ?column? -- 2 (1 row) test= select -25 % -10; ?column? -- -35 (1 row) Only the '%' case looks wrong. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] need help
vamsi krishna wrote: hi, i want to know how CREATE table (creating a relation) See DefineRelation() in backend/commands/tablecmds.c, and the routines it calls. also i want to know how postgres parser the input(create table) and how is this connected to the create table source code files. See the CreateStmt production in backend/parser/gram.y (circa line 1509 in current sources). CREATE TABLE is a utility statement, so a high-level view of the processing is: - the query string is scanned (scan.l) and parsed (gram.y), producing a raw parse tree - in the analysis phase, transformCreateStmt() does a few simple transformations of the raw parse tree and produces a Query representing the CREATE TABLE utility statement - ProcessUtility() in backend/tcop/utility.c invokes DefineRelation() -Neil ---(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] Precedence of %
Bruce Momjian pgman@candle.pha.pa.us writes: Does anyone understand why the precedence of % is strange: test= select -25 % -10; It's treating it as ((-25) %) - (10), which is probably not so surprising given the relative precedence of % and - ... though I have to admit I'm not totally clear why it's not (-(25 %)) - (10) instead. We could maybe hack the precedence of the productions for prefix/postfix %, but I wonder if it wouldn't be smarter to remove 'em altogether (along with the two existing unary % operators). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
