[GENERAL] PostgreSQL recovery when lost some file in data\global
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] New feature: accumulative functions.
2011/9/25 pasman pasmański pasma...@gmail.com:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Download States and Capitals Database
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
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
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 hatua...@gmail.com 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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
In article cabrt9rdxhkcxrq8wbohnikpf-cggktejwdw3q2_kxfedp4p...@mail.gmail.com, Marti Raudsepp ma...@juffo.org 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] Does postgresql 9.0.4 use index on PREFIX%SUFFIX queries?
On Tue, Sep 27, 2011 at 13:00, Harald Fuchs hari.fu...@gmail.com 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
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 CABRT9RDxHKCxrq8wboHnikpF- cggktejwdw3q2_kxfedp4p...@mail.gmail.com, Marti Raudsepp ma...@juffo.org 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?
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 haram...@gmail.com 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=showixPost=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 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 haram...@gmail.com 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=showixPost= 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
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
rich...@simkorp.com.br 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
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 ma...@juffo.org:
2011/9/25 pasman pasmański pasma...@gmail.com:
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
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] 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] 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 Tue, Sep 27, 2011 at 9:15 AM, Rich Shepard rshep...@appl-ecosys.com 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] 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, 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?
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 rshep...@appl-ecosys.com 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 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] [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',
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
2011/9/27, Diego Augusto Molina diegoaugustomol...@gmail.com: 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] 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 diegoaugustomol...@gmail.com: /* 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] 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 pasma...@gmail.com:
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 ma...@juffo.org:
2011/9/25 pasman pasmański pasma...@gmail.com:
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
[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 procpid. 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] live metadata changes v8.3.4
On Tue, Sep 27, 2011 at 1:51 PM, Gauthier, Dave dave.gauth...@intel.com 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] 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
[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
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
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] 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] 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] 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
