Re: [GENERAL] Hung postmaster (8.3.9)
We should probably also check and prohibit including directories as files. On Tuesday, March 2, 2010, Tom Lane wrote: > In the meantime, it seems like we ought to take two defensive steps: -- greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] to_timestamp() and quarters
Hello.
I can't seem to get to_timestamp() or to_date() to work with quarters,
can anyone see what I'm doing wrong?
e.g.
select to_date('2010-1', '-Q');
Gives "2010-01-01" (correct).
select to_date('2010-3', '-Q');
Also gives "2010-01-01" (should be 2010-07-01);
Am I misunderstanding how this function works? EXTRACT(QUARTER FROM
date) works fine.
I'm running Postgresql 8.4.2 on a FreeBSD system.
Thanks,
Asher.
--
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 grant a user read-only access to a database?
Hi, I tried this: names=# grant select on database names to spice; ERROR: invalid privilege type SELECT for database The documentation seems to imply I need to grant SELECT to each table separately. That's a lot of work, and what if new tables are created? Thanks, Antonio -- 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 grant a user read-only access to a database?
On 2 March 2010 11:12, Antonio Goméz Soto wrote: > Hi, > > I tried this: > > names=# grant select on database names to spice; > ERROR: invalid privilege type SELECT for database > > The documentation seems to imply I need to grant SELECT > to each table separately. That's a lot of work, and what if > new tables are created? > > Thanks, > Antonio > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > The privileges you can grant on a database are only related to the creation of tables and connecting to that database. You could create a role which has SELECT-only access, apply that role to all your tables, and assign users (other roles) as members of that role. Regards Thom -- 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 grant a user read-only access to a database?
On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: > On 2 March 2010 11:12, Antonio Goméz Soto > wrote: > > Hi, > > > > I tried this: > > > > names=# grant select on database names to spice; > > ERROR: invalid privilege type SELECT for database > > > > The documentation seems to imply I need to grant SELECT > > to each table separately. That's a lot of work, and what if > > new tables are created? > > > > Thanks, > > Antonio > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > > The privileges you can grant on a database are only related to the > creation of tables and connecting to that database. > > You could create a role which has SELECT-only access, apply that role > to all your tables, and assign users (other roles) as members of that > role. > > Regards > > Thom > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > How to create that ? I'm also interested in this as I need this for backing up my databases. -- Nilesh Govindarajan Site & Server Administrator www.itech7.com
Re: [GENERAL] Putting index entries to XLog
On Thu, 2010-02-25 at 12:41 +0100, Carsten Kropf wrote:
> I have encountered a problem while implementing an index structure. I don't
> have any access to the RM (I don't know, how to set it up properly) using my
> index. However, when I don't have the situation of working with a temporary
> table, I need to put entries using XLogInsert(...). The problem for me is,
> that based on the fact, that I don't have the access to the RM, I can't put
> proper entries to the xlog facility. How can I achieve that my index
> structure can be stored properly at least?
> I'm getting lots of errors, when I just put a XLogRecPtr with data {1,1} as
> LSN of the page. On the other hand when doing XLogInsert calls to work with
> the component properly I don't know which data to put in there. When I'm
> using some kind of RM_ID (like RM_GIST_ID or something like that) I'm getting
> problems because I don't know which code to insert there. I decided to take
> RM_GIST_ID and an invalid code (so it won't call any of the xlog gist
> implementations upon recovery) which, however failed, too. Unfortunately my
> task is to implement a certain structure with PostgreSQL, so all of my
> current work actually depend on this action.
> I hope that somebody will be able to help me according to this issue.
> Thanks in advance
Each new index type requires a new rmgr to allow it to be recoverable.
The rmgr is not part of the index API, so it is not currently possible
to add a new index type *and* make it recoverable without editing the
server source code.
--
Simon Riggs www.2ndQuadrant.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] custom index
On Mon, 2010-03-01 at 14:52 +0100, michael uwe maier wrote: > i want to develop an index for a special problem. > The postgres docs shows the necessary bits in > http://www.postgresql.org/docs/8.4/interactive/indexam.html > especially paragraph 50.2 "Index Access Method Functions". > > Now my question: can i use the (documented) Server Programming > interface within this functions (e.g. SPI_exec within ambuild) > or do i need to use other (possibly undocumented) functions. > Background: The index needs some metadata which can be created > within "normal" tables. Otherwise this information must be built > within the index. Yes. There isn't any other way to pass user-defined metadata to an index at build time. "Storage parameters" exist, but these aren't user extensible, yet. Using SQL within anything other than ambuild would cause the index to be volatile and likely to be non-useful. > And as an appendix to my question: Are there any (simple) examples > for custom indexes ? Look at hash indexes. That is somewhat simpler because of the lack of transaction logging. -- Simon Riggs www.2ndQuadrant.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] need a query
I have data as followos: Table: contact column: phone as varchar +1 (800) 863-3950 ext. 517 +1.510.291.6100 ext2347 +1.714.545.8886 ext 144 714.545.8887 ext 144 714.545.8898 +1.510.291.6101 I need to extract only the phone numbers using a SQL query like: +1 (800) 863-3950 ext. 517 --- 8008603950 +1.510.291.6100 ext2347 5102916100 +1.714.545.8886 ext 144 - 7145458886 714.545.8887 ext 144 --- 7145458887 714.545.8898 -- 7145458898 +1.510.291.6101 - 5102916101 What should be the sql? Any help.
Re: [GENERAL] How to grant a user read-only access to a database?
On 2 March 2010 11:46, Nilesh Govindarajan wrote: > On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: >> >> On 2 March 2010 11:12, Antonio Goméz Soto >> wrote: >> > Hi, >> > >> > I tried this: >> > >> > names=# grant select on database names to spice; >> > ERROR: invalid privilege type SELECT for database >> > >> > The documentation seems to imply I need to grant SELECT >> > to each table separately. That's a lot of work, and what if >> > new tables are created? >> > >> > Thanks, >> > Antonio >> > >> > -- >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-general >> > >> >> The privileges you can grant on a database are only related to the >> creation of tables and connecting to that database. >> >> You could create a role which has SELECT-only access, apply that role >> to all your tables, and assign users (other roles) as members of that >> role. >> >> Regards >> >> Thom >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > How to create that ? I'm also interested in this as I need this for backing > up my databases. > > -- Okay, here's an example: CREATE ROLE readonly; -- This user won't be able to do anything by default, not even log in GRANT SELECT on table_a TO readonly; GRANT SELECT on table_b TO readonly; GRANT SELECT on table_c TO readonly; CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned this user to any group SET ROLE testuser; SELECT * FROM table_a; We get: ERROR: permission denied for relation table_a SET ROLE postgres; DROP ROLE testuser; CREATE ROLE testuser WITH LOGIN IN ROLE readonly; SET ROLE testuser; SELECT * FROM table_a; This would then return the results from table_a Regards Thom -- 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 grant a user read-only access to a database?
On 2/03/2010 8:00 PM, Thom Brown wrote: CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned this user to any group SET ROLE testuser; SELECT * FROM table_a; We get: ERROR: permission denied for relation table_a ... if table_a doesn't have grants to public, which it may well. I like to revoke public access to my schema and to my database to make very, very sure that only roles I've explicitly allowed can get in. I prefer to explicitly revoke all rights from public on objects. -- 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] need a query
2010/3/2 AI Rumman :
> I have data as followos:
>
> Table: contact
> column: phone as varchar
> +1 (800) 863-3950 ext. 517
> +1.510.291.6100 ext2347
> +1.714.545.8886 ext 144
> 714.545.8887 ext 144
> 714.545.8898
> +1.510.291.6101
>
> I need to extract only the phone numbers using a SQL query like:
>
> +1 (800) 863-3950 ext. 517 --- 8008603950
> +1.510.291.6100 ext2347 5102916100
> +1.714.545.8886 ext 144 - 7145458886
> 714.545.8887 ext 144 --- 7145458887
> 714.545.8898 -- 7145458898
> +1.510.291.6101 - 5102916101
>
look regular expression
postgres=# select (regexp_matches(regexp_replace('+1 (800) 863-3950
ext. 517',e'(\\.)|(\\+1)|\\(|\\)| |-','','g'), e'\\d+'))[1];
regexp_matches
8008633950
(1 row)
Regards
Pavel Stehule
> What should be the sql?
>
> Any help.
--
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 grant a user read-only access to a database?
Op 02-03-10 13:00, Thom Brown schreef: On 2 March 2010 11:46, Nilesh Govindarajan wrote: On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: On 2 March 2010 11:12, Antonio Goméz Soto wrote: Hi, I tried this: names=# grant select on database names to spice; ERROR: invalid privilege type SELECT for database The documentation seems to imply I need to grant SELECT to each table separately. That's a lot of work, and what if new tables are created? Thanks, Antonio -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general The privileges you can grant on a database are only related to the creation of tables and connecting to that database. You could create a role which has SELECT-only access, apply that role to all your tables, and assign users (other roles) as members of that role. Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general How to create that ? I'm also interested in this as I need this for backing up my databases. -- Okay, here's an example: CREATE ROLE readonly; -- This user won't be able to do anything by default, not even log in GRANT SELECT on table_a TO readonly; GRANT SELECT on table_b TO readonly; GRANT SELECT on table_c TO readonly; CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned this user to any group SET ROLE testuser; SELECT * FROM table_a; We get: ERROR: permission denied for relation table_a SET ROLE postgres; DROP ROLE testuser; CREATE ROLE testuser WITH LOGIN IN ROLE readonly; SET ROLE testuser; SELECT * FROM table_a; This would then return the results from table_a Regards Thom But I still need to define access to each table separately? Thanks, Antonio. -- 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] to_timestamp() and quarters
Asher Hoskins writes: > I can't seem to get to_timestamp() or to_date() to work with quarters, The source code says * We ignore Q when converting to date because it is not * normative. * * We still parse the source string for an integer, but it * isn't stored anywhere in 'out'. That might be a reasonable position, but it seems like it'd be better to throw an error than silently do nothing. Anybody know what Oracle does with this? 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 grant a user read-only access to a database?
On 2 March 2010 14:49, Antonio Goméz Soto wrote: > Op 02-03-10 13:00, Thom Brown schreef: >> >> On 2 March 2010 11:46, Nilesh Govindarajan wrote: >>> >>> On Tue, Mar 2, 2010 at 4:57 PM, Thom Brown wrote: On 2 March 2010 11:12, Antonio Goméz Soto wrote: > > Hi, > > I tried this: > > names=# grant select on database names to spice; > ERROR: invalid privilege type SELECT for database > > The documentation seems to imply I need to grant SELECT > to each table separately. That's a lot of work, and what if > new tables are created? > > Thanks, > Antonio > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > The privileges you can grant on a database are only related to the creation of tables and connecting to that database. You could create a role which has SELECT-only access, apply that role to all your tables, and assign users (other roles) as members of that role. Regards Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general >>> >>> How to create that ? I'm also interested in this as I need this for >>> backing >>> up my databases. >>> >>> -- >> >> Okay, here's an example: >> >> CREATE ROLE readonly; -- This user won't be able to do anything by >> default, not even log in >> >> GRANT SELECT on table_a TO readonly; >> GRANT SELECT on table_b TO readonly; >> GRANT SELECT on table_c TO readonly; >> >> CREATE ROLE testuser WITH LOGIN; -- At this point we haven't assigned >> this user to any group >> >> SET ROLE testuser; >> SELECT * FROM table_a; >> >> We get: >> ERROR: permission denied for relation table_a >> >> SET ROLE postgres; >> >> DROP ROLE testuser; >> CREATE ROLE testuser WITH LOGIN IN ROLE readonly; >> >> SET ROLE testuser; >> SELECT * FROM table_a; >> >> This would then return the results from table_a >> >> Regards >> >> Thom > > But I still need to define access to each table separately? > > Thanks, > Antonio. > As far as I'm aware. It's only in the upcoming version 9.0 that you can do things like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; Other folk on here may have some alternative suggestions though. Thom -- 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 grant a user read-only access to a database?
On 02/03/2010 14:56, Thom Brown wrote: >> >> But I still need to define access to each table separately? >> >> Thanks, >> Antonio. >> > > As far as I'm aware. It's only in the upcoming version 9.0 that you > can do things like: > > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; > > Other folk on here may have some alternative suggestions though. I think people have in the past posted queries that extract the table names from the system catalogues and then grant privileges on them it might be worthwhile having a trawl through the archives. 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] to_timestamp() and quarters
In response to Tom Lane : > Asher Hoskins writes: > > I can't seem to get to_timestamp() or to_date() to work with quarters, > > The source code says > > * We ignore Q when converting to date because it is not > * normative. > * > * We still parse the source string for an integer, but it > * isn't stored anywhere in 'out'. > Ouch, sound like MySQL / myisam: we accept things like foreign key, but we don't implement that... +1 for raise an error or something, but no 'accept & ignore'. 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] How to grant a user read-only access to a database?
Op 02-03-10 16:14, Raymond O'Donnell schreef: On 02/03/2010 14:56, Thom Brown wrote: But I still need to define access to each table separately? Thanks, Antonio. As far as I'm aware. It's only in the upcoming version 9.0 that you can do things like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; Other folk on here may have some alternative suggestions though. I think people have in the past posted queries that extract the table names from the system catalogues and then grant privileges on them it might be worthwhile having a trawl through the archives. Ray. Ok, will do. Thanks. Antonio -- 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 grant a user read-only access to a database?
if you don't want to search the archives, it could just be easier to look at the catalog tables yourself. If you have no experience with them, many times if you do pg_foo when you are interested in 'foo' you will get something, i.e pg_user also exists. #\d pg_tables View "pg_catalog.pg_tables" Column| Type | Modifiers -+-+--- schemaname | "name" | tablename | "name" | tableowner | "name" | tablespace | "name" | hasindexes | boolean | hasrules| boolean | hastriggers | boolean | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS "tablespace", c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers > 0 AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE c.relkind = 'r'::"char"; and then the sql just comes naturally: select 'grant select on '|| schemaname || '.' || tablename || ' to baz' from pg_tables where schemaname = 'bar' ; Note that it is important to select the schemaname because there could be two different tables in two different schemas with the same tablename. Also you should keep in mind that this will only work for tables, if you start adding views you have to add more to the generation of sql. -Said Said Ramirez Raymond O'Donnell wrote: On 02/03/2010 14:56, Thom Brown wrote: But I still need to define access to each table separately? Thanks, Antonio. As far as I'm aware. It's only in the upcoming version 9.0 that you can do things like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; Other folk on here may have some alternative suggestions though. I think people have in the past posted queries that extract the table names from the system catalogues and then grant privileges on them it might be worthwhile having a trawl through the archives. Ray. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
I am currently using the pg_hotbackup Perl script to backup my production PostgreSQL database. However, the next production database is going to be close to a terrabyte in size. We feel that a tar cvzf command is not the correct OS backup solution to use. Instead we would like to use lvmsnapshot. Is anyone using the pg_hotbackup script but instead of the tar cvzf OS command are they using lvmsnapshot? Or, is anyone using lvmsnapshot and doing point-in-time recoveries? Thanks, Sandra Arnold Sr. Database Administrator DOE/OSTI Oak Ridge, TN
[GENERAL] I suspend the subscription
quiero salir o supender la suscripción.. gracias I suspend the subscription ¡Obtén la mejor experiencia en la web! Descarga gratis el nuevo Internet Explorer 8. http://downloads.yahoo.com/ieak8/?l=e1
[GENERAL] tipo de dato
disculpen alguien tiene o sabe de algun documento que me pueda ayudar para defender la idea de que los tipos de datos si afectan en la velocidad de una consulta por ejemplo. tengo un tipo de dato timestamp y un numerico y lo que quiero dar a entender es que una consulta con un numerico con un tipo de indice B-TREE es mas rapido que uno con fechas y un indice tipo HASH si alguien puede aventarme un manual o un link hacia un web que no seas la de postgres o algo que apoye mi idea muchas gracias!! Si estoy mal hagan el comentario!!!
Re: [GENERAL] to_timestamp() and quarters
Tom Lane wrote: Asher Hoskins writes: I can't seem to get to_timestamp() or to_date() to work with quarters, The source code says * We ignore Q when converting to date because it is not * normative. * * We still parse the source string for an integer, but it * isn't stored anywhere in 'out'. That might be a reasonable position, but it seems like it'd be better to throw an error than silently do nothing. Anybody know what Oracle does with this? +1 for throwing error. Oracle 10g throws ORA-01820: format code cannot appear in date input format. Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Array columns vs normalized table
I work with state labor data which is reported to us in the form industry, year, quarter1, quarter2, quarter3, quarter4 where each quarter represents an employment count. Obviously, this can be normalized to industry, year, quarter, employment Can anyone comment on, or point to me to an article or discussion regarding, why one would use an array column instead of normalizing the data? That is, would there be any benefit to storing it as industry int, year smallint, employment int[ ] where the last column would be a four element array with data for the four quarters. Thanks, --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] disable triggers isolated to transaction only?
I'm planning to split a large table into partitions. During the migration, all new data will be added to the sub-tables, and I will be moving the data from the master table to the proper sub tables at the same time. The trick is that I have an INSERT trigger to keep track of various counters. I need to be able to disable that trigger for the data I'm moving, yet leave it intact for the new data being inserted. My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER ALL within a transaction only affect my transaction, or will it affect anyone inserting into this subtable. If it blocks external inserts that's ok since my transactions are small while moving the data. I guess at worse I lock the table. -- 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] tipo de dato
Miguel, Tenras mas exito si haces tus preguntas en la lista en espanol : pgsql-es-ay...@postgresql.org ya que ahi la majoria de la gente habla ingles y no espanol -Said Said Ramirez Miguel Angel Hernandez Moreno wrote: disculpen alguien tiene o sabe de algun documento que me pueda ayudar para defender la idea de que los tipos de datos si afectan en la velocidad de una consulta por ejemplo. tengo un tipo de dato timestamp y un numerico y lo que quiero dar a entender es que una consulta con un numerico con un tipo de indice B-TREE es mas rapido que uno con fechas y un indice tipo HASH si alguien puede aventarme un manual o un link hacia un web que no seas la de postgres o algo que apoye mi idea muchas gracias!! Si estoy mal hagan el comentario!!! -- 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] Array columns vs normalized table
Hello 2010/3/2 Lee Hachadoorian : > I work with state labor data which is reported to us in the form > > industry, year, quarter1, quarter2, quarter3, quarter4 > > where each quarter represents an employment count. Obviously, this can > be normalized to > > industry, year, quarter, employment > > Can anyone comment on, or point to me to an article or discussion > regarding, why one would use an array column instead of normalizing > the data? That is, would there be any benefit to storing it as for very large timeseries you can use arrays. The storage can be more effective. sample industry year q1 q2 q3 q4 1, 2001, 10,11,12,13 1, 2002, 14, 15,16,17 can be transformed to tuple (industry: 1, start_year: 2002, data: 10,11,12,13,14,15,16,17, . ) your model isn't more safer then normalised tables and I think is better prefer normalized tables. the queries to arrays are difficult - so usually data from arrays are dynamically unpacked to tables. But time series stored as array take significantly less space on disc. Look on http://www.postgresql.org/docs/8.3/static/storage-page-layout.html - you safe repeated row headers. Regards Pavel Stehule I hope so nobody uses this technique. It cannot substitute normalizated tables. > > industry int, year smallint, employment int[ ] if > > where the last column would be a four element array with data for the > four quarters. > > Thanks, > --Lee > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center > > -- > 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] Array columns vs normalized table
On Tue, Mar 2, 2010 at 11:21 AM, Lee Hachadoorian wrote: > I work with state labor data which is reported to us in the form > > industry, year, quarter1, quarter2, quarter3, quarter4 > > where each quarter represents an employment count. Obviously, this can > be normalized to > > industry, year, quarter, employment > > Can anyone comment on, or point to me to an article or discussion > regarding, why one would use an array column instead of normalizing > the data? That is, would there be any benefit to storing it as > > industry int, year smallint, employment int[ ] > > where the last column would be a four element array with data for the > four quarters. I think you might want to step back and ask yourself why you'd want to normalize this data at all? Unless you are trying to operate on all 4 columns at once (comparisons or aggregation, etc.) I see no reason to. It will take more space and give you no new capabilities. If you need do need to operate on all 4 quarters simultaneously then you may very well want to normalize, but in that case there's certainly no advantage in going to an array type. Personally, the only reason I'd see for ever using an array type is when you have many very closely related values that would cause some huge number of rows if the data is stored normalized -- Peter Hunsberger -- 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] Array columns vs normalized table
Lee Hachadoorian wrote: I work with state labor data which is reported to us in the form industry, year, quarter1, quarter2, quarter3, quarter4 where each quarter represents an employment count. Obviously, this can be normalized to industry, year, quarter, employment Can anyone comment on, or point to me to an article or discussion regarding, why one would use an array column instead of normalizing the data? That is, would there be any benefit to storing it as industry int, year smallint, employment int[ ] where the last column would be a four element array with data for the four quarters. Thanks, --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center If you want to do that, I'd recommend: industry int, yearsmallint, emp_q1 int, emp_q2 int, emp_q3 int, emp_q4 int That way it is more clear, easier to query, uses less space and you wont end up with employment data for the 5th quarter or something odd like that. Arrays are great for working with your data during the query process. But you should generally avoid using them to store your data on disk. Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
Arnold, Sandra wrote: I am currently using the pg_hotbackup Perl script to backup my production PostgreSQL database. However, the next production database is going to be close to a terrabyte in size. We feel that a tar cvzf command is not the correct OS backup solution to use. Instead we would like to use lvmsnapshot. Is anyone using the pg_hotbackup script but instead of the tar cvzf OS command are they using lvmsnapshot? I believe that if you take an LVM snapshot and the change size exceeds the snapshot size (a configurable value) then the snapshot gets released, so you might want to be careful with this solution (I don't know how much data you accumulate, or how fast you accumulate it, or if that rate changes, etc.). You'll also need to ensure that you snapshot any and all tablespaces (if you use pg_start_backup() first the snapshots need not be simultaneous, but they still must be done). You might consider using rsync instead, it should be a lot faster than tar, and doesn't have the same downside as using snapshots. You'll also find that it is fairly widely used in the PostgreSQL community. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com Expert PostgreSQL, Python, PHP, and other open source training. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] createdb but revoke dropdb
Hi, In using 8.3. I'm trying to set up programmatic database creation but is there a way that the user creating the databases can be restricting from dropping them? I have two roles, 'adminuser' with createdb permission, and 'dbuser' a user with CRUD privileges. adminuser is a member of the dbuser role, this seems to allow adminuser to createdb databases for dbuser with: createdb -U adminuser -O dbuser new_database_name Adding .pgpass to the linux user's home directory allows createdb to work without additional user input. But now it seems the linux user also has dropdb privileges. How can i restrict this? Perhaps there is a recommended method to disable dropdb? Can anyone suggest? The adminuser has no login privileges so by removing dropdb this should remove the possibility for any hacker chaos other than creating more databases? Thanks in advance for any advice, Ben -- 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] Array columns vs normalized table
Pavel, the idea of using arrays to store long time-series data sounds good, but I take your point that normalized tables might be better and are easier to query. I suppose the choice will be between normalizing or using the denormalized industry int, year smallint, emp_q1 int, emp_q2 int, emp_q3 int, emp_q4 int as suggested by Peter and Scott. We're mostly actually interested in annual numbers, but need to preserve the quarterly data for verification and for unusual analyses. So perhaps storing denormalized with an additional emp_annual int field, and a view that keeps the quarterly data out of sight. Thanks for your replies. Please feel free to comment if you think of anything else. Best, --Lee -- Lee Hachadoorian PhD Student, Geography Program in Earth & Environmental Sciences CUNY Graduate Center -- 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] disable triggers isolated to transaction only?
On Mar 2, 2010, at 9:48 AM, Vick Khera wrote: > I guess at worse I lock the table. Before you go there, assuming you cannot just disable a trigger for a session, then depending on how many counters your insert trigger modifies, it might be better to simply undo the trigger's effects in the same transaction as the migration. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
On Tue, Mar 2, 2010 at 8:43 AM, Arnold, Sandra wrote: > I am currently using the pg_hotbackup Perl script to backup my production > PostgreSQL database. However, the next production database is going to be > close to a terrabyte in size. We feel that a tar cvzf command is not the > correct OS backup solution to use. Instead we would like to use > lvmsnapshot. Is anyone using the pg_hotbackup script but instead of the tar > cvzf OS command are they using lvmsnapshot? > > Or, is anyone using lvmsnapshot and doing point-in-time recoveries? I do not run my databases on LVM because, at least in the past, LVM did not properly honor fsync / write barrier commands. I don't know if this has since been fixed. If your transaction rate on LVM is unnaturally higher than it should / could be, then suspect it is not fsyncing, and putting your data on it is putting it at risk. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
FYI, here's the conversations on the subject of LVM and write barriers from last year about this time: http://www.redhat.com/archives/linux-lvm/2009-March/msg00025.html http://archives.postgresql.org/pgsql-general/2009-03/msg00204.php Note that it's also considered bad form to post the same question to multiple lists. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] The OS Command for pg_hotbackup -- Use lvmsnapshot instead of tar cvzf
On Tue, Mar 2, 2010 at 12:20 PM, Scott Marlowe wrote: > On Tue, Mar 2, 2010 at 8:43 AM, Arnold, Sandra wrote: >> I am currently using the pg_hotbackup Perl script to backup my production >> PostgreSQL database. However, the next production database is going to be >> close to a terrabyte in size. We feel that a tar cvzf command is not the >> correct OS backup solution to use. Instead we would like to use >> lvmsnapshot. Is anyone using the pg_hotbackup script but instead of the tar >> cvzf OS command are they using lvmsnapshot? >> >> Or, is anyone using lvmsnapshot and doing point-in-time recoveries? > > I do not run my databases on LVM because, at least in the past, LVM > did not properly honor fsync / write barrier commands. I don't know > if this has since been fixed. If your transaction rate on LVM is > unnaturally higher than it should / could be, then suspect it is not > fsyncing, and putting your data on it is putting it at risk. From what I've read further on, it seems it's ok as long as it's on a storage system like a battery backed RAID controller. Still, someone on the list did some testing and LVM resulted in a reduced throughput when on large fast RAID arrays, so do some testing before you set off into production with LVM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FSM and VM file
Hi all - There are lot of FSM and VM files getting generated in the base directory. Do we need these files and I don't know the reason why these files are getting generated. I read the documentation, but not able to follow well. I will read it again. But do we need to keep these files or there is any process can we run to clean these. Can you please help? Regards
Re: [GENERAL] FSM and VM file
akp geek wrote on 02.03.2010 22:11: Hi all - There are lot of FSM and VM files getting generated in the base directory. Do we need these files and I don't know the reason why these files are getting generated. I read the documentation, but not able to follow well. I will read it again. But do we need to keep these files or there is any process can we run to clean these. Can you please help? Regards My understanding is that the FSM files are the "Free Space Map" files. But you should never ever under no circumstance delete files manually from the data directory! Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FSM and VM file
Those are the free space map / visibility map files. There is going to be one of each for every relation. No worries :-) They are supposed to be there: http://wiki.postgresql.org/wiki/Segment_Visibility_Map Don't clean them up, doing so could be disastrous. Actually, if I were you, I would avoid doing ANY deletes / purges in the data directory. Everything in there is maintained by the engine. --Scott
Re: [GENERAL] FSM and VM file
I am doing a replication of one of the database and the size of the slave database is growing exponentially . Right now the size of master db is 849M and the slave is 7GB. When I noticed the base directory on the slave , that's when I noticed the fsm and vm files and raised the question. is there any reason why this happens? or is there way to limit the size of the file. my master is 8.3 and slave is 8.4 Appreciate your help Regards On Tue, Mar 2, 2010 at 4:29 PM, Scott Mead wrote: > Those are the free space map / visibility map files. There is going to be > one of each for every relation. > > No worries :-) They are supposed to be there: > > http://wiki.postgresql.org/wiki/Segment_Visibility_Map > > > Don't clean them up, doing so could be disastrous. Actually, if I were > you, I would avoid doing ANY deletes / purges in the data directory. > Everything in there is maintained by the engine. > > --Scott >
[GENERAL] Not all functions in schema pg_catalog are "visible"
Hi, I just noticed that that there are functions defined (such as pg_catalog.time(timestamp) that can only be called when prefixed with pg_catalog. However other functions (that are at first glance defined identically to time()) can be called without prefixing them with pg_catalog. My understanding is that time(timestamp) is there to support the various CAST ( .. ) expressions, but why isn't it exposed as a "regular" function as well (as it seems to work just fine when being called directly) So, just out of curiosity: how could I tell by looking at pg_proc (or other system tables) which of those functions is "public" and which is not? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] disable triggers isolated to transaction only?
Vick Khera writes: > My question is this: will ALTER TABLE ONLY $subtable DISABLE TRIGGER > ALL within a transaction only affect my transaction, or will it affect > anyone inserting into this subtable. If it blocks external inserts > that's ok since my transactions are small while moving the data. I > guess at worse I lock the table. Yeah, ALTER TABLE will lock the table anyway. As long as you re-enable the triggers before committing, it won't affect any other transaction. 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] SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
I do not know how to really describe this... In pascal I would do If (AField and Flag3)=Flag3 then ... Checking to see if AField contains the BIT(s) FLAG3 is set to... is there a way to do this in a SELECT statement -- select * from ATable where AField and 0x0004=0x0004 ? Thanks, O. -- 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] SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
Figured it out: ./sql -S=10.1.10.232 PostgreSQL v8.x Compatible SQL Command Line Tool by 3F, LLC (www.3flabs.com ) SQL> select * from bffcomwiki where flags & 8 != 0; pagename = = = = = = Welcome 1 records found in 3.00 ms. SQL> On Mar 2, 2010, at 8:30 PM, Ozz Nixon wrote: I do not know how to really describe this... In pascal I would do If (AField and Flag3)=Flag3 then ... Checking to see if AField contains the BIT(s) FLAG3 is set to... is there a way to do this in a SELECT statement -- select * from ATable where AField and 0x0004=0x0004 ? Thanks, O. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trigger help - updates to column data with null values
Hello all! I do my best to read and google my way around issues, but I seem to be missing something. Probably simple too. So I have a trigger function, in which I'd like to check if a particular column has changed. It works great, unless either the OLD or NEW values are NULL. The purpose is to create a audit/log of the record. I prefer to only test for certain columns, not the whole table. CREATE OR REPLACE FUNCTION tr_employees_bu() RETURNS trigger AS $BODY$ begin if (new.trainingdate <> old.trainingdate) then insert into log_employees (employeeid, source, datecreated, createdby, oldvalue,newvalue) values (old.employeeid,'trainingdate', now(),new.updatedby, cast(old.codetrainingdate as varchar), cast(new.codetrainingdate as varchar)); end if; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; I tried playing with "coalesce" and "nullif" but didn't get anywhere with those. I think the "if" expression is not right. Thanks for any help! Greg Fischer www.1stbyte.com
Re: [GENERAL] Trigger help - updates to column data with null values
Greg Fischer wrote: > Hello all! > I do my best to read and google my way around issues, but I seem to be > missing something. Probably simple too. > > So I have a trigger function, in which I'd like to check if a particular > column has changed. It works great, unless either the OLD or NEW values > are NULL. The purpose is to create a audit/log of the record. I prefer > to only test for certain columns, not the whole table. > > > CREATE OR REPLACE FUNCTION tr_employees_bu() > RETURNS trigger AS > $BODY$ > begin > if (new.trainingdate <> old.trainingdate) then Instead, try the test: new.trainingdate IS DISTINCT FROM old.trainingdate See the manual for 'IS DISTINCT FROM'. -- 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] Trigger help - updates to column data with null values
NICE!!! Worked beautifully! I figured NULL's were not evaluating as a value, but I didn't have the correct operators: these constructs effectively act as though null were a normal data value, > rather than “unknown”. I guess I wasn't reading the right part of the manuals! LOL! Thanks so much! Greg Fischer www.1stbyte.com On Tue, Mar 2, 2010 at 8:07 PM, Craig Ringer wrote: > Greg Fischer wrote: > > Hello all! > > I do my best to read and google my way around issues, but I seem to be > > missing something. Probably simple too. > > > > So I have a trigger function, in which I'd like to check if a particular > > column has changed. It works great, unless either the OLD or NEW values > > are NULL. The purpose is to create a audit/log of the record. I prefer > > to only test for certain columns, not the whole table. > > > > > > CREATE OR REPLACE FUNCTION tr_employees_bu() > > RETURNS trigger AS > > $BODY$ > > begin > > if (new.trainingdate <> old.trainingdate) then > > Instead, try the test: > > new.trainingdate IS DISTINCT FROM old.trainingdate > > See the manual for 'IS DISTINCT FROM'. > > -- > Craig Ringer >
Re: [GENERAL] SQL Syntax - like FIELD and BITPATTERN = BITPATTERN?
> I do not know how to really describe this... > > In pascal I would do > > If (AField and Flag3)=Flag3 then ... > > Checking to see if AField contains the BIT(s) FLAG3 is set to... is > there a way to do this in a SELECT statement -- select * from ATable > where AField and 0x0004=0x0004 > > ? > > Thanks, > O. > perhaps select * from ATable where AField::bit(16) & B'0100' = B'0100'; Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
