[ADMIN] How to enforce the use of the sequence for serial columns ?

I'd like to ensure that nobody provide the ID in an insert statement when the id is linked to a sequence. I tried it with a trigger, but the id value is fed before the "BEFORE INSERT" test is performed (see below)... Any Idea ? Cheers, Marc CREATE FUNCTION serialtest() RETURNS trigger AS $s

Re: [ADMIN] How to enforce the use of the sequence for serial columns ?

>Trigger based solution where same trig can be used for any number of tables by changing the parameter. >Will throw one of 2 exceptions on failure to use sequence for the insert. Many thanks, This seems to be the simplest solution, Marc ---(end of broadcast)-

Re: [ADMIN] How to enforce the use of the sequence for serial columns

> It's not a full solution anyway since it prevents any kind of update on the table due to check constraints firing even if target field not updated. > Well, we can extend the check within the trigger: if (coalesce old.a=new.a -- Update OR new.a = currval(tg_argv[0] -- Insert ) then

[ADMIN] configuring the postmaster.log

Hello, I'm confused about the logging destination parameters in postgresql.conf My Problem is that the postmaster.log is getting too large and I want to roll it (once per day) Following parameters apply to optional postgresql...log. log_rotation_age = 1440 log_filename = 'postgresql-%Y-%

Re: [ADMIN] configuring the postmaster.log

Sorry , my mistake: The "postmaster.log" that I mentioned is defined in our Postgres start script: startproc -u <@DB_USER@> $PGSQL_BIN -D <@INSTALL_DIR@>/postgresql-data \ >> <@INSTALL_DIR@>/postgresql/postmaster.log 2>&1 Marc ---(end of broadcast)-

[ADMIN] Check for corruptions

with buggy kernels; consider updating your system. Now I'm interested in a way to get the list of all corrupted objects. The only way I know is to check the errors of pg_dump or vacuum. Is there a faster way, something like "vacuum check_only" ? Thanks, Marc Mamin -- Sent via pgsq

[ADMIN] autovacuum question

I care with it in a special way in my situation ? many thanks, Marc Mamin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] plain text difference with pg_dump

' else 'n' end ||'|'|| case when proisstrict =true then 'y' else 'n' end ||'|'|| case when proretset =true then 'y' else 'n' end ||'|'|| provolatile ||'|'|| pronargs ||'|'||

[ADMIN] GRANT SELECT ON DATABASE

sql-sql/2006-05/msg00023.php Can someone points me to the script, or is there a better approach to create such a role ? Thanks, Marc Mamin

[ADMIN] pg_dump custom format and pigz

tom format in order to take advantage of the parallel restore enhancement, but this would force me to give pigz up, hence loosing performances with pg_dump. Is there a way to combine the custom format with pigz? feature request ? best regards, Marc Mamin

Re: [ADMIN] pg_dump custom format and pigz

each 3-500 GB... A better way would be to have a configuration option to tell which compression tool should be used internally by pg_dump. best regards, Marc Mamin -Original Message- From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin-ow...@postgresql.org] On Behalf O

Re: [ADMIN] pg_dump custom format and pigz

arze Sent: Friday, October 23, 2009 4:25 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] pg_dump custom format and pigz Hi Marc, On Fri, Oct 23, 2009 at 03:52:16PM +0200, Marc Mamin wrote: > > You might add pigz as a post-processing step and disabling compression in > > pg_dum

Re: [ADMIN] duplicate key violated errors

Hello, I experienced a similar issue with 8.2.x having a duplicate value in a primary key ! In our case it definitively happened as the plates got out of space, so I did put the fault on us, not on Postgres :-) regards, Marc Mamin -Original Message- From: pgsql-admin-ow

[ADMIN] can't drop an old tablespace

erences to this tablespace. What should I do to safely get rid of this tablespace: - Is there a way to obtains all dependent objects of a tablespace ? - How is this error triggered ? comes it from a check of the folder content, or from the catalog ? many thanks, Marc Mamin

[ADMIN] pg_dump and table exclusion: multiple patterns

I try the same pattern with psql dt\, I get the expected list of tables to exclude... pg_dump --version pg_dump (PostgreSQL) 8.3.9 I'd appreciate some help. best regards, Marc Mamin

Re: [ADMIN] pg_dump and table exclusion: multiple patterns

et the expected result: pg_dump -i -v -nXXX -T 'XXX.*2008*' -T ' XXX.*2009*' -T ' XXX.*201001*' -T XXX.'*201002*' . seems that the use of the -n flag requires to use these schema names within the patterns ... best regards, Marc Mamin >

[ADMIN] FILLFACTOR Tuning

Hello, I'd like to check how reasonable our Fillfactors settings are (for indexes), but I'm not sure which statistics can be used for this. best regards, Marc Mamin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscrip

Re: [ADMIN] Moving database and schema to a new tablespace

otherwise here is a howto foe moving tables and indexes one by one: http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/ HTH, Marc Mamin

[ADMIN] how can I get "initial_value"(setting_name) ?

available, I guess that following may prove useful: - reset_setting (setting_name [, is_local]) - (read)initial_value(setting_name) best regards, Marc Mamin CREATE OR REPLACE FUNCTION public.cic_set_memory_plan (p_plan varchar) returns int AS $$ BEGIN --LSORT, MSORT, LMAINT, RESET if p_plan

Re: [ADMIN] how can I get "initial_value"(setting_name) ?

Oops, not my day :-) many thanks, Marc Mamin -Original Message- From: Guillaume Lelarge [mailto:guilla...@lelarge.info] Sent: Montag, 12. September 2011 12:16 To: Marc Mamin Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] how can I get "initial_value"(setting_name) ? On

[ADMIN] ALTER ROLE foo SET log_connections = true;

Hello, if I try to SET log_connections = true (as super user, for another role) I get the error: parameter "log_connections" cannot be set after connection start. I find it a bit surprising as this wouldn't affect the current session. best regards, Marc Mamin

Re: [ADMIN] Experience with large number of tables in single PostgreSQL instance

. DB size :3500 GB pg_catalog size: 2GB select count(*) from pg_tables; 120'884 select count(*) from pg_indexes; 219'082 select count(*) from pg_attribute; 2'779'199 Server: 48 GB RAM & 12 cores regards, Marc Mamin -- Sent via pgsql-admin mailing list (pgsql-admi