Re: [GENERAL] psqlODBC connection
Thanks Ray and Edson, I was able to finally connect. And the ODBCAD32.exe for 32 as well as 64 bit compatible was present in SYSWOW64 folder. This ODBCAD32.exe contained the PostgreSQL driver in DNS window. Thanks for all the inputs which helped me to figure out the driver and connection string. Cheers, Mamatha -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: Thursday, October 20, 2011 3:54 PM To: Chan, Mamatha Kagathi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] psqlODBC connection On 20/10/2011 05:23, mamatha_kagathi_c...@dell.com wrote: Hi Ray, Thanks for the help. Since I am using classic ASP this is exactly what I am looking for. But there is a problem here. When I go the Create New Data Source window as mentioned in step ii, Ifind only sql server and Oracle drivers. I do not see PostgreSQL ODBC driver. But I am sure PsqlODBC is installed as it appears in the installed program list. Is there anything else I am missing, because of which the driver does not appear in the source?? Please let me know. Hi Mamatha, As Edson said upthread, you might have installed the 32-bit driver on 64-bit Windows, in which case you have to use the 32-bit ODBC manager. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostGIS in a commercial project
Hello, we are using PostgreSQL in our projects and would like to integrate PostGIS as well. Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to disable all pkey/fkey constraints globally
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of J.V. Sent: Friday, October 21, 2011 1:11 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] how to disable all pkey/fkey constraints globally Is there a simpler way than this to query the database for meta-data and get the constraint definitions? If I have the constraint name (which I do), I could store the constraint definition to a file or database table and recreate them if I could get the definition. This seems like a very simple thing to do, but nowhere can I find the meta- data I would need to first save the constraint, to later re-create it. thanks If you know constraint name and schema, then I don't see how you could possibly make it any simpler than the way I already suggested: Look into table pg_constraint and function pg_get_constraintdef. Of course, since this involves the use of a system catalogue, this is neither portable nor very reliable in the long run. You can also get all the information about foreign key constraints from information_schema tables, but that is more complicated. You'll need to join table_constraints, referential_constraints and key_column_usage, probably more than once. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On 20/10/2011 23:16, Henry Drexler wrote: On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell r...@iol.ie mailto:r...@iol.ie wrote: Are you sure about this? Try using RAISE NOTICE statements in the function to output the value of nnlength each time it's executed. Ray. Thank you for showing me the 'Rase Notice' , I had not seen that before and it helped me solve my problem right away. Glad you got sorted. What was the problem in the end? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GIN : Working with term positions
Hello,
I'm using a GIN index for a text column on a big table. I use it to rank
the rows, but I also need to get the term positions for each document of
a subset of documents. I assume these positions are stored in the index,
because doc says positions can be used for cover density ranking and
because to_tsvector function gives them :
select * from to_tsvector('I get lexemes and I get term positions.');
to_tsvector
'get':2,6 'lexem':3 'posit':8 'term':7
I can get the term positions with to_tsvector but only by parsing the
result string, is there any more handy way ? Something like :
select * from term_and_positions('I get lexemes and I get term positions.');
term| positions
-+---
'get' | {2,6}
'lexem' | {3}
Then, from the term positions, I need to get the character offset of
these term positions. I assume it is NOT stored in the GIN index. By
character offset I mean the character count from string begining to the
term. For the previous example it would be : 'get' -- {2,20}.
I thought about using ts_headline to return the whole text with term
tagged and then parse it to compute the character offsets from the tags.
But this function is very slow, seems like it does not use the GIN index
at all. And I suppose it can't because there is no way to know from a
term position where its substring is in the text.
Now I think the only solution is to make my own C function parsing the
text like to_tsvector does and counting terms AND characters read from
the begining of the text to match them. I got a look on the code, and it
does not seems easy to do because characters offset or string length are
never used by the parsetext function (ts_parse.c). If you have any other
suggestion, would love to hear that !
Regards, Yoann Moreau
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
When I was doing the mockups in excel using mid as a substitute for postgres substitute, I had to do -1 but that was actually not necessary as it was shorting the values, so the corrected plpgsql has the line nnlength := length(newnode); instead of nnlength := length(newnode)-1; On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell r...@iol.ie wrote: On 20/10/2011 23:16, Henry Drexler wrote: On Thu, Oct 20, 2011 at 5:41 PM, Raymond O'Donnell r...@iol.ie mailto:r...@iol.ie wrote: Are you sure about this? Try using RAISE NOTICE statements in the function to output the value of nnlength each time it's executed. Ray. Thank you for showing me the 'Rase Notice' , I had not seen that before and it helped me solve my problem right away. Glad you got sorted. What was the problem in the end? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie
Re: [GENERAL] PostGIS in a commercial project
I'm not a lawyer, cockroach, or hobbyist license enthusiast. Here's my impression. First, I'm sure this must come IP a lot. Have you looked for a PostGIS licensing faq? Checked their mailing lists? The issues with bundling MySQL were mostly around the GPL-licensed client library, something that doesn't apply to Pg's bsd-licensed libpq. You don't, AFAIK, need to link to any PostGIS client side library to use PostGIS, so that shouldn't be an issue. For the server side stuff you would certainly have to distribute any postgis code changes, plus probably any server code changes since you're bundling them with postgis. Your SQL and data would be no more affected than a program is affected when compiled with the GPL-licensed gcc. Personally I can't see any reason for concern. You'd have to distribute a GPL notice and/or PostGIS sources with the product to customers, of course, but that's not a biggie. If you use something like a PostGIS extension to libpq / libpqtypes / etc client side, and that is also GPL, that might be a problem. On Oct 21, 2011 3:26 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Hello, we are using PostgreSQL in our projects and would like to integrate PostGIS as well. Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql - FDW, ForeignScanState and subqueries
So, according to your advice, I've looked at the IndexScan implementation and here is what I came up with. At execution time, if I find an operation expression involving a Param, the param expression is evaluated with ExecEvalExpr (after being initted on the fly), and then considered exactly the same as a Const-constvalue. Is there any problem with that I should know about ? I should maybe cache the resulting ExprState information during plan time, and only evaluate it during execution time. Would that make a huge difference ? Regarding the columns extraction information, I'll try to move my code in the plan-time hook. Thank you very much ! Best Regards, -- Ronan Dunklau 2011/10/20 Tom Lane t...@sss.pgh.pa.us Ronan Dunklau rdunk...@gmail.com writes: Now, if I query my table like this (a subquery, joined on the outer query), what info should I be able to parse from the PlanState ? select name, (select max(value) from test t2 where t2.name = t1.name) as max from test t1; I don't really know much about postgresql internals, regarding execution plans, but here is what I guessed from what I managed to extract from the plan tree so far: - The outer query is executed once, restricting only the needed columns - The subquery is executed once for each row, with: - all columns from the table are requested in a target entry node, even if the query only need the name and value columns. - the value corresponding to the name from the outer query is somehow passed in as an Expr of type T_Param, in the quals field. How can I retrieve the value from the Param struct ? You shouldn't be trying to do it at that level --- at most you ought to do ExecEvalExpr on the expression tree. Otherwise you're going to end up reinventing most of execQual.c to cover all cases. Right now we don't have very much infrastructure for helping FDWs push restriction clauses over to the far end, which is what you seem to be trying to do. I hope that will emerge in 9.2 or 9.3. If you want to help you could start by looking at what the indexscan machinery does to extract usable indexquals, because that's more or less the same problem. If you feel that's out of your league, I'd suggest not bothering with pushing clauses across right now. The source does not help me much with what to do regarding the various fields in the struct. Does postgresql really fetch all columns in a subselect, or am I just parsing the tree in a wrong way ? Hmm, yeah, the planner thinks it's cheaper to extract all columns than just some of them. This is appropriate for plain tables but I can see that it's a bit dubious for a foreign table. Maybe we should shut off the use_physical_tlist optimization for foreign tables. However, it's not really intended that FDWs should be relying on the execution-time targetlist to figure out which columns to pull across anyway. The right way to make such an optimization is for the plan-time hook to determine which columns are needed (look at the reltargetlist) and save that information in the plan node. regards, tom lane
Re: [GENERAL] force JDBC driver fetch / autocommit parameters?
Can you be more specific as to what you are looking for ? I presume there is something between you and the connection so when you turn autocommit on it doesn't work ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, Oct 20, 2011 at 4:34 PM, S. Balch sba...@gmail.com wrote: Greetings, I'm using the postgresql-9.1-901.jdbc4 driver in an application (that I don't control) that's not passing along my specified fetch and autocommit parameters. Is there anyway I can force the driver to use my required parameters outside of the application? Thanks, Sean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgres/postgis eats memory
Hi guys, on a test maschine from my university i'm running into a weird problem. the issue (i attached detail info): The running query eats more and more memory.(seen in htop) It'll start using virtual memory and after eating up that one, too, postgres will crash with: psql:/home/mguether/sql/benchmark.sql:6: NOTICE: std::bad_alloc psql:/home/mguether/sql/benchmark.sql:6: ERROR: GEOS intersects() threw an error! once it gave me an psql:/home/mguether/sql/benchmark.sql:6: ERROR: out of memory DETAIL: Failed on request of size 182949. after the crash, all memory/swap is free again, so it really is postgres :) i don't really know how to debug it any more detailed or fix this issue in any way. Does anyone here have a hint, where the issue might be? thanks, martin here some information: the maschine: 16 GB RAM, i7 4cores x64 2.6.32-21-server ubuntu installed postgres: psql (PostgreSQL) 8.4.9 including postgis POSTGIS=1.4.0 GEOS=3.1.0-CAPI-1.5.0 PROJ=Rel. 4.7.1, 23 September 2009 USE_STATS i changed two parameters from the default config: shared_buffers = 512MB work_mem = 256MB using osm2pgsql I important some GEO data, and want to execute this query: SELECT a.osm_id, w.osm_id FROM planet_osm_line w, planet_osm_polygon a WHERE ST_Intersects(w.way,a.way) AND w.highway IS NOT NULL; the EXPLAIN gives me: Nested Loop (cost=0.00..26427424.16 rows=1511021 width=8) Join Filter: _st_intersects(w.way, a.way) - Seq Scan on planet_osm_line w (cost=0.00..49213.10 rows=1137488 width=837) Filter: (highway IS NOT NULL) - Index Scan using osm_areas_index on planet_osm_polygon a (cost=0.00..23.10 rows=6 width=824) Index Cond: (w.way a.way) (6 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reading PG data from MySQL stored procedure
Craig Ringer ring...@ringerc.id.au wrote: On 10/21/2011 03:56 AM, Gauthier, Dave wrote: The software system they are being forced to use gives them the ability to send queries to a MySQL which has already been connected to. However, they do have the authority to add things to that DB, like stored procedures. This user isn't coding anything per-se, they're just using the interface provided. But they can call a stored procedure/function because that's ligit sql. The data that flows from that goes into other parts of the system for reporting, etc... . AFAIK, the only way you'd be able to get from MySQL to Pg directly would be to install a user-defined function written in C that used libpq to connect to Pg. You can't do that over a basic connection to MySQL, you need the ability to install binaries on the server. The system is too locked down to permit what you want to do on the MySQL end. You'd have to make a connection to Pg from the client side, extract the data you wanted and send it down the connection handle for MySQL that you already have. There's a bit too much hand-waving and not enough specifics about language, environment, etc to say anything more. Is this some kind of report-writing system? A RAD environment? What? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Temporally disabled foreign key constraint check?
Good morning, Is there a way to temporally disabled foreign key constraints something like: SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 Thanks a lot! Emi -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca+1 514 848-2424 x5884 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temporally disabled foreign key constraint check?
On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu em...@encs.concordia.ca wrote: Good morning, Is there a way to temporally disabled foreign key constraints something like: SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 You can disable *triggers* on a table (which will disable all the FK constraints, but not things like 'not nul' or 'unique'). For Disable: update pg_class set reltriggers=0 where relname = 'TEST'; For Enable: update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='TEST'; --Raghu
Re: [GENERAL] Temporally disabled foreign key constraint check?
raghu ram raghuchenn...@gmail.com hat am 21. Oktober 2011 um 17:12 geschrieben: On Fri, Oct 21, 2011 at 8:33 PM, Emi Luem...@encs.concordia.cawrote: Good morning, Is there a way to temporally disabled foreign key constraints something like: SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 You can disable *triggers* on a table (which will disable all the FK constraints, but not things like 'not nul' or 'unique'). For Disable: update pg_class set reltriggers=0 where relname = 'TEST'; For Enable: update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='TEST'; No, don't manipulate pg_* - tables. Use instead ALTER TABLE ... DISABLE TRIGGER ... Regards, Andreas --Raghu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temporally disabled foreign key constraint check?
Thank you first. I believe that upate pg_class can only be done by superuser, right? Besides, if I need the whole schema's foreign keys to be disabled and then enabled later. Is there a simple command could do it? Similar to mysql's set FOREIGN_KEY_CHECKS = false/true? Emi On 10/21/2011 11:12 AM, raghu ram wrote: On Fri, Oct 21, 2011 at 8:33 PM, Emi Lu em...@encs.concordia.ca mailto:em...@encs.concordia.ca wrote: Good morning, Is there a way to temporally disabled foreign key constraints something like: SET FOREIGN_KEY_CHECKS=0 When population is done, will set FOREIGN_KEY_CHECKS=1 You can disable *triggers* on a table (which will disable all the FK constraints, but not things like 'not nul' or 'unique'). For Disable: update pg_class set reltriggers=0 where relname = 'TEST'; For Enable: update pg_class set reltriggers = count(*) from pg_trigger where pg_class.oid=tgrelid and relname='TEST'; --Raghu -- Emi Lu, ENCS, Concordia University, Montreal H3G 1M8 em...@encs.concordia.ca+1 514 848-2424 x5884 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] force JDBC driver fetch / autocommit parameters?
Dave, We're just running the JVM out of memory with a large query result. By turning off autocommit and setting a reasonable fetch size this problem goes away. The application using this driver does have a way to set these, but it seems to be broken. Thanks, Sean On Fri, Oct 21, 2011 at 9:51 AM, Dave Cramer p...@fastcrypt.com wrote: Can you be more specific as to what you are looking for ? I presume there is something between you and the connection so when you turn autocommit on it doesn't work ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, Oct 20, 2011 at 4:34 PM, S. Balch sba...@gmail.com wrote: Greetings, I'm using the postgresql-9.1-901.jdbc4 driver in an application (that I don't control) that's not passing along my specified fetch and autocommit parameters. Is there anyway I can force the driver to use my required parameters outside of the application? Thanks, Sean
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell r...@iol.ie wrote:
Glad you got sorted. What was the problem in the end?
Ray.
apart from the solution I sent earlier I have now noticed an abberation -
and in testing I have not isolated but have a simple example.
for instance, using the function ln will reduce to match l but nl will not
reduce to match l. There are other examples but this was the simplest I
could find.
All that is going on here is removing a character from the string and
comparing.
In the 'raise notice' you can see that it has properly broken up the 'nl'
into first an 'n' and compared it to the next row's 'l' then it broke it
into an 'l' out of the 'nl' and compared that to the 'n', bit it did not
match, you will see others that have worked.
Function
create or replace function nnodetestt(text) returns text language plpgsql
immutable as $$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
RAISE NOTICE 'number %', nnlength;
for i in 1..(nnlength) loop
select into t
node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'),('ls o'),('ls '),('lsn_o'),('lsn_'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i);
RAISE NOTICE 'textconv: %' , left(newnode, i-1)||right(newnode, nnlength-i);
end loop;
return t;
END;
$$
Select Statement
select
node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'),('ls o'),('ls '),('lsn_o'),('lsn_'))
blast(node)
Output of Select Statement
+---++
| node | nnodetestt |
+---++
| nl||
| l ||
| ln| l |
| l ||
| ls o | ls |
| ls||
| lsn_o | lsn_ |
| lsn_ ||
+---++
Raise Notice output (just for the first 4 rows
NOTICE: number 2
NOTICE: number 1
CONTEXT: SQL statement select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)
PL/pgSQL function nnodetestt line 11 at SQL statement
NOTICE: textconv:
CONTEXT: SQL statement select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)
PL/pgSQL function nnodetestt line 11 at SQL statement
NOTICE: textconv: l
NOTICE: textconv: n
NOTICE: number 1
NOTICE: textconv:
NOTICE: number 2
NOTICE: textconv: n
NOTICE: number 1
CONTEXT: SQL statement select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)
PL/pgSQL function nnodetestt line 11 at SQL statement
NOTICE: textconv:
CONTEXT: SQL statement select node,
nnodetestt(node)
from
(Values('nl'),('l'),('ln'),('l'))
blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i)
PL/pgSQL function nnodetestt line 11 at SQL statement
NOTICE: textconv: l
NOTICE: number 1
NOTICE: textconv:
Total query runtime: 19 ms.
4 rows retrieved.
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On Fri, Oct 21, 2011 at 1:02 PM, Henry Drexler alonup...@gmail.com wrote:
On Fri, Oct 21, 2011 at 6:10 AM, Raymond O'Donnell r...@iol.ie wrote:
Glad you got sorted. What was the problem in the end?
Ray.
apart from the solution I sent earlier I have now noticed an abberation -
and in testing I have not isolated but have a simple example.
for instance, using the function ln will reduce to match l but nl will not
reduce to match l. There are other examples but this was the simplest I
could find.
All that is going on here is removing a character from the string and
comparing.
In the 'raise notice' you can see that it has properly broken up the 'nl'
into first an 'n' and compared it to the next row's 'l' then it broke it
into an 'l' out of the 'nl' and compared that to the 'n', bit it did not
match, you will see others that have worked.
here is a simpler shorter example, one working, the other one not:
create or replace function nnodetestt(text) returns text language plpgsql
immutable as $$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
RAISE NOTICE 'number %', nnlength;
for i in 1..(nnlength) loop
select into t node from
(Values('whats'),('what'),('listetomelease'),('listetomeplease'))
blast(node) where node = left(newnode, i-1)||right(newnode, nnlength-i);
RAISE NOTICE 'nnlength %', nnlength;
--raise notice 'increment %',right(newnode, nnlength-i);
RAISE NOTICE 'textbreakout: %' , left(newnode, i-1)||right(newnode,
nnlength-i);
end loop;
return t;
END;
$$
select
node,
nnodetestt(node)
from
(Values('whats'),('what'),('listetomelease'),('listetomeplease'))
blast(node)
and the messages:
NOTICE: number 5
NOTICE: nnlength 5
NOTICE: textbreakout: hats
NOTICE: nnlength 5
NOTICE: textbreakout: wats
NOTICE: nnlength 5
NOTICE: textbreakout: whts
NOTICE: nnlength 5
NOTICE: textbreakout: whas
NOTICE: nnlength 5
NOTICE: textbreakout: what
NOTICE: number 4
NOTICE: nnlength 4
NOTICE: textbreakout: hat
NOTICE: nnlength 4
NOTICE: textbreakout: wat
NOTICE: nnlength 4
NOTICE: textbreakout: wht
NOTICE: nnlength 4
NOTICE: textbreakout: wha
NOTICE: number 14
NOTICE: nnlength 14
NOTICE: textbreakout: istetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: lstetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: litetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: lisetomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listtomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listeomelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetmelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetoelease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomlease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeease
NOTICE: nnlength 14
NOTICE: textbreakout: listetomelase
NOTICE: nnlength 14
NOTICE: textbreakout: listetomelese
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeleae
NOTICE: nnlength 14
NOTICE: textbreakout: listetomeleas
NOTICE: number 15
NOTICE: nnlength 15
NOTICE: textbreakout: istetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: lstetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: litetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: lisetomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listtomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listeomeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetmeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetoeplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomplease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomelease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepease
NOTICE: nnlength 15
NOTICE: textbreakout: listetomeplase
NOTICE: nnlength 15
NOTICE: textbreakout: listetomeplese
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepleae
NOTICE: nnlength 15
NOTICE: textbreakout: listetomepleas
Total query runtime: 93 ms.
4 rows retrieved.
Re: [GENERAL] PostGIS in a commercial project
At 09:26 21/10/2011, Thomas Kellerer wrote: Hello, we are using PostgreSQL in our projects and would like to integrate PostGIS as well. Now PostGIS is licensed under the GPL and I wonder if we can use it in a commercial (customer specific) project then. The source code will not be made open source, but of course the customer will get the source code. Is it still OK to use the GPL licensed PostGIS in this case? Is that then considered a derivative work because the application will not work without PostGIS? If it's pure GPL, then postgresql is automagically relicenced to GPL, because postgresql allows relicencing and GPL force it to be GPL. Your source code must be in GPL too. Remember, it's a virus licence and has the same problem that Midas king had. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
I realize I have sent a lot of messages on this thread so this will be the
last one unless I come up with a solution, then I will post that.
The idea behind this is to take a string and remove one character from it
successively and try to match that against any of the nodes in the query.
So for the following query 'pig dog cat' should be matched to 'pig dogcat'
when 'pig dog cat' is passed through the function. The reason for this is
because when successively removing characters 'pig dog cat' will get to the
point of 'pig dogcat' and therefore equal to the other node. (this process
can be seen in the raise notice output below).
The confusing thing is this works with other word pairs such as 'ls' 'l' and
longer ones, but there are many that it fails on for some inexplicable(to
me) reason.
Function:
-
create or replace function nnodetestt(text) returns text language plpgsql
immutable as $$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
RAISE NOTICE 'number %', nnlength;
for i in 1..(nnlength) loop
select into t node from (Values('pig dogcat'),('pig dog cat')) blast(node)
where node = left(newnode, i-1)||right(newnode, nnlength-i);
-- RAISE NOTICE 'nnlength %', nnlength;
--raise notice 'increment %',right(newnode, nnlength-i);
RAISE NOTICE 'textbreakout: %' , left(newnode, i-1)||right(newnode,
nnlength-i);
end loop;
return t;
END;
$$
Query:
-
select
node,
nnodetestt(node)
from
(Values('pig dogcat'),('pig dog cat'))
blast(node)
Raise Notice Output:
-
NOTICE: number 10
NOTICE: textbreakout: ig dogcat
NOTICE: textbreakout: pg dogcat
NOTICE: textbreakout: pi dogcat
NOTICE: textbreakout: pigdogcat
NOTICE: textbreakout: pig ogcat
NOTICE: textbreakout: pig dgcat
NOTICE: textbreakout: pig docat
NOTICE: textbreakout: pig dogat
NOTICE: textbreakout: pig dogct
NOTICE: textbreakout: pig dogca
NOTICE: number 11
NOTICE: textbreakout: ig dog cat
NOTICE: textbreakout: pg dog cat
NOTICE: textbreakout: pi dog cat
NOTICE: textbreakout: pigdog cat
NOTICE: textbreakout: pig og cat
NOTICE: textbreakout: pig dg cat
NOTICE: textbreakout: pig do cat
NOTICE: textbreakout: pig dogcat- here you can see it matches, so it
should be working
NOTICE: textbreakout: pig dog at
NOTICE: textbreakout: pig dog ct
NOTICE: textbreakout: pig dog ca
Total query runtime: 12 ms.
2 rows retrieved.
Re: [GENERAL] plpgsql at what point does the knowledge of the query come in?
On Fri, Oct 21, 2011 at 2:57 PM, Henry Drexler alonup...@gmail.com wrote:
I realize I have sent a lot of messages on this thread so this will be the
last one unless I come up with a solution, then I will post that.
Resolved.
Ray - thanks again for your help.
The pattern was it was only matching those that had a change to the end of
the partner word.
so the function was going through then only returning the last comparison.
I needed to add an if statement into the function to 'return r' when it
evaluated to true.
Thus:
create or replace function nnodetestt(text) returns text language plpgsql
immutable as $$
DECLARE
newnode alias for $1;
nnlength integer;
t text;
nmarker text;
BEGIN
nnlength := length(newnode);
RAISE NOTICE 'number %', nnlength;
for i in 1..(nnlength) loop
select into t node from
(Values('one'),('on'),('fivehundredsixtyseven'),('fivehundredsixtysevens'),('one
two three fou'),('one two three four'),('onetwo three ninety'),('one two
three ninety')) blast(node) where node = left(newnode, i-1)||right(newnode,
nnlength-i);
-- RAISE NOTICE 'nnlength %', nnlength;
--raise notice 'increment %',right(newnode, nnlength-i);
RAISE NOTICE 'textbreakout: %' , left(newnode, i-1)||right(newnode,
nnlength-i);
if t = left(newnode, i-1)||right(newnode, nnlength-i) then return t;
end if;
end loop;
return t;
END;
$$
select
node,
nnodetestt(node)
from
(Values('one'),('on'),('fivehundredsixtyseven'),('fivehundredsixtysevens'),('one
two three fou'),('one two three four'),('onetwo three ninety'),('one two
three ninety'))
blast(node)
[GENERAL] Anyone using the solaris 11 precompiled binaries on opensolaris snv_134
Hi, I'm trying to run the precomplied binaries form postgresql.org for solaris 11 intel. The Read-me says it has been compiled on opensolaris 2010.11 which is (to the best of my knowledge snv_134). My machine is also snv_134. When I do ldd /usr/postgres/9.1-pgdg/bin/postgres it tells me that: libsocket.so.1 (SUNW_1.7) = (version not found) $ pvs /lib/libsocket.so.1 libnsl.so.1 (SUNW_1.7, SUNWprivate_1.1); libc.so.1 (SUNW_1.23, SUNWprivate_1.1); libsocket.so.1; SUNW_1.6; SUNW_1.5; SUNW_1.4; SUNW_1.3; SUNW_1.2; SUNW_1.1; SUNW_0.7; SUNWprivate_1.3; SUNWprivate_1.2; SUNWprivate_1.1; So my libc has only the interfaces up to SUNW_1.6. How can this happen that two snv_134 machines have different libsockets? I thought that the new Solaris pkg Package management solved this Problem. Thanks for your insight Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anyone using the solaris 11 precompiled binaries on opensolaris snv_134
Hi, I'm trying to run the precomplied binaries form postgresql.org for solaris 11 intel. The Readme says it has been compiled on opensolaris 2010.11 which is (to the best of my knowledge snv_134). My machine is also snv_134. When I do ldd /usr/postgres/9.1-pgdg/bin/postgres it tells me that: libsocket.so.1 (SUNW_1.7) = (version not found) $ pvs /lib/libsocket.so.1 libnsl.so.1 (SUNW_1.7, SUNWprivate_1.1); libc.so.1 (SUNW_1.23, SUNWprivate_1.1); libsocket.so.1; SUNW_1.6; SUNW_1.5; SUNW_1.4; SUNW_1.3; SUNW_1.2; SUNW_1.1; SUNW_0.7; SUNWprivate_1.3; SUNWprivate_1.2; SUNWprivate_1.1; So my libc has only the interfaces up to SUNW_1.6. How can this happen that two snv_134 machines have different libsockets? Thanks for your insight Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT Query on DB table preventing inserts
Hi, Sorry for the late response on this. On Wed, Aug 31, 2011 at 09:40, Tomas Vondra t...@fuzzy.cz wrote: On 31 Srpen 2011, 1:07, Dan Scott wrote: On Tue, Aug 30, 2011 at 13:52, Daniel Verite dan...@manitou-mail.org wrote: Dan Scott wrote: the insert process is unable to insert new rows into the database You should probably provide the error message on insert or otherwise describe how it's not working. Normally reading does not unintentionally prevent writing in a concurrent session. I've investigated a little further and it seems that they don't really 'fail'. Just start taking significantly longer to insert, and the messages start backing up and eventually stop being sent because What messages are you talking about? I guess I need to explain the system a little better. The purpose of this database is to log HL7 messages received from another system. We then parse these messages to extract the data. So the 'messages' in this case are the messages that I'm receiving from the remote system, they are my data which I'm inserting into the database. they're not being acknowledged. I can see a few WARNING: pgstat wait timeout messages around the time that this is happening in the syslog. The pgstat messages are a typical symptom of I/O bottleneck - it just means you'ro doing a lot of writes, more than the drives can take. Enable checkpoint logging (log_checkpoints=on) and watch the system stats (e.g. using 'iostat -x' or vmstat), my bet is this is a checkpoint or pdflush issue. Checkpoint logging is enabled and I've setup monitoring using munin. iostat -x 10 shows the following (vdb is the data drive): avg-cpu: %user %nice %system %iowait %steal %idle 0.150.000.100.100.00 99.65 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util vda 0.00 0.000.000.70 0.00 5.60 8.00 0.001.14 0.86 0.06 vdb 0.00 3.300.005.40 0.0069.60 12.89 0.058.57 1.52 0.82 dm-0 0.00 0.000.000.70 0.00 5.60 8.00 0.001.14 0.86 0.06 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.300.000.100.150.00 99.45 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util vda 0.00 0.000.001.50 0.0012.00 8.00 0.001.67 0.47 0.07 vdb 0.00 3.500.004.60 0.0064.80 14.09 0.023.96 1.50 0.69 dm-0 0.00 0.000.001.50 0.0012.00 8.00 0.001.67 0.47 0.07 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.050.000.050.150.00 99.75 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util vda 0.00 0.000.000.80 0.00 6.40 8.00 0.000.88 0.37 0.03 vdb 0.00 1.300.002.80 0.0032.80 11.71 0.013.79 1.75 0.49 dm-0 0.00 0.000.000.80 0.00 6.40 8.00 0.000.88 0.37 0.03 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.050.000.050.000.00 99.90 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util vda 0.00 0.000.000.40 0.00 3.20 8.00 0.001.75 0.75 0.03 vdb 0.00 0.100.000.70 0.00 6.40 9.14 0.000.71 0.71 0.05 dm-0 0.00 0.000.000.40 0.00 3.20 8.00 0.001.75 0.75 0.03 dm-1 0.00 0.000.000.00 0.00 0.00 0.00 0.000.00 0.00 0.00 Which column should I be looking at? Anyway we need more info about your system - Pg version, amount of RAM, shared buffers, checkpoint settings (segments, completion) and page cache config (/proc/sys/vm/). A few lines of vmstat/iostat output would help too. The server is running Scientific Linux 6.0, PostgreSQL 9.4.9, 2GB ram. The PostgreSQL configuration is fairly standard: [root@aorta ~]# grep shared /data/pgsql/data/postgresql.conf # Note: Increasing max_connections costs ~400 bytes of shared memory per shared_buffers = 24MB # min 128kB # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory #shared_preload_libraries = '' # (change requires restart) # Note: Each lock table slot uses ~270 bytes of shared memory, and there are [root@aorta ~]#
[GENERAL] adding a column takes FOREVER!
All, I'm adding a column in postgres 8.3 with the syntax: alter table images add column saveState varchar(1) default '0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries. Why so long? Is there a way to speed that up? The table has ~ 50 columns. Thanks, Eric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgres/postgis eats memory
On 10/21/2011 09:05 PM, Martin Guether wrote: Hi guys, on a test maschine from my university i'm running into a weird problem. the issue (i attached detail info): The running query eats more and more memory.(seen in htop) It'll start using virtual memory and after eating up that one, too, postgres will crash with: psql:/home/mguether/sql/benchmark.sql:6: NOTICE: std::bad_alloc psql:/home/mguether/sql/benchmark.sql:6: ERROR: GEOS intersects() threw an error! That's an allocation within PostGIS (as it's C++) failing. Given that the issues you're having are mainly with PostGIS I'd recommend asking on the PostGIS mailing lists for help. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] force JDBC driver fetch / autocommit parameters?
On 10/21/2011 10:38 PM, S. Balch wrote: Dave, We're just running the JVM out of memory with a large query result. By turning off autocommit and setting a reasonable fetch size this problem goes away. The application using this driver does have a way to set these, but it seems to be broken. I don't think there's any support for forcing these params at the moment. You could modify the JDBC driver to hard-code these parameters and just use your hacked JDBC driver. That'd probably be a maintenance mess in the long run, though. It'd be better to write a patch to add JDBC URL parameter support for overriding them, start using a patched driver you recompile yourself, and submit that to pgsql-j...@postgresql.org for inclusion in future versions so you don't have to worry about keeping it up to date. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] adding a column takes FOREVER!
On 10/22/2011 06:45 AM, Eric Smith wrote: All, I'm adding a column in postgres 8.3 with the syntax: alter table images add column saveState varchar(1) default '0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries. Why so long? Is there a way to speed that up? The table has ~ 50 columns. PostgreSQL has to re-write the table to add the column with its new value. I guess in theory PostgreSQL could keep track of the default for the new column and write it in lazily when a row is touched for some other reason. That'd quickly get to be a nightmare if the user ALTERed the column again to change the default (you'd have to write the _old_ default to all the columns before making the change) and in many other circumstances, though. You can ALTER your table to add the column without the default, ALTER it again to add the default, then manually UPDATE the values to the new default in the background if you want. Doing it that way will cause the new column to be initially added as NULL, which doesn't require a full table re-write at ALTER time. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] adding a column takes FOREVER!
Eric Smith eric_h_sm...@mac.com writes: I'm adding a column in postgres 8.3 with the syntax: alter table images add column saveState varchar(1) default '0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries. Why so long? Is there a way to speed that up? The table has ~ 50 columns. As Craig explained, that does require updating every row ... but for only 14000 rows, it doesn't seem like it should take that long. A quick test with 8.3 on my oldest and slowest machine: regression=# create table foo as select generate_series(1,14000) as x; SELECT Time: 579.518 ms regression=# alter table foo add column saveState varchar(1) default '0'; ALTER TABLE Time: 482.143 ms I'm thinking there is something you haven't told us about that creates a great deal of overhead for updates on this table. Lots and lots o' indexes? Lots and lots o' foreign key references? Inefficient triggers? Or maybe it's just blocking behind somebody else's lock? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
