[GENERAL] partitioning and dynamic query creation
Hello list,
I'm currently thinking about a possibility to create a dynamic insert
statement inside a trigger function to put the data in the correct partition.
What am I talking about ?
I want to put data dependant on a timestamp column ("datetime") in seperate
partitions. Therefore I created the partitions, checks and a trigger function
with the following code:
""
...
date_part='';
date_part = to_char(NEW.datetime,'') || to_char(NEW.datetime,'MM');
tablename = 'table_' || date_part;
RAISE NOTICE 'target table: %', tablename;
EXECUTE 'insert into ' || tablename::regclass || ' values (NEW.*);';
--IF ( DATE (NEW.datetime) >= DATE '2010-11-01' AND
-- DATE (NEW.datetime) < DATE '2010-12-01' ) THEN
--INSERT INTO tab_tour201011 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2010-12-01' AND
--DATE (NEW.datetime) < DATE '2011-01-01' ) THEN
--INSERT INTO tab_tour201012 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2011-01-01' AND
--DATE (NEW.datetime) < DATE '2011-02-01' ) THEN
--INSERT INTO tab_tour201101 VALUES (NEW.*);
--ELSIF ( DATE (NEW.datetime) >= DATE '2011-02-01' AND
--DATE (NEW.datetime) < DATE '2011-03-01' ) THEN
--INSERT INTO tab_tour201102 VALUES (NEW.*);
...
""
The above code throws the following error while trying to insert data:
""
NOTICE: target table: table_201102
ERROR: missing FROM-clause entry for table "new"
LINE 1: insert into table_201102 values (NEW.*);
^
QUERY: insert into table_201102 values (NEW.*);
CONTEXT: PL/pgSQL function "insert_trigger" line 10 at EXECUTE statement
""
O.K., most probably this is caused by the fact that the statement "string"
includes the characters NEW, but not the values...or what?!?!
The commented lines are working as expected and I think this is the common way
of handling partitions.
Now my question:
is it possible at all to create the insert statement on the fly, to avoid
modifying the trigger function each time a new partition has been added ?
any help appreciated::GERD::
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_standby
thanks Greg, yes, we want SELinux in enforcing mode. Thereby (and to ensure persistence) just chcon is the wrong way and * semanage fcontext -a -t postgresql_t '' * restorecon -vvFR is much better ;-) regards--GERD-- On Wednesday, August 04, 2010 07:56:56 am Greg Smith wrote: > Gerd Koenig wrote: > > thanks for the hint, yes, SELinux caused the troubles. It complained > > about wrong filecontext while starting postgres via init-script. > > > > Filecontext was: var_lib_t and it should be: postgresql_t > > If you want to keep SELinux on, basically you have to relabel the > directory you are putting those into so it can access them. You might > find some useful hints on that topic at > http://serverfault.com/questions/32333/how-does-selinux-affect-the-home-dir > ectory (including the observation that /home is a bad place for them). You > might think you can just run the chcon command to reset the labels, but it > doesn't quite work like that; you have to change the policy and then use > restorecon to correct them. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_standby
Hello Greg, thanks for the hint, yes, SELinux caused the troubles. It complained about wrong filecontext while starting postgres via init-script. Filecontext was: var_lib_t and it should be: postgresql_t regards...GERD On Tuesday, August 03, 2010 11:54:45 pm Greg Smith wrote: > Gerd Koenig wrote: > > Since even the init-script starts pg as user postgres I have no idea what > > differs from init-script to direct call of pg_ctl as user postgres...?!?! > > Do you have SELinux turned on? That can do weird stuff like this--the > init script will be running with restrictions the manual pg_ctl launch > will not have. Should be information in /var/log/ somewhere if that's > the case. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with pg_standby
Hi again, I just want to drop you an additional note. After several attempts of debugging pg_standby is restoring the WAL files as expected, but I cannot explain why... First startup of postgres was with init-script provided by the rpm installation (/etc/init.d/postgresql start as user root). The pg processes have been started thereby as user postgres. An additional stop/start by init-script ended up in the same problem situation, where pg_standby obviously wasn't able to get the file 0001001E001D.0020.backup, even if it was there (and readable by user postgres ;-) ). Afterwards (3rd attempt) I started postgres not by init-script, but by pg_ctl as user postgres, and..hejheureka..pg_standby finds all needed files and restores them. Since even the init-script starts pg as user postgres I have no idea what differs from init-script to direct call of pg_ctl as user postgres...?!?! Somebody an explanation ? kind regardsGERD On Tuesday, August 03, 2010 10:37:12 pm Gerd Koenig wrote: > Hello, > > we currently setup a standby database with archive_command sending the WALs > from master to standby. > This works as expected, but the standby database doesn't restore the WALs > from the given directory in recovery.conf and I have no idea why... > > recovery.conf: > > restore_command = '/usr/bin/pg_standby -d -s 20 -t /tmp/pgsql.trigger.5432 > /var/lib/pgsql/wal_exchange %f %p %r 2>> > /var/lib/pgsql/data/pg_log/standby.log' > > The file standby.log contains: > > Trigger file: /tmp/pgsql.trigger.5432 > Waiting for WAL file: 0001.history > WAL file path : /var/lib/pgsql/wal_exchange/0001.history > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 20 seconds > Max wait interval : 0 forever > Command for restore : cp "/var/lib/pgsql/wal_exchange/0001.history" > "pg_xlog/RECOVERYHISTORY" > Keep archive history: and later > running restore :cp: cannot stat > `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such > file or directory > not restored: history file not found > > Trigger file: /tmp/pgsql.trigger.5432 > Waiting for WAL file: 0001001E001D.0020.backup > WAL file path : > /var/lib/pgsql/wal_exchange/0001001E001D.0020.backup > Restoring to... : pg_xlog/RECOVERYHISTORY > Sleep interval : 20 seconds > Max wait interval : 0 forever > Command for restore : cp > "/var/lib/pgsql/wal_exchange/0001001E001D.0020.backup" > "pg_xlog/RECOVERYHISTORY" > Keep archive history: and later > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > WAL file not present yet. Checking for trigger file... > --- > > And in the directory "/var/lib/pgsql/wal_exchange" I have dozens of WALs in > the meantime, but they didn't get restored, and in the logs I receive "WAL > file not present" > The file "pg_xlog/RECOVERYHISTORY" doesn't exist, btw..., and the file > xx0020.backup contains: > "" > START WAL LOCATION: 1E/1D20 (file 0001001E001D) > STOP WAL LOCATION: 1E/1F00 (file 0001001E001E) > CHECKPOINT LOCATION: 1E/1D20 > START TIME: 2010-08-03 21:11:23 CEST > LABEL: initial_backup > STOP TIME: 2010-08-03 21:44:06 CEST > "" > > I can stop and restart the standby db as often as I want ending up in a > working sync-mechanism, as long as the WALs being copied from master to > standby, right ? > > Any help appreciated, many thanks .GERD. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with pg_standby
Hello, we currently setup a standby database with archive_command sending the WALs from master to standby. This works as expected, but the standby database doesn't restore the WALs from the given directory in recovery.conf and I have no idea why... recovery.conf: restore_command = '/usr/bin/pg_standby -d -s 20 -t /tmp/pgsql.trigger.5432 /var/lib/pgsql/wal_exchange %f %p %r 2>> /var/lib/pgsql/data/pg_log/standby.log' The file standby.log contains: Trigger file: /tmp/pgsql.trigger.5432 Waiting for WAL file: 0001.history WAL file path : /var/lib/pgsql/wal_exchange/0001.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 20 seconds Max wait interval : 0 forever Command for restore : cp "/var/lib/pgsql/wal_exchange/0001.history" "pg_xlog/RECOVERYHISTORY" Keep archive history: and later running restore :cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory cp: cannot stat `/var/lib/pgsql/wal_exchange/0001.history': No such file or directory not restored: history file not found Trigger file: /tmp/pgsql.trigger.5432 Waiting for WAL file: 0001001E001D.0020.backup WAL file path : /var/lib/pgsql/wal_exchange/0001001E001D.0020.backup Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 20 seconds Max wait interval : 0 forever Command for restore : cp "/var/lib/pgsql/wal_exchange/0001001E001D.0020.backup" "pg_xlog/RECOVERYHISTORY" Keep archive history: and later WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... WAL file not present yet. Checking for trigger file... --- And in the directory "/var/lib/pgsql/wal_exchange" I have dozens of WALs in the meantime, but they didn't get restored, and in the logs I receive "WAL file not present" The file "pg_xlog/RECOVERYHISTORY" doesn't exist, btw..., and the file xx0020.backup contains: "" START WAL LOCATION: 1E/1D20 (file 0001001E001D) STOP WAL LOCATION: 1E/1F00 (file 0001001E001E) CHECKPOINT LOCATION: 1E/1D20 START TIME: 2010-08-03 21:11:23 CEST LABEL: initial_backup STOP TIME: 2010-08-03 21:44:06 CEST "" I can stop and restart the standby db as often as I want ending up in a working sync-mechanism, as long as the WALs being copied from master to standby, right ? Any help appreciated, many thanks .GERD. -- 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] Trying to install ODBC driver on Windows XP notebook
Hi Phil, On Tuesday, June 22, 2010 10:19:33 pm Phil Jackson wrote: > I have latest Postgresql installed on a notebook and now trying to > install the driver which I have downloaded and run so that it is now > available in the ODBC Data Source Administrator > > This machine has CA security suite, latest version installed and I have > added a network rule that allows all applications using TCP and UDP on > Port 4532 to gain access In and Out Do you really have opened port 4532, or is it a typo ? (should be 5432, of course ;-) ). > > I have the correct database name and get the following message when I > try to Test the connection. > > Could not connect to the server > No Connection could be made because the target machine actively refused it. > (192.168.1.7:5432) Does your postmaster listen on this address ? Check your postgresql.conf for parameter "listen_addresses" What's the error message in detail ? regaradsGERD. -- 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] pgpool
Hi Geoffrey, you do not need to connect to your database directly, just connect to pgpool itself. e.g.: your database runs on port 5434, pgpool runs on port 5432 => * pgpool has to be configured in that way that it connects to the database on port 5434 * you/your app's should connect to the server where pgpool is running on port 5432 (the connection to the database is established by pgpool) hth...::GERD::... On Monday, June 21, 2010 02:10:22 pm Geoffrey wrote: > So, I've got it installed, and tweaked the configuration, but I simply > can not figure out how to connect to my databases via pgpool. Is this > simply transparent? I don't see how. > > So I have a postmaster running on port 5434, how do I connect to that > database via pgpool? I simply can not find this piece of info in the docs? -- 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] no such file euc2004... while initdb 9.0Beta
Hi Shoaib, thanks for your hint it solved the problem just by "yum update postgresql-*" since I had the correct repository already ;-) regards...GERD... On Wednesday 05 May 2010 08:28:08 am Shoaib Mir wrote: > On Wed, May 5, 2010 at 4:05 PM, Gerd Koenig wrote: > > Hello, > > > > I've tried to install the latest 9.0Beta (Fedora12, pgdg repository), but > > with > > no success. "initdb" returns with an error described below. > > > > > > blub#> yum list postgres* > > Loaded plugins: refresh-packagekit > > Installed Packages > > postgresql.i3869.0-alpha4_1PGDG.fc12 @pgdg90 > > postgresql-docs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 > > postgresql-libs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 > > postgresql-server.i3869.0-alpha4_1PGDG.fc12 @pgdg90 > > Looks like you still using the alpha4 packages, try following this: > > http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-ins > tall-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html > > <http://people.planetpostgresql.org/devrim/index.php?/archives/43-How-to-in > stall-PostgreSQL-9.0-Beta-1-to-FedoraCentOSRHEL.html>and see if that > fixes the problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] no such file euc2004... while initdb 9.0Beta
Hello, I've tried to install the latest 9.0Beta (Fedora12, pgdg repository), but with no success. "initdb" returns with an error described below. blub#> yum list postgres* Loaded plugins: refresh-packagekit Installed Packages postgresql.i3869.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-docs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-libs.i386 9.0-alpha4_1PGDG.fc12 @pgdg90 postgresql-server.i3869.0-alpha4_1PGDG.fc12 @pgdg90 blub#> /etc/init.d/postgresql-9.0 initdb Initializing database: [FAILED] blub#> cat /var/lib/pgsql/9.0/pgstartup.log The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to "english". fixing permissions on existing directory /var/lib/pgsql/9.0/data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 28MB creating configuration files ... ok creating template1 database in /var/lib/pgsql/9.0/data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... FATAL: could not access file "$libdir/euc2004_sjis2004": No such file or directory STATEMENT: CREATE OR REPLACE FUNCTION euc_jis_2004_to_shift_jis_2004 (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/euc2004_sjis2004', 'euc_jis_2004_to_shift_jis_2004' LANGUAGE C STRICT; child process exited with exit code 1 initdb: removing contents of data directory "/var/lib/pgsql/9.0/data" == Where should the file "euc2004..." with this function "euc..." come from ..? thanks in advanceGERD. -- 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] Postgres 9.1 - Release Theme
Hi Dave, thanks for this funny post on April, 1st ;-) It's currently the highlight of my working day here regards..GERD.. On Thursday 01 April 2010 10:13:16 am Dave Page wrote: > Following a great deal of discussion, I'm pleased to announce that the > PostgreSQL Core team has decided that the major theme for the 9.1 > release, due in 2011, will be 'NoSQL'. > > There is a growing trend towards NoSQL databases, with major sites > like Twitter and Facebook utilising them extensively. NoSQL databases > often include multi-master replication, clustering and failover > features that have long been requested in PostgresSQL, but have been > extremely difficult to implement with SQL which has prevented us from > advancing Postgree in the way that we'd like. > > To address this, the intention is to remove SQL support from > Postgres, and replace it with a language called 'QUEL'. This will > provide us with the flexibility we need to implement the features of > modern NoSQL databases. With no SQL support there will obviously be > some differences in the query syntax that must be used to access your > data. For example, the query: > > select (e.salary/ (e.age - 18)) as comp from employee as e where > e.name = "Jones" > > would be rewritten as: > > range of e is employee retrieve (comp = e.salary/ (e.age - 18)) where > e.name = "Jones" > > Aggregate syntax in QUEL is particularly powerful. For example, the query: > > select dept, > avg(salary) as avg_salary, > sum(salary) as tot_salary > from > employees > group by > dept > > may be written as: > > range of e is employee > retrieve (e.dept, > avg_salary = avg(e.salary by e.dept), > tot_salary = sum(e.salary by e.dept) > ) > > Note that the grouped column can be specified for each individual > aggregate. > > We will be producing a comprehensive guide to the QUEL syntax to aid > with application migration. We appreciate the difficulty that this > change may cause some users, but feel we must embrace the NoSQL > philosophy in order to remain "The world's most advanced Open Source > database" > > "There's no question that, at 21 years old, the SQL standard is past its > prime," said core developer and standards expert Peter Eisentraut. "It's > time for us to switch to something fresher. I personally would have > preferred XSLT, but QUEL is almost as good." > > Project committer Heikki Linnakangas added: "By replacing SQL with > QUEL not only will will be able to add new features to Postgres that > were previously too difficult, but we'll also increase user loyalty as > it'll be much harder for them to change to a different, SQL-based > database. That'll be pretty cool." > > You may also notice that without SQL, the project name is somewhat > misleading. To address that, the project name will be changed to > 'PostgreQUEL' with the 9.1 release. We expect this will also put an > end to the periodic debates on changing the project name. > > Dave Page > On behalf of the PostgreSQL Core Team -- /\ | Gerd König | - Service Manager IT - | | TRANSPOREON GmbH | Magirus-Deutz-Str. 16 | DE - 89077 Ulm | | Tel: +49 [0]731 16906 106 | Fax: +49 [0]731 16906 99 | koe...@transporeon.com | www.transporeon.com | \/ TTRANSPOREON GmbH, District Court Ulm, HRB 722056, Directors: Peter Förster, Roland Hötzl, Martin Mack, Marc-Oliver Simon -- 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] function to grant select on all tables in several schemas
Hello Andreas,
thanks for your support, perfect :-)
regardsGERD
On Wednesday 20 January 2010 4:59:57 pm Andreas Kretschmer wrote:
> Gerd Koenig wrote:
> > Hello,
> >
> > I'm looking for a solution to grant select to a group to have "read-only"
> > group across all tables/views/.. in several schemas. I already found some
> > workarounds and I decided to create a function to cover this topic.
> > But calling this function throws the error:
> > ""
> > ERROR: column "´r´" does not exist
> > LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´,
> > ´v´,´... ^
> > QUERY: SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> > pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t,
> > pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid
> > AND s.nspname = ´tisys´ order by s.nspname
> > CONTEXT: PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over
> > SELECT rows
> > ""
> >
> > The function was created by:
> > ""
> > CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> > RETURNS TEXT AS '
> > DECLARE
> > sql text;
> > rel record;
> > BEGIN
> > FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> > pg_catalog.quote_ident(t.relname) AS relation_name
> > FROM pg_class t, pg_namespace s
> > WHERE t.relkind IN (´r´, ´v´,´S´)
> > AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname
> > LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' ||
> > rel.relation_name || '' TO ro_group'';
> > RAISE NOTICE ''%'', sql;
> > EXECUTE sql;
> > END LOOP;
> > RETURN ''OK'';
> > END;
> > ' LANGUAGE 'plpgsql';
> > COMMENT ON FUNCTION grant_select_to_ro_group()
> > IS 'Give select privilege ON all relations in the given schema TO
> > ro_group.'; ""
> >
> > ...and has been called by:
> > ""
> > select grant_select_to_ro_group();
> > ""
> >
> > any hints appreciated..GERD
>
> You should better use $$ instead of ' for the function-body.
> (unless you have a very old pg-version ...)
>
> I think the ´ as quoting-sign is also wrong...
>
>
> Rewrite your function to:
>
> CREATE OR REPLACE FUNCTION grant_select_to_ro_group()
> RETURNS TEXT AS $$
> DECLARE
> sql text;
> rel record;
> BEGIN
> FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name,
> pg_catalog.quote_ident(t.relname) AS relation_name
> FROM pg_class t, pg_namespace s
> WHERE t.relkind IN ('r', 'v','S')
> AND t.relnamespace=s.oid AND s.nspname = 'tisys' order by s.nspname
> LOOP sql := 'GRANT SELECT ON ' || rel.schema_name || '.' ||
> rel.relation_name || ' TO ro_group';
> RAISE NOTICE '%', sql;
> EXECUTE sql;
> END LOOP;
> RETURN 'OK';
> END;
> $$ LANGUAGE 'plpgsql';
>
>
> now it works: (i have copy&paste your function into a file and edit it
> there)
>
> kretsch...@tux:~$ psql test
> Zeitmessung ist an.
> psql (8.4.2)
> Geben Sie »help« für Hilfe ein.
>
> test=# \i grant.sql
> CREATE FUNCTION
> Zeit: 239,453 ms
> test=*# select grant_select_to_ro_group();
> grant_select_to_ro_group
> --
> OK
> (1 Zeile)
>
> Zeit: 48,836 ms
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
/\
| Gerd König
| - Infrastruktur -
|
| TRANSPOREON GmbH
| Magirus-Deutz-Str. 16 | Stadtregal
| DE - 89077 Ulm
|
| Tel: +49 [0]731 16906 106
| Fax: +49 [0]731 16906 99
| koe...@transporeon.com
| www.transporeon.com
|
\/
TRANSPOREON GmbH, Amtsgericht Ulm, HRB 722056
Geschäftsf.: Peter Förster, Roland Hötzl, Marc-Oliver Simon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] function to grant select on all tables in several schemas
Hello, I'm looking for a solution to grant select to a group to have "read-only" group across all tables/views/.. in several schemas. I already found some workarounds and I decided to create a function to cover this topic. But calling this function throws the error: "" ERROR: column "´r´" does not exist LINE 1: ...OM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´... ^ QUERY: SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname CONTEXT: PL/pgSQL function "grant_select_to_ro_group" line 5 at FOR over SELECT rows "" The function was created by: "" CREATE OR REPLACE FUNCTION grant_select_to_ro_group() RETURNS TEXT AS ' DECLARE sql text; rel record; BEGIN FOR rel IN SELECT pg_catalog.quote_ident(s.nspname) AS schema_name, pg_catalog.quote_ident(t.relname) AS relation_name FROM pg_class t, pg_namespace s WHERE t.relkind IN (´r´, ´v´,´S´) AND t.relnamespace=s.oid AND s.nspname = ´tisys´ order by s.nspname LOOP sql := ''GRANT SELECT ON '' || rel.schema_name || ''.'' || rel.relation_name || '' TO ro_group''; RAISE NOTICE ''%'', sql; EXECUTE sql; END LOOP; RETURN ''OK''; END; ' LANGUAGE 'plpgsql'; COMMENT ON FUNCTION grant_select_to_ro_group() IS 'Give select privilege ON all relations in the given schema TO ro_group.'; "" ...and has been called by: "" select grant_select_to_ro_group(); "" any hints appreciated..GERD -- 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] high load on server
Hello Scott, thanks for answering. Scott Marlowe schrieb: 2009/4/3 Gerd König : Hello, since 2 days ago we're facing an increased load on our database server (opensuse10.3-64bit, PostgreSQL 8.3.5, 8GB Ram). This high load stays the whole working day. How man cores? The server contains two "model name: Intel(R) Xeon(R) CPU X5355 @ 2.66GHz" CPU's, thereby 8 cores... == current situation: == #>top top - 14:09:46 up 40 days, 8:08, 2 users, load average: 7.60, 7.46, 7.13 ... Mem: 8194596k total, 5716680k used, 2477916k free, 185516k buffers Swap: 4200988k total, 204k used, 4200784k free, 5041448k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 17478 postgres 15 0 610m 455m 444m R 52 5.7 0:08.78 postmaster 17449 postgres 15 0 606m 497m 489m S 37 6.2 0:16.35 postmaster 22541 postgres 16 0 607m 522m 516m R 31 6.5 123:25.17 postmaster 17491 postgres 15 0 618m 447m 435m S 22 5.6 0:03.97 postmaster 17454 postgres 15 0 616m 474m 457m S 18 5.9 0:15.88 postmaster 22547 postgres 15 0 608m 534m 527m S 18 6.7 100:12.01 postmaster 17448 postgres 16 0 616m 517m 501m S 17 6.5 0:15.60 postmaster 17451 postgres 15 0 611m 491m 479m S 11 6.1 0:25.04 postmaster 17490 postgres 15 0 606m 351m 344m S 10 4.4 0:02.69 postmaster 22540 postgres 15 0 607m 520m 513m S2 6.5 33:46.47 postmaster 17489 postgres 15 0 604m 316m 311m S2 4.0 0:03.34 postmaster Next time hit c first to see what the postmasters are up to. good hint, I'll perform this the next time the server runs under higher load (probably on monday...) I assume the problem is caused by heavy writing slows down the server?!?...why?=> The problem might be that you're assuming there's a problem. Looking at the rest of your diags, you're data set fits in memory, I/O wait is < 10% and there are no processes waiting for a CPU to free up, they're all running. Looks healthy to me. Perfect, probably our customers didn't work that much in the past, but now they do ;-) kind regards...:GERD:... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL CE?
Hi Steve, I know that EnterpriseDB offers 3 levels of certification. Perhaps one of them suits your needs..?!?! regards...:GERD:... steve.gnuli...@gmail.com schrieb: Just to know, if actually there's -or where- now, a PostgreSQL Certified Engineer program. I think that obtaining -if able- a Postgresql CE and some kind of linux certification like RHCE o LPI, could be a lot of interest. Finally, to know if there's any book (english) for the PostgreSQL Certification, like in Japanese. Thank you for your time, Steve, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
