[HACKERS] Connect from background worker thread to database
Hello everyone. I am new to writing extensions to PostgreSQL. And I am not familiar with the C language (My professional languages: Ruby, Golang, Java). But still want to write an extension for PostgreSQL. After articles of Michael Paquier about background workers in PostgreSQL, I realized what I can create what I always want - web interface for this database. This interface will provide some info about database (like in couchbase: http://docs.couchbase.com/couchbase-manual-2.0/images/web-console-cluster-overview.png ) and can be used as REST API. Right now this server should work as JSON API and I will build on top of this API web interface on some JS framework. My initiative right now is working as web server just with some hello string. But I don't understand how should I connect to database from threads in web server. All source you can find here: https://github.com/le0pard/pg_web This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. Thanks in advance, Alexey
Re: [HACKERS] Connect from background worker thread to database
Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
If I remove comment from BackgroundWorkerInitializeConnection https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L179 and comment InitPostgres https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L98 , I have the same errors in log: 2013-11-24 13:35:24 UTC ERROR: stack depth limit exceeded 2013-11-24 13:35:24 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 13:35:24 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; 2013-11-24 13:35:25 UTC LOG: worker process: pg_web (PID 1957) exited with exit code 1 2013-11-24 13:35:26 UTC LOG: starting background worker process pg_web 2013-11-24 13:35:26 UTC INFO: Start web server on port 8080 If I change InitPostgres to BackgroundWorkerInitializeConnection and leave BackgroundWorkerInitializeConnection in bgw_main, I will have this error: 2013-11-24 13:39:58 UTC ERROR: invalid processing mode in background worker 2013-11-24 13:39:58 UTC LOG: worker process: pg_web (PID 2719) exited with exit code 1 2013-11-24 13:39:59 UTC LOG: starting background worker process pg_web Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund
[HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. -- Alexey Vasiliev
Re: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
On Mon, Nov 25, 2013 at 2:10 AM, Alexey Vasiliev leopard...@inbox.ru wrote: Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. You should not need an extra PQconnectdb to connect to a database using a background worker for your case as far as I understood. By using that you are requiring a connection to database without using the internal infrastructure in place, meaning that your bgworker is not connected to the database server from the inside but from the outside, like a normal client would do. Aren't to trying to have a background worker connected to a database when it is initialized with BgWorkerStart_PostmasterStart? Bgworkers using this start-up mode are not eligible to initialize database connections. Please use either BgWorkerStart_ConsistentState or BgWorkerStart_RecoveryFinished and BackgroundWorkerInitializeConnection will allow a connection to database without any extra work. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Понедельник, 25 ноября 2013, 8:31 +09:00 от Michael Paquier michael.paqu...@gmail.com: On Mon, Nov 25, 2013 at 2:10 AM, Alexey Vasiliev leopard...@inbox.ru wrote: Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de : Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. You should not need an extra PQconnectdb to connect to a database using a background worker for your case as far as I understood. By using that you are requiring a connection to database without using the internal infrastructure in place, meaning that your bgworker is not connected to the database server from the inside but from the outside, like a normal client would do. Aren't to trying to have a background worker connected to a database when it is initialized with BgWorkerStart_PostmasterStart? Bgworkers using this start-up mode are not eligible to initialize database connections. Please use either BgWorkerStart_ConsistentState or BgWorkerStart_RecoveryFinished and BackgroundWorkerInitializeConnection will allow a connection to database without any extra work. -- Michael Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. Thanks again. -- Alexey Vasiliev
Re: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev leopard...@inbox.ru wrote: Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. There is a design problem with your application when trying to create threads with mg_start in order to grab events. Note that I am not familiar with mongoose, but with bgworkers you cannot simply create new threads that would be able to connect to server concurrently. A model that would be more suited with bgworkers would be something like: - Handle event messages by for example opening a port or monitoring the events on a single bgworker launched by bgw_main, with for example a message queue model (possible with 9.3). Connection to database would be done with a single connection, managed within the loop of bgw_main. - Create a new bgworker dynamically each time a new event comes in (possible only with 9.4~). The top work would be done by a central bgworker initialized with server, which would treat events and kick new slave workers when necessary. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Понедельник, 25 ноября 2013, 13:31 +09:00 от Michael Paquier michael.paqu...@gmail.com: On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev leopard...@inbox.ru wrote: Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. There is a design problem with your application when trying to create threads with mg_start in order to grab events. Note that I am not familiar with mongoose, but with bgworkers you cannot simply create new threads that would be able to connect to server concurrently. A model that would be more suited with bgworkers would be something like: - Handle event messages by for example opening a port or monitoring the events on a single bgworker launched by bgw_main, with for example a message queue model (possible with 9.3). Connection to database would be done with a single connection, managed within the loop of bgw_main. - Create a new bgworker dynamically each time a new event comes in (possible only with 9.4~). The top work would be done by a central bgworker initialized with server, which would treat events and kick new slave workers when necessary. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Thanks. I will look how to do this in the best way by your suggestions. -- Alexey Vasiliev
Re: [HACKERS] CONNECT BY and WITH ...
On Tue, Jul 08, 2008 at 07:28:36AM +0200, Hans-Juergen Schoenig wrote: good morning everybody, i know that this is really a hot potato on the mailing list but i think it is useful to discuss this issue. in the past few months we have been working with a customer to improve evgen's CONNECT BY patch. as we have a nice and promising WITH RECURSIVE patch the original CONNECT BY codes are pretty obsolete. however, in the past view weeks I have been asked more than once if it is possible to use the current with patch and add the parser support for CONNECT BY to it. so, people had the choice whether to go with CONNECT BY syntax (to be Oracle compliant, which is important) or ANSI SQL compliant (which is important as well). how are the feelings towards an improvement like that? i would ease the pain of many people for sure. This has been discussed to death several times. We're only going to do the WITH RECURSIVE (i.e. the SQL standard) tree handling. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CONNECT BY and WITH ...
good morning everybody, i know that this is really a hot potato on the mailing list but i think it is useful to discuss this issue. in the past few months we have been working with a customer to improve evgen's CONNECT BY patch. as we have a nice and promising WITH RECURSIVE patch the original CONNECT BY codes are pretty obsolete. however, in the past view weeks I have been asked more than once if it is possible to use the current with patch and add the parser support for CONNECT BY to it. so, people had the choice whether to go with CONNECT BY syntax (to be Oracle compliant, which is important) or ANSI SQL compliant (which is important as well). how are the feelings towards an improvement like that? i would ease the pain of many people for sure. best regards, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CONNECT BY PRIOR
On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote: Hey Simon, I'm doing some research into recursive query planning in terms of theory as-well-as actual implementation in other RDBMS. Let me get back to you when I have some more definitive info. My first reaction would be to have a sort of Repeat node, with two subnodes, the Tail and the Loop. The procedure would be to extract a tuple from the Tail (optionally returning it). Then put that tuple as the input to the Loop and start pulling tuples out of that. Problem is, those new tuples may have to be sent through the loop again so you have a buffering problem. But it seems a fairly generic way of dealing with it. Ofcourse, once you've done that, you might be getting very close to a Turing complete executor, no? :) Have a nice day, On 11/14/05, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote: I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Sounds interesting. What approach are you taking to the plan shape? The current approach would be to have additional plan nodes for each join. Coping with a dynamic number of operations will do interesting things in the planner. I face a similar dynamic problem with joins to partitioned tables. Do you have any thoughts about this area? Best Regards, Simon Riggs -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpkqMLlVNYBM.pgp Description: PGP signature
Re: [HACKERS] CONNECT BY PRIOR
On Tue, Nov 15, 2005 at 04:13:47PM +0200, Hannu Krosing wrote: On T, 2005-11-15 at 09:53 +0100, Martijn van Oosterhout wrote: On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote: Hey Simon, I'm doing some research into recursive query planning in terms of theory as-well-as actual implementation in other RDBMS. Let me get back to you when I have some more definitive info. My first reaction would be to have a sort of Repeat node, with two subnodes, the Tail and the Loop. The procedure would be to extract a tuple from the Tail (optionally returning it). Then put that tuple as the input to the Loop and start pulling tuples out of that. Will this work for both DEPTH FIRST and BREADTH FIRST recursion ? This would be BREADTH FIRST recursion. If you want depth first you just need to feed the tuples in reverse order (LIFO rather than FIFO). The only thing to keep between runs is sets of tuples, and we already know how to do that (Sort for example). Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp0SjjVO69Ii.pgp Description: PGP signature
Re: [HACKERS] CONNECT BY PRIOR
On T, 2005-11-15 at 09:53 +0100, Martijn van Oosterhout wrote: On Tue, Nov 15, 2005 at 01:38:31AM -0500, Jonah H. Harris wrote: Hey Simon, I'm doing some research into recursive query planning in terms of theory as-well-as actual implementation in other RDBMS. Let me get back to you when I have some more definitive info. My first reaction would be to have a sort of Repeat node, with two subnodes, the Tail and the Loop. The procedure would be to extract a tuple from the Tail (optionally returning it). Then put that tuple as the input to the Loop and start pulling tuples out of that. Will this work for both DEPTH FIRST and BREADTH FIRST recursion ? Problem is, those new tuples may have to be sent through the loop again so you have a buffering problem. But it seems a fairly generic way of dealing with it. Ofcourse, once you've done that, you might be getting very close to a Turing complete executor, no? :) Have a nice day, -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CONNECT BY PRIOR
On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote: I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Sounds interesting. What approach are you taking to the plan shape? The current approach would be to have additional plan nodes for each join. Coping with a dynamic number of operations will do interesting things in the planner. I face a similar dynamic problem with joins to partitioned tables. Do you have any thoughts about this area? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CONNECT BY PRIOR
Hey Simon, I'm doing some research into recursive query planning in terms of theory as-well-as actual implementation in other RDBMS. Let me get back to you when I have some more definitive info. On 11/14/05, Simon Riggs [EMAIL PROTECTED] wrote: On Sat, 2005-11-12 at 15:27 -0500, Jonah H. Harris wrote: I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2.Sounds interesting.What approach are you taking to the plan shape? The current approachwould be to have additional plan nodes for each join. Coping with adynamic number of operations will do interesting things in the planner. I face a similar dynamic problem with joins to partitioned tables.Do you have any thoughts about this area?Best Regards, Simon Riggs
[HACKERS] CONNECT BY PRIOR
Hi, I'm just a little bit confused because I expected postgresql to be able t connect by prior but as I have seen it is not. :-( Are there any plans to support this in the main distribution? If have found a patch to porstgres but I don't want to apply any patches but only use the vanilla postgresql. BTW: The patch is available at http://gppl.moonbone.ru/ Cheers, Yann ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] CONNECT BY PRIOR
Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. On 11/12/05, Yann Michel [EMAIL PROTECTED] wrote: Hi,I'm just a little bit confused because I expected postgresql to be ablet connect by prior but as I have seen it is not. :-(Are there any plans to support this in the main distribution? If have found a patch to porstgres but I don't want to apply any patches butonly use the vanilla postgresql.BTW: The patch is available at http://gppl.moonbone.ru/ Cheers,Yann---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] CONNECT BY PRIOR
Hi, On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote: Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Fine! Looking forward to that! Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CONNECT BY PRIOR
That's GREAT news, hope you can make it to 8.2 .. it will be really a good hit On 11/13/05, Yann Michel [EMAIL PROTECTED] wrote: Hi, On Sat, Nov 12, 2005 at 03:27:32PM -0500, Jonah H. Harris wrote: Yann, I am working on the standard WITH syntax for recursive query support and hope to get it into 8.2. Fine! Looking forward to that! Cheers, Yann ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Connect By for 8.0
Hans-Jürgen Schönig wrote: Neil Conway wrote: Robert Treat wrote: Actually i believe people want both syntax's as the former is used by oracle and the latter by db2 (iirc) I think the past consensus has been to adopt the SQL standard syntax. Is there any reason to also support the Oracle syntax other than for compatibility? (And if that is it, I think it's a pretty flimsy reason.) -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Neil, Compatibility is the ONLY reason and it is related to money (the only language people understand). We have done a lot of migration here and I can tell you that support for Oracle style CONNECT BY would make more people happy than the SQL style syntax. The reason for that is very simple: Code can be migrated without any changes (= without introducing potential bugs). I know that SQL standards are tremendously important but if there is an easy way to support Oracle syntax as well this is definitely the preferred way to go. I think it is important not to think in dogmas (in this case this means SQL syntax is always better) - there should be a reasonable compromise between compatibility and standard. My compromise would be: Support both syntaxes if possible. I can see your point, but imagine if we had Oracle compatibility for lots of cases --- our system would have either non-standard or duplicate ways of doing things, and that would be quite confusing. -- 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Connect By for 8.0
Bruce Momjian pgman@candle.pha.pa.us writes: Hans-Jürgen Schönig wrote: My compromise would be: Support both syntaxes if possible. I can see your point, but imagine if we had Oracle compatibility for lots of cases --- our system would have either non-standard or duplicate ways of doing things, and that would be quite confusing. Oracle has the resources to outtalk, outshout, and outlast everyone else on the SQL standards committee. Despite that, their syntax was not adopted as the standard. This should give you some clue about how badly their syntax sucks. Now why exactly should we adopt it? 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
Re: [HACKERS] Connect By for 8.0
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Hans-Jürgen Schönig wrote: My compromise would be: Support both syntaxes if possible. Great... code away. I would suggest a: oracle_compat = true/false in the postgresql.conf Yes I am kidding. The differences between Oracle and PostgreSQL for most things is not that great. If the small syntatical differences are the only thing holding them from using PostgreSQL they were not that serious in the first place. Sincerely, Joshua D. Drake I can see your point, but imagine if we had Oracle compatibility for lots of cases --- our system would have either non-standard or duplicate ways of doing things, and that would be quite confusing. Oracle has the resources to outtalk, outshout, and outlast everyone else on the SQL standards committee. Despite that, their syntax was not adopted as the standard. This should give you some clue about how badly their syntax sucks. Now why exactly should we adopt it? 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 -- 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 PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Connect By for 8.0
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Hans-Jürgen Schönig) wrote: I think it is important not to think in dogmas (in this case this means SQL syntax is always better) - there should be a reasonable compromise between compatibility and standard. My compromise would be: Support both syntaxes if possible. As long as they can co-exist, that seems plausible. I'd personally rather see the standard form, as it's got a more diverse set of uses. If introducing the CONNECT BY form did anything to prevent implementing it the RIGHT way, I'm pretty sure I'm not the only one that would be most displeased. -- output = (cbbrowne @ gmail.com) http://linuxfinances.info/info/slony.html If anyone ever markets a really well-documented Unix that doesn't require babysitting by a phalanx of provincial Unix clones, there'll be a lot of unemployable, twinky-braindamaged misfits out deservedly pounding the pavements. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Connect By for 8.0
Neil Conway wrote: Robert Treat wrote: Actually i believe people want both syntax's as the former is used by oracle and the latter by db2 (iirc) I think the past consensus has been to adopt the SQL standard syntax. Is there any reason to also support the Oracle syntax other than for compatibility? (And if that is it, I think it's a pretty flimsy reason.) -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Neil, Compatibility is the ONLY reason and it is related to money (the only language people understand). We have done a lot of migration here and I can tell you that support for Oracle style CONNECT BY would make more people happy than the SQL style syntax. The reason for that is very simple: Code can be migrated without any changes (= without introducing potential bugs). I know that SQL standards are tremendously important but if there is an easy way to support Oracle syntax as well this is definitely the preferred way to go. I think it is important not to think in dogmas (in this case this means SQL syntax is always better) - there should be a reasonable compromise between compatibility and standard. My compromise would be: Support both syntaxes if possible. Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Connect By for 8.0
Robert Treat wrote: Actually i believe people want both syntax's as the former is used by oracle and the latter by db2 (iirc) I think the past consensus has been to adopt the SQL standard syntax. Is there any reason to also support the Oracle syntax other than for compatibility? (And if that is it, I think it's a pretty flimsy reason.) -Neil ---(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] Connect By for 8.0
Christopher Kings-Lynne wrote: I notice the CONNECT BY patch has been updated for 8.0: http://gppl.moonbone.ru/ Seriously, we really need to get this into 8.1. Convert it to the standard WITH RECURSIVE syntax if necessary... Yep, we are just waiting for someone to do the work. -- 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
[HACKERS] Connect By for 8.0
I notice the CONNECT BY patch has been updated for 8.0: http://gppl.moonbone.ru/ Seriously, we really need to get this into 8.1. Convert it to the standard WITH RECURSIVE syntax if necessary... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] connect to 7.5 devel(win32) failed
I am has some trouble when connect to 7.5 devel PostgreSQL server, see belows my setup:===os=Windows2000server version=the non-MSI snapshot at http://www.hagander.net/pgsql/win32snap/ has been "downgraded" to the latest known working snapshot path=c:\postgres\bin;c:\postgres\libpgdata=c:\postgres\dataTZ=CST note:==I has NO modify the configure file generated by initdb, and has NO firewall installed in my server cmd to run postmaster(run as postgre user):==postmaster -D "C:/postgres/data" result1: some lines of the log(postmaster)===LOG: select() failed in statistics buffer: LOG: statistics collector process (PID 1876) was terminated by signal 1 result2:===When I use pgAdmin III to connect the PostgreSQL server,a error is returned: could not receive server response to SSL negotiation packet: Connection reset by peer(0x2746/10054) Do You Yahoo!? "" 100
Re: [HACKERS] connect to 7.5 devel(win32) failed
Moving the -admin, please don't reply to -hackers on this. On Fri, 2004-07-30 at 00:04, Coloring Graph wrote: I am has some trouble when connect to 7.5 devel PostgreSQL server, see belows my setup: === os=Windows2000 server version=the non-MSI snapshot at http://www.hagander.net/pgsql/win32snap/ has been downgraded to the latest known working snapshot path=c:\postgres\bin;c:\postgres\lib pgdata=c:\postgres\data TZ=CST note: == I has NO modify the configure file generated by initdb, and has NO firewall installed in my server cmd to run postmaster(run as postgre user): == postmaster -D C:/postgres/data result1: some lines of the log(postmaster) === LOG: select() failed in statistics buffer: LOG: statistics collector process (PID 1876) was terminated by signal 1 result2: === When I use pgAdmin III to connect the PostgreSQL server, a error is returned: Is pgadminIII using local domain sockets or TCP/IP sockets? Out of the box pgsql usually just answers local domain sockets, and I don't have a clue what windows supports in that way, etc... Since local domain sockets only work for processes on the same machine, this means that, after initial install, postgresql is not visible on anything other than the box it's installed on. You'll need to edit pg_hba.conf to give other machines permission to authenticate, as well as postgresql.conf to accept tcpip connections, then restart the server. Learning to admin on a pre-beta 1st port of windows could be a bit of work, so hang in there. These questions should probably go on -general or -admin first. ---(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
