Re: [HACKERS] How to implement an auto-increment column for a system catalog table?
On Mon, 2009-03-16 at 10:18 +0800, Xin Wang wrote: I am using Postgres to build the prototype in a research project. I need to create a new system catalog table with an auto-increment column. For a ordinary table, CREATE SEQUENCE or a serial type can be used to implement the auto-increment column, but it seems Postgres do not support creating a sequence or serial types in system catalogs. How to implement an auto-increment column for a catalog table? Other catalog tables use a new oid for each new row/entry. Just do it in the code for adding each new entry. Look at backend/catalog/heap.c heap_create_with_catalog(), or something simpler like backend/commands/dbcommands.c createdb(). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] How to implement an auto-increment column for a system catalog table?
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2009-03-16 at 10:18 +0800, Xin Wang wrote: I am using Postgres to build the prototype in a research project. I need to create a new system catalog table with an auto-increment column. Other catalog tables use a new oid for each new row/entry. Just do it in the code for adding each new entry. Look at backend/catalog/heap.c heap_create_with_catalog(), or something simpler like backend/commands/dbcommands.c createdb(). Note that in fact the bottom level heap_insert code will handle assigning a new OID for you. The only cases where calling code needs to do that explicitly are when the OID has to be known before creation of the new row for some reason (usually, circular OID references). regards, tom lane -- 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] hstore improvements?
Ron == Ron Mayer rm...@cheapcomplexdevices.com writes: Ron Currently hstore gives me an indexed operator to query if a Ron hstore contains a single key. It'd be nice if there were as way Ron to extend this so that I could ask for only records that have Ron all or any the keys in a query. Ron 'a=1, b=1'::hstore ? 'a,b' Ron In one database I put ids of each of the keys in a hstore into a Ron largely redundant intarray to be able to do fast queries for Ron rows containing all the hstore-keys in a set. I think trying to overload this onto ? would be possibly a bit too confusing; would hstore ? text[] mean any match, or all match? How about hstore ? text[] for all match, and hstore ?| text[] for any match? Ron Even cooler might be extending the hstore '?' operator to Ron allow expressions similar to intarray's queries: Ron 'a=1, b=1'::hstore ? 'a|b' Ron 'a=1, b=1'::hstore ? 'ab' Ron 'a=1, b=1'::hstore ? 'a(b|c)' Ron I don't have a need for the more general expressions, but if Ron the code can be borrowed from intarray to handle both, that'd Ron be sweet. While that is possible, it involves adding a lot more than I planned to at this stage. Ron I once wanted a variation of hstore where a key could have Ron multiple values(and the ability to query them). Ron 'a=x, a=y'::hstore @ 'a=x' Ron I imagine most EAV systems allow multiple values for each Ron attribute - and a hstore that supported this could probably Ron be a pretty nice general solution for many EAV systems. IIRC Ron other people asked about similar on the lists before. This is definitely going too far - such a thing would have to be a new type entirely. -- Andrew. -- 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] Review: B-Tree emulation for GIN
Teodor Sigaev wrote: You might want to declare extra_data as just void *, instead of an array of pointers. The data type implementation might want to store something there that's not per-key, but applies to the whole query. I see that you're passing it to comparePartial, but that seems to be just future-proofing. What kind of a data type are you envisioning that would wildspeed module (http://www.sigaev.ru/cvsweb/cvsweb.cgi/wildspeed/) - for each key from it's needed to store some info. Right now it's coded directly in Datum, but it looks ugly (at least for me). Ok, I guess it's best to leave it as you had in the patch then. make use of it? It seems that you could pass the same information in the partial key Datum itself that extractQuery returns. You're currently using it as a way to avoid some palloc's in gin_tsquery_consistent(). That seems like a pretty dirty hack. I doubt there's any meaningful performance advantage from that, but if there is, I think you could use a statically allocated array instead. It's easy to un-dirty that hack, but before I'd like to see your comments about thoughts above. Yeah, please revert that hack. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] cross-compiling plpython
So, I realized that there does not seem to be an easy way for cross compiling plpython. The configure checks for the locally installed python include locations and eventually the plpython makefile ends up picking all the local includes too. Does anyone have any experiences on cross-compiling plpython before? Obviously I can hack the Makefile to point to the host environment headers, but is there a neater way? Also I suspect the plpython.so so generated might not load on the host later too because its looking for the libpython.so somewhere else too. Regards, Nikhils -- http://www.enterprisedb.com
Remaining items for 8.4 (was Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1710))
Bruce Momjian wrote: Well, we have been trying to go simplify the SE-PostgreSQL patch since September, and while we have made progress, we still have work to do, and at this point I think we have run out of time. I think we have given it a fair shot, but I don't think it is going to make 8.4. Agreed. At some point we just have to wrap up and cut the release. Tweaking indefinitely is not fair to all those patches that have already been pushed back, nor to those that have already been committed and are waiting to be released as part of 8.4. Apart from SE-PostgreSQL, we have four remaining items on the commitfest page: GIN fast insert I agree with Tom that we should just disable regular index scans for GIN. If someone misses it in 8.4, we can try to find a way to do it safely in 8.5. Removing existing capability is a bit worrisome, but I'm even less happy with the out of memory condition in this patch and in the partial match patch that has been committed already. That really needs to be cleaned up. B-Tree emulation for GIN I think this is in pretty good shape. Improve Performance of Multi-Batch Hash Join for Skewed Data Sets I believe everyone's happy with the performance testing that's been done. Some of the logic ought to be moved to the planner, and maybe there's some other cleanup to do. Proposal of PITR performance improvement Hmm. The first version of this was submitted back in October, as an external tool. There's still some outstanding issues: http://archives.postgresql.org//pgsql-hackers/2008-10/msg01387.php. I think we should push this to 8.5, for the same reasons as SE-PostgreSQL. On the positive side, the external tool is on pgFoundry for use with 8.4 (and earlier releases too?). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] cross-compiling plpython
Nikhil Sontakke wrote: So, I realized that there does not seem to be an easy way for cross compiling plpython. The configure checks for the locally installed python include locations and eventually the plpython makefile ends up picking all the local includes too. Does anyone have any experiences on cross-compiling plpython before? Obviously I can hack the Makefile to point to the host environment headers, but is there a neater way? Also I suspect the plpython.so so generated might not load on the host later too because its looking for the libpython.so somewhere else too. Did you check debian source packages? At least I believe they do not compile everything on the respective system so there must be some hooks for crosscompiling. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
[HACKERS] Problem with accesing Oracle from plperlu function when using remote pg client.
Greetings to All! I've tried to find solution of my problem on other pg mailing lists but without bigger effect. I have a table A in PG. There is also table A in Oracle. I want to import specific row from oracle to pg, so i create plperlu function CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) RETURNS dok_za AS $BODY$ In IPL: create_connection; select all columns on oracle from table a where id = a_id; returning tuple; $BODY$ LANGUAGE 'plperlu' VOLATILE; then i can use such function in pl/pgsql; DECLARE: var A%ROWTYPE; BEGIN; ... select * into var from import.ora_a_row(100); END;... Like you see it's very, very convenient. And it works, but only when I make select * from import.ora_a_row(100); from psql on postgresql server(local client). When I try to make that select in pgadmin or from remote machine I have tns error: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant). When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). Anybody ancounter this kind of problem or maybe it's a bug in plperlu? I'll be grateful for any of Your help. Regards Tomasz
Re: [HACKERS] Problem with accesing Oracle from plperlu function when using remote pg client.
On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote: Greetings to All! I've tried to find solution of my problem on other pg mailing lists but without bigger effect. I have a table A in PG. There is also table A in Oracle. I want to import specific row from oracle to pg, so i create plperlu function CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) RETURNS dok_za AS $BODY$ In IPL: create_connection; select all columns on oracle from table a where id = a_id; returning tuple; $BODY$ LANGUAGE 'plperlu' VOLATILE; then i can use such function in pl/pgsql; DECLARE: var A%ROWTYPE; BEGIN; ... select * into var from import.ora_a_row(100); END;... Like you see it's very, very convenient. And it works, but only when I make select * from import.ora_a_row(100); from psql?? on postgresql server(local client). When I try to make that select in pgadmin or from remote machine I have tns error: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant). When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). Anybody ancounter this kind of problem or maybe it's a bug in plperlu? I'll be grateful for any of Your help. Regards Tomasz This looks like an ENVIRONMENT variable problem. The server does not run with the same set of settings as your psql program. I think that it will work once you get those issues ironed out. Good luck, Ken -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Thank you for quick answer. I understand, but when I print from plperlu function notices with result of `env` they're the same in both cases (from remote and local client). So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable. So I don't have a clue how can I iron out this issue. Dnia 16 marca 2009 15:27 Kenneth Marshall lt;k...@rice.edugt; napisał(a): On Mon, Mar 16, 2009 at 03:16:07PM +0100, Tomasz Olszak wrote: gt; Greetings to All! gt; gt; I've tried to find solution of my problem on other pg mailing lists but without bigger effect. gt; gt; I have a table A in PG. There is also table A in Oracle. gt; I want to import specific row from oracle to pg, so i create plperlu function gt; gt; CREATE OR REPLACE FUNCTION import.ora_a_row(a_id numeric) gt; RETURNS dok_za AS gt; $BODY$ gt; gt; In IPL: gt; create_connection; gt; select all columns on oracle from table a where id = a_id; gt; returning tuple; gt; gt; $BODY$ gt; LANGUAGE 'plperlu' VOLATILE; gt; gt; then i can use such function in pl/pgsql; gt; gt; DECLARE: gt; var A%ROWTYPE; gt; BEGIN; gt; ... gt; select * into var from import.ora_a_row(100); gt; END;... gt; gt; Like you see it's very, very convenient. gt; gt; And it works, but only when I make select * from import.ora_a_row(100); from psql?? on postgresql server(local client). gt; When I try to make that select in pgadmin or from remote machine I have tns error: gt; gt; TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at line 20 gt; gt; I've tried with different postgresql versions and different perls, and different DBI Oracle packages, so i think pg or perl versions are not causes(Of course environment variables are propably set etc.). Oracle Base directory is about 1.6 gigabyte so I think it's full client(not instant). gt; gt; When I used PGADMIN 1.6 on postgresql server and left host editline blank(specifying only a pgport) it worked too. gt; But when I've written localhost as host it didn't work (the same with connecting psql -h localhost -U user database ). gt; gt; Anybody ancounter this kind of problem or maybe it's a bug in plperlu? gt; gt; I'll be grateful for any of Your help. gt; gt; Regards gt; gt; Tomasz gt; This looks like an ENVIRONMENT variable problem. The server does not run with the same set of settings as your psql program. I think that it will work once you get those issues ironed out. Good luck, Ken
[HACKERS] small but useful patches for text search
Hi there, I and Teodor have several small, but useful patches for text search: 1. Support of filtering dictionaries and unaccent dictionary/function. This is often requested feature, which solves, for example, problem with correct headlines for text with accents. See example and docs http://www.sai.msu.su/~megera/wiki/unaccent 2. Add prefix search support to the synonym dictionary. Star sign '*' at the end of definition word indicates, that definition word is a prefix and to_tsquery() function will transform that definition to the prefix search format. Notice, it is ignored in to_tsvector(). Some examples http://www.sai.msu.su/~megera/wiki/2009-03-13 There are limitations - no support for thesaurus dictionary, ts_debug doesn't support all these features (it needs to be rewritten to C). There is no problem with back compatibility. We would like to have your opinion what to do with these patches - leave them for 8.5 or provide them to hackers to review for 8.4. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak tols...@o2.pl wrote: So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable. It has nothing to do with the environment variables. So I don't have a clue how can I iron out this issue. Finally, my low-level Oracle knowledge does benefit Postgres :) It's a TNS parsing error due to a combination of Oracle's use of a Lispish s-expression-like name-value pair format and Postgres' process listing format for remote connections. On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text. Because Oracle picks up Postgres' backend text, postgres: www postgres 192.168.1.1(13243), the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection. This doesn't happen when you're connected to PG locally, because the backend text is, postgres: www postgres [local]. The solution to this is to change the following line in src/backend/postmaster/postmaster.c: remote_port[0] == '\0' ? %s : %s(%s) TO remote_port[0] == '\0' ? %s : %s[%s] OR remote_port[0] == '\0' ? %s : %s:%s Which I would prefer as a nice change to make overall. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] small but useful patches for text search
Oleg Bartunov o...@sai.msu.su writes: We would like to have your opinion what to do with these patches - leave them for 8.5 or provide them to hackers to review for 8.4. In theory 8.5, though you wouldn't be the first to start sneaking in late commits and given how long before the release I can't really think people would complain too much. Things have reached a perverse state. We've now been in a holding pattern for 4 1/2 months while development has basically ceased. Aside from committers, no contributors have been able to make any progress on any work for already that time and months remain before any reviewers will pay any attention to submissions. I have a bunch of ideas I wanted to follow up posix_fadvise with including posix_fallocate and more uses of posix_fadvise. I also wanted to return to the ordered merge-append which I still think is critical for partitioned table plans. I think it's clear that stretching feature freezes is a failed policy. Aside from delaying everyone else's work, it hasn't helped the projects we held the release for either. Those projects would have hit 8.5 in due course just fine but now surely we'll delay 8.5 based on the 8.4 release date. So they'll be delayed just like everyone else's work. I think in the future we should adopt a policy that only minor patches can be received after the first commitfest. Major patches are expected to submitted in the *first* commitfest and reworked based on feedback on subsequent commitfests. The last commitfest should be a real feature-freeze where only bug-fixes and minor changes are accepted, not major features. There seems to be a lot of resistance on the basis that there would be a long wait until features are seen in a release. Personally I'm only interested in when features get committed, not when they hit a release. But in any case I think experience shows that this would result in hitting the same release anyways and that release would be sooner as well. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris escribió: On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text. Because Oracle picks up Postgres' backend text, postgres: www postgres 192.168.1.1(13243), the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection. This doesn't happen when you're connected to PG locally, because the backend text is, postgres: www postgres [local]. Wow, that's a really idiotic thing for Oracle to do. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: Finally, my low-level Oracle knowledge does benefit Postgres :) :-) It's a TNS parsing error due to a combination of Oracle's use of a Lispish s-expression-like name-value pair format and Postgres' process listing format for remote connections. On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text. Because Oracle picks up Postgres' backend text, postgres: www postgres 192.168.1.1(13243), the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection. This doesn't happen when you're connected to PG locally, because the backend text is, postgres: www postgres [local]. The solution to this is to change the following line in src/backend/postmaster/postmaster.c: remote_port[0] == '\0' ? %s : %s(%s) TO remote_port[0] == '\0' ? %s : %s[%s] OR remote_port[0] == '\0' ? %s : %s:%s Which I would prefer as a nice change to make overall. Any way to override it in the Oracle client? With an enviroment variable or something? Because making that change would probably break a bunch of pg admin scripts on random systems around the world.. (yes, they should be using pg_stat_activity, but I'll bet you there are a lot of stuff out there based on the ps output) (if we do change it, the colon notification seems to be the reasonable one given that's how we usually write ip/port pairs) //Magnus -- 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] hstore improvements?
Ron, I imagine most EAV systems allow multiple values for each attribute - and a hstore that supported this could probably be a pretty nice general solution for many EAV systems. IIRC other people asked about similar on the lists before. Well, not usually. Generally, since EAV systems are entity-attribute-value, the key for the table is generally entity-attribute. I've seen a few which were entity-attribute-attributenumber-value, but those are rare. Overally, I think the ability to stick an array into an hstore value would take care of most real uses. Beyond that, we're encouraging people to use hstore in ways it won't actually scale. --Josh -- 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] hstore improvements?
Andrew Gierth wrote: I have a patch almost done that adds some obvious but currently missing functionality to hstore... If there's any other features that people find notably missing from hstore, I could stick them in too; any requests? Currently hstore gives me an indexed operator to query if a hstore contains a single key.It'd be nice if there were as way to extend this so that I could ask for only records that have all or any the keys in a query. 'a=1, b=1'::hstore ? 'a,b' In one database I put ids of each of the keys in a hstore into a largely redundant intarray to be able to do fast queries for rows containing all the hstore-keys in a set. Even cooler might be extending the hstore '?' operator to allow expressions similar to intarray's queries: 'a=1, b=1'::hstore ? 'a|b' 'a=1, b=1'::hstore ? 'ab' 'a=1, b=1'::hstore ? 'a(b|c)' I don't have a need for the more general expressions, but if the code can be borrowed from intarray to handle both, that'd be sweet. I once wanted a variation of hstore where a key could have multiple values(and the ability to query them). 'a=x, a=y'::hstore @ 'a=x' I imagine most EAV systems allow multiple values for each attribute - and a hstore that supported this could probably be a pretty nice general solution for many EAV systems. IIRC other people asked about similar on the lists before. Also, hstore has an (undocumented) limit of 65535 bytes for keys and values, and it does not behave very cleanly when given longer values (it truncates them mod 2^16, rather than erroring). That gives rise to two obvious questions: (1) are those lengths reasonable? they strike me as being rather long for keys and rather short for values; and (2) should exceeding the lengths throw an error? -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah, you're the man :). Thank you very much, I tried to solve it for about 2 weeks. I know that few people in the net have the same problem too. I simply chanche that line, recompile postgresql and wait for some better solution. I know that a lot of people uses DBI-LINK. It simply doesn't work when you envoking functions(for example make_ancessor or somethink like that) from remote client like pgAdmin :). Regards to all Thank you one more time Jonah. Dnia 16 marca 2009 17:26 Jonah H. Harris lt;jonah.har...@gmail.comgt; napisał(a): On Mon, Mar 16, 2009 at 11:09 AM, Tomasz Olszak lt;tols...@o2.plgt; wrote: So it looks like that plperlu function is executing from remote and local clients with the same set of environment variable. It has nothing to do with the environment variables. So I don't have a clue how can I iron out this issue. Finally, my low-level Oracle knowledge does benefit Postgres :) It's a TNS parsing error due to a combination of Oracle's use of a Lispish s-expression-like name-value pair format and Postgres' process listing format for remote connections. On connection, the Oracle client sends the current application name to the Oracle server (which is listed in the V$SESSION view); in the case of Postgres, the program name is the current backend process name text. Because Oracle picks up Postgres' backend text, postgres: www postgres 192.168.1.1(13243), the (13243) screws up Oracle's TNS parser which prevents it from resolving the connection. This doesn't happen when you're connected to PG locally, because the backend text is, postgres: www postgres [local]. The solution to this is to change the following line in src/backend/postmaster/postmaster.c: remote_port[0] == '\0' ? %s : %s(%s) TO remote_port[0] == '\0' ? %s : %s[%s] OR remote_port[0] == '\0' ? %s : %s:%s Which I would prefer as a nice change to make overall. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: Remaining items for 8.4 (was Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1710))
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Improve Performance of Multi-Batch Hash Join for Skewed Data Sets I believe everyone's happy with the performance testing that's been done. Some of the logic ought to be moved to the planner, and maybe there's some other cleanup to do. I'll take this up next. AFAIR refactoring to put that which should be in the planner into the planner was the only significant issue. regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris jonah.har...@gmail.com writes: The solution to this is to change the following line in src/backend/postmaster/postmaster.c: We're not going to break a bunch of other applications in order to make some undocumented, unsupported Oracle thingie work (until they change it...). Got another solution? regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 1:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: We're not going to break a bunch of other applications in order to make some undocumented, unsupported Oracle thingie work (until they change it...). Got another solution? Unfortunately, that's the way Oracle has done it since before the existence of POSTGRES. I first encountered it while working on database links from PG to Oracle at EnterpriseDB, and the reason Tomasz couldn't find the answer to this online is because it's such a rare problem that Oracle has no reason to change it. Really, how many people have parenthesis in their program names? Similarly, the problem has always existed when connecting to Oracle from Postgres using DBI-Link or the oralink contrib module, there's just so few PG people connecting to Oracle that it hasn't really come up before. As for alternate solutions, the only thing I can think of is a config parameter to disable rewrite of the ps line. Frankly, I don't recall ever seeing a script that looked for (port) in the process list, but there are probably some home-grown ones out there. As for me, I'd prefer to separate the host and port via a colon, just as everything else does, but that isn't backward compatible. I would expect this to become more of an issue when we start getting SQL/MED more closely integrated with the server and people can more easily connect to other databases. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 12:50 PM, Tomasz Olszak tols...@o2.pl wrote: Thank you very much, I tried to solve it for about 2 weeks. I know that few people in the net have the same problem too. No problem :) -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 12:36 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Jonah H. Harris escribió: Wow, that's a really idiotic thing for Oracle to do. Well, being able to find out what applications are connected to the database is nice. But, it would also be nice if they stopped parsing the program name if/when it encounters a left/right parenthesis. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
All, Probably somebody should resurrect the Oralink project instead. http://pgfoundry.org/projects/oralink/ --Josh -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris escribió: I first encountered it while working on database links from PG to Oracle at EnterpriseDB, and the reason Tomasz couldn't find the answer to this online is because it's such a rare problem that Oracle has no reason to change it. Really, how many people have parenthesis in their program names? Similarly, the problem has always existed when connecting to Oracle from Postgres using DBI-Link or the oralink contrib module, there's just so few PG people connecting to Oracle that it hasn't really come up before. I have seen a bunch of reports of people not being able to connect to Oracle via DBI-Link on the spanish list, so this definitely has bitten some people. I'm not sure that it's all that rare. As for alternate solutions, the only thing I can think of is a config parameter to disable rewrite of the ps line. We already have one; it's called update_process_title. Maybe DBI-Link could set the title to something else before attempting the connection. (And perhaps locally set update_process_title to off.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: We already have one; it's called update_process_title. I have it turned off, and I still see the remote IP/port in the process list. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: We already have one; it's called update_process_title. I have it turned off, and I still see the remote IP/port in the process list. I am thinking the title doesn't update _after_ you turn it off, but it was updated when the session started. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@momjian.us writes: Jonah H. Harris wrote: I have it turned off, and I still see the remote IP/port in the process list. I am thinking the title doesn't update _after_ you turn it off, but it was updated when the session started. Yeah, we intentionally set the title during backend startup, else you could not tell backends apart from the postmaster let alone each other. The GUC is only meant to eliminate the performance-hogging behavior of changing the title for every command. I'm finding it hard to believe that there is no way to override what Oracle's client library does --- there are *plenty* of situations where you don't really want a client command line exposed to the whole world. regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris escribió: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.comwrote: We already have one; it's called update_process_title. I have it turned off, and I still see the remote IP/port in the process list. Yeah, apparently init_ps_display changes the title inconditionally. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:26 PM, Jonah H. Harris jonah.har...@gmail.comwrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: We already have one; it's called update_process_title. I have it turned off, and I still see the remote IP/port in the process list. Ahh, this is why: init_ps_display():set_ps_display(initial_str, true); Perhaps it should obey the configuration setting as well? -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm finding it hard to believe that there is no way to override what Oracle's client library does --- there are *plenty* of situations where you don't really want a client command line exposed to the whole world. AFAIK, there is no way to override that. It's very low-level in their client stack, is operating-system specific, and has been there forever. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Alvaro Herrera alvhe...@commandprompt.com writes: Maybe DBI-Link could set the title to something else before attempting the connection. (And perhaps locally set update_process_title to off.) Making the (unwarranted?) assumption that Oracle's library only captures the title during connect, it seems like DBI-Link could be made to just transiently override the title while connecting. There should be no need to break user-visible behavior for this. The whole thing seems mighty bizarre though. Given the number of different ways we have to set the process title, I rather wonder whether what we do will always determine what Oracle sees, on every platform. regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 02:30:28PM -0400, Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:26 PM, Jonah H. Harris jonah.har...@gmail.comwrote: On Mon, Mar 16, 2009 at 2:04 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: We already have one; it's called update_process_title. I have it turned off, and I still see the remote IP/port in the process list. Ahh, this is why: init_ps_display():set_ps_display(initial_str, true); Perhaps it should obey the configuration setting as well? -- Jonah H. Harris, Senior DBA myYearbook.com What about have the GUC support off, on, and a format string to use to fix this problem. Ken -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 2:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm finding it hard to believe that there is no way to override what Oracle's client library does --- there are *plenty* of situations where you don't really want a client command line exposed to the whole world. AFAIK, there is no way to override that. It's very low-level in their client stack, is operating-system specific, and has been there forever. Someone should raise a support request / whatever they call them with Oracle to get this fixed on their side.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Someone should raise a support request / whatever they call them with Oracle to get this fixed on their side.. Heh. Why would they fix it when it's only a problem for 1% of their users in odd corner cases? -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 3:03 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Someone should raise a support request / whatever they call them with Oracle to get this fixed on their side.. Heh. Why would they fix it when it's only a problem for 1% of their users in odd corner cases? Because that's what a respectable business does when a customer runs into a bug with software they sell. Whether or not they actually will fix it, I don't know, but they surely won't if no-one complains them about it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: PostgreSQL Memory Management
Hi, Please find my original message below. This was originally posted to pgsql-general, but didn't get any response. Thanks, -Umar -- Forwarded message -- From: Umar Farooq umarf...@gmail.com Date: Mon, Mar 9, 2009 at 12:39 PM Subject: PostgreSQL Memory Management To: pgsql-gene...@postgresql.org Hi All, I have a few general question about PGSQL's internals of memory management and I think this list is appropriate for that. I am interested to find out: (1) how does PostgreSQL internally use physical memory allocated to it? (2) what are different type of allocation units? (3) what is a particular allocation unit used for e.g., to store log-tail, query plans, procedure cache etc.? I've already gone through README file in /backend/src/util/mmgr. Thanks, -Umar
Re: [HACKERS] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Because that's what a respectable business does when a customer runs into a bug with software they sell. It's not a bug, it's expected behavior. Not that I think it couldn't be better handled. I'm not trying to dig at this, but looking at it in terms of flexibility, rather than us change the way we display a port in the ps-line because it may break a couple hundred scripts, you seem to think it's more reasonable for a company with a product utilized by millions of users, installed in countless governments, and deployed in mission-critical areas, to risk changing a fairly mature and well-tested behavior because it affects fewer than 1% of its users per year; specifically, users who are trying to interoperate with a competing database? If it were my business, it doesn't seem like something I would put much effort into :) Whether or not they actually will fix it, I don't know, but they surely won't if no-one complains them about it. Wouldn't hurt :) -- Jonah H. Harris, Senior DBA myYearbook.com
[HACKERS] typmod is always -1
Hello
I am playing with custom typmod. I did simple wrapper over varchar type.
PG_FUNCTION_INFO_V1(mvarcharin);
Datum
mvarcharin(PG_FUNCTION_ARGS)
{
elog(NOTICE, %d, PG_GETARG_INT32(2));
return DirectFunctionCall3(varchar,
DirectFunctionCall3(varcharin,
PG_GETARG_DATUM(0),
PG_GETARG_DATUM(1),
Int32GetDatum(-1)),
PG_GETARG_DATUM(2),
/* original typmod */
BoolGetDatum(true));
/* explit casting, quite truncate */
}
CREATE TYPE mvarchar (
INPUT = mvarcharin,
OUTPUT = mvarcharout,
LIKE = pg_catalog.varchar,
typmod_in = pg_catalog.varchartypmodin,
typmod_out = pg_catalog.varchartypmodout
);
I have a problem - every call of mvarcharin is with typmod = -1.
postgres=# create table x(a mvarchar(3));
CREATE TABLE
Time: 29,930 ms
postgres=# \d x
Table public.x
┌┬─┬───┐
│ Column │ Type│ Modifiers │
├┴─┴───┤
│ a │ mvarchar(3) │ │
└──┘
postgres=# INSERT INTO x values('abcdef');
NOTICE: -1
INSERT 0 1
Time: 2,244 ms
postgres=#
can somebody navigate me?
regards
Pavel Stehule
--
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] small but useful patches for text search
Gregory Stark st...@enterprisedb.com writes: I think it's clear that stretching feature freezes is a failed policy. Yeah, it's the same old same old --- once again, we've bent over backwards to try to accommodate large patches at the end of a development cycle. I'm not sure that that's ever going to stop, because every time there are people cheerleading for said patches and insisting that the release will be so much better if we wait for them. Somehow we keep expecting that they'll get fixed and landed in a short period of time. A saner policy would mandate that large patches land near the start of a development cycle, but I don't know how we get people to do that. regards, tom lane -- 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] small but useful patches for text search
On Mon, Mar 16, 2009 at 8:41 PM, Tom Lane t...@sss.pgh.pa.us wrote: I'm not sure that that's ever going to stop, because every time there are people cheerleading for said patches and insisting that the release will be so much better if we wait for them. Well 8.3 was better for having HOT. But I still feel we got lucky with that. I was pretty nervous we would find some major data corruption bug after 8.3 was out. Pavan did great work and others in EDB put a lot of work into testing it, but systematic testing -- as great as it is --just isn't the same as having many other people using it day in and day out and doing *unexpected* things with it. The problem I see isn't (just) that things take longer to land than we expect. The problem is that working on them during feature freeze a) means they have to be perfect since there won't be any more chance to refine them and b) means nobody else can work on new development while they're being worked on. -- greg -- 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] small but useful patches for text search
On Mar 16, 2009, at 1:41 PM, Tom Lane wrote: A saner policy would mandate that large patches land near the start of a development cycle, but I don't know how we get people to do that. I think that if you can strictly time-limit the final CommitFest in the same way that you time-limit the others, then whatever doesn't get in that last one will be deferred to the first fest for the next major version. For those patches that *do* get in for the last CommitFest, they have to be ready within a month of the last day of the CommitFest month. So if the last fest for 8.4 was December, then any patches not ready by Feb 1 would be put off. This doesn't guarantee that people will get those big patches in earlier, but it will surely encourage them to do so. Just an idea. Best, David -- 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] small but useful patches for text search
On Mon, Mar 16, 2009 at 04:41:29PM -0400, Tom Lane wrote: Gregory Stark st...@enterprisedb.com writes: I think it's clear that stretching feature freezes is a failed policy. Yeah, it's the same old same old --- once again, we've bent over backwards to try to accommodate large patches at the end of a development cycle. I'm not sure that that's ever going to stop, because every time there are people cheerleading for said patches and insisting that the release will be so much better if we wait for them. Somehow we keep expecting that they'll get fixed and landed in a short period of time. A saner policy would mandate that large patches land near the start of a development cycle, but I don't know how we get people to do that. It's not easy, in the sense of timing and will, but it's not complex, technically. Basically, we start from a largest size we'll allow at the last commit fest, and increase it to linearly to, say, twice the size of the largest patch proposed at the first commit fest. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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
Re: [HACKERS] small but useful patches for text search
David E. Wheeler wrote: On Mar 16, 2009, at 1:41 PM, Tom Lane wrote: A saner policy would mandate that large patches land near the start of a development cycle, but I don't know how we get people to do that. I think that if you can strictly time-limit the final CommitFest in the same way that you time-limit the others, then whatever doesn't get in that last one will be deferred to the first fest for the next major version. The earlier commitfests were not time-limited either. They lasted until all the patches were dealt with; either committed or bumped to next commit fest. It's just that when you know there still at least one more commitfest a couple of months ahead, everyone feels more happy to bump a patch and to have one's patch bumped to the next one. In the last one, it's a lot harder to do that because that means bumping to the next release, and you don't even know when the next commitfest is. The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. In the next release cycle, I think we need to be more explicit about that policy throughout the release cycle and really enforce it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] small but useful patches for text search
Hi, Le 16 mars 09 à 21:41, Tom Lane a écrit : Gregory Stark st...@enterprisedb.com writes: I think it's clear that stretching feature freezes is a failed policy. A saner policy would mandate that large patches land near the start of a development cycle, but I don't know how we get people to do that. I think Open Source showed that you can't have both time based releases and feature based releases. Anytime any project try to accomodate those two objectives, they fail at both. It seems that PostgreSQL is leaning towards time based releases, which I think made up the majority in our community. What I'd propose is to refuse new patches posted in last two commit fests. Those are reserved to bake what we release. I'm not sure that's the best compromise you can do, even if it's definitely one, but it has the merit to be quite clear and easy to understand for contributors: you want your code to appear in X.Y, get it reviewed at least once (with feedbacks) before we enter the last but one commitfest. If you fail at this, you get to (try to) have your code in X.Y+1, which won't be released that far away (about 1 year away from a known date). Regards, -- dim -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 3:21 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Because that's what a respectable business does when a customer runs into a bug with software they sell. It's not a bug, it's expected behavior. You really call it expected that a process with a parenthesis in the process title can't use OCI, but gets an obscure error message instead? Sure, it's a corner case, and in most cases it can be worked around, but it's still a bug. Hmm, I wonder if you could do something malicious with it. Like, run a query along the lines of SELECT $$ (HOST=10.0.0.123) $$, connect()... to divert the connection to another server. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 5:22 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, I wonder if you could do something malicious with it. Like, run a query along the lines of SELECT $$ (HOST=10.0.0.123) $$, connect()... to divert the connection to another server. Not any more malicious than a connection string in and of itself. It's only used as a hierarchical name-value pair string, nothing is executed from it. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] small but useful patches for text search
On Mon, Mar 16, 2009 at 5:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The earlier commitfests were not time-limited either. They lasted until all the patches were dealt with; either committed or bumped to next commit fest. It's just that when you know there still at least one more commitfest a couple of months ahead, everyone feels more happy to bump a patch and to have one's patch bumped to the next one. In the last one, it's a lot harder to do that because that means bumping to the next release, and you don't even know when the next commitfest is. The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. In the next release cycle, I think we need to be more explicit about that policy throughout the release cycle and really enforce it. I mostly agree with this, but there is one fly in the ointment: if a patch really hasn't been seriously looked at by a committer, bumping it recreates one of the problems CommitFests were designed to avoid - patch limbo. I feel pretty good about the decisions to postpone Hot Standby and SE-PostgreSQL (not that my personal opinion necessarily counts for much, but that's how I feel); I would have felt less good about it if the same decision had been made a month ago. But on the other hand that means 8.4 will be a month later. If we could have gone through the same process two months earlier, or if we could have released 8.4beta and done those reviews on the side during the beta period, that would have been best of all. I basically agree with the idea of time-based releases, but if the committers cherry-pick the patches that are easily dealt with and never touch the more complex ones, it leads to a different set of problems... I'm not sure what can be done about that, though. ...Robert -- 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] small but useful patches for text search
Robert Haas wrote: On Mon, Mar 16, 2009 at 5:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The earlier commitfests were not time-limited either. They lasted until all the patches were dealt with; either committed or bumped to next commit fest. It's just that when you know there still at least one more commitfest a couple of months ahead, everyone feels more happy to bump a patch and to have one's patch bumped to the next one. In the last one, it's a lot harder to do that because that means bumping to the next release, and you don't even know when the next commitfest is. The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. In the next release cycle, I think we need to be more explicit about that policy throughout the release cycle and really enforce it. I mostly agree with this, but there is one fly in the ointment: if a patch really hasn't been seriously looked at by a committer, bumping it recreates one of the problems CommitFests were designed to avoid - patch limbo. I feel pretty good about the decisions to postpone Hot Standby and SE-PostgreSQL (not that my personal opinion necessarily counts for much, but that's how I feel); I would have felt less good about it if the same decision had been made a month ago. But on the other hand that means 8.4 will be a month later. If we could have gone through the same process two months earlier, or if we could have released 8.4beta and done those reviews on the side during the beta period, that would have been best of all. Well, we have been working on stuff for the past month so it was not like we were waiting on SE-PG to move forward. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, I wonder if you could do something malicious with it. There are any number of scenarios where exposing the client command-line contents to other database users represents a security hole, quite independently of whether anything falls over depending on the line contents. (I wonder whether there are any Oracle clients that accept a password on the command line, for instance.) The only reason this complaint is directed to us, and not Oracle, is that the complainant knows how far he's likely to get complaining to Oracle :-( regards, tom lane -- 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] typmod is always -1
Pavel Stehule pavel.steh...@gmail.com writes: I have a problem - every call of mvarcharin is with typmod = -1. Sure your typmod_in function works? Also, there are a bunch of scenarios where we rely on a cast function to apply the typmod rather than passing it to the input function initially. I'm not sure if the particular case you're checking here falls into that category, but you definitely should have a length conversion cast function in pg_cast if you expect to do anything useful with typmod. regards, tom lane -- 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] small but useful patches for text search
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. Uh, no, that's historical revisionism, cf http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan We expected and scheduled for a longer-than-normal final commitfest. There's two months in the original schedule, whereas expectation was that earlier ones would be less than a month (which mostly they were). What we did say, and didn't enforce, was that patches too large to be reviewed in a reasonably short time would be bounced. We thought we'd be able to make that stick if large patches got reviewed and applied in an incremental fashion over the series of commitfests. For one reason or another that never happened for SEPostgres. We should try to analyze exactly why not, although I think the bottom-line answer there has to do with nobody being particularly eager to work on it. Hot Standby had a different timeline, and quite frankly should have never been seriously considered for 8.4 at all. But I think that as long as SEPostgres was looming on the horizon, we didn't see the point of being strict about deadlines ... regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Mon, Mar 16, 2009 at 8:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, I wonder if you could do something malicious with it. There are any number of scenarios where exposing the client command-line contents to other database users represents a security hole, quite independently of whether anything falls over depending on the line contents. (I wonder whether there are any Oracle clients that accept a password on the command line, for instance.) Sure they let you pass the password on the command line, but they don't recommend it. Most of the utilities accept the syntax: utility user/p...@instance Just doing u...@instance will generally prompt for a password. Ahh, the number of passwords I've recovered from shell history files as a consultant... good times :) The only reason this complaint is directed to us, and not Oracle, is that the complainant knows how far he's likely to get complaining to Oracle :-( I don't doubt that. But, like I said, it's really a matter of the application name. In our case, Postgres falls into that corner case and we either choose to do something about it or we don't. I put the temporary solution out there for anyone that has the problem. If we want to fix it long-term, we'd have to look at one of the previously discussed alternatives to using (port). I don't particularly care one way or another, but if we were to change the ps line format, I just wanted to say that I preferred host:port rather than host(port). -- Jonah H. Harris, Senior DBA myYearbook.com
[HACKERS] Re: Problem with accesing Oracle from plperlu functionwhen using remote pg client.
On Tue, Mar 17, 2009 at 1:16 AM, Jonah H. Harris jonah.har...@gmail.com wrote: If we want to fix it long-term, we'd have to look at one of the previously discussed alternatives to using (port). That's still just a work-around, not a long-term fix. What happens if the user has parentheses or something else which confuses Oracle in their database name or username? -- greg -- 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: Problem with accesing Oracle from plperlu functionwhen using remote pg client.
That said, I don't see the problem switching to hostname:port. That seems like a more standard format anyways. It's not a complete solution to this problem but it does seem like it's less likely to confuse people and programs, Oracle included. We're getting awfully conservative about pretty minor changes these days. -- greg -- 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] small but useful patches for text searcht
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. Uh, no, that's historical revisionism, cf http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Development_Plan We expected and scheduled for a longer-than-normal final commitfest. There's two months in the original schedule, whereas expectation was that earlier ones would be less than a month (which mostly they were). What we did say, and didn't enforce, was that patches too large to be reviewed in a reasonably short time would be bounced. We thought we'd be able to make that stick if large patches got reviewed and applied in an incremental fashion over the series of commitfests. For one reason or another that never happened for SEPostgres. We should try to analyze exactly why not, although I think the bottom-line answer there has to do with nobody being particularly eager to work on it. I think SE-Postgres development timeline of going from feature-complete to give us the features we want really hampred things, and the fact that we didn't give SE-Postgres much feedback earlier, for the same reason (feature complete to give us the features we want). Hot Standby had a different timeline, and quite frankly should have never been seriously considered for 8.4 at all. But I think that as long as SEPostgres was looming on the horizon, we didn't see the point of being strict about deadlines ... Hot Standby wasn't in the original plan for 8.4, but someone suggested Hey, let's try., and we did. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] small but useful patches for text searcht
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Hot Standby had a different timeline, and quite frankly should have never been seriously considered for 8.4 at all. But I think that as long as SEPostgres was looming on the horizon, we didn't see the point of being strict about deadlines ... Hot Standby wasn't in the original plan for 8.4, but someone suggested Hey, let's try., and we did. Simon certainly made a heroic try at it, and I give him full marks for that. But HS was obviously not ready on 1 November. The point I was trying to make was that if SEPostgres had not been there, we'd have probably said on 1 November sorry, this has to wait for 8.5. As it was, we let him carry on trying to get the patch to a committable state. And of course all these things feed on each other --- when it's obvious that there is no immediate deadline, it's easy to let things slide a bit further. regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@momjian.us writes: I think any serious tools are now using pg_stat_activity. I saw we make the change in 8.4 and just document it. I wouldn't make the change for Oracle but rather for clarity. I think this is a non-solution, because it fails to guarantee that the process title contains nothing that will confuse Oracle. As somebody already pointed out, it's possible to have parens in a user or database name ... and for that matter, how sure are we that parens are the only trouble case? You'll be potentially breaking scripts that work now, in order to reduce but not eliminate the problem with Oracle. IMHO the right fix is the one that was touched on upthread: have DBI-Link temporarily install a plain-vanilla, punctuation-free process title (eg, just postgres) while starting an Oracle connection. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Remaining items for 8.4 (was Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1710))
Hi, I believe all the issues pointed out in http://archives.postgresql.org//pgsql-hackers/2008-10/msg01387.php as been covered in the current patch, as discussed with Simon. I also understand that we're running out of time. I'd like to push this to pgFoundry first and then work again together with Sync.Rep and Hot Standby for 8.5. 2009/3/16 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Bruce Momjian wrote: Well, we have been trying to go simplify the SE-PostgreSQL patch since September, and while we have made progress, we still have work to do, and at this point I think we have run out of time. I think we have given it a fair shot, but I don't think it is going to make 8.4. Agreed. At some point we just have to wrap up and cut the release. Tweaking indefinitely is not fair to all those patches that have already been pushed back, nor to those that have already been committed and are waiting to be released as part of 8.4. Apart from SE-PostgreSQL, we have four remaining items on the commitfest page: GIN fast insert I agree with Tom that we should just disable regular index scans for GIN. If someone misses it in 8.4, we can try to find a way to do it safely in 8.5. Removing existing capability is a bit worrisome, but I'm even less happy with the out of memory condition in this patch and in the partial match patch that has been committed already. That really needs to be cleaned up. B-Tree emulation for GIN I think this is in pretty good shape. Improve Performance of Multi-Batch Hash Join for Skewed Data Sets I believe everyone's happy with the performance testing that's been done. Some of the logic ought to be moved to the planner, and maybe there's some other cleanup to do. Proposal of PITR performance improvement Hmm. The first version of this was submitted back in October, as an external tool. There's still some outstanding issues: http://archives.postgresql.org//pgsql-hackers/2008-10/msg01387.php. I think we should push this to 8.5, for the same reasons as SE-PostgreSQL. On the positive side, the external tool is on pgFoundry for use with 8.4 (and earlier releases too?). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- -- Koichi Suzuki -- 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] small but useful patches for text searcht
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Hot Standby had a different timeline, and quite frankly should have never been seriously considered for 8.4 at all. But I think that as long as SEPostgres was looming on the horizon, we didn't see the point of being strict about deadlines ... Hot Standby wasn't in the original plan for 8.4, but someone suggested Hey, let's try., and we did. Simon certainly made a heroic try at it, and I give him full marks for that. But HS was obviously not ready on 1 November. The point I was trying to make was that if SEPostgres had not been there, we'd have probably said on 1 November sorry, this has to wait for 8.5. As it was, we let him carry on trying to get the patch to a committable state. Well, we had many other patches in November so it isn't clear that SE-PG was somehow what kept hot standby in-play. And of course all these things feed on each other --- when it's obvious that there is no immediate deadline, it's easy to let things slide a bit further. True, but we haven't been sitting around doing nothing, and we had to do most of what we have done since November whether we had SE-PG or host standby in play. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I think any serious tools are now using pg_stat_activity. I saw we make the change in 8.4 and just document it. I wouldn't make the change for Oracle but rather for clarity. I think this is a non-solution, because it fails to guarantee that the process title contains nothing that will confuse Oracle. As somebody already pointed out, it's possible to have parens in a user or database name ... and for that matter, how sure are we that parens are the only trouble case? You'll be potentially breaking scripts that work now, in order to reduce but not eliminate the problem with Oracle. IMHO the right fix is the one that was touched on upthread: have DBI-Link temporarily install a plain-vanilla, punctuation-free process title (eg, just postgres) while starting an Oracle connection. I was suggesting the colon rather to be clearer, not as an Oracle fix. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Jonah H. Harris wrote: On Mon, Mar 16, 2009 at 8:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, I wonder if you could do something malicious with it. There are any number of scenarios where exposing the client command-line contents to other database users represents a security hole, quite independently of whether anything falls over depending on the line contents. (I wonder whether there are any Oracle clients that accept a password on the command line, for instance.) Sure they let you pass the password on the command line, but they don't recommend it. Most of the utilities accept the syntax: utility user/p...@instance Just doing u...@instance will generally prompt for a password. Ahh, the number of passwords I've recovered from shell history files as a consultant... good times :) The only reason this complaint is directed to us, and not Oracle, is that the complainant knows how far he's likely to get complaining to Oracle :-( I don't doubt that. But, like I said, it's really a matter of the application name. In our case, Postgres falls into that corner case and we either choose to do something about it or we don't. I put the temporary solution out there for anyone that has the problem. If we want to fix it long-term, we'd have to look at one of the previously discussed alternatives to using (port). I don't particularly care one way or another, but if we were to change the ps line format, I just wanted to say that I preferred host:port rather than host(port). I think I was the one who originally added the port in paretheses, and I agree that a colon would have made more sense, but I never thought of it. postgres test 127.0.0.1(57966) idle vs. postgres test 127.0.0.1:57966 idle In fact my old BSD ps looks like: postgres test 127.0.0.1(58013) idle (postmaster) The old argv[0] is in parentheses. I think any serious tools are now using pg_stat_activity. I saw we make the change in 8.4 and just document it. I wouldn't make the change for Oracle but rather for clarity. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
Bruce Momjian br...@momjian.us writes: I was suggesting the colon rather to be clearer, not as an Oracle fix. Well, we could certainly debate the change on its own merits, but I'm not seeing that it's enough nicer to justify a risk of breaking ps-watching scripts. regards, tom lane -- 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] Problem with accesing Oracle from plperlu functionwhen using remote pg client.
I wrote: Well, we could certainly debate the change on its own merits, but I'm not seeing that it's enough nicer to justify a risk of breaking ps-watching scripts. Also, on second thought: what about IPv6 addresses? Colon doesn't look like a very good idea at all if you suppose that what's in front of it is IPv6. regards, tom lane -- 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] small but useful patches for text search
On Mon, Mar 16, 2009 at 6:20 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Mar 16, 2009 at 5:10 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The earlier commitfests were not time-limited either. They lasted until all the patches were dealt with; either committed or bumped to next commit fest. It's just that when you know there still at least one more commitfest a couple of months ahead, everyone feels more happy to bump a patch and to have one's patch bumped to the next one. In the last one, it's a lot harder to do that because that means bumping to the next release, and you don't even know when the next commitfest is. The original plan was that anything not 100% ready to commit at the beginning of the last commit fest will be bumped to the next release, and beta would start right after the first commit fest, a week or two after the submission deadline. We failed to enforce that. In the next release cycle, I think we need to be more explicit about that policy throughout the release cycle and really enforce it. I mostly agree with this, but there is one fly in the ointment: if a patch really hasn't been seriously looked at by a committer, bumping it recreates one of the problems CommitFests were designed to avoid - patch limbo. I feel pretty good about the decisions to postpone Hot Standby and SE-PostgreSQL (not that my personal opinion necessarily counts for much, but that's how I feel); I would have felt less good about it if the same decision had been made a month ago. But on the other hand that means 8.4 will be a month later. If we could have gone through the same process two months earlier, or if we could have released 8.4beta and done those reviews on the side during the beta period, that would have been best of all. Well, we have been working on stuff for the past month so it was not like we were waiting on SE-PG to move forward. Stuff related to the CommitFest? AFAICS, the only committer who has done any significant review or committing of patches in the last month is Heikki, who extensively reworked and then committed infrastructure changes for recovery on February 18th (2 days shy of a month ago) and then extensively reviewed Hot Standby and SE-PostgreSQL. It's really, really good that those patches have finally received some extensive review, both because now some version of each of them will likely make it into 8.5, and because now we have only a handful of patches left that Tom has said are pretty close to being committable. But I don't see how you can say it didn't delay the release. Frankly, if we'd rejected on January 1st all of the patches that (1) were submitted on time, (2) had been reviewed in a timely fashion, and (3) had not been resubmitted in largely committable form, we would have bounced infrastructure changes for recovery, hot standby, column-level privileges, autovacuum and reloptions, and probably parallel restore. If the committers who subsequently worked to get those changes into the tree had devoted an equal amount of effort to what would have been left in the commitfest after so doing, we would long since be done with this commitfest and into beta. But committers are free to spend their time on the projects they think are most interesting or which their employer is willing to pay them to work on, so that didn't happen. This is kind of understandable from the point of view of the committers, and it's even sorta good for the project as a whole to the extent that it prioritizes more interesting features over less interesting ones, but it's pretty frustrating for non-committer contributors. Any non-committer who pushes for a hard limit on the length of the commitfest is taking the risk that next time the patch that none of the committers are interested in will be theirs. So nobody really wants to argue for that. But the alternative is to argue that the commitfest should continue until all the patches have gotten some reasonable minimum amount of review, and that means the commitfest can continue for a very, very long time during which non-committers can't expect to get any new patches looked at. That's not very appealing either. The commitfest process really only works if the committers review all of the patches submitted within some reasonable period of time - if that isn't possible, there really isn't going to be a satisfactory solution. ...Robert -- 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] typmod is always -1
2009/3/17 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I have a problem - every call of mvarcharin is with typmod = -1. Sure your typmod_in function works? Also, there are a bunch of scenarios where we rely on a cast function to apply the typmod rather than passing it to the input function initially. I'm not sure if the particular case you're checking here falls into that category, but you definitely should have a length conversion cast function in pg_cast if you expect to do anything useful with typmod. thank you. It is it. What I understand, this behave is little bit confusing and undocumented :( regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
