[GENERAL] PostgreSQL Portable for Linux
Good morning / afternoon / night, I already updated the PostgreSQL Portable for Linux Debian and him flawors. Tested on Debian Jessie and Ubuntu 17.4. Its a 7z file.https://sourceforge.net/projects/pgsqlportable/files/9.6/pgsql%209.6x64.7z/download Please look the description of the project to run it. Its start executing "sh start.sh" on terminal, stop postgresql executing "sh stop.sh", restart postgresql executing "sh restart.sh" Thanks to williamp with the help!
[GENERAL] Tablespace limit feature
Hi, Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace?? Thanks and Regards
Re: [GENERAL] Tablespace limit feature
I need to limit the tablespace file because I need to control the hard disk space used in a tablespace. Anybody knows how to do this? Or if it this in the PostgreSQL roadmap? Thanks El Martes 4 de noviembre de 2014 11:52, Guillaume Lelarge guilla...@lelarge.info escribió: Le 4 nov. 2014 16:29, Stephen Frost sfr...@snowman.net a écrit : * Alejandro Carrillo (faster...@yahoo.es) wrote: Can PostgreSQL's tablespace limit space in MB? Or exists another way to limit space in a table of a tablespace?? You can set up quotas on the underlying filesystem, but that will limit the entire tablespace. Having quotas and limits for users is something which I've wanted for a long time, but it's not likely to happen any time particularly soon.. Lack of time or technical issue? For the latter, may I ask which issue you see? (I might be interesting in pursuing this)
Re: [GENERAL] Tablespace limit feature
But This feature is very necessary to control the amount space expensed by a postgresql user or tablespace. This feature could be used in PostgreSQL Sharing Hosting, see: Shared Database Hosting - PostgreSQL wiki | | | | | | | | | Shared Database Hosting - PostgreSQL wikiThe objective of this page is to provide a place to exchange ideas on howbest to run PostgreSQL in a shared hosting environment as suggested here. | | | | Ver en wiki.postgresql.org | Vista previa por Yahoo | | | | | El Martes 4 de noviembre de 2014 12:35, Stephen Frost sfr...@snowman.net escribió: * Guillaume Lelarge (guilla...@lelarge.info) wrote: Yeah. I guess there are a lot of questions yet to be answered. But AFAIUI, there's no real technical issue. More like discussions to agree on what it should do and how. Didn't mean to imply there was some specific technical issue. We have other limits (temp_file_limit), after all. I think the bigger issue will be convincing others that it's a useful enough feature to warrent the infrastucture to support it (a new catalog table would be needed, for one thing). I'm all for it, but others may not be. Thanks, Stephen
[GENERAL] Support for Alert
Hi, PostgreSQL have a way to put alerts about number of connections, tablespace used, etc like the DBMS_SERVER_ALERT package of Oracle? Thanks you
[GENERAL] not(t_xmax = 0)
Hi,
I need to filter xid != 0. I tried this not(t_xmax = 0) but I dont sure that
this work ok
How I do it?
Create or replace function obtener_info_reg(tabla varchar, buscareliminado
boolean, curs refcursor) returns void as
$$
declare
query_string varchar;
strwhere varchar;
strsql varchar;
blocksize integer := 8192;
begin
query_string:='delete from result_pag';
EXECUTE query_string;
for p in 0..pg_relpages(tabla)-1 loop
query_string:= '';
strsql:='insert into result_pag SELECT '||p||' page,*,
('||p||'*'|| blocksize ||') + lp_off posini_reg,
('||p||'*'|| blocksize ||') + lp_off + 17 posini_infomask,
(t_infomask-1024) t_infomask_old, to_hex(t_infomask) t_infomask_hex,
to_hex(t_infomask-1024) t_infomask_old
FROM heap_page_items(get_raw_page(''' || tabla || ''', '||p||'))';
strwhere:='';
if buscareliminado= true then
--add where t_max != 0 to query
strwhere:= ' where not(t_xmax = 0)';
end if;
query_string:= query_string || strsql || strwhere;
EXECUTE query_string;
end loop;
OPEN curs FOR EXECUTE 'select * from result_pag ';
end;
$$ language plpgsql;
Re: [GENERAL] not(t_xmax = 0)
I try to do a function that let know which rows are deleted in a table using the xmax !=0 De: Kevin Grittner kgri...@mail.com Para: Alejandro Carrillo faster...@yahoo.es; pgsql-general@postgresql.org Enviado: Domingo 20 de enero de 2013 12:19 Asunto: Re: [GENERAL] not(t_xmax = 0) Alejandro Carrillo wrote: I need to filter xid != 0. I tried this not(t_xmax = 0) but I dont sure that this work ok How I do it? Please give a high-level description of what you are trying to accomplish and why. I didn't find it at all clear from your sample function, -Kevin -- 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] not(t_xmax = 0)
this function didn't work to know if a row can surely dead? http://doxygen.postgresql.org/tqual_8c_source.html#l01236 De: Tom Lane t...@sss.pgh.pa.us Para: Kevin Grittner kgri...@mail.com CC: Alejandro Carrillo faster...@yahoo.es; pgsql-general@postgresql.org Enviado: Domingo 20 de enero de 2013 15:30 Asunto: Re: [GENERAL] not(t_xmax = 0) Kevin Grittner kgri...@mail.com writes: Alejandro Carrillo wrote: I try to do a function that let know which rows are deleted in a table using the xmax !=0 That's not something you're going to have much luck with by using plpgsql to try to parse heap pages. No, because xmax being nonzero doesn't prove much: the row might be updated/deleted, or it might only have been locked by SELECT FOR UPDATE, or it might be perfectly fine because the updating transaction rolled back. You could get a limited amount of information by checking the hint bits in t_infomask, but if the hint bits aren't set you won't know what the row's state is. You'd really need to do this in C using the tqual.c functions for the results to be trustworthy. I'm a bit surprised that contrib/pageinspect doesn't have a mechanism for pulling out only committed-good heap tuples... regards, tom lane
Re: [GENERAL] pg_dirtyread doesnt work
Anybody knows why could be happening: ERROR: invalid memory alloc request size
1850015748
Thanks
De: Alejandro Carrillo faster...@yahoo.es
Para: pgsql-general@postgresql.org pgsql-general@postgresql.org
Enviado: Jueves 27 de diciembre de 2012 16:17
Asunto: [GENERAL] pg_dirtyread doesnt work
Hi,
After of very tried to compile this PostgreSQL C function for Windows, I
compile that (with VS C++ 2008), but the function get a error when try to read
a deleted row. The example:
CREATE FUNCTION pg_dirtyread(oid)
RETURNS setof record
AS E'$libdir/pg_dirtyread', 'pg_finfo_pg_dirtyread' LANGUAGE C STRICT ;
Create table hola(
id bigserial,
dato1
varchar(199) not null,
fecha date
);
/*insert 3 rows and delete 1 row*/
select * from pg_dirtyread('hola'::regclass) t (id bigint,dato1 varchar(199),
fecha date);
ERROR: invalid memory alloc request size 1850015748
Anybody can help me?
Thanks
Re: [GENERAL] pg_dirtyread doesnt work
So I going to say, Alvaro. The table structure should not create a table with TOAST De: Alvaro Herrera alvhe...@2ndquadrant.com Para: Phil Sorber p...@omniti.com CC: Alejandro Carrillo faster...@yahoo.es; pgsql-general@postgresql.org pgsql-general@postgresql.org Enviado: Viernes 28 de diciembre de 2012 15:10 Asunto: Re: [GENERAL] pg_dirtyread doesnt work Phil Sorber escribió: Most likely it's because of TOAST'd records. The module is pretty naive and needs to be updated to handle such cases. It doesn't look like a column with this definition would be toasted, though: On Fri, Dec 28, 2012 at 10:53 AM, Alejandro Carrillo faster...@yahoo.es wrote: Create table hola( id bigserial, dato1 varchar(199) not null, fecha date ); -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dirtyread doesnt work
Hi,
After of very tried to compile this PostgreSQL C function for Windows, I
compile that (with VS C++ 2008), but the function get a error when try to read
a deleted row. The example:
CREATE FUNCTION pg_dirtyread(oid)
RETURNS setof record
AS E'$libdir/pg_dirtyread', 'pg_finfo_pg_dirtyread' LANGUAGE C STRICT ;
Create table hola(
id bigserial,
dato1 varchar(199) not null,
fecha date
);
/*insert 3 rows and delete 1 row*/
select * from pg_dirtyread('hola'::regclass) t (id bigint,dato1 varchar(199),
fecha date);
ERROR: invalid memory alloc request size 1850015748
Anybody can help me?
Thanks
Re: [GENERAL] logger table
Did you use pg_audit? https://github.com/jcasanov/pg_audit De: Philipp Kraus philipp.kr...@flashpixx.de Para: pgsql-general@postgresql.org Enviado: Domingo 23 de diciembre de 2012 22:01 Asunto: [GENERAL] logger table Hello, I need some ideas for creating a PG based logger. I have got a job, which can run more than one time. So the PK is at the moment jobid cycle number. The inserts in this table are in parallel with the same username from different host (clustering). The user calls in the executable myprint and the message will insert into this table, but at the moment I don't know a good structure of the table. Each print call can be different length, so I think a text field is a good choice, but I don't know how can I create a good PK value. IMHO a sequence can be create problems that I'm logged in with the same user on multiple hosts, a hash key value like SHA1 based on the content are not a good choice, because content is not unique, so I can get key collisions. I would like to create on each print call a own record in the table, but how can I create a good key value and get no problems in parallel access. I think there can be more than 1000 inserts each second. Does anybody can post a good idea? Thanks Phil -- 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] Read recover rows
But pg_dirty_read only runs in Linux. It doesnt run in windows.
De: Alvaro Herrera alvhe...@2ndquadrant.com
Para: Alejandro Carrillo faster...@yahoo.es
CC: pgsql-general@postgresql.org pgsql-general@postgresql.org
Enviado: Jueves 13 de diciembre de 2012 21:51
Asunto: Re: [GENERAL] Read recover rows
Alejandro Carrillo escribió:
Hi,
1) Anybody knows how to create a table using a table
file? It isn't a fdw, is a file that compose the table in postgresql and
get with the pg_relation_filepath function. Ex:
select pg_relation_filepath('pg_proc');
Make sure the server is down and replace a table's file with the file
you have. You can just create a dummy empty table with exactly the same
row type as the one that had the table the file was for; you need to
recreate dropped columns as well.
2) Anybody knows a JDBC or a multiplatform code that let read the delete
rows of a table without writing of a table file?
You already tried pg_dirtyread, I imagine, after I suggested it to you
in the spanish list? You can use it through JDBC.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Read recover rows
Hi,
1) Anybody knows how to create a table using a table
file? It isn't a fdw, is a file that compose the table in postgresql and
get with the pg_relation_filepath function. Ex:
select pg_relation_filepath('pg_proc');
2) Anybody knows a JDBC or a multiplatform code that let read the delete rows
of a table without writing of a table file?
Thanks
Re: [GENERAL] Read recover rows
Hi,
1) Isn't a script. The file is a table file get using the function
pg_relation_filepath:
select pg_relation_filepath('pg_proc');
2) :( help!
De: Adrian Klaver adrian.kla...@gmail.com
Para: Alejandro Carrillo faster...@yahoo.es
CC: pgsql-general@postgresql.org pgsql-general@postgresql.org
Enviado: Jueves 13 de diciembre de 2012 17:59
Asunto: Re: [GENERAL] Read recover rows
On 12/13/2012 02:23 PM, Alejandro Carrillo wrote:
Hi,
1) Anybody knows how to create a table using a table file? It isn't a
fdw, is a file that compose the table in postgresql and get with the
pg_relation_filepath function. Ex:
select pg_relation_filepath('pg_proc');
Not sure what you are asking. The above just returns a file path to the
on disk representation of the table.
Are you looking for
CREATE TABLE AS
http://www.postgresql.org/docs/9.2/interactive/sql-createtableas.html
OR
CREATE TABLE LIKE
http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html
OR
Do want to create a table from a script?
2) Anybody knows a JDBC or a multiplatform code that let read the delete
rows of a table without writing of a table file?
Not sure if that is possible, someone else may have a better answer.
Thanks
--
Adrian Klaver
adrian.kla...@gmail.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] Read recover rows
I have a file CALLED 11649 in the path: %PG_DATA%\base\11912
This file is a table data in postgresql. Ex: pg_proc.
Now I copied this file, renamed it and I want to connect this file to another
new table with the same structure and data.
How I can do this?
De: Adrian Klaver adrian.kla...@gmail.com
Para: Alejandro Carrillo faster...@yahoo.es
CC: pgsql-general@postgresql.org pgsql-general@postgresql.org
Enviado: Jueves 13 de diciembre de 2012 18:23
Asunto: Re: [GENERAL] Read recover rows
On 12/13/2012 03:14 PM, Alejandro Carrillo wrote:
Hi,
1) Isn't a script. The file is a table file get using the function
pg_relation_filepath:
select pg_relation_filepath('pg_proc');
The above does NOT return a file it just returns the file path name. Postgres
stores it tables(relations) with numbers. This function just helps which
number is associated with a table.
2) :( help!
Still not sure what you are trying to do:)
Wild guess, pg_proc was deleted and you want to recover it?
-- Adrian Klaver
adrian.kla...@gmail.com
Re: [GENERAL] Read recover rows
Are you trying to recover a table by copying in a table from somewhere else? Yes because I can't modify the original file De: Adrian Klaver adrian.kla...@gmail.com Para: Alejandro Carrillo faster...@yahoo.es CC: pgsql-general@postgresql.org pgsql-general@postgresql.org Enviado: Jueves 13 de diciembre de 2012 18:39 Asunto: Re: [GENERAL] Read recover rows On 12/13/2012 03:30 PM, Alejandro Carrillo wrote: I have a file CALLED 11649 in the path: %PG_DATA%\base\11912 This file is a table data in postgresql. Ex: pg_proc. Now I copied this file, renamed it and I want to connect this file to another new table with the same structure and data. How I can do this? As far as I know you cannot. If you want to create a new table from an existing table you will need to use the SQL commands I mentioned previously. This assumes the original table exists. Are you trying to recover a table by copying in a table from somewhere else? -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] [pgsql-es-ayuda] como exportar separado por comas una tabla grande
http://www.postgresql.org/docs/current/static/sql-copy.html De: Hellmuth Vargas hiv...@gmail.com Para: Lista Postgres ES pgsql-es-ay...@postgresql.org; pgsql-general@postgresql.org Enviado: Miércoles 24 de octubre de 2012 12:22 Asunto: [pgsql-es-ayuda] como exportar separado por comas una tabla grande Hola Lista tengo una tabla con la siguiente estructura CREATE TABLE datos_para_proceso ( id bigint, identificador character varying, nombre character varying(255), nombreusuario text, identificacion character varying(255), tema character varying(255), notatema text, subtema character varying(255), notasubtema text, pregunta character varying(255), notapregunta text ) Tamaño en disco: 5585 MB ( se saco con la consulta SELECT pg_size_pretty(SUM(tamanos)) FROM ( SELECT pg_column_size(row(b.*)) as tamanos FROM (select * from datos_para_proceso) as b ) AS c; ) numero de filas: 31054642 Esta en una base de datos PostgreSQL 9.2 a 64 Bit en un Linux CentOS 6 La idea es exportar esta tabla en formato csv, trate de hacerlo en psql directamente en el servidor: data=# \f '|' Field separator is |. data=# \a Output format is unaligned. data=# \o /tmp/datos_para_proceso.csv data=# select * from datos_para_proceso; /opt/PostgreSQL/9.2/bin/psql: línea 30: 9608 Terminado (killed) LD_PRELOAD=$PLL LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PG_BIN_PATH/../lib $PG_BIN_PATH/psql.bin $@ Y obvio el archivo quedo vacío, como podría exportar los datos?? Muchas gracias lista -- Cordialmente, Ing. Hellmuth I. Vargas S.
