Re: [GENERAL] Large index operation crashes postgres
5 x postmaster taking memory: 93.3 % 18.7 % 0.3 % 0.2 % 0.0 % 112.5% Looks like there is someone living beyond its means? Frans 2010/3/24 Tom Lane : > Hm. I wonder about a memory leak in there somewhere. Have you checked > the process size while this is going on? > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to retrive List of Tables in a Database using...
Yogi Yang 007 wrote: 3. Retrieve list of all Group Roles 4. Retrieve list of all Login Roles there is no difference between these, except in usage. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to retrive List of Tables in a Database using...
On Thu, Mar 25, 2010 at 1:26 AM, John R Pierce wrote:
> Yogi Yang 007 wrote:
>
>> Hello,
>>
>> I am new to pgSQL. I would like to know if there is a way to do the
>> following using pure SQL:
>> 1. Retrieve list of all Tables in a database
>> 2. Retrieve list of all Functions, Triggers, Sequences, Views, etc.
>> 3. Retrieve list of all Group Roles
>> 4. Retrieve list of all Login Roles
>> 5. Structure of a Table
>>
>
If you want to learn the postgres catalogs, fire up 'psql -E'
When you use the meta-commands like \dt -- lists tables then it will
show you the SQL it ran to generate the table list. Run those meta-commands
and pay attention to the emitted SQL, it's helpful.
--Scott
>
> most of that stuff can be fetched from the INFORMATION_SCHEMA, such as ..
>
> select * from information_schema.tables where table_schema not in
> ('pg_catalog','information_schema');
>
> select * from information_schema.columns where table_schema='schemaname'
> and table_name='tablename';
>
>
> note, * on both of these returns a lot of data, you may want to be more
> selective, depending on just what infoyou need to know, especially about the
> columns.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
Re: [GENERAL] How to retrive List of Tables in a Database using...
Yogi Yang 007 wrote:
Hello,
I am new to pgSQL. I would like to know if there is a way to do the
following using pure SQL:
1. Retrieve list of all Tables in a database
2. Retrieve list of all Functions, Triggers, Sequences, Views, etc.
3. Retrieve list of all Group Roles
4. Retrieve list of all Login Roles
5. Structure of a Table
most of that stuff can be fetched from the INFORMATION_SCHEMA, such as ..
select * from information_schema.tables where table_schema not in
('pg_catalog','information_schema');
select * from information_schema.columns where
table_schema='schemaname' and table_name='tablename';
note, * on both of these returns a lot of data, you may want to be more
selective, depending on just what infoyou need to know, especially about
the columns.
--
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] "\d pg_class" fails in PG 8.4
Hi Tom, As you are best always on this Community Fourm. Thanks for your help. It got resolved. Regards Raghavendra On Thu, Mar 25, 2010 at 10:03 AM, Tom Lane wrote: > Tadipathri Raghu writes: > > Following is the error when i try to describe the catalog table. > > > postgres=# \d pg_class > > ERROR: column "reltriggers" does not exist at character 41 > > STATEMENT: SELECT relhasindex, relkind, relchecks, reltriggers, > > relhasrules, > > relhasoids , reltablespace > > FROM pg_catalog.pg_class WHERE oid = '1259' > > ERROR: column "reltriggers" does not exist at character 41 > > LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... > > Could you please guide me in this, and what is the cause for it.. > > Apparently you're using an old version of psql ... but I'm curious to > know just which, because I can't duplicate that exact error display with > any release branch. Anyway, pg_class.reltriggers is gone in 8.4, but > pre-8.4 psql versions don't know that. > >regards, tom lane >
Re: [GENERAL] How to retrive List of Tables in a Database using...
Hi Yogi Yang,
Psql is a very strong tool, and easy to use. Please find the answers for
your queries
>>1. Retrieve list of all Tables in a database
>>2. Retrieve list of all Functions, Triggers, Sequences, Views, etc.
>>3. Retrieve list of all Group Roles
postgres=#\d{t|i|s|v|S|g|n}
you can use any of the things as per your requirement
>>4. Retrieve list of all Login Roles
>>5. Structure of a Table
postgres=# \d
Hope this will help you out
Regards
Raghavendar
TIA
Yogi Yang
--
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] "\d pg_class" fails in PG 8.4
Tadipathri Raghu writes: > Following is the error when i try to describe the catalog table. > postgres=# \d pg_class > ERROR: column "reltriggers" does not exist at character 41 > STATEMENT: SELECT relhasindex, relkind, relchecks, reltriggers, > relhasrules, > relhasoids , reltablespace > FROM pg_catalog.pg_class WHERE oid = '1259' > ERROR: column "reltriggers" does not exist at character 41 > LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... > Could you please guide me in this, and what is the cause for it.. Apparently you're using an old version of psql ... but I'm curious to know just which, because I can't duplicate that exact error display with any release branch. Anyway, pg_class.reltriggers is gone in 8.4, but pre-8.4 psql versions don't know that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to retrive List of Tables in a Database using...
Hello, I am new to pgSQL. I would like to know if there is a way to do the following using pure SQL: 1. Retrieve list of all Tables in a database 2. Retrieve list of all Functions, Triggers, Sequences, Views, etc. 3. Retrieve list of all Group Roles 4. Retrieve list of all Login Roles 5. Structure of a Table TIA Yogi Yang -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "\d pg_class" fails in PG 8.4
Hi All, Following is the error when i try to describe the catalog table. Version Info postgres=# select version(); version --- PostgreSQL 8.4.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14), 64-bit (1 row) Error === postgres=# \d pg_class ERROR: column "reltriggers" does not exist at character 41 STATEMENT: SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, relhasoids , reltablespace FROM pg_catalog.pg_class WHERE oid = '1259' ERROR: column "reltriggers" does not exist at character 41 LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... Could you please guide me in this, and what is the cause for it.. Regards Raghavendra
[GENERAL] boualem guechtouli
http://welltrade-hydraulik.com/virginie.html _ Hotmail: Trusted email with Microsoft’s powerful SPAM protection. http://clk.atdmt.com/GBL/go/210850552/direct/01/
Re: [GENERAL] data type to store a lot of text and tables of data
On 24/03/2010 22:39, Karina Guardado wrote: > You know I need to design a table where some attributes have a data type > that allow me to store text mixed with tables and other information. I > use PHP code to get the information and store it in to the database so I > wonder if this is posible to do. I found in the link you provided me > that If character varying is used without length specifier, the type > accepts strings of any size. So this is useful for me when only text has > to be stored but when the user wants to store information mixed with > tables like the following : I suppose it depends on what the format of the data is the example you posted is HTML, which is pure text, so could be stored in a VARCHAR or TEXT column. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data type to store a lot of text and tables of data
You know I need to design a table where some attributes have a data type that allow me to store text mixed with tables and other information. I use PHP code to get the information and store it in to the database so I wonder if this is posible to do. I found in the link you provided me that If character varying is used without length specifier, the type accepts strings of any size. So this is useful for me when only text has to be stored but when the user wants to store information mixed with tables like the following : Reciba un respetuoso saludo y deseo de éxitos en sus actividades. El motivo de la presente es para solicitarle se realice una corrección en el plan de mantenimiento preventivo y correctivo de fotocopiadoras, duplicadores e impresoras debido a que en el Centro de Cómputo aparecen, según el reporte, 4 impresoras asignadas y en realidad solamente tenemos dos que son las que detallo a continuación: Impresora Marca Modelo No. De Inventario Observaciones X HP LaserJet 1300 12040-3701-080-0005 Si aparece en el listado. X HP LaserJet 3055 12040-0603-080-0002 No aparece en el listado I hope this help, thanks a lot, karina On Wed, Mar 24, 2010 at 4:08 PM, Raymond O'Donnell wrote: > On 24/03/2010 22:01, Karina Guardado wrote: > > > what should be the data type to store a file that can be xls, doc, pdf > > for example. > > Sorry, I missed this bit - for binary files you can use the bytea type. > Look under "Binary data types" in the docs. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] How to dump JUST procedures/funnctions?
Hi Tony, Thanks VERY much! That got me on my way. I'm going to try and convert this to a PG stored function, Since you know that the script has limitations, you or othres reading might want to know the ones I've found: 1) Regardless of what you set the schema to, the only ones that are dumped are those on the search path due to pg_catalog.pg_function_is_visible(p.oid) 2) COST and ROWS are lost3) Declared parameter names are lost 4) IN, OUT, etc parameter direction modes are lost 5) If any parameter mode other than IN is declared, they are lost -- you'd need pg_catalog.oidvectortypes(array_to_string(p.proallargtypes, ' ')::oidvector) in this case ONLY. 6) The script does not generate any winning lottery numbers Carlo "Tony Wasson" wrote in message news:6d8daee31003221657h53f486d7r164b27587415e...@mail.gmail.com... On Sat, Mar 20, 2010 at 11:09 PM, Carlo Stonebanks wrote: Is pg_get_functiondef an 8.4 appears to be an 8.4 function? I don't see it in the 8.3 documentation and the servers in question are all 8.3. Any alternatives for 8.3? pg_proc has the code body, but not the function declaration, etc. I've attached a simple perl script to dump all functions per schema from a database. I won't claim this is perfect, but it does the trick for me. Works with 8.3. If you have 2 functions called public.foo, it will put them in public/foo.sql with CREATE OR REPLACE FUNCTION formatting around it. Hope this is useful to others. Tony Wasson -- -- 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] data type to store a lot of text and tables of data
On 24/03/2010 22:01, Karina Guardado wrote: > what should be the data type to store a file that can be xls, doc, pdf > for example. Sorry, I missed this bit - for binary files you can use the bytea type. Look under "Binary data types" in the docs. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data type to store a lot of text and tables of data
On 24/03/2010 22:01, Karina Guardado wrote: > for example something like the following : > > *Table 9-25. Date/Time Operators* > > Operator Example Result > + date '2001-09-28' + integer '7' date '2001-10-05' > + date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00:00' > + date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00:00' > + interval '1 day' + interval '1 hour'interval '1 day 01:00:00' > + timestamp '2001-09-28 01:00' + interval '23 hours' timestamp > '2001-09-29 00:00:00' > + time '01:00' + interval '3 hours' time '04:00:00' > - - interval '23 hours' interval '-23:00:00' > - date '2001-10-01' - date '2001-09-28' integer '3' > - date '2001-10-01' - integer '7' date '2001-09-24' > - date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00:00' > - time '05:00' - time '03:00' interval '02:00:00' > - time '05:00' - interval '2 hours' time '03:00:00' > - timestamp '2001-09-28 23:00' - interval '23 hours' timestamp > '2001-09-28 00:00:00' > - interval '1 day' - interval '1 hour'interval '1 day -01:00:00' > - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' interval > '1 day 15:00:00' > * 900 * interval '1 second' interval '00:15:00' > * 21 * interval '1 day' interval '21 days' > * double precision '3.5' * interval '1 hour' interval '03:30:00' > / interval '1 hour' / double precision '1.5' interval '00:40:00' > > > > is it possible? or is better to use an atached file and store it ? and > what should be the data type to store a file that can be xls, doc, pdf > for example. Sorry, I still don't get it... what *exactly* are you trying to do? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data type to store a lot of text and tables of data
for example something like the following : *Table 9-25. Date/Time Operators* OperatorExampleResult + date '2001-09-28' + integer '7'date '2001-10-05' + date '2001-09-28' + interval '1 hour'timestamp '2001-09-28 01:00:00' + date '2001-09-28' + time '03:00'timestamp '2001-09-28 03:00:00' + interval '1 day' + interval '1 hour'interval '1 day 01:00:00' + timestamp '2001-09-28 01:00' + interval '23 hours'timestamp '2001-09-29 00:00:00' + time '01:00' + interval '3 hours'time '04:00:00' - - interval '23 hours'interval '-23:00:00' - date '2001-10-01' - date '2001-09-28'integer '3' - date '2001-10-01' - integer '7'date '2001-09-24' - date '2001-09-28' - interval '1 hour'timestamp '2001-09-27 23:00:00' - time '05:00' - time '03:00'interval '02:00:00' - time '05:00' - interval '2 hours'time '03:00:00' - timestamp '2001-09-28 23:00' - interval '23 hours'timestamp '2001-09-28 00:00:00' - interval '1 day' - interval '1 hour'interval '1 day -01:00:00' - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00'interval '1 day 15:00:00' * 900 * interval '1 second'interval '00:15:00' * 21 * interval '1 day'interval '21 days' * double precision '3.5' * interval '1 hour'interval '03:30:00' / interval '1 hour' / double precision '1.5'interval '00:40:00' is it possible? or is better to use an atached file and store it ? and what should be the data type to store a file that can be xls, doc, pdf for example. On Wed, Mar 24, 2010 at 3:55 PM, Raymond O'Donnell wrote: > On 24/03/2010 21:48, Karina Guardado wrote: > > Hi, > > > > Is there a special data type to use to store a lot of text and tables of > > data in a column or attribute? > > For large amounts of text, just use the TEXT data type: > >http://www.postgresql.org/docs/8.4/static/datatype-character.html > > Not sure what you mean by "tables of data". > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] data type to store a lot of text and tables of data
On 24/03/2010 21:48, Karina Guardado wrote: > Hi, > > Is there a special data type to use to store a lot of text and tables of > data in a column or attribute? For large amounts of text, just use the TEXT data type: http://www.postgresql.org/docs/8.4/static/datatype-character.html Not sure what you mean by "tables of data". Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] data type to store a lot of text and tables of data
2010/3/24 Karina Guardado > Hi, > > Is there a special data type to use to store a lot of text and tables of > data in a column or attribute? > > thanks in advance, > > regards, > > karina > El Salvador, Centroamerica > Normally text can be stored in a type named "TEXT". Maybe there would be some other solution to speed things up, but first define how much is "a lot of". regards Szymon Guz
[GENERAL] data type to store a lot of text and tables of data
Hi, Is there a special data type to use to store a lot of text and tables of data in a column or attribute? thanks in advance, regards, karina El Salvador, Centroamerica
Re: [GENERAL] find a string contained in an attribute
Thanks a lot for your help that's exactly what I wanted. It worked fine. regards, karina On Wed, Mar 24, 2010 at 3:25 PM, Szymon Guz wrote: > 2010/3/24 Karina Guardado > > Hi, >> >> I want to know if it is possible to search for a string of characters >> inside an attribute for example I have the following table and values >> >> cod_unidad | nombre_uni >> +- >> 1 | Facultad de Ciencias Naturales y Matemática >> 2 | Facultad de Ciencias Humanidades >> 3 | Facultad de Ingeniería >> 4 | Facultad de Agronomía >> 5 | Oficinas Centrales >> 6 | test >> >> >> I want to retrieve all the rows where I find the word Ciencias for >> example. Is there a function or with select is possible to do it? >> >> thanks in advance, >> >> karina >> El Salvador, Centroamerica >> > > For example something like this should work: > > SELECT * FROM table WHERE nombre_uni like '%Ciencias%'; > > more you can find here: > http://www.postgresql.org/docs/8.4/interactive/functions-matching.html > > regards > Szymon Guz >
Re: [GENERAL] find a string contained in an attribute
2010/3/24 Karina Guardado > Hi, > > I want to know if it is possible to search for a string of characters > inside an attribute for example I have the following table and values > > cod_unidad | nombre_uni > +- > 1 | Facultad de Ciencias Naturales y Matemática > 2 | Facultad de Ciencias Humanidades > 3 | Facultad de Ingeniería > 4 | Facultad de Agronomía > 5 | Oficinas Centrales > 6 | test > > > I want to retrieve all the rows where I find the word Ciencias for example. > Is there a function or with select is possible to do it? > > thanks in advance, > > karina > El Salvador, Centroamerica > For example something like this should work: SELECT * FROM table WHERE nombre_uni like '%Ciencias%'; more you can find here: http://www.postgresql.org/docs/8.4/interactive/functions-matching.html regards Szymon Guz
[GENERAL] find a string contained in an attribute
Hi, I want to know if it is possible to search for a string of characters inside an attribute for example I have the following table and values cod_unidad | nombre_uni +- 1 | Facultad de Ciencias Naturales y Matemática 2 | Facultad de Ciencias Humanidades 3 | Facultad de Ingeniería 4 | Facultad de Agronomía 5 | Oficinas Centrales 6 | test I want to retrieve all the rows where I find the word Ciencias for example. Is there a function or with select is possible to do it? thanks in advance, karina El Salvador, Centroamerica
Re: [GENERAL] Large index operation crashes postgres
Can you do?
alter table placex add column geometry_sector integer;
update placex set geometry_sector = geometry_sector(geometry);
P.
On Wed, Mar 24, 2010 at 1:15 PM, Frans Hals wrote:
> Hi,
>
> running a geo-database from a dump restore where still one of the most
> important indexes is missing and so the search is slow.
> Whenever I try to add the follwing index to the table "placex", one of
> the postmaster processes dies and the server restarts.
>
> I try:
> CREATE INDEX idx_placex_sector ON placex USING btree
> (geometry_sector(geometry), rank_address, osm_type, osm_id);
>
> The table counts around 50.000.000 rows.
> The first 20.000.000 are indexed in 20-30 minutes. Nice!
> Then indexing becomes slow and slower, first taking 100.000 rows in
> ten minutes while further consequently decreasing speed.
> When the job reaches something around row 25.000.000 postgres goes down:
>
> WARNING: terminating connection because of crash of another server process
> DETAIL: The postmaster has commanded this server process to roll back
> the current transaction and exit, because another server process
> exited abnormally and possibly corrupted shared memory.
>
> I have checked RAM and changed the HD with no success.
> Experimenting with a lot of different memory settings in the conf-file
> didn't help either.
> Is there anybody else who experienced this and found a way to create this
> index?
> Server is postgres 8.3.9 with 4 GB dedicated RAM.
>
> gemoetry_sector function looks like this (postgis):
>
> DECLARE
> NEWgeometry geometry;
> BEGIN
> -- RAISE WARNING '%',place;
> NEWgeometry := place;
> IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
> ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
> OR ST_Y(ST_Centroid(NEWgeometry))::text in
> ('NaN','Infinity','-Infinity') THEN
> NEWgeometry := ST_buffer(NEWgeometry,0);
> IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
> ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
> OR ST_Y(ST_Centroid(NEWgeometry))::text in
> ('NaN','Infinity','-Infinity') THEN
> RETURN NULL;
> END IF;
> END IF;
> RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 +
> (500-ST_Y(ST_Centroid(NEWgeometry))::integer);
> END;
>
> The subcalled St_Centroid is a postgis C-function located in
> /usr/lib/postgresql/8.3/lib/liblwgeom.
>
>
> Anybody out there has an idea what happens or better how to reach the
> 50.000.000?
>
> Thanks
> Frans
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large index operation crashes postgres
Frans Hals writes: > Whenever I try to add the follwing index to the table "placex", one of > the postmaster processes dies and the server restarts. Can you provide a stack trace from the crash? > I try: > CREATE INDEX idx_placex_sector ON placex USING btree > (geometry_sector(geometry), rank_address, osm_type, osm_id); > The table counts around 50.000.000 rows. > The first 20.000.000 are indexed in 20-30 minutes. Nice! > Then indexing becomes slow and slower, first taking 100.000 rows in > ten minutes while further consequently decreasing speed. > When the job reaches something around row 25.000.000 postgres goes down: On what exactly do you base these statements about number of rows processed? CREATE INDEX doesn't provide any such feedback that I'm aware of. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Large index operation crashes postgres
Hi,
running a geo-database from a dump restore where still one of the most
important indexes is missing and so the search is slow.
Whenever I try to add the follwing index to the table "placex", one of
the postmaster processes dies and the server restarts.
I try:
CREATE INDEX idx_placex_sector ON placex USING btree
(geometry_sector(geometry), rank_address, osm_type, osm_id);
The table counts around 50.000.000 rows.
The first 20.000.000 are indexed in 20-30 minutes. Nice!
Then indexing becomes slow and slower, first taking 100.000 rows in
ten minutes while further consequently decreasing speed.
When the job reaches something around row 25.000.000 postgres goes down:
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back
the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory.
I have checked RAM and changed the HD with no success.
Experimenting with a lot of different memory settings in the conf-file
didn't help either.
Is there anybody else who experienced this and found a way to create this index?
Server is postgres 8.3.9 with 4 GB dedicated RAM.
gemoetry_sector function looks like this (postgis):
DECLARE
NEWgeometry geometry;
BEGIN
-- RAISE WARNING '%',place;
NEWgeometry := place;
IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
OR ST_Y(ST_Centroid(NEWgeometry))::text in
('NaN','Infinity','-Infinity') THEN
NEWgeometry := ST_buffer(NEWgeometry,0);
IF ST_IsEmpty(NEWgeometry) OR NOT ST_IsValid(NEWgeometry) OR
ST_X(ST_Centroid(NEWgeometry))::text in ('NaN','Infinity','-Infinity')
OR ST_Y(ST_Centroid(NEWgeometry))::text in
('NaN','Infinity','-Infinity') THEN
RETURN NULL;
END IF;
END IF;
RETURN (500-ST_X(ST_Centroid(NEWgeometry))::integer)*1000 +
(500-ST_Y(ST_Centroid(NEWgeometry))::integer);
END;
The subcalled St_Centroid is a postgis C-function located in
/usr/lib/postgresql/8.3/lib/liblwgeom.
Anybody out there has an idea what happens or better how to reach the
50.000.000?
Thanks
Frans
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Fw: [GENERAL] Clipping values
- Original Message > From: Tom Lane > You could code that directly with CASE > operations, but it would probably > be easier to use GREATEST/LEAST, along the > lines of >new_percentage = LEAST(num_intervals * .1, > 1); Ah, I didn't know about the LEAST function! I love you (platonically, of course. My fiancée might have issues otherwise). Cheers, Ovid -- Buy the book - http://www.oreilly.com/catalog/perlhks/ Tech blog - http://blogs.perl.org/users/ovid/ Twitter - http://twitter.com/OvidPerl Official Perl 6 Wiki - http://www.perlfoundation.org/perl6 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] question (or feature-request): over ( partition by ... order by LIMIT N)
Hello @all, I know, i can do: select * from (select ... row_number() over (...) ...) foo where row_number < N to limit the rows per group, but the inner select has to retrieve the whole set of records and in the outer select most of them discarded. Why isn't there an over ( ... LIMIT N) ? Other (better) solution? Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Revoking CREATE TABLE
Hi Tom,
Thanks for your help and the hint (off-line) to use the \dn+ command.
You've hit the nail on the head sir!
\dn+
WARNING: nonstandard use of \\ in a string literal at character 281
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
List of
schemas
Name| Owner |Access
privileges | Description
+--+
--+--
information_schema | postgres | {postgres=UC/postgres,=U/postgres}
|
pg_catalog | postgres | {postgres=UC/postgres,=U/postgres}
| system catalog schema
pg_toast | postgres |
| reserved schema for TOAST tables
pg_toast_temp_1| postgres |
|
public | postgres |
{postgres=UC/postgres,=U/postgres,gb_ro=UC/postgres,gb_owner=C/postgres}
| standard public schema
(5 rows)
I'd not used that before.
It shows that the gb_ro user also had extra privs granted to it for the
public schema too (unless I'm reading it wrong). I've revoked them
('all' and 'create') and it works fine now!
There should be 2 additional nologin roles on the db - gb_role and
gb_role_ro - all object privs should be via them, gb_ro should not have
its own privileges.
Thanks again, you're a star!
Pif
--
The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Fw: [GENERAL] Clipping values
Ovid writes: > I have a table with four values which are constantly lowered by user action. > These four values must rise over time, in five minute intervals. The core of > the function, in pseudo-code, would look something like this: > FOREACH row IN SELECT * FROM some_table WHERE some_percentages are less > than 1 > LOOP > new_percentage = num_intervals * .1 > 1 > ? 1 > : num_intervals * .1; > UPDATE some_table SET some_percentages = new_percentage WHERE id = > row.id > END LOOP > But, of course, doing that for four percentages (with at least one having a > different increment value than .1) You could code that directly with CASE operations, but it would probably be easier to use GREATEST/LEAST, along the lines of new_percentage = LEAST(num_intervals * .1, 1); regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Revoking CREATE TABLE
"Tony Webb" writes: > Thanks Tom, > I think I'm still doing something wrong. > As a superuser I run: > #revoke create on schema public from public; > REVOKE > As the read only user straight after running the above: > create table barney2(col1 integer); > CREATE TABLE It works for me: regression=# create user ro; CREATE ROLE regression=# \c - ro You are now connected to database "regression" as user "ro". regression=> create table t1 (f1 int); CREATE TABLE [ in another session, as superuser ] regression=# revoke create on schema public from public; REVOKE [ back to first session ] regression=> create table t2 (f1 int); ERROR: permission denied for schema public Are you sure you revoked the privilege in the same database the read only user is working in? > What should I try next? Presumably the privilege is being inherited from > another role? Not unless you manually set things up that way. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fw: [GENERAL] Clipping values
In response to a private email from someone else on this list, I thought I should send the following clarification. I have a table with four values which are constantly lowered by user action. These four values must rise over time, in five minute intervals. The core of the function, in pseudo-code, would look something like this: FOREACH row IN SELECT * FROM some_table WHERE some_percentages are less than 1 LOOP new_percentage = num_intervals * .1 > 1 ? 1 : num_intervals * .1; UPDATE some_table SET some_percentages = new_percentage WHERE id = row.id END LOOP But, of course, doing that for four percentages (with at least one having a different increment value than .1) Cheers, Ovid -- 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] Revoking CREATE TABLE
Thanks Tom, I think I'm still doing something wrong. As a superuser I run: #revoke create on schema public from public; REVOKE As the read only user straight after running the above: create table barney2(col1 integer); CREATE TABLE \d barney2 Table "public.barney2" Column | Type | Modifiers +-+--- col1 | integer | What should I try next? Presumably the privilege is being inherited from another role? Cheers Pif -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Clipping values
I have the following domain defined: CREATE DOMAIN percentage AS real CONSTRAINT percentage_check CHECK (((VALUE >= 0.0) AND (VALUE <= 1.0))); The various values (aa,bb,cc and dd) defined as "percentage" can increase over time, to a maximum value of 1.0. In fact, I have one table with four different "percentage" values which can increase. The plpsql function I'm writing is going to be called from a cron job every 5 minutes to update "aa, bb, cc and dd" percentages every 5 minutes. The amount of the update is a set amount (we'll say .1 for the sake of argument) times the number of five minute intervals (num_intervals * .1) which have elapsed since the last time it was called. However, "aa + (num_intervals * .1)" might exceed the constraint value of 1.0. Thus, for four different values, I'm looking at ugly IF/ELSE checks to clip those values back to 1.0. This is expected to be a large table and I would really like have this code be simpler. Below is the start of my function. What could I insert into the LOOP to make this simple and correct? CREATE FUNCTION event_update_percentages() RETURNS void AS $$ DECLARE last_update TIMESTAMP := (SELECT update_percentages FROM event_manager); -- 5 minute intervals (60 seconds * 5) num_intervals INTEGER := (SELECT EXTRACT (EPOCH FROM now() - last_update )::int/300); item some_table%ROWTYPE; BEGIN IF num_intervals > 0 THEN FOR item IN SELECT * FROM some_table WHERE ( aa < 1 OR bb < 1 OR cc < 1 OR dd < 1 ) LOOP -- ??? END LOOP; UPDATE event_manager SET update_percentages = now(); END IF; RETURN; END; $$ LANGUAGE plpgsql; Better yet, am I approaching this entirely the wrong way? If there is a simpler solution to gradually increase those variables over time, I'd welcome it. If this is not clear, please let me know and I can try to explain more. Cheers, Ovid -- Buy the book - http://www.oreilly.com/catalog/perlhks/ Tech blog- http://blogs.perl.org/users/ovid/ Twitter - http://twitter.com/OvidPerl Official Perl 6 Wiki - http://www.perlfoundation.org/perl6 -- 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] Revoking CREATE TABLE
"Tony Webb" writes: > I can grant table privileges to the USER and RO but how do I stop these > two users from creating new tables etc? Revoke CREATE privilege on the public schema from PUBLIC (and then grant it back to OWNER and whoever else you want to have it). If you don't want them creating temp tables either, similarly revoke the database-level TEMP privilege. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Revoking CREATE TABLE
Hi, I would like a setup with the following: Three users - one, called OWNER, that owns the tables and can drop, alter and change data in the tables; another called USER that can edit data in the tables created by the owner but cannot create new tables or drop any tables and a third user called RO which has read only access to OWNER's tables but cannot change any data or create new tables etc. I can grant table privileges to the USER and RO but how do I stop these two users from creating new tables etc? I'm creating the tables from a script so I don't think I can easily create a schema so am relying on the public schema. I suspect I'm missing something fundamental here :-\ All help gratefully received. Thanks Pif -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE.
Re: [GENERAL] Warm Standby Setup Documentation
On Mon, Mar 22, 2010 at 9:21 AM, Ogden wrote: > I have looked all over but could not find any detailed docs on setting up a > warm standby solution using PostgreSQL 8.4. I do know of > http://www.postgresql.org/docs/8.4/static/warm-standby.html but was > wondering if there was a more detailed document on this topic. > > Are people using this as a viable backup/hot spare solution? How has it > worked out? > > Thank you > > Ogden > We use it, it works pretty well, although it's a bit of a pain to set up the first time. We have two spares, one which is an equivalent sized machine we use for failover, and one which is a smaller machine that we use for worst-case-scenario file system snapshots/backups. The one thing you should be aware of is that when you fail over, your spare has no spares. I have not found a way around this problem yet. So, when you fail over, there is a window where you have no backups while you're building the new spares. This can be pretty nerve wracking if your database is like ours and it takes 3-6 hours to bring a new spare online from scratch. I was able to build out our solution reading the docs and asking questions on the mailing list. The information is in the docs, you just have to read it a few times for it to sink in. Bryan
Re: [GENERAL] Out of Memory during Insert
yue peng writes: > I encountered an out of memory error during executing un INSERT into > table1(v1,v2,v3) SELECT c1,c2,c3 from table2 where . Most likely the OOM is because of growth of the pending-trigger-event queue --- do you have any foreign key references in that table? Possible solutions are to insert fewer rows at a time, or to drop the FK constraint and then re-create it after you do the bulk insertion. You might also try updating to a newer PG version ... 8.4 and later use only 12 bytes per pending INSERT trigger not 40. That's not necessarily going to be enough to fix this particular case, of course. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Concatenate [solved]
Am 24.03.2010 14:41, schrieb Merlin Moncure:
On Wed, Mar 24, 2010 at 6:22 AM, Frank jansen wrote:
Hi,
can you help me with this tricky concat i have?
I have a function with an execute statement, one line of it doing an md5
hash of some concatenated xml paths with values. I cannot get this one work,
postgres is always complaing about some things, like: "functions and
operators can take at most one set argument"
EXECUTE'
...
md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
(unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
your problem is coming from unnest. it takes the xml array and
returns a set. generally speaking, functions do not operate on a
complete set, only its rows. do you want a single digest for the
entire xpath result on each side or one for each row?
if you want a digest for the entire result, the unnest() call is not
needed. just cast the returned array to text and digest that. if you
want to digest each row of the unnest, you need to use table
expressions and join them together.
merlin
Thank you for your support!
But i resolved this issue by using ARRAY_TO_STRING and array_cat
Kind regards,
Frank Jansen
--
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] Concatenate
On Wed, Mar 24, 2010 at 6:22 AM, Frank jansen wrote:
> Hi,
>
>
> can you help me with this tricky concat i have?
> I have a function with an execute statement, one line of it doing an md5
> hash of some concatenated xml paths with values. I cannot get this one work,
> postgres is always complaing about some things, like: "functions and
> operators can take at most one set argument"
>
> EXECUTE'
> ...
> md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
> (unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
your problem is coming from unnest. it takes the xml array and
returns a set. generally speaking, functions do not operate on a
complete set, only its rows. do you want a single digest for the
entire xpath result on each side or one for each row?
if you want a digest for the entire result, the unnest() call is not
needed. just cast the returned array to text and digest that. if you
want to digest each row of the unnest, you need to use table
expressions and join them together.
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] Concatenate
Frank jansen wrote:
> can you help me with this tricky concat i have?
> I have a function with an execute statement, one line of it doing an md5
> hash of some concatenated xml paths with values. I cannot get this one
> work, postgres is always complaing about some things, like: "functions
> and operators can take at most one set argument"
>
> EXECUTE'
> ...
> md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
> (unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
>
That's too little, can you show more of the statement?
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] Out of Memory during Insert
On 24 March 2010 10:57, yue peng wrote: > Is there any other ways to still insert same amount of data and avoid this > OOM error ? > > I'd expect COPY to be the most effective way of bulk loading data into a database. http://www.postgresql.org/docs/current/static/sql-copy.html Or do inserts in smaller batches. Do you happen to have any triggers or constraints on the table? Regards Thom
[GENERAL] Out of Memory during Insert
Dear, Psqlers, I encountered an out of memory error during executing un INSERT into table1(v1,v2,v3) SELECT c1,c2,c3 from table2 where . The recordset of Select query is around 30M record. And I got following Message : --- ERROR: out of memoryDETAIL: Failed on request of size 40.' in 'insert into --- I found my postgresql process used up 3G Memory . I guess postgresql try to first get all the result of select , and then insert into Table . As the process can't allocate more memory for result of select , and then I got OOM error. Can someone verify my guess ? Or what else could be the reason of OOM ? Is there any other ways to still insert same amount of data and avoid this OOM error ? Thanks , -- Yue
Re: [GENERAL] Help me with this multi-table query
On 03/24/2010 01:14 PM, Dean Rasheed wrote: On 24 March 2010 05:17, Nilesh Govindarajan wrote: On 03/24/2010 12:45 AM, Dean Rasheed wrote: On 23 March 2010 11:07, Nilesh Govindarajanwrote: Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid<>0 order by u.uid; I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean Thanks a lot !! It worked. How to do it using subqueries ? Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean The second method is the best. It takes 3.311 ms to execute. The first method suggested by you takes 5.7 ms, and the worst is my method which takes 60ms (boo). Thanks a lot :) :) :) -- Nilesh Govindarajan Site & Server Administrator www.itech7.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] Concatenate
Hi,
can you help me with this tricky concat i have?
I have a function with an execute statement, one line of it doing an md5
hash of some concatenated xml paths with values. I cannot get this one
work, postgres is always complaing about some things, like: "functions
and operators can take at most one set argument"
EXECUTE'
...
md5_hash=md5(unnest(xpath(''/foo/bar'',xml_content::xml))::text) ||
(unnest(xpath(''/foo/bar2'',xml_content::xml))::text)
Kind regards,
Frank Jansen
--
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] PL/pgSQL & OVERLAPS operator (SOLVED!)
--- On Tue, 3/23/10, Tom Lane wrote: > Yeah --- that SELECT will result in no change to the > variables, ie, > they'll still be NULL. So the OVERLAPS always fails. Tom & Andreas, I thank you for your help. Renaming the variables solved the problem. :-) Regards, Tuo -- 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] Help me with this multi-table query
On 24 March 2010 05:17, Nilesh Govindarajan wrote: > On 03/24/2010 12:45 AM, Dean Rasheed wrote: >> >> On 23 March 2010 11:07, Nilesh Govindarajan wrote: >>> >>> Hi, >>> >>> I want to find out the userid, nodecount and comment count of the userid. >>> >>> I'm going wrong somewhere. >>> >>> Check my SQL Code- >>> >>> select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join >>> node >>> n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by >>> u.uid having u.uid<> 0 order by u.uid; >>> >> >> I think you want select u.uid, count(distinct n.nid) nc , >> count(distinct c.cid) cc from ... >> otherwise you're counting each node/comment multiple times as the rows in >> the >> join multiply up (note 85 x 174 = 14790). >> >> For big tables, this could start to become inefficient, and you might >> be better off >> doing your queries 1 and 2 above as sub-queries and joining them in an >> outer query. >> >> Regards, >> Dean > > Thanks a lot !! It worked. > How to do it using subqueries ? > Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
