Re: [GENERAL] Identifying old/unused views and table
On Wed, 2011-09-28 at 09:04 +0800, Craig Ringer wrote: > On 09/28/2011 04:51 AM, Jason Long wrote: > > I have an application with a couple hundred views and a couple hundred > > tables. > > > > Is there some way I can find out which views have been accessed in the > > last 6 months or so? Or some way to log this? > > > > I know there are views and tables that are no longer in used by my > > application and I am looking for a way to identify them. > > Look at the pg_catalog.pg_stat* tables > I fail to see how that gives him any answer on the views, and tables no longer used. AFAICT, there's no way to know for views (apart from logging all queries in the log). As for tables, still apart from the log, pg_stat_user_tables could give an answer if he was monitoring it at least the last six months. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- 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] Download States and Capitals Database
- Original Message - > From: "Adarsh Sharma" > > This Link gives the capitals of all countries but I need the states > and their capitals in all these countries too.. > But I think this is not possible because very few countries are > divided into states and some into cantons, county etc. Yep. The only one I can think of off the top of my head is the USA, although I can't exactly claim accurate geopolitical knowledge about the whole world :) Here in Belgium, we have a bunch of provinces. France has departements. It varies a lot. I suppose you could build a hierarchy of geographical subdivisions, but you'd still have to save the appropriate naming schemes per-country. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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] Download States and Capitals Database
On Wed, 2011-09-28 at 09:39 +0530, Adarsh Sharma wrote: > This Link gives the capitals of all countries but I need the states > and their capitals in all these countries too.. > But I think this is not possible because very few countries are > divided into states and some into cantons, county etc. > > > Thanks > > planas wrote: > > > On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: > > > > > Dear all, > > > > > > I googled a lot and find data of all countries , cities , > > > location etc from Geo Spatial websites but I am able to find the > > > data that shows all states & their respective capitals in world. > > > Please let me know if anyone as prior information about this ? > > > > > > > > > Thanks > > > > > > Try this link from Wikipedia > > http://en.wikipedia.org/wiki/World_capital_cities_by_country . I > > just searched for world capitals by country. > > -- > > Jay Lozier > > jsloz...@gmail.com > > I would try Wikipedia and search for lists of states, cantons, provinces, etc of each country. The lists I have needed have had the capitals listed for each state, etc. Tedious but if you have to google for list it might be easier. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Download States and Capitals Database
This Link gives the capitals of all countries but I need the states and their capitals in all these countries too.. But I think this is not possible because very few countries are divided into states and some into cantons, county etc. Thanks planas wrote: On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: Dear all, I googled a lot and find data of all countries , cities , location etc from Geo Spatial websites but I am able to find the data that shows all *states & their respective capitals* in world. Please let me know if anyone as prior information about this ? Thanks Try this link from Wikipedia http://en.wikipedia.org/wiki/World_capital_cities_by_country . I just searched for world capitals by country. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] Identifying old/unused views and table
On 09/28/2011 04:51 AM, Jason Long wrote: I have an application with a couple hundred views and a couple hundred tables. Is there some way I can find out which views have been accessed in the last 6 months or so? Or some way to log this? I know there are views and tables that are no longer in used by my application and I am looking for a way to identify them. Look at the pg_catalog.pg_stat* tables -- 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] bytea columns and large values
On 09/28/2011 01:01 AM, David North wrote: testdb=# select * from problem_table; ERROR: invalid memory alloc request size 2003676411 What Pg version are you using? On which platform? 32-bit or 64-bit OS? If 64-bit, with a 32-bit or 64-bit build of PostgreSQL? -- 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] Problem with the 9.1 one-click installer Windows7 64bit
the process explorer was the big help for me. what i did while the installation was running was open up process explorer saw what it was running and obviously figured out what it was trying to do and i just went and manually did it myself then i'd kill the icalc.exe...after that another one popped up and i did it again the first time was for the user i was logged in as...the second was for the postgres user...after i set the permissions manually i killed the other icalcs.exe and then it continued to initdb... hope this helps -- View this message in context: http://postgresql.1045698.n5.nabble.com/Problem-with-the-9-1-one-click-installer-Windows7-64bit-tp4794537p4845892.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bytea columns and large values
My application uses a bytea column to store some fairly large binary values (hundreds of megabytes). Recently I've run into a problem as my values start to approach the 1GB limit on field size: When I write a 955MB byte array from Java into my table from JDBC, the write succeeds and the numbers look about right: testdb=# select count(*) from problem_table; count --- 1 (1 row) testdb=# select pg_size_pretty(pg_total_relation_size('problem_table')); pg_size_pretty 991 MB (1 row) However, any attempt to read this row back fails: testdb=# select * from problem_table; ERROR: invalid memory alloc request size 2003676411 The same error occurs when reading from JDBC (even using getBinaryStream). Is there some reason why my data can be stored in <1GB but triggers the allocation of 2GB of memory when I try to read it back? Is there any setting I can change or any alternate method of reading I can use to get around this? Thanks, -- David North, Software Developer, CoreFiling Limited http://www.corefiling.com Phone: +44-1865-203192 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Identifying old/unused views and table
I have an application with a couple hundred views and a couple hundred tables. Is there some way I can find out which views have been accessed in the last 6 months or so? Or some way to log this? I know there are views and tables that are no longer in used by my application and I am looking for a way to identify them. -- 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] live metadata changes v8.3.4
On Tue, Sep 27, 2011 at 1:51 PM, Gauthier, Dave wrote: > How does one make a metadata change to a DB that's actively being used. > Specifically, I want to drop a view, drop some columns from a table that's > used in the view, recreate the view without those columns. BEGIN TRANSACTION; DROP VIEW someView ...; ALTER TABLE DROP COLUMN someColumn1, DROP COLUMN someColumn2; CREATE VIEW someView AS ...; COMMIT; --Wait for pre-existing locks to complete and hope your client app doesn't break. -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] live metadata changes v8.3.4
Hi: How does one make a metadata change to a DB that's actively being used. Specifically, I want to drop a view, drop some columns from a table that's used in the view, recreate the view without those columns. In the past, I've resorted to connecting as a super user, running "select procpid from pg_stat_activity..." then pg_ctl kill ABRT . This would create a window where I could get in and make the change. But it also created some angry users whos processes got killed. V8.3.4 on linux. Thanks for any advice !
Re: [GENERAL] New feature: accumulative functions.
Thanks Marti for inspiration :). Monotonic functions allows to skip some sorts in window expressions containing them: select winfun1(...) over(order by x), winfun2(...) over(order by f(x)) from ... 2011/9/27, pasman pasmański : > Yes, accumulative functions may be used for sorting,groupping and > merge joins with limit. > > Groupping looks simplest to implement, and comparable to performance > of functional index > . > > 2011/9/27, Marti Raudsepp : >> 2011/9/25 pasman pasmański : >>> My english is not perfect, by accumulative i think about monotonically >>> increasing function. >>> >>> It works that for clause WHERE f(x)=const: >>> 1. Read root page of index_on_x and get x1 ... Xn >>> 2. Calculate f(x1) ... f(xn) for this page >>> 3. When f(x1)<=const<= f(xn) then x1 <= searched x <= xn and we can >>> test smaller range (xlower, xgreater). >>> 4. Otherwise no rows satisfy condition. >> >> I can't get very excited about this feature for index scans. However, >> I think there's another, more interesting use case: sorting >> >> I frequently write queries like: >> SELECT date_trunc('month', somedate), sum(foo) >> GROUP BY date_trunc('month', somedate); >> >> Currently the planner doesn't realize that instead of >> GroupAggregate+Sort, it can use the already existing sorted index on >> just (somedate). Alternatively I would need to create a separate >> date_trunc functional index for daily, weekly and monthly aggregates >> for EACH meaningful time zone. >> >> This would be a planner-only change and nothing the executor needs to >> know >> of. >> >> Now obviously HashAggregate helps a lot with these kinds of queries, >> but there are still cases where GroupAggregate would be a win -- for >> instance, queries with a LIMIT. >> >> Regards, >> Marti >> > > > -- > > pasman > -- pasman -- 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] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/27 Diego Augusto Molina : > /* Created by Diego Augusto Molina in 2011 for Tucuman > Government, > Argentina. */ OK, few random comments: *) when posting schema definitions, particularly in email format, try not to use dumped definitions from pg_dump or pgadmin. This creates a lot of noise in the script that detracts from what you are trying to do. Also an attached file would probably have been more appropriate. *) using smallint key for client_inet is really dubious. why not just use the inet itself? *) what is the audet table for? Are you truly storing a record for every field of every audited table? This will be incredibly efficient, especially for large, wide tables. *) surely, creating a table called 'table' is not a good idea. *) this approach obviously is a lot more complicated than hstore. however, for 8.4 and down, hstore won't work. but, what about just storing the record as text? *) I can't quite follow the perl criteron steps -- what is happening there? What are the loops doing? merlin -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
On Tue, 27 Sep 2011, John R Pierce wrote: Eeek! you NEVER directly access the postgres data files. rather, you connect to postgres via a socket, and ask it to fetch the data for you, thats just how it works. Well, when the LO odbc window asks for the location of the database to which to connect, what do I tell it? to use ODBC, you'd need to give it the DSN information, I don't know the exact format, but in general, its something like [PostgreSQL] Description = Postgres Database FRED Driver = PostgreSQL Trace = Yes TraceFile = sql.log Database= FRED Servername = localhost UserName= fred Password= Port= 5432 I'm not presented with an opportunity to offer any of this information anywhere. Guess the most practical thing to do is give up trying to use LO as a front end. I'll just write INSTALL INTO ... statements in emacs then use psql to read them into the table. Thanks all, Rich -- 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] [Solved] Generic logging system for pre-hstore using plperl triggers
2011/9/27, Diego Augusto Molina : > Honestly, I don't remember why I used triggers instead of rules in the > "audit" and "audet" tables. I remember now, that's because in my case, operations over tuples are done very lightly (one or two in the same sentence at a time). So, for a case as such, rules end up beeing more expensive than triggers (right?). -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- 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] [Solved] Generic logging system for pre-hstore using plperl triggers
Sh*#@, the code was wrapped! if you can suggest me an external web to host the code I can put it there and send the link. Maybe the wiki Nevertheless, I can't seem to publish in the wiki. I think I've followed all the steps but still can't. Anyway, there aren't many long-long lines, except for those whoch are comments. Honestly, I don't remember why I used triggers instead of rules in the "audit" and "audet" tables. -- Diego Augusto Molina diegoaugustomol...@gmail.com ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán desestimados. EN: Please, avoid attaching Microsoft Office documents. They shall be discarded. LINK: http://www.gnu.org/philosophy/no-word-attachments.html -- 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] [Solved] Generic logging system for pre-hstore using plperl triggers
/* Created by Diego Augusto Molina in 2011 for Tucuman Government, Argentina. */ /* -- Execute the following accordingly to your needs. CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plperl'; */ CREATE ROLE auditor NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE audit LOGIN ENCRYPTED PASSWORD 'test.1234' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; CREATE SCHEMA audit AUTHORIZATION audit; ALTER ROLE auditor SET search_path=audit; ALTER ROLE audit SET search_path=audit; SET search_path=audit; SET SESSION AUTHORIZATION audit; CREATE SEQUENCE seq_audit INCREMENT 1 MINVALUE -9223372036854775808 MAXVALUE 9223372036854775807 START 0 CACHE 1 CYCLE; ALTER TABLE seq_audit OWNER TO audit; CREATE SEQUENCE seq_elems INCREMENT 1 MINVALUE -32768 MAXVALUE 32767 START 0 CACHE 1 CYCLE; ALTER TABLE seq_elems OWNER TO audit; CREATE TABLE field ( id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass), value name NOT NULL, CONSTRAINT field_pk PRIMARY KEY (id) WITH (FILLFACTOR=100), CONSTRAINT field_uq_value UNIQUE (value) WITH (FILLFACTOR=100) ) WITH ( OIDS=FALSE ); ALTER TABLE field OWNER TO audit; GRANT ALL ON TABLE field TO audit; GRANT SELECT ON TABLE field TO auditor; CREATE TABLE client_inet ( id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass), value inet NOT NULL DEFAULT inet_client_addr(), CONSTRAINT dir_inet_pk PRIMARY KEY (id ) WITH (FILLFACTOR=100), CONSTRAINT dir_inet_uq_value UNIQUE (value) WITH (FILLFACTOR=95) ) WITH ( OIDS=FALSE ); ALTER TABLE client_inet OWNER TO audit; GRANT ALL ON TABLE client_inet TO audit; GRANT SELECT ON TABLE client_inet TO auditor; CREATE TABLE schema ( id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass), value name NOT NULL, CONSTRAINT schema_pk PRIMARY KEY (id ) WITH (FILLFACTOR=100), CONSTRAINT schema_uq_value UNIQUE (value ) WITH (FILLFACTOR=100) ) WITH ( OIDS=FALSE ); ALTER TABLE schema OWNER TO audit; GRANT ALL ON TABLE schema TO audit; GRANT SELECT ON TABLE schema TO auditor; CREATE TABLE table ( id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass), value name NOT NULL, CONSTRAINT table_pk PRIMARY KEY (id ) WITH (FILLFACTOR=100), CONSTRAINT table_uq_value UNIQUE (value ) WITH (FILLFACTOR=100) ) WITH ( OIDS=FALSE ); ALTER TABLE table OWNER TO audit; GRANT ALL ON TABLE table TO audit; GRANT SELECT ON TABLE table TO auditor; CREATE TABLE user ( id smallint NOT NULL DEFAULT nextval('seq_elems'::regclass), value name NOT NULL DEFAULT "current_user"(), CONSTRAINT user_pk PRIMARY KEY (id ) WITH (FILLFACTOR=100), CONSTRAINT user_uq_value UNIQUE (value ) WITH (FILLFACTOR=95) ) WITH ( OIDS=FALSE ); ALTER TABLE user OWNER TO audit; GRANT ALL ON TABLE user TO audit; GRANT SELECT ON TABLE user TO auditor; CREATE TABLE audit ( id bigint, type character(1), tstmp timestamp with time zone DEFAULT now(), schema smallint, table smallint, user smallint, client_inet smallint, client_port integer DEFAULT inet_client_port(), pid integer DEFAULT pg_backend_pid() ) WITH ( OIDS=FALSE ); ALTER TABLE audit OWNER TO audit; GRANT ALL ON TABLE audit TO audit; GRANT SELECT ON TABLE audit TO auditor; CREATE TABLE audet ( id bigint, field smallint, is_pk boolean, before text, after text ) WITH ( OIDS=FALSE ); ALTER TABLE audet OWNER TO audit; GRANT ALL ON TABLE audet TO audit; GRANT SELECT ON TABLE audet TO auditor; CREATE OR REPLACE FUNCTION tgf_ins_audet() RETURNS trigger AS $BODY$ begin execute E'insert into audet_' || tg_argv[0] || E' ( id, field, is_pk, before, after ) values ( '||coalesce(new.id::text,'NULL')||E', '||coalesce(new.field::text,'NULL')||E', '||coalesce(new.is_pk::text,'NULL')||E', '||coalesce(quote_literal(new.before),'NULL')||E', '||coalesce(quote_literal(new.after),'NULL')||E' )'; return null; end;$BODY$ LANGUAGE plpgsql VOLATILE; ALTER FUNCTION tgf_ins_audet() SET search_path=auditoria; ALTER FUNCTION tgf_ins_audet() OWNER TO audit; GRANT EXECUTE ON FUNCTION tgf_ins_audet() TO audit; CREATE OR REPLACE FUNCTION tgf_ins_audit() RETURNS trigger AS $BODY$ begin execute E'insert into audit_' || tg_argv[0] || E' ( id, type, tstmp, schema, table, user, client_inet, client_port, pid ) values ( '||coalesce(new.id::text,'NULL')||E', '||coalesce(quote_literal(new.type),'NULL')||E', '||coalesce(quote_literal(new.tstmp),'NULL')||E', '||coalesce(new.schema::text,'NULL')||E', '||coalesce(new.table::text,'NULL')||E', '||coalesce(new.user::text,'NULL')||E', '||coalesce(new.client_inet::text,'NULL')||E', '||coal
Re: [GENERAL] Quick-and-Dirty Data Entry with LibreOffice3?
On 09/27/11 11:20 AM, Rich Shepard wrote: All my postgresql databases are in /usr/local/pgsql/data/base/ in numbered subdirectories. I've no idea in which one resides the database I want. Is there a way to determine where my database is located? Eeek! you NEVER directly access the postgres data files. rather, you connect to postgres via a socket, and ask it to fetch the data for you, thats just how it works. your database and all the databases on that system are in /usr/local/pgsql/data ... they involve multiple of those subdirectories, you can't seperate the pieces from the whole without breaking it completely. to use ODBC, you'd need to give it the DSN information, I don't know the exact format, but in general, its something like [PostgreSQL] Description = Postgres Database FRED Driver = PostgreSQL Trace = Yes TraceFile = sql.log Database= FRED Servername = localhost UserName= fred Password= Port= 5432 -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
You will need to use the PostgreSQL ODBC driver. I should mention that I haven't used ODBC from a Linux client before. I found the some instructions in the mailing list archive: http://archives.postgresql.org/pgsql-odbc/2002-02/msg00023.php On Tue, Sep 27, 2011 at 11:20 AM, Rich Shepard wrote: > On Tue, 27 Sep 2011, Richard Broersma wrote: > >> ODBC handles this by converting these to a text representation that Open >> Office can handle. > > Richard, > > Progress has been made. I built and installed unixODBC from > slackbuilds.org and now the hangup is identifying the location and name of > the database to LO. > > All my postgresql databases are in /usr/local/pgsql/data/base/ in numbered > subdirectories. I've no idea in which one resides the database I want. > > Is there a way to determine where my database is located? > > Thanks, > > Rich > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Regards, Richard Broersma Jr. -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
On Tue, 27 Sep 2011, Richard Broersma wrote: ODBC handles this by converting these to a text representation that Open Office can handle. Richard, Progress has been made. I built and installed unixODBC from slackbuilds.org and now the hangup is identifying the location and name of the database to LO. All my postgresql databases are in /usr/local/pgsql/data/base/ in numbered subdirectories. I've no idea in which one resides the database I want. Is there a way to determine where my database is located? Thanks, Rich -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
On Tue, 27 Sep 2011, Richard Broersma wrote: I'd recommend using JDBC ODBC driver for PostgreSQL. Open Office via direct JDBC has a hard time with some PostgreSQL data-types. ODBC handles this by converting these to a text representation that Open Office can handle. Richard, LibreOffice tells me it cannot generate an ODBC connection because it cannot find libodbc.so.1. There is no such library on my system. I'll see if I can find it via SlackBuilds.org. Rich -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
On Tue, Sep 27, 2011 at 9:15 AM, Rich Shepard wrote: > A Google search for 'libreoffice-sdbc-postgresql' actually turns up a link > that supposed to have a .txz version for Slackware, but the only packages on > the site are .deb. Oh, well. I'll keep looking. I'd recommend using JDBC ODBC driver for PostgreSQL. Open Office via direct JDBC has a hard time with some PostgreSQL data-types. ODBC handles this by converting these to a text representation that Open Office can handle. -- Regards, Richard Broersma Jr. -- 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] Sending Results From One Function As Input into Another Function
Thanks for the response Laurenz. I will give it a go... Jeff -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, September 26, 2011 7:50 AM To: Jeff Adams *EXTERN*; pgsql-general@postgresql.org Subject: RE: [GENERAL] Sending Results From One Function As Input into Another Function Jeff Adams wrote: > I need to send the results (SETOF RECORDS) from one function into another > function, to produce another result (SETOF RECORDS). I am not quite sure how > to do get this done. The first function filters a large table down a more > manageable dataset. I want to send the results of this first function to > another function, where computations are performed. I could combine into a > single function, but I would lose some flexibility that I would like to > maintain by keeping the two functions separate. Preliminary research > suggests that cursors might be the way to go, but I am not too experienced > with the use of cursors and was unable to find good examples. Any help would > be greatly appreciated... Here's an example: SELECT * FROM test; id | val +--- 1 | one 2 | two 3 | three 4 | four (4 rows) CREATE FUNCTION filter() RETURNS refcursor LANGUAGE plpgsql STABLE STRICT AS $$DECLARE /* assignment gives the cursor a name */ curs refcursor := 'curs'; BEGIN OPEN curs FOR SELECT id, val FROM test WHERE id%2=0; RETURN curs; END;$$; CREATE FUNCTION compute(curs refcursor) RETURNS text LANGUAGE plpgsql STABLE STRICT AS $$DECLARE v test; -- row type for table r text := ''; BEGIN LOOP FETCH curs INTO v; EXIT WHEN v IS NULL; r := r || v.val; END LOOP; RETURN r; END;$$; SELECT compute(filter()); compute - twofour (1 row) Yours, Laurenz Albe -- 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] Quick-and-Dirty Data Entry with LibreOffice3?
On Mon, 26 Sep 2011, planas wrote: Which version of LO are you using and which Linux? I have some experience with using LO as a front-end when pgAdmin is not the best tool. LO-3.4.3 on Slackware-13.1/32-bit. I have noticed that with Ubuntu you need to use the 3.3.x series from the repository. There is pg connector for LO 3.3.x in the repository. Getting 3.4.x to connect is more of a pain in Ubuntu. I think the correct driver is libreoffice-sdbc-postgresql. A Google search for 'libreoffice-sdbc-postgresql' actually turns up a link that supposed to have a .txz version for Slackware, but the only packages on the site are .deb. Oh, well. I'll keep looking. Thanks, Rich -- 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] Download States and Capitals Database
On Tue, 2011-09-27 at 12:18 +0530, Adarsh Sharma wrote: > Dear all, > > I googled a lot and find data of all countries , cities , > location etc from Geo Spatial websites but I am able to find the data > that shows all states & their respective capitals in world. > Please let me know if anyone as prior information about this ? > > > Thanks Try this link from Wikipedia http://en.wikipedia.org/wiki/World_capital_cities_by_country . I just searched for world capitals by country. -- Jay Lozier jsloz...@gmail.com
Re: [GENERAL] New feature: accumulative functions.
Yes, accumulative functions may be used for sorting,groupping and merge joins with limit. Groupping looks simplest to implement, and comparable to performance of functional index . 2011/9/27, Marti Raudsepp : > 2011/9/25 pasman pasmański : >> My english is not perfect, by accumulative i think about monotonically >> increasing function. >> >> It works that for clause WHERE f(x)=const: >> 1. Read root page of index_on_x and get x1 ... Xn >> 2. Calculate f(x1) ... f(xn) for this page >> 3. When f(x1)<=const<= f(xn) then x1 <= searched x <= xn and we can >> test smaller range (xlower, xgreater). >> 4. Otherwise no rows satisfy condition. > > I can't get very excited about this feature for index scans. However, > I think there's another, more interesting use case: sorting > > I frequently write queries like: > SELECT date_trunc('month', somedate), sum(foo) > GROUP BY date_trunc('month', somedate); > > Currently the planner doesn't realize that instead of > GroupAggregate+Sort, it can use the already existing sorted index on > just (somedate). Alternatively I would need to create a separate > date_trunc functional index for daily, weekly and monthly aggregates > for EACH meaningful time zone. > > This would be a planner-only change and nothing the executor needs to know > of. > > Now obviously HashAggregate helps a lot with these kinds of queries, > but there are still cases where GroupAggregate would be a win -- for > instance, queries with a LIMIT. > > Regards, > Marti > -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
> -Mensagem original- > De: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Em nome de Marti Raudsepp > Enviada em: segunda-feira, 26 de setembro de 2011 17:42 > Para: Edson Carlos Ericksson Richter > Cc: pgsql-general@postgresql.org > Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX > queries? > > On Mon, Sep 26, 2011 at 15:16, Edson Carlos Ericksson Richter > wrote: > > select * from notafiscal where numeroctc like ‘POA%34345’; > > > > Prefix is normally 3 characters, suffix varyies. > > > > Is Postgresql 9.0.4 able to use an BTREE index on notafiscal.numeroctc > to execute this query? > > As mentioned by other posters, you should use a btree index with > text_pattern_ops opclass to speed up this query. > > For queries like these, it's often faster to match the text in *reverse*. > You can create two indexes like this: > > create index on foobar (txt text_pattern_ops); create index on foobar > (reverse(txt) text_pattern_ops); > > And then write your queries like this: > SELECT * FROM foobar > WHERE txt like 'POA%34345' > AND reverse(txt) like reverse('POA%34345'); Just perfect! It not only works, but time dropped from 5s to 94ms. Regards, Edson. > > PostgreSQL will automatically choose one or both indexes for executing > this query. > > Regards, > Marti > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general smime.p7s Description: S/MIME cryptographic signature
RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
> -Mensagem original- > De: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Em nome de Marti Raudsepp > Enviada em: terça-feira, 27 de setembro de 2011 09:59 > Para: Alban Hertroys > Cc: PG-General Mailing List > Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX > queries? > > Alban, you forgot to reply to the mailing list. Please use the "reply to > all" button in your email client. :) > > On Tue, Sep 27, 2011 at 14:21, Alban Hertroys wrote: > > If performance is an issue, I'd suggest coding it as a C function. > > > > A quick google search turned up: > > http://discuss.fogcreek.com/techInterview/default.asp?cmd=show&ixPost= > > 2077 > > for possible implementations. > > > > Of course, for Postgres we'd use pmalloc/pfree instead of malloc/free, > > as the result value will be a Datum. > > These can't be adapted directly because they don't behave right in > UTF-8 encoding. > > However, there's already a C implementation in the "orafce" project, no > point in reinventing the wheel: > http://pgfoundry.org/projects/orafce/ > > I have created a wiki page for collecting the most useful implementations. > Anyone is welcome to improve: > https://wiki.postgresql.org/wiki/Reverse_string This wiki page is great! Best resource, for sure. Would be nice to get precompiled binaries for orafce. It's very interesting package that IMHO should be included in main distro of Postgres. For Linux it's easy to get and compile... but for Win64 it's harder... good C compilers in Win64 is a nightmare (even CygWin complains about everything on the ".h world"). I'll try one of the portable solutions (for Win dev stations) and use the orafce in Linux srevers. Thanks for all the tips! Regards, Edson. > > (I didn't include Pavel Stehule's implementation because that only works > on 9.0, not earlier. I did include another SQL implementation) > > Regards, > Marti > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
Alban, you forgot to reply to the mailing list. Please use the "reply to all" button in your email client. :) On Tue, Sep 27, 2011 at 14:21, Alban Hertroys wrote: > If performance is an issue, I'd suggest coding it as a C function. > > A quick google search turned up: > http://discuss.fogcreek.com/techInterview/default.asp?cmd=show&ixPost=2077 > for possible implementations. > > Of course, for Postgres we'd use pmalloc/pfree instead of malloc/free, > as the result value will be a Datum. These can't be adapted directly because they don't behave right in UTF-8 encoding. However, there's already a C implementation in the "orafce" project, no point in reinventing the wheel: http://pgfoundry.org/projects/orafce/ I have created a wiki page for collecting the most useful implementations. Anyone is welcome to improve: https://wiki.postgresql.org/wiki/Reverse_string (I didn't include Pavel Stehule's implementation because that only works on 9.0, not earlier. I did include another SQL implementation) Regards, Marti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
RES: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
> -Mensagem original- > De: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] Em nome de Harald Fuchs > Enviada em: terça-feira, 27 de setembro de 2011 07:01 > Para: pgsql-general@postgresql.org > Assunto: Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX > queries? > > In article cggktejwdw3q2_kxfedp4p...@mail.gmail.com>, > Marti Raudsepp writes: > > > Ah, the reverse() function is not included with PostgreSQL 9.0 yet. > > This is what I use: > > > CREATE FUNCTION reverse(input text) RETURNS text LANGUAGE plpgsql > > IMMUTABLE STRICT AS $$ DECLARE > > result text = ''; > > i int; > > BEGIN > > FOR i IN 1..length(input) BY 2 LOOP > > result = substr(input,i+1,1) || substr(input,i,1) || result; > > END LOOP; > > RETURN result; > > END$$; > > Pavel Stehule has found a better solution for that: > > CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$ > SELECT string_agg(substring($1 FROM i FOR 1), '') > FROM generate_series(length($1), 1, -1) g(i) $$ language sql; > > But the best, of course, is upgrading to 9.1. Upgrade to 9.1 into production servers is not na option. It will take about a year before I can migrate all databases and establish replication and everything else (probably, Christams Holidays in December or Carnival in February next). Some users demand 24x7 from this databases, I can't just say "stop for one-two hour". Thanks for the tip, I'll give a try for both funtions and let you know the results. Would save time for future... Regards, Edson. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
On Tue, Sep 27, 2011 at 13:00, Harald Fuchs wrote: > Pavel Stehule has found a better solution for that: > > CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$ > SELECT string_agg(substring($1 FROM i FOR 1), '') > FROM generate_series(length($1), 1, -1) g(i) > $$ language sql; I don't want to get into a pissing contest, but I'm not sure by which criteria this is "better". When I needed this function, I compared the speed many different approaches (6 different versions from the mailing lists). The one I posted above was the winner, a slightly tuned version of the original by Shoaib Mir. When testing this right now, it takes half the time of the function you posted, for short non-Unicode strings at least. Regards, Marti -- 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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
In article , Marti Raudsepp writes: > Ah, the reverse() function is not included with PostgreSQL 9.0 yet. > This is what I use: > CREATE FUNCTION reverse(input text) RETURNS text > LANGUAGE plpgsql IMMUTABLE STRICT AS $$ > DECLARE > result text = ''; > i int; > BEGIN > FOR i IN 1..length(input) BY 2 LOOP > result = substr(input,i+1,1) || substr(input,i,1) || result; > END LOOP; > RETURN result; > END$$; Pavel Stehule has found a better solution for that: CREATE OR REPLACE FUNCTION reverse(text) RETURNS text AS $$ SELECT string_agg(substring($1 FROM i FOR 1), '') FROM generate_series(length($1), 1, -1) g(i) $$ language sql; But the best, of course, is upgrading to 9.1. -- 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] PostgreSQL recovery when lost some file in data\global
Hi Tuan Hoang Anh, Are you able to bring up the cluster ?? Please let us know what problem you are facing. Thanks Venkat On Tue, Sep 27, 2011 at 12:08 PM, tuanhoanganh wrote: > I am running PostgreSQL 9.0.1 32bit on windows 2003. Last night my disk had > some problem and i lost some file in data\global. > Is there anyway to recovery postgresql. > > Thanks in advance. Sorry for my English. > > Tuan Hoang Anh >
Re: [GENERAL] : PostgreSQL Online Backup
Venkat Balaji wrote: > Our problem is - > > We had mistakenly executed "rsync" on the running PostgreSQL data directory (production) and we did > not run "pg_start_backup()". > > Will this harm production ? can this lead to corruption ? I assume that you used rsync to copy *from* the data directory. This cannot lead to data corruption. Only performance might suffer temporarily due to the additional I/O. The backup made with rsync will be unusable without pg_start_backup(). Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general