[GENERAL] plql and or clausule
hello this is my first question. I am new in postgres and using plsql. i am making this (bellow) i want to insert one copy of one record into the log table but if there is some change into the original recor to update into this record two fields but i have one rror can you help me please? CREATE OR REPLACE FUNCTION lst_tot_mytable_LOG() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO lst_tot_mytable_log SELECT 'U', now(), OLD.*; IF OLD.Peticionario != NEW.Peticionario or OLD.interlocclte != NEW.interlocclte or OLD.Equipo != NEW.Equipo or OLD.RespTecnico != NEW.RespTecnico or OLD.RespOrganiz != NEW.RespOrganiz THEN UPDATE lst_tot_mytable set fultimamodificacion = now(), esmodificadoerspectoaanterior = true WHERE nropeti = OLD.nropeti; ELSIF OLD.Peticionario == NEW.Peticionario or OLD.interlocclte == NEW.interlocclte or OLD.Equipo == NEW.Equipo or OLD.RespTecnico == NEW.RespTecnico or OLD.RespOrganiz == NEW.RespOrganiz THEN UPDATE lst_tot_mytable set NEW.fultimamodificacion = now(), NEW.esmodificadoerspectoaanterior = fase WHERE nropeti = OLD.nropeti; END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $$; the eror si this: ERROR: el operador no existe: character varying == character varying LINE 1: SELECT OLD.Peticionario == NEW.Peticionario or OLD.interlocc... ^ HINT: Ningún operador coincide con el nombre y el tipo de los argumentos. Puede ser necesario agregar conversiones explícitas de tipos. QUERY: SELECT OLD.Peticionario == NEW.Peticionario or OLD.interlocclte == NEW.interlocclte or OLD.Equipo == NEW.Equipo or OLD.RespTecnico == NEW.RespTecnico or OLD.RespOrganiz == NEW.RespOrganiz or OLD.FAlta == NEW.FAlta or OLD.FRequerida == NEW.FRequerida or OLD.Titulo == NEW.Titulo or OLD.Cliente == NEW.Cliente or OLD.Organico == NEW.Organico or OLD.Pri == NEW.Pri or OLD.Estado == NEW.Estado or OLD.FEstado == NEW.FEstado or OLD.CCCA == NEW.CCCA or OLD.Aplicacion_Actvdad == NEW.Aplicacion_Actvdad or OLD.Servicio == NEW.Servicio or OLD.FPrevistaInicioPeticion == NEW.FPrevistaInicioPeticion or OLD.FPrevistaFinPeticion == NEW.FPrevistaFinPeticion or OLD.autpet_Horas == NEW.autpet_Horas or OLD.autpet_Importe == NEW.autpet_Importe or OLD.auteje_Horas == NEW.auteje_Horas or OLD.auteje_Importe == NEW.auteje_Importe or OLD.aprpet_Horas == NEW.aprpet_Horas or OLD.aprpet_Importe == NEW.aprpet_Importe or OLD.apreje_Horas == NEW.apreje_Horas or OLD.apreje_Importe == NEW.apreje_Importe or OLD.ultprvpet_Horas == NEW.ultprvpet_Horas or OLD.ultprvpet_Importe == NEW.ultprvpet_Importe or OLD.ultprveje_Horas == NEW.ultprveje_Horas or OLD.ultprveje_Importe == NEW.ultprveje_Importe or OLD.realpet_Horas == NEW.realpet_Horas or OLD.realpet_Importe == NEW.realpet_Importe or OLD.realeje_Horas == NEW.realeje_Horas or OLD.realeje_Importe == NEW.realeje_Importe or OLD.CodFacturacion == NEW.CodFacturacion or OLD.Facturable == NEW.Facturable or OLD.ProyCliente == NEW.ProyCliente CONTEXT: función PL/pgSQL lst_tot_mytable_log() en la línea 12 en IF sentencia SQL: «UPDATE lst_tot_mytable set fultimamodificacion = now(), esmodificadoerspectoaanterior = true WHERE nropeti = OLD.nropeti» función PL/pgSQL lst_tot_mytable_log() en la línea 13 en sentencia SQL ** Error ** ERROR: el operador no existe: character varying == character varying SQL state: 42883 Hint: Ningún operador coincide con el nombre y el tipo de los argumentos. Puede ser necesario agregar conversiones explícitas de tipos. Context: función PL/pgSQL lst_tot_mytable_log() en la línea 12 en IF sentencia SQL: «UPDATE lst_tot_mytable set fultimamodificacion = now(), esmodificadoerspectoaanterior = true WHERE nropeti = OLD.nropeti» función PL/pgSQL lst_tot_mytable_log() en la línea 13 en sentencia SQL
[GENERAL] Converting 7.x to 8.x
What would be the faster way to convert a 7.4.x database into an 8.x database? A dump of the database takes over 20 hours so we want to convert the database without having to do a dump and resptore.
[GENERAL] Changing access permissions without re-starting the database
Hi Forum, Is there a way to change the database access permissions for different IP addresses without having to re-start PostgreSQL? I often want to preclude all IP address but one from accessing the database. Currently, I change the pg_hba.conf file and re-start the database but I would like to be able to do the same thing without having to re-start. Thanks in advance for your response
[GENERAL] Size of data stored in bytea record?
Is there a way that I can find out the size of a bytea record? I am storing images into bytea fields and I just want to know the size of the image that it has been saved in the field
Re: [GENERAL] Reindexdb
Thank you Tom. I will look at reindexing the database ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_restore -L option
Could someone clarify the use of the –L option in pg_restore? I have a pg_dump of a database (options –Ft –b) and I want to restore most of the dump with the exception of one table into another database. Can I do the following?: 1) restore the tar file of the dump into a “list” file with pg_restore –l 2) comment out the line for the table that I want to exclude, and 3) restore the tar file with the following command: pg_restore –a –L -d Thanks in advance for your response?
[GENERAL] Settings for autovacuum for batch uploading of data?
Hello Forum, I would appreciate it if you could recommend settings to use auto-vacuum in my version 7.4 database. I am uploading several thousands records in the database at a rate of ~1 second per record (the data is uploading from a different server). I have noticed that the performance of the uploading is improved if I vacuum the database every so often so I decided to take advantage of auto-vacuuming to maintain the best performance. Since I am uploading several thousands records, I set autovacuum to 50 minutes with –s 3000 in the hope that a great deal of data would be committed before a regular vacuum would take place and that vacuuming would interfere the least possible with the uploading. Thanks in advance for your recommendations.
[GENERAL] Eliminating start error message: "unary operator expected"
Title: Eliminating start error message: "unary operator expected" Hi Forum, What should I correct in order to eliminate the following error message on start: Executing /etc/rc.d/init.d/postgresql start .. Starting postgresql service: -sh: [: ==: unary operator expected [ OK ] We installed a v7.3.2 and PostgreSQL seems to be running very well but we want to get rid of this message. Thanks in advance for your response
Re: [GENERAL] Eliminating start error message: "unary operator
Title: RE: [GENERAL] Eliminating start error message: "unary operator
Hi Tom,
I got the following log when I tried to output the messages from pg_ctl (enclosed is the text file also). I modified the postscript script to get the log writeeen at bootup. The line of postscript script that I modified was:
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start > /dev/null 2>&1" < /dev/null
To:
su -l postgres -s /bin/sh -c "/bin/sh -x /usr/bin/pg_ctl -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start > /tmp/mylog 2>&1" < /dev/null
Indeed, I do not see an error anywhere.
++ basename /usr/bin/pg_ctl
+ CMDNAME=pg_ctl
+ help=pg_ctl is a utility to start, stop, restart, reload configuration files,
or report the status of a PostgreSQL server.
Usage:
pg_ctl start [-w] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop [-W] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-D DATADIR] [-s] [-m SHUTDOWN-MODE] [-o "OPTIONS"]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
Common options:
-D DATADIR location of the database storage area
-s only print errors, no informational messages
-w wait until operation completes
-W do not wait until operation completes
--help show this help, then exit
--version output version information, then exit
(The default is to wait for shutdown, but not for start or restart.)
If the -D option is omitted, the environment variable PGDATA is used.
Options for start or restart:
-l FILENAME write (or append) server log to FILENAME. The
use of this option is highly recommended.
-o OPTIONS command line options to pass to the postmaster
(PostgreSQL server executable)
-p PATH-TO-POSTMASTER normally not necessary
Options for stop or restart:
-m SHUTDOWN-MODE may be 'smart', 'fast', or 'immediate'
Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart
Report bugs to <[EMAIL PROTECTED]>.
+ advice=Try 'pg_ctl --help' for more information.
+ bindir=/usr/bin
+ VERSION=7.3.2
+ umask 077
+ echo '\c'
+ grep -s c
+ ECHO_N=echo -n
+ ECHO_C=
+ echo /usr/bin/pg_ctl
+ grep /
++ echo /usr/bin/pg_ctl
++ sed 's,/[^/]*$,,'
+ self_path=/usr/bin
+ '[' -x /usr/bin/postmaster ']'
+ '[' -x /usr/bin/psql ']'
+ PGPATH=/usr/bin
+ po_path=/usr/bin/postmaster
+ wait=
+ wait_seconds=60
+ logfile=
+ silence_echo=
+ shutdown_mode=smart
+ '[' 7 -gt 0 ']'
+ shift
+ PGDATA=/var/lib/pgsql/data
+ export PGDATA
+ shift
+ '[' 5 -gt 0 ']'
+ shift
+ po_path=/usr/bin/postmaster
+ shift
+ '[' 3 -gt 0 ']'
+ shift
+ POSTOPTS=-p 5432
+ shift
+ '[' 1 -gt 0 ']'
+ op=start
+ shift
+ '[' 0 -gt 0 ']'
+ '[' xstart = x ']'
+ '[' -z /var/lib/pgsql/data ']'
+ '[' -z '' ']'
+ wait=no
+ sig=-TERM
+ '[' start = reload ']'
+ DEFPOSTOPTS=/var/lib/pgsql/data/postmaster.opts.default
+ POSTOPTSFILE=/var/lib/pgsql/data/postmaster.opts
+ PIDFILE=/var/lib/pgsql/data/postmaster.pid
+ '[' start = status ']'
+ '[' start = stop -o start = restart -o start = reload ']'
+ '[' start = start -o start = restart ']'
+ oldpid=
+ '[' -f /var/lib/pgsql/data/postmaster.pid ']'
+ '[' -z '-p 5432' ']'
+ eval set X '-p 5432'
++ set X -p 5432
+ shift
+ '[' -n '' ']'
+ '[' -n '' ']'
+ '[' no = yes ']'
+ echo 'postmaster successfully started'
postmaster successfully started
+ exit 0
+ /usr/bin/postmaster -p 5432
LOG: database system was shut down at 2003-06-25 13:04:50 EDT
LOG: checkpoint record is at 0/12155DD8
LOG: redo record is at 0/12155DD8; undo record is at 0/0; shutdown TRUE
LOG: next transaction id: 3002; next oid: 632310
LOG: database system is ready
-Original Message-
From: Carlos
Sent: Tuesday, June 24, 2003 1:56 PM
To: 'Tom Lane'; Carlos
Cc: '[EMAIL PROTECTED]'; Dain
Subject: RE: [GENERAL] Eliminating start error message: "unary operator
Hi Tom,
Thank you very much for your help. In order to get the pg_ctl trace at start up, I would appreciate it if you could advise me on how to modify the postscrpt script. The line in question in the script is: su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl -D $PGDATA -p /usr/bin/pos
[GENERAL] Install files for version 7.4
Title: Install files for version 7.4 Hello Forum, Could someone please send me the link and the names of the files to install the version 7.4 in Windows 2000 server and XP Pro? I cannot discern the files that one is supposed to download. Thanks in advance for your response. Carlos Oliva Senior Programmer/Analyst Positive Business Solutions, Inc. Cincinnati, OH 45240-1640 (513) 772 - 2255 ext 129 [EMAIL PROTECTED] Yahoo ID: ramboid_1997
[GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Dear Gurus : First let me say hello from Buenos Aires, Argentina. I took this emails addresses from internet ( page www.postgresql.org ) Now I will try to explain which is my problem ( excuse my poor level of English, please ). I have a Java application that must read a data provided by two ( 2 ) cursors returned by a function stored in a database. I know to retrieve data if the function have one ( 1 ) cursor, but with two I can't. I will very pleased if any of you, in your free time of course, can explain me how, inside the java program, after connecting via jdbc to the database, I extract the data returned by the second cursor. Many thanks ( muchas gracias ) and I wait for yours replies as soon as you can.
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
update DW_BSC.PERSPECTIVE p
set p.NAME = v_name,
p.DESCRIPTION = v_descr,
p.SHORT_DESC = v_short_desc,
p.USR_ID_UPD = v_user_id,
p.USR_DATE_UPD = v_fecha
where P.ID = v_id;
open perspectives_cursor FOR select 'ok.
update' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;
when 'I' then
if (v_id = 0) then
RAISE NOTICE 'v_name : %', v_name;
RAISE NOTICE 'v_short_desc : %',
v_short_desc;
RAISE NOTICE 'v_descr : %', v_descr;
RAISE NOTICE 'v_user_id : %',v_user_id;
RAISE NOTICE 'v_fecha : %', v_fecha;
insert into dw_bsc.perspective
(name, short_desc, description,
usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd)
values
(v_name, v_short_desc, v_descr,
v_user_id, v_fecha, null, null);
open perspectives_cursor FOR select 'ok.
insert' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
else
RAISE NOTICE 'El id pasado al procedure
no es 0, imposible procesar INSERT !!!';
open perspectives_cursor FOR select
'NULL' as resultado1;
return next perspectives_cursor;
open goals_persps_cursor FOR select
'null' as resultado2;
return next goals_persps_cursor;
open null_cursor FOR select 'null' as
resultado3;
return next null_cursor;
end if;
end case;
end;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100
ROWS 1000;
ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer,
character varying, character varying, character varying, integer, date)
OWNER TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO public;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO usr_dw_bsc_sys_adm;
GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying,
integer, character varying, character varying, character varying,
integer, date) TO ro_dw_bsc_sys_adm;
I can extract the data contained into cursor named perspectives_cursor
in the java application, but the data caontained into cursor named
goals_persps_cursor not.
This function was tested by this
begin;
select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
null);
fetch all from "";
end;
inside the query gui tool provided by pgAdmin III
The connection into the java application was changed to
con.setAutoCommit(false);
I think I do not forget nothing else
Some help will be appreciated very, very, very much !
-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA
On 12/10/2015 05:38 AM, Corradini, Carlos wrote:
> Dear Gurus :
>
> First let me say hello from Buenos Aires, Argentina.
> I took this emails addresses from internet ( page www.postgresql.org
> <http://www.postgresql.org> )
>
> Now I will try to explain which is
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Mr. Adrian, first let me say many thanks for your replies, were very
helpful for me. But, I must to say this other .
I take a copy from the function from the gui tool of pgadmin III called
query sql, the original function name all the parameters, I do not know
why this gui tool change that.
You say that I am returning only one cursor, but I can say that I am
doing exactly what are you explaining in your example, I am doing return
next for each cursor to return.
I think I am doing something wrong in the java program but I can't
realize what is this .I will go now to see the examples in the URL's
you did put below.
Many thanks for all and excuse me for disturbing you and make you waste
your free time in this.
Again, many thanks
-Mensaje original-
De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m.
Para: Corradini, Carlos; pgsql-j...@postgresql.org;
pgsql-general@postgresql.org
CC: bo...@ejurka.com
Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF
refcursor in JAVA
On 12/11/2015 04:56 AM, Corradini, Carlos wrote:
> Mr. Adrian, here i transcribe the code of the function
Notes in line.
>
> -- Function: dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date)
>
> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer,
> character varying, character varying, character varying, integer,
date);
>
> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives(
> character varying,
> integer,
> character varying,
> character varying,
> character varying,
> integer,
> date)
>RETURNS SETOF refcursor AS
First you can name your function parameters:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html
#PLPGSQL-DECLARATION-PARAMETERS
40.3.1. Declaring Function Parameters
If you do that then you can simplify the below. In other words in above
the first parameter becomes:
v_oper varchar(1)
Saves creating a NULL variable and assigning to it as below.
> $BODY$
> declare
>
> v_oper varchar(1) := null;
> v_id integer := null;
> v_name varchar(50) := null;
> v_short_desc varchar(150) := null;
> v_descr varchar(500) := null;
> v_user_id integer := null;
> v_fecha date := null;
> v_resu integer := null;
> perspectives_cursor refcursor := null;
> goals_persps_cursor refcursor := null;
> null_cursor refcursor := null;
>
>
> begin
>
> v_oper := $1;
> v_id := $2;
> v_name := $3;
> v_short_desc := $4;
> v_descr := $5;
> v_user_id := $6;
> v_fecha := $7;
>
>
> -- oper R = READ
> -- oper D = DELETE
> -- oper M = UPDATE
> -- oper I = INSERT
>
> I can extract the data contained into cursor named perspectives_cursor
> in the java application, but the data caontained into cursor named
> goals_persps_cursor not.
Well you are only returning one refcursor, so that is all you are going
to get. To return more than one cursor, modified example from the docs:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-cursors.html
See bottom of page for original example.
CREATE OR REPLACE FUNCTION public.myfunc(cur_a refcursor, cur_b
refcursor, arg_1 varchar(1))
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'arg_1 is %', arg_1;
OPEN cur_a FOR SELECT * FROM tbl_a;
RETURN NEXT $1;
OPEN cur_b FOR SELECT * FROM tbl_b;
RETURN NEXT $2;
END;
$function$
;
test=> begin ;
BEGIN
test=> SELECT * FROM myfunc('a', 'b', '1');
NOTICE: arg_1 is 1
myfunc
a
b
(2 rows)
test=> fetch all from a;
fld_1
---
1
2
3
(3 rows)
test=> fetch all from b;
fld_1
---
4
5
6
(3 rows)
See this post from Kevin Grittner for an alternate method:
http://www.postgresql.org/message-id/CACjxUsMy_zngFHBia+-QQuR8pOy87VU-L1
e6hppwndu2skj...@mail.gmail.com
He also includes some notes on how to make this work with JDBC.
>
> This function was tested by this
>
>
> begin;
> select * from dw_bsc.proc_perspectives('R', 1, null, null, null, null,
> null);
>
> fetch all from "";
> end;
>
> inside the query gui tool provided by pgAdmin III
>
> The connection into the java application was changed to
> con.setAutoCommit(false);
>
> I think I do not forget nothing else
>
> Some help will be appreciated very, very, very much !
>
>
> -Mensaje original-
> De: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
> Enviado el: jueves, 10 de diciembre de 2015 06:25 p.m.
> Para: Corradini, Carlos; pgsql-j...@postgresql.
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Dear Mr. Adrian Well, I must be honest, with your and Mr. Kevin explanations, the Java program have worked fine and have printed the data obtained from a two cursors inside a PostgreSQL Database Stored Function. Then, I can confirm that this version of DB ( 9.4 ) use the OUT parameter with refcursors and works fine. The JDBC interface provided by the Server Postgresql can read the data inserted into these two cursors via a callablestatement.registeroutparameter. Many thanks -Mensaje original- De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Enviado el: viernes, 11 de diciembre de 2015 12:27 p.m. Para: Corradini, Carlos; pgsql-j...@postgresql.org; pgsql-general@postgresql.org CC: bo...@ejurka.com Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On 12/11/2015 07:10 AM, Corradini, Carlos wrote: > Mr. Adrian, first let me say many thanks for your replies, were very > helpful for me. But, I must to say this other . > > I take a copy from the function from the gui tool of pgadmin III > called query sql, the original function name all the parameters, I do > not know why this gui tool change that. I have no I idea either, seems silly to me. > > You say that I am returning only one cursor, but I can say that I am > doing exactly what are you explaining in your example, I am doing > return next for each cursor to return. From your previous post: CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives( character varying, integer, character varying, character varying, character varying, integer, date) RETURNS SETOF refcursor AS You use two cursors internally, but are returning only one above. Unless you do something like Kevin Grittner and I showed there is no way for Postgres to know how to assign the internal cursors to return values that can be used externally. > > I think I am doing something wrong in the java program but I can't > realize what is this .I will go now to see the examples in the > URL's you did put below. > > Many thanks for all and excuse me for disturbing you and make you > waste your free time in this. Well the point of the list is to answer questions and if I did not have the time or the interest I would not answer, so do not worry about it. > > Again, many thanks > > -Mensaje original- > De: Adrian Klaver [mailto:adrian.kla...@aklaver.com] > Enviado el: viernes, 11 de diciembre de 2015 11:37 a.m. > Para: Corradini, Carlos; pgsql-j...@postgresql.org; > pgsql-general@postgresql.org > CC: bo...@ejurka.com > Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF > refcursor in JAVA > > On 12/11/2015 04:56 AM, Corradini, Carlos wrote: >> Mr. Adrian, here i transcribe the code of the function > > Notes in line. > >> >> -- Function: dw_bsc.proc_perspectives(character varying, integer, >> character varying, character varying, character varying, integer, > date) >> >> -- DROP FUNCTION dw_bsc.proc_perspectives(character varying, integer, >> character varying, character varying, character varying, integer, > date); >> >> CREATE OR REPLACE FUNCTION dw_bsc.proc_perspectives( >> character varying, >> integer, >> character varying, >> character varying, >> character varying, >> integer, >> date) >> RETURNS SETOF refcursor AS > > First you can name your function parameters: > > http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.ht > ml > #PLPGSQL-DECLARATION-PARAMETERS > > 40.3.1. Declaring Function Parameters > > > If you do that then you can simplify the below. In other words in > above the first parameter becomes: > > v_oper varchar(1) > > Saves creating a NULL variable and assigning to it as below. > >> $BODY$ >> declare >> >> v_oper varchar(1) := null; >> v_id integer := null; >> v_name varchar(50) := null; >> v_short_desc varchar(150) := null; >> v_descr varchar(500) := null; >> v_user_id integer := null; >> v_fecha date := null; >> v_resu integer := null; >> perspectives_cursor refcursor := null; >> goals_persps_cursor refcursor := null; >> null_cursor refcursor := null; >> >> >> begin >> >> v_oper := $1; >> v_id := $2; >> v_name := $3; >> v_short_desc := $4; >> v_descr := $5; >> v_user_id := $6; >> v_fecha := $7; >> >> >> -- oper R = READ >> -- oper D = DELETE >> -- oper M = UPDATE >> -- oper I = INSERT > > > &g
Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Yes, I was thinking to post my solution in this list for any who need to know how to receive more than one cursors from a function stored in a PostgreSQL database, but, living and working in Argentina, made an impossible work last week ( abnormal tasks to do are normal here ) , but now I have 5 minutes, then, I decided to post the java code. The solution is very, very simple having a stored function with, for example, 2 input parameters and 2 cursors returned, I do not use RETURNS SETOF REFCURSORS in the function, but I have use the key OUT as out parameter ( just I use in Oracle Stored Procedures ) in the function, then, in the java program, after made the connection to the database, I have used the CallableStatement setting the value of all the inputs parameters with the values and type needed in the function, and as for the data returned ( in this case the cursors ), I have used the REGISTEROUTPARAMETER ( this method is provided by the calllablestatement java class ) with each value returned ( in this case the cursors ) numbered from 1 for the first out parameter and n for the last, informing the type TYPES.OTHER too. After made the java call ( callablestatement.execute() ), I set a public ( in this example ) variable as a RECORDSET and assigning each cursor to each RECORDSET variable : I use the integer variable called num_cursor for the first returned and num_cursor_02 for the second ( je .. I have broken my mind and brain thinking for the perfect names ... ). rs = (ResultSet) cs.getObject(num_cursor); rs1 = (ResultSet) cs.getObject(num_cursor_02); the data obtained by the GETOBJECT method, being a cursor, must be parsed to a RECORDSET, and not, is very important this point, never use the name you gave to each cursor in the function stored, simply provide a number for which cursor want to put in each recordset variable. after, using a java loop as "while recordset.next()" method, you can extract each value with getxxx(namedvariable) returned into each cursor and it's all ... I hope I have been the most clear as my poor level of English could be Many thanks for all and specially to the postgresql community list !! -Mensaje original- De: Kevin Grittner [mailto:kgri...@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
;, v_id; RAISE NOTICE 'v_name : %', v_name; RAISE NOTICE 'v_short_desc : %', v_short_desc; RAISE NOTICE 'v_descr : %', v_descr; RAISE NOTICE 'v_user_id : %',v_user_id; RAISE NOTICE 'v_fecha : %', v_fecha; insert into dw_bsc.perspective (name, short_desc, description, usr_id_ins, usr_date_ins, usr_id_upd, usr_date_upd) values (v_name, v_short_desc, v_descr, v_user_id, v_fecha, null, null); open perspectives_cursor FOR select 'ok. insert' as resultado1; c1 := perspectives_cursor; -- return next perspectives_cursor; open goals_persps_cursor FOR select 'null' as resultado2; c2 := goals_persps_cursor; -- return next goals_persps_cursor; else RAISE NOTICE 'El id pasado al procedure no es 0, imposible procesar INSERT !!!'; open perspectives_cursor FOR select 'NULL' as resultado1; c1 := perspectives_cursor; -- return next perspectives_cursor; open goals_persps_cursor FOR select 'null' as resultado2; c2 := goals_persps_cursor; -- return next goals_persps_cursor; end if; -- exception -- when others then -- raise notice 'Se dio el error número %',sqlstate,' con descripción %', sqlerrm; -- end; end case; end; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; ALTER FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) OWNER TO usr_dw_bsc_sys_adm; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO public; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO usr_dw_bsc_sys_adm; GRANT EXECUTE ON FUNCTION dw_bsc.proc_perspectives(character varying, integer, character varying, character varying, character varying, integer, date) TO ro_dw_bsc_sys_adm; One more thing, I am a DBA ORACLE and not a Developer, please, excuse me if I made "horrors" in the programming, I promise to be more effective next time ! As I understand, it's all what you need, isn't you -Mensaje original- De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] Enviado el: lunes, 14 de diciembre de 2015 10:20 a.m. Para: Corradini, Carlos CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA > I hope I have been the most clear as my poor level of English could be.. It would be great if you could express that in java + sql as well, so the exact code can be added to JDBC driver test suite as a regression test. Vladimir -- 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] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
Ok. I understand, to put there a pull request, I must to register into this webpage ?? -Mensaje original- De: Vladimir Sitnikov [mailto:sitnikov.vladi...@gmail.com] Enviado el: lunes, 14 de diciembre de 2015 10:53 a.m. Para: Corradini, Carlos CC: Kevin Grittner; Adrian Klaver; List; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA >As I understand, it's all what you need, isn't you Ideally I would like to see a pull request at https://github.com/pgjdbc/pgjdbc/pulls, however your code seems to be good enough so somebody else can pick it up, simplify a bit, and file a PR. Vladimir -- 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] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA
I forgot one more thing ... The return I made in the stored function is : RETURNS RECORD AS ' Excuse me for the forget ... -Mensaje original- De: Kevin Grittner [mailto:kgri...@gmail.com] Enviado el: viernes, 11 de diciembre de 2015 06:54 p.m. Para: Corradini, Carlos CC: Adrian Klaver; pgsql-j...@postgresql.org; pgsql-general@postgresql.org; Kris Jurka Asunto: Re: [GENERAL] [JDBC] plpgsql function with RETURNS SETOF refcursor in JAVA On Fri, Dec 11, 2015 at 2:11 PM, Corradini, Carlos wrote: > with your and Mr. Kevin explanations, the Java program have worked > fine and have printed the data obtained from a two cursors inside a > PostgreSQL Database Stored Function. > > Then, I can confirm that this version of DB ( 9.4 ) use the OUT > parameter with refcursors and works fine. The JDBC interface provided > by the Server Postgresql can read the data inserted into these two > cursors via a callablestatement.registeroutparameter. For the benefit of others who may later have a similar problem and find this thread, it would be great if you could provide a little self-contained example of a Java program which uses the technique that you settled on. Thanks! -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SQL question: checking all required items
? Try Select * from people where person_id in ( Select person_ID from Items_for_people group by Person_id Having Count(*) = ( Select count(*) from Items Where is_required = true)) Or something like that. That's the idea. Probe it and tell us. (May be the sintaxis it's not correct, but I'm new in postgresql. In sql server it's ok) De: [EMAIL PROTECTED] en nombre de Raymond O'Donnell Enviado el: Vie 10/08/2007 03:07 p.m. Para: 'PostgreSQL' Asunto: [GENERAL] SQL question: checking all required items Hi all, Given the following tables - create table people ( person_id text primary key, person_name text, [...etc...] ); create table items ( item_id text primary key, item_name text, is_required boolean, [...etc...] ); create table items_for_people ( person_id text, item_id text, primary key (person_id, item_id), foreign key person_id references people(person_id), foreign key item_id references items(item_id) ); - how can I find those people who don't have _all_ of the items which are marked "required"? In other words, how do I select those rows in "people" which don't have a corresponding row in "items_for_people" for *each* row in "items" which has is_required=true? Many thanks, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Help with this query (some join stuff I think)
? Try some thing like ths: SELECT companies.id, companies.name, companies.nickname, (Select count(*) from videos where companies.id=videos.company_id and videos.status= 'complete') num_videos FROM companies ORDER BY num_videos DESC Hope this help Carlos E. Ortiz De: [EMAIL PROTECTED] en nombre de Pat Maddox Enviado el: Jue 16/08/2007 02:59 p.m. Para: pgsql-general@postgresql.org Asunto: [GENERAL] Help with this query (some join stuff I think) I've got a bunch of companies that are associated with several videos. The videos have different statuses. I want to select all the companies in the database, and order them by videos that have a complete status. Here's what I have so far SELECT companies.id, companies.name, companies.nickname, COUNT(company_id) AS num_videos FROM companies LEFT JOIN videos ON companies.id=videos.company_id GROUP BY companies.id, companies.name, companies.nickname ORDER BY num_videos DESC LIMIT 10 This orders the companies by number of videos...but it says nothing about the video status. If I include a WHERE videos.status='complete' then it filters out the companies that have no complete videos. I want to include those companies in the result set though, saying that num_videos is 0. Anyone know how to get that done? Pat ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Restricting user access to an absolute minimum?
Hi, I'm trying to give access to *some* data to a third-party user. I want to create a view with a choice of columns and a reduced set of rows (via a where clause as part of the view definition), and give this user access to that *and only that* --- where the "and only that" goes in the most strict sense possible! The thing is, I create a test user; I log in as that user to the "main" database (with psql), I type \d and I see the list of tables. Perhaps much worse, I do \d some_table or \d vw_some_view and I see the definition for any arbitrary table or view. How can I avoid all that? That is, I would like the user to be able to do *exclusively* "select * from vw_" and see the data that results from that query. I tried logging in as superuser and did: revoke all on schema public from new_user; revoke all on pg_tables from new_user; revoke all on pg_views from new_user; and another half dozen or so, but still the user is able to view whatever it wants to view (not the data, of course --- if I do "select * from some_table" then of course, I get access denied). There must be solutions to this, right? Any tips appreciated!! Thanks, Carlos -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dumping from older version
Raymond O'Donnell wrote: Just wondering - when using a newer pg_dump to dump from an older Postgres, does pg_dump automatically generate INSERT statements for the data rather than using COPY? I noticed this today when transferring data to a newer server - pg_dump generated INSERTs although I didn't ask for them. Not a problem, but I was curious. Maybe you used the switch -d to specify the database? (like with psql and some other client applications). The switch -d in pg_dump goes for "Generate inserts instead of COPY commands" Double-check the syntax/switches for pg_dump (pg_dump --help) HTH, Carlos -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Autostart PostgreSQL in Ubuntu
Johann Maar wrote: But if I try to start PostgreSQL by running "sudo /etc/init.d/ postgresql start" it will fail because it tries to write a PID file to "/var/run/postgresql" which does not exist. If I create this directory and set the permissions for postgres to write it works (!), but after the next restart of the machine the directory is already gone. With Red Hat systems, you would do chkconfig postgresql on if you installed the postgresql that they distribute. On Ubuntu (and I imagine with all Debian-based systems), AFAIK you have to manually adjust the init scripts for the runlevels that you want. Assuming that you want postgresql started at runlevels 3 and 5, there should be symlinks in /etc/rc3.d and /etc/rc5.d pointing to /etc/init.d/postgresql (so that the boot sequence runs /etc/init.d/postgresql start for you --- so to speak) These symlinks should be named S??postgresql (where ?? is a two-digit code that indicates the order in which the service is started --- maybe 99 or some high number would be convenient, so that it is started after other services like networking). A ls -l /etc/rc?.d should help you visualize what you need to do. If you're not familiar at all with all this, do a search on runlevels and init scripts; I'm sure you'll find plenty of documents/tutorials out there. Or The big, "brute force" hammer, would be to add a line in the /etc/rc.local file with the very command that you're running to start it (without sudo, of course, since that'a already being run by root) HTH, Carlos -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Find "smallest common year"
Stefan Schwarzer wrote: Sorry, I forgot to mention my table design, which is like this: name 20012002 2003 2004 2005 - Afghanistan Albania (Yep, I know, bad table design :-)). I tried to change it to the more common "id | year | value" format, but there were too many SQL problems afterwards for the different queries/calculations we need to have) May I suggest that you concentrate on solving *those* problems instead of the programmatically trivial computation of lowest common value? Notice that a *really trivial* programming exercise becomes highly involved in your case --- if I'm understanding correctly what you have, I assume you'd have to check one by one the fields for NULL or non-NULL values --- that's intolerably ugly, IMHO, and it is a very simple and direct consequence of an as-unsound-as-it-gets db/table design. Carlos -- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Please change default characterset for database cluster
CN wrote: > Hi! > "initdb" use SQL_ASCII as the default characterset encoding when it is > not given option "-E" and when it can not correctly derive one from > locale. I suggest "initdb" use UNICODE instead of SQL_ASCII because > UNICODE is far more useful than SQL_ASCII. > > Not all webmasters are willing to spend time reading "initdb" > documentation. I have encountered a free web hosting providing > PhpPgAdmin through which I can create my databases. Problem is that all > newly created databases use SQL_ASCII which is completely useless to me. > Their PhpPgAdmin script does not support "-E" switch for "createdb". As > a result, I have to abandon that service all together. Was "initdb" > using UNICODE as the default characterset, everthing would be perfect. In addition to the general comment that the world does not necessarily revolve around you, and that you should not expect all software products in the world to be customized to suit *your* needs, I have to highlight how horrifying this is: > Not all webmasters are willing to spend time reading "initdb" > documentation. This is truly horrifying --- well, fortunately, one could hope that it is as wrong as the rest of your message; that dumb and lazy end users and computer illiterate people are not willing to spend time reading documentation or instructions is ok... But webmasters and database administrators??? Do you *seriously* expect that some highly complex software like a DB server should be handled by people who are not willing to read documentation That's the most preposterous notion I've read in the last few months! Another detail to add --- for a lot of people, Unicode is a useless feature that has a very important performance hit. For a *very large* fraction of applications, I see it generally advised to use a database with no encoding (which SQL_ASCII essentially is), and in the situations where some locale-aware processing is needed, then the client application can do it. Of course, there are also many many applications where a DB with Unicode encoding is very useful. In those cases, the administrators can create a database with Unicode encoding (you seem to be one of those that are too busy to be willing to spend time reading the documentation of *createdb*), regardless of what default encoding was specified with initdb. Oh, and BTW, welcome to version 8 of PostgreSQL ... The default encoding for initdb is . Ta-d!!! Unicode !!! Carlos -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Please change default characterset for database cluster
Michael Fuhr wrote: On Fri, Sep 28, 2007 at 09:32:43PM -0400, Carlos Moreno wrote: Oh, and BTW, welcome to version 8 of PostgreSQL ... The default encoding for initdb is . Ta-d!!! Unicode !!! No, it isn't. If you get UTF8 (formerly UNICODE) as a default then it's because initdb is picking it up from your environment. http://www.postgresql.org/docs/8.2/interactive/app-initdb.html "The default is derived from the locale, or SQL_ASCII if that does not work." Right --- I made the "over-assumption" based on the fact that all the systems where I've installed it (all Fedora flavors of Linux) use UTF8 as system locale, and thus that one in a sense becomes the "default" ... Not sure about other flavors of Unix, but certainly on the Windows world all bets would be off (not like anyone would care, huh? ;-) ) Carlos -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Understanding PostgreSQL Storage Engines
I know that MySQL uses MyISAM storage engine by default and was just trying to look on Google to try and see if I could understand what storage engine does PostgreSQL use by default when I generate a database / table. Is there some way someone (me) who knows nothing about how a ORDBMS works understand the difference between all storage engine options and which does PostgreSQL use by default. Thanks for any help... -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NoSQL -vs- SQL
Just wondering how you guys feel about NoSQL and I just wanted to share the following article... http://www.linuxjournal.com/article/10770 Looking to read your feedback and / or opinions. -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Adding a New Column Specifically In a Table
OK so I have read the docs and Google to try and find a way to add a new column to an existing table. My problem is I need this new column to be created 3rd rather than just dumping this new column to the end of my table. I can't find anywhere how I can insert my new column as the 3rd table column rather than the last (seventh). Does anyone know how I can accomplish this or if it's even possible. Seems like a common task but I checked the documentation and may have missed it in my reading. ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; The above command dumps the 'employer' column at the very end of my table which is not what I want. Thanks for any assistance... -- 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] Adding a New Column Specifically In a Table
On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens wrote: > OK so I have read the docs and Google to try and find a way to add a > new column to an existing table. My problem is I need this new column > to be created 3rd rather than just dumping this new column to the end > of my table. I can't find anywhere how I can insert my new column as > the 3rd table column rather than the last (seventh). Does anyone know > how I can accomplish this or if it's even possible. Seems like a > common task but I checked the documentation and may have missed it in > my reading. > > > ALTER TABLE users ADD COLUMN employer VARCHAR(50) NOT NULL; Ah sadly I just found this after I pressed 'send' and realized PostgreSQL doesn't support it...that sucks :( http://wiki.postgresql.org/wiki/Alter_column_position -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4 Data Not Compatible with 9.0.1 Upgrade?
I did an upgrade on my database server this past weekend and the database fails to start. I checked /var/log/postgresql and found the reason: [r...@slave ~]# ps aux | grep postgres root 5189 0.0 0.0 8128 956 pts/0S+ 12:28 0:00 grep postgres [r...@slave ~]# /etc/rc.d/postgresql start :: Starting PostgreSQL [BUSY] server starting [DONE] [r...@slave ~]# ps aux | grep postgres root 5205 0.0 0.0 8128 960 pts/0R+ 12:28 0:00 grep postgres [r...@slave ~]# tail -n 50 /var/log/postgresql.log FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. Does anyone know if this is a issue with PostgreSQL or with the way Arch Linux packages the upgrade? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 12:45 PM, Bill Moran wrote: > I can't speak for Arch Linux' upgrade setup, but going from 8.4 -> 9.0 > requires that the data directory either be dumped/recreated, or ran > through the new upgrade process (which (as yet) I have no experience > with). > > If the Arch Linux stuff doesn't do that automatically, then you'll have > to do it manually. I just read: http://www.postgresql.org/docs/9.0/static/release-9-0 Sadly I blindly upgraded my database w/o doing a dump / restore so can anyone tell me if I am dead in the water or is there a way I can recover from this error on my part? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 12:52 PM, Richard Broersma wrote: > oops: http://www.postgresql.org/docs/9.0/interactive/pgupgrade.html Thanks for the URL. I will try this but I am confused how to proceed? Can I attempt this with PostgreSQL 9.0.1-2 server installed and the data is still 8.4 or do I need to find a way to uninstall 9.0.1-2 and reinstall the 8.4 server? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 1:32 PM, Scott Marlowe wrote: > you would do it with 9.0.x installed, and there should be a program in > one of the 9.0 packages that has pg_upgrade in it. So I have my 8.4.4-6 databases backed up. I don't know if I needed the default 'postgres' database dumped but I did that one too just in case. I then upgraded the server to 9.0.1-2 and my question is how do you create a new database in PostgeSQL 9.0 coming from 8.4.4-6 when the server refuses to start. I can't connect to PostgreSQL simply because the logs tell me the data is not compatible. Am I missing something? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 2:00 PM, Bill Moran wrote: > To clarify my earlier comments, if you're going to use pg_upgrade, you > probably won't need to downgrade to 8.4. My comments about putting > 8.4 back on would have be necessary if you were going to go the old > dump/restore route. I've already downgraded / dumped the databases and upgraded to the latest version. I was then going to create the new databases however I can't connect because of the invalid data error: I guess I'm just missing something here...I didn't choose to go with the 'pg_upgrade' script simply because I wasn't aware of it's location and was worried it would dork up my data so I reverted back to 8.4.4-6 and got a clean backup of my databases. So is there nothing I can do from my position now? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Mon, Nov 1, 2010 at 2:27 PM, Steve Crawford wrote: > I'm guessing you are missing an initdb. Move your old data directory > somewhere else for now and do a new initdb so you can start up version 9. When you say 'old data' can you be more specific as to the path and possible files I need to move? I go to '/var/lib/postgres/data/' directory however I am not sure where from that folder structure I need to start moving files away without breaking basic server functionality & connection data. -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
So I am still in the dark about the entire upgrade or step up process from 8.4.4-6 to 9.0.1-2. I have my 4 databases all backed up which I did when my server was 8.4.4-6 using the 'pg_dump' utility. That worked fine. So after I backed up my databases, I then upgraded the daemon to 9.0.1-2 and from there I don't understand the process. I obviously can't connect to the PostgreSQL database after I upgrade because I get the error: FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1. I have located the '/usr/bin/pg_upgrade' but I don't understand how I go about this. I am worried because I have 12 unique database users with unique / individual grants on specific databases. I don't want to start from scratch so I would assume this is where the 'pg_upgrade' script comes into play. How do I proceed from this point on? Obviously I can't access or connect to PostgreSQL once I upgrade to 9.0.1-2 so I assume at this time I need to execute the 'pg_upgrade' script to move forward, right? -- 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] 8.4 Data Not Compatible with 9.0.1 Upgrade?
On Tue, Nov 2, 2010 at 10:53 AM, Steve Clark wrote:
> mv /var/lib/postgres/data /var/lib/postgres/data.old
Before I move or rename '/var/lib/postgres/data', what version of
PostgreSQL should I be at? 8.4 or 9.0?
> You will then have to do an initdb to create the basic 9.x databases.
> You can then use psql or pg_restore depending on how you dumped
> your data to restore your databases.
I simply ran the following command:
/usr/bin/pg_dump finance > finance.sql
> With fedora you use either:
> /etc/init.d/postgresql initdb
> or
> service postgresql initdb
> to initialize the 9.x database system.
Sadly that command didn't pan out for Arch Linux:
# /etc/rc.d/postgresql initdb
usage: /etc/rc.d/postgresql {start|stop|restart}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding Schema's
I've recently switched from MySQL & have read the documentation for 'schema's' however I guess I'm just not at that level or really daft when it comes to database design. http://www.postgresql.org/docs/current/static/ddl-schemas.html I'm trying to understand the relation between actual databases & tables but can't grasp the relation with schema's so I was wondering if someone has an easy way of explaining this beyond the documentation I linked above. I notice my fresh 9.0 install has a default schema called 'public' which every newly database I create defaults to and I also created two new schema's called 'fire' & 'ice' but from what I have written above, obviously I don't have any understanding of how they work. I greatly appreciate any info and or help since I appear to be lost. -Carlos -- 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] Understanding Schema's
On Tue, Dec 14, 2010 at 7:17 PM, Joshua D. Drake wrote: > You can cross query a schema but not a database. > > So you can create: > > create table fire.foo() > create table ice.foo() > > And they are isolated from each other physically and logically but you > can query them both: > > SELECT fire.*, ice.* join on (id) Why would anyone in a random scenario want to have independent schema's to cross query? I'm just trying to see how this would be useful in any scenario. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What Programs Do You Use For PG?
I'm just wondering what programs you guys / girls are using PostgreSQL for. So far I've installed PG 9 on my Debian Linux server and manually created all my databases, schema's, and tables for my personal email / address book. It's very basic and small but I was wondering if you guys know of any programs that I can install that I could beef up my PG database with? Just looking for basic applications that use PG as a back-end so I can see how different programs are developed to create tables and schema's. I tried looking on line for an open source email address book that would use PostgreSQL as a back end but couldn't find one. Anyone have any recommendations? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Role Membership
I was looking at my users and realized none of my users are members of
a specific group or role. Not sure if there's a difference between the
two (role / group) in PostgreSQL, is there?
easports=# \du
List of roles
Role name |Attributes | Member of
---+---+---
carlos | Superuser | {}
postgres | Superuser, Create role, Create DB | {}
Now my question is about the section 'Member of' and how this is
commonly utilized by most PG administrators. Is this the same thing as
explained here:
http://www.postgresql.org/docs/8.2/interactive/role-membership.html
If I am not mistaken I can simply create a role called 'accounting'
and add several users to the 'accounting' group rather than juggling
several dozen user grants, correct? If anyone can please tell me if
I'm hot or cold on this issue as well as anything you would think is
helpful for me to know that is not in the documentation that you
learned as a PG administrator.
Thanks so much!
--
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] Role Membership
On Mon, Dec 20, 2010 at 10:41 AM, Adrian Klaver wrote: > Roles = users/groups. In older versions there where users and groups, that has > been consolidated into the concept of a role. If it makes it easier I use the > concept of roles with login privileges as a users and roles without as groups. So I did a \dg & a \du according to '\?' & I can't see the difference between the two commands. One is listed as showing 'users' and the other for 'groups' but the output looks identical to me. Perhaps my database is not that robust yet. So if I wanted to create a group / role for accounting, would I simply just do: CREATE ROLE accounting; My question is I can't find: 1. How to view all previously created roles on my database. '\dg' doesn't show me the new role I created above. 2. How to see which 'users' are all members of 'accounting'? Would that be done simply with '\dg'? -- 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] Role Membership
On Mon, Dec 20, 2010 at 12:05 PM, Scott Marlowe wrote:
> Odd, mine does. Got a complete example of creating a role and not seeing it?
>
> Here's mine:
> smarlowe=# create role stans;
> CREATE ROLE
> smarlowe=# \dg
> List of roles
> Role name | Attributes | Member of
> ---+--+---
> postgres | Superuser | {}
> : Create role
> : Create DB
> smarlowe | Superuser | {}
> : Create role
> : Create DB
> stans | Cannot login | {}
I guess I am still confused by role / group & user accounts. If you
create a role / group called 'finance', it then shows up as a user
when I do \dg? Then how do I make users a member of the 'finance' role
/ group if they're listed just like regular users are?
easports=# CREATE ROLE finance;
CREATE ROLE
easports=# \dg
List of roles
Role name |Attributes | Member of
---+---+---
cmennens | Superuser | {}
finance | Cannot login | {}
postgres | Superuser, Create role, Create DB | {}
From the above listing, I would expect 'finance' to not be listed with
my users since finance is a role / group, not a single user. I want to
make specific users members of 'finance'. Am I missing something or
just slow today?
>> 2. How to see which 'users' are all members of 'accounting'? Would
>> that be done simply with '\dg'?
>
> Yeah.
--
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] Role Membership
On Mon, Dec 20, 2010 at 1:32 PM, Scott Marlowe wrote: > No user, no group, they're al roles. Roles are both / either. Ah now I understand. Thank you! > You grant them that: > > grant rolename to username; > > Then you only ever have to grant / revoke a role to change > permissions, no need to do a million grants all over the place on each > table. Just grant it once to the role, grant the role to the user, > viola, you're done. OK I now understand: postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+---+--+-+-+--- caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ide | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (8 rows) postgres=# ALTER DATABASE ide OWNER TO it; ALTER DATABASE postgres=# \l List of databases Name| Owner | Encoding | Collation |Ctype| Access privileges ---+---+--+-+-+--- caldega | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tiburon | cmennens | UTF8 | en_US.UTF-8 | en_US.UTF-8 | ide | it| UTF8 | en_US.UTF-8 | en_US.UTF-8 | orlando | jmadeline | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | software | mwilshaw | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres Thanks for helping me out! -Carlos -- 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 forums ... take 2
On Mon, Dec 20, 2010 at 7:26 PM, Thom Brown wrote: > I know this topic has gone quiet, I still think it's worth investing > time and resources in. I don't expect any progress to be made until > the new year now, but I hope we can continue this after the Christmas > period is over. > > And nice work collating the discussions so far onto the wiki. That > should make it easier for everyone to keep up with developments :) I would like to be more involved in this! I've got lots of time to devote and available resources so let me know what is needed. I would like to see this grow into a full dedicated "official" web forums. -Carlos Mennens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding Roles & Grant Options
I've been reading the documentation and I'm trying to understand what
'GRANT' options make up a 'superuser' in PostgreSQL.
I've got my account which is 'carlos' and then I have an account
called 'jason'. Can someone please explain the difference between the
two roles:
postgres=# \dg
List of roles
Role name | Attributes | Member of
---+--+---
carlos | Superuser| {it}
it| Cannot login | {}
jason | Create role | {it}
: Create DB
postgres | Superuser| {}
: Create role
: Create DB
Obviously 'Carlos' is a superuser but what does that exactly entail
beyond CREATEDB & CREATEROLE?
http://www.postgresql.org/docs/8.1/static/app-createuser.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rename Schema Removes Unique Constraints?
I decided last night to rename my 'public' schema (Not sure of that's a good / bad idea) since I'm still learning about how schema's work on PostgreSQL. My question is: 1. If I have a constraint (specifically 'unique') on a specific table, when I rename the public schema, does that impact my ''unique', 'not null', and 'primary key' constraints? 2. When you install a new PostgreSQL server at home or work, do you guys generally leave the default 'public' schema or do you at some point rename or use new schema for reasons I'd like to understand. I don't understand how schema's really fit into the picture but I'm interested in knowing what others do with their default schema. Thanks for the 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] linux server configuration
On Thu, Jan 6, 2011 at 6:20 AM, Sim Zacks wrote: > We are about to build a new database server, our plan is to use Debian. > > Is there documentation of recommended server configurations for Linux, such > as kernel parameters, preferred file system, etc that work best with > postgresql? > > I'm not talking about the pg configuration, which I have seen a lot of > documentation about, more on getting the OS ready. My company has two identical PostgreSQL servers running on Debian (Squeeze) & we didn't tune the kernel and left it as it was installed by Debian. I partitioned the drives as 'ext4' & and the data is stored on a iSCSI NAS (RAID 5) configuration. The servers have been rock solid. Just be super careful that you don't blindly upgrade the server (using apt-get upgrade) and accidentally swap from 8.4 > 9.0. I ran into this problem and it was very messy. This is a PG issue however, not a Debian issue. On a side note I've ran PostgreSQL 8.4 in a production environment on the following: - CentOS 5 64-bit - RHEL 5 64-bit - Arch Linux 64-bit - Ubuntu 10.04 Server - Slackware Linux 13 64-bit - Gentoo Linux (Uggh) 64-bit Out of all those distributions, I can honestly say that between Arch Linux and Debian, no other distribution comes close. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database Design Question
I was sitting down thinking the other day about when is it good to generate a new database or just use an existing one. For example, lets say my company name is called 'databasedummy.org' and I have a database called 'dbdummy'. Now I need PostgreSQL to manage several applications for my company: - webmail - software - mediawiki - phpbb forum Now what I've been doing is just creating multiple tables in the 'dbdummy' database but each table is owned by different users depending on their role. Is this bad? Should I be creating new databases for each application above rather than one single company database? Just trying to understand good DBA design practice. This is obviously a very general question but any feedback on what good or bad issues would come from me dumping all my tables for applications in one database or spread out across multiple databases on PostgreSQL. Thank you! -- 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] Database Design Question
Thanks for all the suggestions and everyone appears to agree that if the applications don't need to share data, then I should split them up into separate database and nothing more. I appreciate your input and explanations as well. -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Remove Role Membership
I've been searching the documentation and I've tried ALTER ROLE, REVOKE, etc etc etc & can't seem to find anything that shows me how to remove membership roles from a particular user / role. I've granted a user name 'david' a member of 'finance' role but how do I remove the role membership from 'david'? Sorry for sounding completely dumb but I just can't seem to find this in the documentation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Additional Grants To SuperUser?
I created a role named 'carlos' which is my current user account with
'superuser' grants but my question is when I look at 'postgres'
account, he has additional grants that I don't understand.
List of roles
Role name | Attributes | Member of
---+-+---
carlos | Superuser | {}
jmadeline | Create DB | {}
mwilshaw | Create DB | {}
postgres| Superuser | {}
: Create role
: Create DB
So from what I see above, 'carlos' is a superuser but do I need to
grant him 'CREATEROLE' & 'CREATEDB' rights along with 'SUPERUSER' or
is 'SUPERUSER' by itself good enough?
--
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] Additional Grants To SuperUser?
On Fri, Feb 4, 2011 at 2:18 PM, David Johnston wrote:
> Not to be smart about it but you could just logon as carlos (or a different
> superuser you create for this purpose) and issue "Create Database xxx" and
> "Create Role xxx" statements and see whether they work. A superuser should
> (imo) be able to do everything (including dropping) without any additional
> permissions required so unless you see that carlos cannot I would say you
> are good.
Yes but I'm trying to understand the difference because the default
'postgres' user that is auto-configured to have 'SUPERUSER',
'CREATEDB', & 'CREATEROLE' grants. I'm trying to understand if those
are redundant grants or if there is a reason PostgreSQL developers
grant the 'postgres' user with SUPERUSER, CREATEDB, & CREATEROLE.
Seems to me logically that if a someone is a superuser, then they
should be able to CREATEDB & CREATEROLE, no? So why would the
'postgres' user need those additional attributes?
postgres=# \du
List of roles
Role name | Attributes | Member of
+-+---
cmennens | Superuser | {}
postgres | Superuser | {}
: Create role
: Create DB
--
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] Additional Grants To SuperUser?
On Fri, Feb 4, 2011 at 5:08 PM, Dmitriy Igrishin wrote: > These all (SUPERUSER, CREATEDB, SUPERUSER) are role attributes. > By performing ALTER ROLE postgres NOSUPERUSER it is possible to > turn role with a superuser status into a role that just can create databases > and manage roles (admin, but without superuser privileges). So is it very bad to alter ANY of the default role attributes granted to the 'postgres' user? I don't know if removing role attributes from him will have negative consequences to features / functional tasks of the PostgreSQL server / client application(s). -- 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 Password Access
Today on a new PostgreSQL 9.0.3 server I created a new user:
CREATE ROLE carlos LOGIN CREATEDB CREATEROLE;
CREATE ROLE
I then set a password and comment on the user:
ALTER ROLE carlos WITH PASSWORD 'letmein';
ALTER ROLE
COMMENT ON ROLE carlos IS 'Database Administrator';
COMMENT
So I now try to connect to the database from my desk workstation:
carlos@laptop:~$ psql -h db1 -d postgres
psql (8.4.7, server 9.0.3)
WARNING: psql version 8.4, server version 9.0.
Some psql features might not work.
Type "help" for help.
postgres=> SELECT current_user;
current_user
--
carlos
(1 row)
postgres=> \du
List of roles
Role name | Attributes | Member of
-------+-+---
carlos| Create role | {}
: Create DB
maggie| Create DB | {}
postgres | Superuser | {}
: Create role
: Create DB
Why am I not prompted for a password when I connect from my laptop to
the server? I didn't grant attributes of 'INHERIT' to carlos so
shouldn't I be prompted for my password? Am I missing something here?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Storing Media Types
I've created a basic table called 'employees' & I've been asked to store a profile photo of all employees. I've looked on Google and the 9.0 documentation but can't find any clear instructions on how I would be to insert photo's stored in a local directory on the server (/var/lib/postgres/data/media/pics). I'm trying to understand how I would create an entry into the table I show below to be able to add photo's for each user. Does anyone have an example of what the code would look like and or offer any assistance? ghost=> CREATE TABLE employees ( id INT PRIMARY KEY NOT NULL UNIQUE, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, email VARCHAR(50) UNIQUE NOT NULL, branch VARCHAR(50) NOT NULL, position VARCHAR(50) NOT NULL, office INT NOT NULL, dob DATE NOT NULL, photo ? ) ; NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees" NOTICE: CREATE TABLE / UNIQUE will create implicit index "employees_email_key" for table "employees" CREATE TABLE -Carlos -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL ODBC Driver Help
I'm trying to figure out how I can have users in the office connect their Microsoft Office 2007 clients to our company database server running PostgreSQL 8.4.7. I've configured PostgreSQL to accept incoming connections and allow users to login however I read that I need to have each client install a MSI pack that allows ODBC drivers to talk to the database server. I've installed the ODBC driver from the following URL: The Drivers can be found at: PostgreSQL: http://www.postgresql.org/ftp/odbc/versions/msi/ I installed it on the machine running Office 2007 but beyond that I have no idea how to initialize a connection from Excel or Access to the database server. Can anyone please help me figure out how to connect? I have my 'pg_hba.conf' & 'postgresql.conf' files configured perfectly to accept incoming SSL connections from my internal network on the default port for PostgreSQL. -- 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 ODBC Driver Help
On Tue, Feb 15, 2011 at 12:46 PM, A.M. wrote: > I googled "connect excel postgresql" and found this: > > http://port25.technet.com/videos/research/excelopendbprimer.pdf > > which seems to take one through all the steps. I have been using that actual .PDF as a guide and I guess it's different on Office 2007 than show on that site using Office 2003. I guess I will keep searching to find out what I need to do in order to connect Office 2007 Excel or Access to PostgreSQL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Auto Adjust Age
I've only been using PostgreSQL since Oct 2010 and it's my first experience with SQL or any ORDBMS. I've searched on the web and been creating my own database users, databases, tables from scratch which has been interesting to say the least but now I would like to know if this is possible in SQL or PostgreSQL since I can't find anything online that shows me how to do so. I've created a table called 'users' and I have it configured as follows: CREATE TABLE users ( id integer PRIMARY KEY UNIQUE NOT NULL, --ID fname character varying(40) NOT NULL, --First name lname character varying(40) NOT NULL, --Last name email character varying NOT NULL, --email address office integer NOT NULL, --Office number dob date NOT NULL, --Date of birth age integer NOT NULL --Age ) ; Is there a way in SQL I can have the users 'age' be auto adjusted based on the 'id' & 'dob'? I would assume this is possible because if you have 100 employees, I doubt someone has time to sit and change everyone's age from 31 > 32 on their birthday. Can someone please help explain how this works or what the SQL code would look like assuming that it's possible? I have no advanced far enough to see what triggers and views are so perhaps it's just my level with SQL in general. Thank you so much. -- 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] Auto Adjust Age
On Wed, Apr 6, 2011 at 11:20 AM, Andrew Sullivan wrote:
> Why do you have the age stored at all? When you SELECT from the table
> and want someone's age, just do
>
> SELECT [. . .], extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
> . . . FROM users . . .
>
> By and large, it's not a good idea to store something you can
> calculate from other data you have.
I'm very sorry as I didn't know PG or SQL could auto calculate age
with existing parameters. I'm trying to learn as much SQL as I can
during my spare time. I tried the following and it worked great!
ide=# SELECT extract('years' from age(CURRENT_TIMESTAMP,dob)) as age
FROM users;
age
-
31
(1 row)
THANK YOU;
--
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] Auto Adjust Age
On Wed, Apr 6, 2011 at 11:24 AM, JC de Villa wrote: > Theres also the age() function > > SELECT age(dob); > > Should give you > > age > - > 31 years 5 mons 17 days > > If you want to be really exact about it. :) That worked awesome too! ide=# SELECT age(dob) FROM users; age -- 31 years 10 mons 12 days (1 row) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changed SSL Certificates
I had self signed SSL certificates on my database server but since then removed them and received updated certificates from the security team. I removed (backedup) the old server.crt & server.key and now have db1_ssl.crt & db1_ssl.key in the identical location as the old SSL certificates. I then went to /etc/postgres/8.4/main and removed the old symbolic links for the old certificates and generated new symbolic links: ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key I then restarted PostgreSQL and got the following error: 2011-04-08 09:54:34 EDT FATAL: could not load server certificate file "server.crt": No such file or directory 2011-04-08 10:00:43 EDT FATAL: could not load server certificate file "server.crt": No such file or directory I looked for anywhere else in /var/lib/postgres/ & /etc/postgres/ but can't find anything else that's calling the old certificates. I changed the ownership on the certificates and symbolic links to either root or postgres and nothing worked. It fails to start with the following error: root@db1:/# /etc/init.d/postgresql start Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2011-04-08 12:36:54 EDT FATAL: could not load server certificate file "server.crt": No such file or directory ... failed! I checked the documentation page: http://www.postgresql.org/docs/8.4/static/libpq-ssl.html Table 30-4. Libpq/Client SSL File Usage FileContentsEffect ~/.postgresql/postgresql.crtclient certificate requested by server ~/.postgresql/postgresql.keyclient private key proves client certificate sent by owner; does not indicate certificate owner is trustworthy ~/.postgresql/root.crt trusted certificate authorities checks server certificate is signed by a trusted certificate authority ~/.postgresql/root.crl certificates revoked by certificate authorities server certificate must not be on this list Can anyone tell me what I'm doing wrong or missing here? I can't disable SSL per DoD requirements sadly. -Carlos -- 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] Changed SSL Certificates
On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz wrote: > Hi, > When linking to the certificate and key you should specify the full path. > ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt > ln -s /etc/ssl/private/db1_ssl.key /full/path/to/db1_ssl.key Thanks for the quick reply Diego. I posted the commands above and I used the full path to the certificates as you can see. Here's the info: lrwxrwxrwx 1 postgres postgres 26 Apr 8 10:43 db1_ssl.crt -> /etc/ssl/certs/db1_ssl.crt lrwxrwxrwx 1 postgres postgres 28 Apr 8 10:50 db1_ssl.key -> /etc/ssl/private/db1_ssl.key The 1st part is just the symbolic link referenced in /var/lib/postgresql/8.4/main but you can see it knows to reference the symbolic links to /etc/ssl/... I'm thinking there's some random configuration file for PostgreSQL that has pointers to the old server.crt and server.key files but I've searched /etc/postgres/ and /var/lib/postgresql/8.4/main completely and can't find it what so ever. I am not authorized to disable SSL per DoD standards / requirements sadly. Any thing else I am missing? I can't be the 1st person to switch SSL certificates during utilization. -- 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] Changed SSL Certificates
On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver wrote: > Per here: > http://www.postgresql.org/docs/8.4/static/ssl-tcp.html > File Contents Effect > server.crt server certificate requested by client > server.key server private key proves server certificate sent by > owner; does not indicate certificate owner is trustworthy > root.crt trusted certificate authorities checks that client > certificate is signed by a trusted certificate authority > root.crl certificates revoked by certificate authorities client > certificate must not be on this list > > Rename your certs to above. Oh I mis-understood. I just need to rename my symbolic links, not my actual certificate file names. Changed symbolic link names and everything is happy again. Thanks so much for everyones help! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rename or Re-Create Constraints?
I've searched and really can't find a definitive example or someone renaming a constraint. I renamed a table yesterday and noticed that the constraint name was still named the old table name: inkpress=# ALTER TABLE accounts RENAME TO fashion; ALTER TABLE inkpress=# \d fashion Table "public.fashion" Column | Type | Modifiers -+---+--- id | integer | not null vendor | character varying(40) | not null account | integer | not null email | character varying(40) | not null state | character(2) | not null Indexes: "accounts_pkey" PRIMARY KEY, btree (id) "accounts_account_key" UNIQUE, btree (account) "accounts_email_key" UNIQUE, btree (email) "accounts_vendor_key" UNIQUE, btree (vendor) 1. Do I need to remove all the table constraints or is there a way to rename them? 2. When renaming the table, is there a way to rename both the table and all associated constraints? I've looked over the following guide and am more confused than ever: http://www.postgresql.org/docs/8.1/static/sql-altertable.html -- 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] Rename or Re-Create Constraints?
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them. (This works in HEAD anyway, not sure how far back.) I'm sorry but I don't understand what that means or how to relate that to a SQL command to rename the constraint. Do you have an example of how that command would look? >> 2. When renaming the table, is there a way to rename both the table >> and all associated constraints? > > No, there's nothing automatic for that. IIRC there used to be code to > try to do this when you renamed a single column; but we took it out, > probably because it risked unexpected failures due to index name > collisions. > > 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] Rename or Re-Create Constraints?
On Fri, Apr 8, 2011 at 8:35 PM, Tom Lane wrote: > I believe you can rename the underlying indexes and the constraints will > follow them. (This works in HEAD anyway, not sure how far back.) Below is my table: inkpress=# \d marketing Table "public.marketing" Column | Type | Modifiers -+---+--- id | integer | not null vendor | character varying(40) | not null account | integer | not null email | character varying(40) | not null state | character(2) | not null Indexes: "accounts_pkey" PRIMARY KEY, btree (id) "accounts_account_key" UNIQUE, btree (account) "accounts_email_key" UNIQUE, btree (email) "accounts_vendor_key" UNIQUE, btree (vendor) I renamed the table name from 'accounts' to 'marketing' however all the constraints listed under 'indexes' are still named 'accounts_*' and I've tried to rename them but I can't find any information with an example command to rename the constraints: ALTER TABLE marketing ...??? I can't find any update / alter SQL commands to correct the constraint inconsistency. :( -- 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] Rename or Re-Create Constraints?
On Sat, Apr 9, 2011 at 12:58 PM, Tom Lane wrote: > ALTER INDEX accounts_pkey RENAME TO whatever > > On very old versions of PG you may have to spell that "ALTER TABLE" > instead of "ALTER INDEX", but it's the same thing either way. Thank you so much for clearing that up for me Tom! I just couldn't find anything documented or do I understand SQL enough to work through that w/o an example. I read the PostgreSQL documentation all morning and just couldn't find it. Also to make sure I did this correct, if I had an existing table w/o a PRIMARY KEY index / constraint, is the following correct? CREATE UNIQUE INDEX users_pkey ON public.users (id); CREATE INDEX I'm guessing that's how I generate a index / constraint on an existing table when it was generated during the table creation SQL command, right? Is there a difference between an INDEX and a CONSTRAINT? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] help with a procedure
Hi everyone, I wonder if you could help me with a procedure that I would
like to perform in postgresql.
I have an insert query like this:
INSERT INTO products (product_id, description, price, qty, ...) values
('01', 'some description', 10.15, 5, ...)
then if there is no problem, perform another query like:
INSERT INTO store(store_id, description, price, qty, ...) values ('02',
'some description', 10.15, 5, ...)
So the second query depends of the first query, if the first one succeed
the second will perform the second insert
I would like to do something like:
SELECT myProcedure(product_id, p_description, price, qty, store_id,
store_description );
waiting for a response from procedure, maybe true or false.
Thanks in advance...
--
"El desarrollo no es material es un estado de conciencia metal"
Re: [GENERAL] help with a procedure
2014-06-05 9:32 GMT-06:00 Carlos Carcamo :
> Hi everyone, I wonder if you could help me with a procedure that I would
> like to perform in postgresql.
>
> I have an insert query like this:
>
> INSERT INTO products (product_id, description, price, qty, ...) values
> ('01', 'some description', 10.15, 5, ...)
>
> then if there is no problem, perform another query like:
>
> INSERT INTO store(store_id, description, price, qty, ...) values ('02',
> 'some description', 10.15, 5, ...)
>
> So the second query depends of the first query, if the first one succeed
> the second will perform the second insert
>
> I would like to do something like:
>
> SELECT myProcedure(product_id, p_description, price, qty, store_id,
> store_description );
>
> waiting for a response from procedure, maybe true or false.
>
> Thanks in advance...
>
>
>
> --
> "El desarrollo no es material es un estado de conciencia metal"
>
Sorry, the second query looks like:
INSERT INTO store(store_id, description, product_id, price, qty, ...)
values ('02', 'some description', '01', 10.15, 5, ...)
--
"El desarrollo no es material es un estado de conciencia metal"
Re: [GENERAL] help with a procedure
What I need is some help with the procedure in postgres, I have searched in
google, but I dont know how to do it, the goal is perform the first insert
and then the second insert if the first one succeed in one procedure
instead of using two separate inserts.
Do you have some code like that?
2014-06-05 10:14 GMT-06:00 Seref Arikan :
> Sorry, I meant: "calling a stored procedure you'll write in postgres from
> php"
>
>
> On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan
> wrote:
>
>> Hi Carlos,
>> When you say procedures, do you mean calling a stored procedure you'll
>> write from php? Or executing the individual INSERT from php sequentially?
>> For the first scenario, you'd need to write a postgresql stored procedure
>> (I suggest you google: PL/pgSQL tutorial) and call the from php. For the
>> second, well, it is exactly what I said before: you'll need to open a
>> connection to postgres, execute your statements under a transaction and
>> commit. I'd suggest you either search for php and postgres or ask this to a
>> php mail group. Your goal here appears to be understanding how to call
>> postgres from php.
>>
>> Regards
>> Seref
>>
>>
>>
>>
>> On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo
>> wrote:
>>
>>> Thanks for answering...
>>> I'm using php with postgresql 9.1, I have never used procedures with
>>> php, I'm new with postgresql
>>>
>>>
>>> 2014-06-05 9:45 GMT-06:00 Seref Arikan :
>>>
>>> Hi Carlos,
>>>> Unless I'm missing something here, your queries are probably being
>>>> called from a programming language (java/c# etc) and your database access
>>>> api should support transactions. If you perform both operations under the
>>>> same db transaction and commit your transaction things should be fine. If
>>>> there is a problem with the first INSERT, your api should throw an
>>>> exception and you won't be able to commit the transaction (you may/may not
>>>> need to call rollback in your catch block), so it'll either be both calls
>>>> executed or none.
>>>>
>>>> You may want to read about how db transactions are handled in your
>>>> programming environment.
>>>>
>>>> Regards
>>>> Seref
>>>>
>>>>
>>>>
>>>> On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo
>>>> wrote:
>>>>
>>>>>
>>>>> 2014-06-05 9:32 GMT-06:00 Carlos Carcamo :
>>>>>
>>>>> Hi everyone, I wonder if you could help me with a procedure that I
>>>>>> would like to perform in postgresql.
>>>>>>
>>>>>> I have an insert query like this:
>>>>>>
>>>>>> INSERT INTO products (product_id, description, price, qty, ...)
>>>>>> values ('01', 'some description', 10.15, 5, ...)
>>>>>>
>>>>>> then if there is no problem, perform another query like:
>>>>>>
>>>>>> INSERT INTO store(store_id, description, price, qty, ...) values
>>>>>> ('02', 'some description', 10.15, 5, ...)
>>>>>>
>>>>>> So the second query depends of the first query, if the first one
>>>>>> succeed the second will perform the second insert
>>>>>>
>>>>>> I would like to do something like:
>>>>>>
>>>>>> SELECT myProcedure(product_id, p_description, price, qty, store_id,
>>>>>> store_description );
>>>>>>
>>>>>> waiting for a response from procedure, maybe true or false.
>>>>>>
>>>>>> Thanks in advance...
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>>
>>>>>
>>>>> Sorry, the second query looks like:
>>>>> INSERT INTO store(store_id, description, product_id, price, qty, ...)
>>>>> values ('02', 'some description', '01', 10.15, 5, ...)
>>>>>
>>>>> --
>>>>> "El desarrollo no es material es un estado de conciencia metal"
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> "El desarrollo no es material es un estado de conciencia metal"
>>>
>>
>>
>
--
"El desarrollo no es material es un estado de conciencia metal"
Re: [GENERAL] help with a procedure
Just a little, I will read it again, thanks for your help.
2014-06-05 10:39 GMT-06:00 David G Johnston :
>
> Have you read this chapter of the documentation?
>
> http://www.postgresql.org/docs/9.3/interactive/plpgsql.html
>
>
> Carlos Carcamo wrote
> > What I need is some help with the procedure in postgres, I have searched
> > in
> > google, but I dont know how to do it, the goal is perform the first
> insert
> > and then the second insert if the first one succeed in one procedure
> > instead of using two separate inserts.
> >
> > Do you have some code like that?
> >
> > 2014-06-05 10:14 GMT-06:00 Seref Arikan <
>
> > serefarikan@
>
> > >:
> >
> >> Sorry, I meant: "calling a stored procedure you'll write in postgres
> from
> >> php"
> >>
> >>
> >> On Thu, Jun 5, 2014 at 5:13 PM, Seref Arikan <
>
> > serefarikan@
>
> > >
> >> wrote:
> >>
> >>> Hi Carlos,
> >>> When you say procedures, do you mean calling a stored procedure you'll
> >>> write from php? Or executing the individual INSERT from php
> >>> sequentially?
> >>> For the first scenario, you'd need to write a postgresql stored
> >>> procedure
> >>> (I suggest you google: PL/pgSQL tutorial) and call the from php. For
> the
> >>> second, well, it is exactly what I said before: you'll need to open a
> >>> connection to postgres, execute your statements under a transaction and
> >>> commit. I'd suggest you either search for php and postgres or ask this
> >>> to a
> >>> php mail group. Your goal here appears to be understanding how to call
> >>> postgres from php.
> >>>
> >>> Regards
> >>> Seref
> >>>
> >>>
> >>>
> >>>
> >>> On Thu, Jun 5, 2014 at 5:03 PM, Carlos Carcamo <
>
> > eazyduiz@
>
> > >
> >>> wrote:
> >>>
> >>>> Thanks for answering...
> >>>> I'm using php with postgresql 9.1, I have never used procedures with
> >>>> php, I'm new with postgresql
> >>>>
> >>>>
> >>>> 2014-06-05 9:45 GMT-06:00 Seref Arikan <
>
> > serefarikan@
>
> > >:
> >>>>
> >>>> Hi Carlos,
> >>>>> Unless I'm missing something here, your queries are probably being
> >>>>> called from a programming language (java/c# etc) and your database
> >>>>> access
> >>>>> api should support transactions. If you perform both operations under
> >>>>> the
> >>>>> same db transaction and commit your transaction things should be
> fine.
> >>>>> If
> >>>>> there is a problem with the first INSERT, your api should throw an
> >>>>> exception and you won't be able to commit the transaction (you
> may/may
> >>>>> not
> >>>>> need to call rollback in your catch block), so it'll either be both
> >>>>> calls
> >>>>> executed or none.
> >>>>>
> >>>>> You may want to read about how db transactions are handled in your
> >>>>> programming environment.
> >>>>>
> >>>>> Regards
> >>>>> Seref
> >>>>>
> >>>>>
> >>>>>
> >>>>> On Thu, Jun 5, 2014 at 4:36 PM, Carlos Carcamo <
>
> > eazyduiz@
>
> > >
> >>>>> wrote:
> >>>>>
> >>>>>>
> >>>>>> 2014-06-05 9:32 GMT-06:00 Carlos Carcamo <
>
> > eazyduiz@
>
> > >:
> >>>>>>
> >>>>>> Hi everyone, I wonder if you could help me with a procedure that I
> >>>>>>> would like to perform in postgresql.
> >>>>>>>
> >>>>>>> I have an insert query like this:
> >>>>>>>
> >>>>>>> INSERT INTO products (product_id, description, price, qty, ...)
> >>>>>>> values ('01', 'some description', 10.15, 5, ...)
> >>>>>>>
> >>>>>>> then if there is no problem, perform another query like:
> >>>>>>>
> >>>>>>> INSERT INTO store(store_id, description, price, qty, ...)
[GENERAL] pg_dump: [archiver] -C and -c are incompatible options
Hello list, I need some help with this error: ... pg_dump: saving encoding = UTF8 pg_dump: saving standard_conforming_strings = on pg_dump: [archiver] -C and -c are incompatible options pg_dump: *** aborted because of error Process returned exit code 1. I get this error when i'm trying to create a backup of some of my tables of the database, i'm using pgadmin3, I've searched on internet about it, but I couldn't find useful info. I will appreciate any help, thanks in advance. -- "El desarrollo no es material es un estado de conciencia mental" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] execute trigger after another one
Hello list, hope you're well. I need some help with postgres. I have two triggers that are executed after insert, update and delete. One trigger is with plpgsql language and the other one is with pl/python, the first one just update some tables and last one calls a *.sh that is executed to perform some stuff, both triggers works great, the problem is that I need that the triggers execute in order, first the plpgsql and then the plpython one. I read about order of execution of triggers, is supposed that postgres executes triggers in alphabetical order, so I called the plpgsql a_trigger and the second one b_trigger (as an example), but it seems that the second one always executes first. Is there any way to make triggers execute in a specific order? Thanks in advance. -- "El desarrollo no es material es un estado de conciencia mental" -- 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] execute trigger after another one
2014-12-04 16:46 GMT-06:00 David G Johnston :
> Carlos Carcamo wrote
>> I read about order of execution of triggers, is supposed that postgres
>> executes triggers in alphabetical order, so I called the plpgsql
>> a_trigger and the second one b_trigger (as an example), but it seems
>> that the second one always executes first.
>>
>> Is there any way to make triggers execute in a specific order?
>
> If two triggers would otherwise fire at the same time then alphabetical
> order is used to break ties. But in all situations before triggers will
> always fire before after triggers.
>
> But since you haven't show us the exact CREATE TRIGGER statements you are
> using whether that is why yours are not behaving is impossible to tell.
sorry for that, here some code:
-- Trigger #1
CREATE OR REPLACE FUNCTION tgfn_kardex()
RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
--logic here
END IF;
--more code
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
-- then
CREATE TRIGGER tgfn_kardex
AFTER INSERT OR UPDATE OR DELETE
ON in_kardex
FOR EACH ROW
EXECUTE PROCEDURE tgfn_kardex();
-- Trigger #2
CREATE OR REPLACE FUNCTION update_remote()
RETURNS trigger AS
$BODY$
import os
os.system('./var/www/update_remote.sh')
$BODY$
LANGUAGE plpython3u VOLATILE
-- then
CREATE TRIGGER update_remote
AFTER INSERT OR UPDATE OR DELETE
ON in_kardex
FOR EACH ROW
EXECUTE PROCEDURE update_remote();
> Also, you say "it seems" - can you put forth specific proof that one is
> firing before the other?
Yes because my update_remote.sh file calls a php file to update a
table in mysql, and it is updated after I perform another query to
in_kardex, so the mysql table is one query behind postgres...
Any thoughts?
--
"El desarrollo no es material es un estado de conciencia mental"
--
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] execute trigger after another one
2014-12-04 17:31 GMT-06:00 Adrian Klaver :
> On 12/04/2014 03:11 PM, Carlos Carcamo wrote:
>>
>> 2014-12-04 16:46 GMT-06:00 David G Johnston :
>>>
>>> Carlos Carcamo wrote
>>>>
>>>> I read about oGlenrder of execution of triggers, is supposed that postgres
>>>> executes triggers in alphabetical order, so I called the plpgsql
>>>> a_trigger and the second one b_trigger (as an example), but it seems
>>>> that the second one always executes first.
>>>>
>>>> Is there any way to make triggers execute in a specific order?
>>>
>>>
>>> If two triggers would otherwise fire at the same time then alphabetical
>>> order is used to break ties. But in all situations before triggers will
>>> always fire before after triggers.
>>>
>>> But since you haven't show us the exact CREATE TRIGGER statements you are
>>> using whether that is why yours are not behaving is impossible to tell.
>>
>>
>> sorry for that, here some code:
>>
>> -- Trigger #1
>> CREATE OR REPLACE FUNCTION tgfn_kardex()
>>RETURNS trigger AS
>> $BODY$
>> BEGIN
>> IF (TG_OP = 'INSERT') THEN
>>--logic here
>> END IF;
>> --more code
>> RETURN NULL;
>> END;
>> $BODY$
>>LANGUAGE plpgsql VOLATILE
>>
>> -- then
>>
>> CREATE TRIGGER tgfn_kardex
>>AFTER INSERT OR UPDATE OR DELETE
>>ON in_kardex
>>FOR EACH ROW
>>EXECUTE PROCEDURE tgfn_kardex();
>>
>>
>> -- Trigger #2
>> CREATE OR REPLACE FUNCTION update_remote()
>>RETURNS trigger AS
>> $BODY$
>> import os
>> os.system('./var/www/update_remote.sh')
>> $BODY$
>>LANGUAGE plpython3u VOLATILE
>>
>> -- then
>>
>> CREATE TRIGGER update_remote
>>AFTER INSERT OR UPDATE OR DELETE
>>ON in_kardex
>>FOR EACH ROW
>>EXECUTE PROCEDURE update_remote();
>>
>>> Also, you say "it seems" - can you put forth specific proof that one is
>>> firing before the other?
>>
>>
>> Yes because my update_remote.sh file calls a php file to update a
>> table in mysql, and it is updated after I perform another query to
>> in_kardex, so the mysql table is one query behind postgres...
>>
>> Any thoughts?
> My bet is that the query run from your shell script can't see your changes
> because the original transaction is still open.
--Stephen Cook
Good point, I hadn't thought about it...
> You do not say what version of Postgres you are using, but if 9.3+ then you
> might want to look at the MySQL FDW:
I'm using 9.1
> https://github.com/EnterpriseDB/mysql_fdw
--
"El desarrollo no es material es un estado de conciencia mental"
--
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] Sample databases
On Wed, Jan 16, 2013 at 9:53 AM, Vraj Mohan wrote: > Is there a good sample database (with decent data volumes) for > postgresql? I am interested in one for learning and automated testing. > > I looked at http://pgfoundry.org/projects/dbsamples/ (specifically at > pagila), but it seemed incomplete and not maintained, I've spent some months searching a year ago and found the same thing as you. All the sample databases for PG are terrible. They're old, not maintained, incomplete, or just useless. I too would love to have a sample database with large data and relational values. Sadly unless you create one yourself, I don't think there's one publicly available that meets our expectations. -- Carlos Mennens -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can't Drop Role
For some reason I'm removed all grants and roles from this users
account but I'm unable to remove / drop him from the database as
follows:
postgres=# \du
List of roles
Role name | Attributes
| Member of
---++---
cmennens | No inheritance, Cannot login | {}
jmadeline | Superuser, No inheritance, Create role, Create DB, Replication | {}
mrbs | No inheritance | {}
mwilshaw | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rcube | No inheritance | {}
postgres=# DROP ROLE cmennens;
ERROR: role "cmennens" cannot be dropped because some objects depend on it
DETAIL: owner of function pg_logfile_rotate()
owner of function pg_file_length(text)
owner of function pg_file_read(text,bigint,bigint)
owner of function pg_logdir_ls()
owner of function pg_file_unlink(text)
owner of function pg_file_rename(text,text)
owner of function pg_file_rename(text,text,text)
owner of function pg_file_write(text,text,boolean)
owner of extension adminpack
--
Carlos Mennens
--
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 Backup Booklet
Based on Amazon reviews and others in the PG community, I would 100% get something like this. High Availability / Fail Over are my biggest concerns with PostgreSQL Database. Thanks so much for sharing and good luck!!! On Wed, Apr 3, 2013 at 10:09 AM, Shaun Thomas wrote: > Hey! > > So, Packt approached me a few months ago and asked me to put together a > very basic series of short step-by-step instructions on backing up > PostgreSQL. The title is "Instant PostgreSQL Backup and Restore How-to." > > I tried to cover all of the basic approaches used by most admins, and I > tested it on a guy at work who's a SQL Server DBA but recently took over > care and feeding of a PG database. He said it was easier to understand than > the documentation, at least for just getting everything working and sorting > out more advanced details later. > > I know it's not exactly Greg Smith's performance book, but I'm glad to > contribute how I can. I'm not entirely sure it's worth adding to the book > page: > > http://www.postgresql.org/**docs/books/<http://www.postgresql.org/docs/books/> > > But if it is, I'll provide any help or extra information necessary. If > anyone has questions, I'm here to answer them. :) > > -- > Shaun Thomas > OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 > 312-676-8870 > stho...@optionshouse.com > > __** > > See > http://www.peak6.com/email_**disclaimer/<http://www.peak6.com/email_disclaimer/>for > terms and conditions related to this email > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> > -- Carlos Mennens
[GENERAL] Role Authentication Failure
I can't understand what is going on with my PostgreSQL server. For
some reason after I GRANT my role 'carlos' to the 'dba' group role, I
lose the ability to login. I've reset the password over and over for
'carlos' and even reversing the grant doesn't remedy the
authentication failure. The logs are extremely vague:
2013-04-14 00:41:52 EDT FATAL: password authentication failed for user "carlos"
My roles look as follows:
postgres=# \dg
List of roles
Role name | Attributes | Member of
---+--------+---
carlos| Superuser, Create role, Create DB, Replication | {}
dba | No inheritance, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
As you can see I've removed 'dba' role from 'carlos' but he still
can't login. My only option is to DROP the 'carlos' role and re-create
him.
The problem occurs when I:
GRANT dba TO carlos;
I don't understand which role (carlos or dba) needs INHERIT or
NOINHERIT if that's causing this...
--
Carlos Mennens
--
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] Role Authentication Failure
I'm doing this all in psql. Example: CREATE ROLE carlos LOGIN CREATEDB CREATE ROLE REPLICATION; Then set password \password carlos Now I create the ROLE: CREATE ROLE dba NOLOGIN; So now I have two roles: -carlos = user role -dba = group role I can login just fine as 'carlos' now with no authentication failure. But when I do: GRANT dba TO carlos; That's the end of 'carlos' being able to login. What am I doing wrong? The only files I have edited in PostgreSQL post installation is pg_hba.conf & postgres.conf. It's a new 9.1.9 installation with no data yet. I just don't understand why granting a role to a user destroys his authentication and even REVOKE his dba group role doesn't fix 'carlos'. On Mon, Apr 15, 2013 at 11:27 AM, Adrian Klaver wrote: > > > On Mon, Apr 15, 2013 at 7:30 AM, Carlos Mennens > wrote: >> >> I can't understand what is going on with my PostgreSQL server. For >> some reason after I GRANT my role 'carlos' to the 'dba' group role, I >> lose the ability to login. I've reset the password over and over for >> 'carlos' and even reversing the grant doesn't remedy the >> authentication failure. The logs are extremely vague: >> >> 2013-04-14 00:41:52 EDT FATAL: password authentication failed for user >> "carlos" >> >> > > How are you doing the above, via psql. pgAdmin, other or some combination? > Do you have a .pgpass file set up? > >> >> As you can see I've removed 'dba' role from 'carlos' but he still >> can't login. My only option is to DROP the 'carlos' role and re-create >> him. >> >> The problem occurs when I: >> >> GRANT dba TO carlos; >> >> I don't understand which role (carlos or dba) needs INHERIT or >> NOINHERIT if that's causing this... >> >> >> -- >> Carlos Mennens >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > > > -- > Adrian Klaver > adrian.kla...@gmail.com -- Carlos Mennens -- 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] Role Authentication Failure
I dropped both roles (Carlos & DBA) from the database and I will show
you exactly what I'm doing:
postgres=# \du
List of roles
Role name | Attributes | Member of
---++---
chris | Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication | {}
I am creating the new roles as the 'postgres' database user:
postgres=# CREATE ROLE carlos LOGIN CREATEDB CREATEROLE REPLICATION;
CREATE ROLE
postgres=# CREATE ROLE dba NOLOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
---+----+---
carlos| Create role, Create DB, Replication| {}
chris | Create DB, Replication | {}
dba | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
I was curious what happens when I used 'CREATE GROUP' versus 'CREATE
ROLE' so I gave it a shot but looks like it is the same:
postgres=# CREATE GROUP web;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
---+--------+---
carlos| Create role, Create DB, Replication| {}
chris | Create DB, Replication | {}
dba | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
web | Cannot login | {}
postgres=# \password carlos
Enter new password:
Enter it again:
Now I login as 'carlos':
carlos@debian:~$ psql -d postgres
Password:
psql (9.1.9)
Type "help" for help.
postgres=>
but...
--
Carlos Mennens
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Inherit Superuser Role Help
What am I missing here?
postgres=> SELECT current_user;
current_user
--
carlos
(1 row)
postgres=> CREATE DATABASE carlosdb;
ERROR: permission denied to create database
postgres=> \du
List of roles
Role name | Attributes
| Member of
---+--+---
carlos|
| {dba}
chris |
| {web}
dba | Superuser, Create role, Create DB, Cannot login, Replication | {}
postgres | Superuser, Create role, Create DB, Replication | {}
web | Cannot login | {}
Shouldn't 'carlos' be a superuser based on him being a member of a
role which has createdb and superuser rights granted to it?
--
Carlos Mennens
--
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 implement a value alias or synonym
http://www.postgresql.org/message-id/440d446e.7040...@cybertec.at From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Wednesday, July 10, 2013 3:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How to implement a value alias or synonym Hi: v9.0.1 on linux Is there a way to query on synonyms of a value transparent to the user? For example, a column called "animal" can have any text value, including 'horse' and 'cow' and 'pig'. But I want the user to find all the animal='pig' records if they specify 'hog' instead. So.. select * from thetable where animal = 'pig' ..would generate the same results as... select * from thetable where animal = 'hog' There are no 'hog' records in the table, just 'pig' records. And I want to have control over the synonyms, perhaps in some sort of ref table ? Thanks in Advance for any ideas.
Re: [GENERAL] How to implement a value alias or synonym
Also this: http://www.postgresql.org/docs/9.2/static/textsearch-configuration.html From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Wednesday, July 10, 2013 3:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] How to implement a value alias or synonym Hi: v9.0.1 on linux Is there a way to query on synonyms of a value transparent to the user? For example, a column called "animal" can have any text value, including 'horse' and 'cow' and 'pig'. But I want the user to find all the animal='pig' records if they specify 'hog' instead. So.. select * from thetable where animal = 'pig' ..would generate the same results as... select * from thetable where animal = 'hog' There are no 'hog' records in the table, just 'pig' records. And I want to have control over the synonyms, perhaps in some sort of ref table ? Thanks in Advance for any ideas.
Re: [GENERAL] OLAP
according to what you write pentaho best fits your needs On Tue, Aug 27, 2013 at 5:52 PM, Pavel Stehule wrote: > > Dne 28. 8. 2013 0:05 "Jerry Sievers" napsal(a): > > > > > Alban Hertroys writes: > > > > > On Aug 27, 2013, at 19:07, Paul Jungwirth > wrote: > > > > > >> Hi Alban, > > >> > > >> I think Postgres works great for OLAP work > > > > > > What do you base that on? > > > I don't really doubt that the database layer is up to the task, I'm > much more worried about maintaining the model and the cube data and all > that typical OLAP stuff that I've mostly just heard about. > > > > > >> , and Amazon's Red Shift is > > >> even based on Postgres. 100 million sales should be not problem at > > >> all. My understanding is Greenplum also builds on top of Postgres, so > > >> if you ever do outgrow your Postgres installation, that would be an > > >> easy migration path. > > > > > > What's the benefit of GreenPlum for OLAP? Isn't it a columnar > database? And a pretty old fork of Postgres at that? > > > GreenPlum has a pretty steep price-tag too. > > > > Vertica is another case of an analytics focused platform that dirived > > from Postgres, version 8.0 IIR > > vertica use a similar interface, but internally use nothing from pg. it > was written from zero. > > > It was, by the time I first looked at it back about 4 years ago, only > > superficially resembling Postgres. Performance was absolutely > > shocking in terms of how quickly it processed queries over certain > > kinds of data... and for which the set of expected queries to be run > > over same was identifiable in advance. > > > > Sample queries are given to a moddeler which in turn creates a set of > > "projections" which are physical manifestations of the backend storage > > intended to optimize for this specialized workload. > > > > Vertica and I presume Green Plumb are *not* well suited for an OLTP > > role so it takes a fair amount of learning to make good use of them. > > > > Just FWIW. > > > > > I didn't really look into Red Shift, perhaps I should… > > > > > > Anyway, I'm not at all sure I want to use some product that's heavily > modified from Postgres. If it is, it has to be really really good. > > > > > >> One Postgres OLAP tool to consider is Pentaho. > > >> That will save you lots of time around ETL, ad-hoc reporting, and > > >> other standard OLAP functionality. > > > > > > How is Pentaho an OLAP tool? Aren't you mixing up a few things? > > > We already use Pentaho for ETL, so I'm a bit familiar with it. Why do > you consider it suitable for managing an OLAP database? > > > > > > How would Pentaho manage cube rollup triggers, business models, > dimensions and stuff like that? > > > We don't want to hand code those, that's far too error-prone and far > too much work to keep track of. That stuff needs to be automated, > preferably similar to what we're used to from Gentia (well, not me - I > can't make heads or tails of Gentia, but the person who asked me about PG's > suitability has been developing with it for years). That's what we're > comparing to. > > > > > > Unfortunately, I can't find any decent information about Gentia for > reference. Apparently these days they're all about NoSQL databases and > such. That's not what we have - I guess the clunky GUI is a hint that it's > something of the past... > > > > > > > > > BTW, please don't top-post. > > > > > > > > >> On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys > wrote: > > >>> Hi all, > > >>> > > >>> At work we have a system that's built on top of a proprietary OLAP > database > > >>> system (Rocket Gentia). We're looking into replacing that system with > > >>> something that's still supported and in such a way that we can also > access > > >>> the data from our reporting software (WebFOCUS by information > builders). > > >>> > > >>> Because I'm always advocating PG, I was asked whether PG would be > suitable > > >>> for this, but I'm not really familiar enough with OLAP databases to > be able > > >>> to comment on that. > > >>> > > >>> I got three prerequisites for a solution, namely: > > >>> 1. It must contain correct information, > > >>> 2. It must be fast and > > >>> 3. It must be easy to maintain the data and the models; that's a > task for a > > >>> 3rd party back-end application, but it would be helpful to be able > to name > > >>> something to the higher-ups. > > >>> > > >>> Next to that, because we're also going to access the system using our > > >>> reporting software (which is read-only access), it would be best if > the > > >>> entire data model and all the business rules are stored inside the > database > > >>> so that we're looking at the data in the same way that the > "back-end" sees > > >>> it. > > >>> > > >>> For size, we're looking at about 20 years of sales and shipment data > all > > >>> over the world (although mostly in Europe) for about 5mln sold > products per > > >>> year. > > >>> > > >>> I suspect there might be some "middleware" that handles the model
Re: [GENERAL] OLAP
Checkout the Saiku, the future of Open Source Interactive OLAP( http://analytical-labs.com ) On Tue, Aug 27, 2013 at 8:34 PM, Carlos Saritama wrote: > according to what you write pentaho best fits your needs > > > On Tue, Aug 27, 2013 at 5:52 PM, Pavel Stehule wrote: > >> >> Dne 28. 8. 2013 0:05 "Jerry Sievers" napsal(a): >> >> > >> > Alban Hertroys writes: >> > >> > > On Aug 27, 2013, at 19:07, Paul Jungwirth < >> p...@illuminatedcomputing.com> wrote: >> > > >> > >> Hi Alban, >> > >> >> > >> I think Postgres works great for OLAP work >> > > >> > > What do you base that on? >> > > I don't really doubt that the database layer is up to the task, I'm >> much more worried about maintaining the model and the cube data and all >> that typical OLAP stuff that I've mostly just heard about. >> > > >> > >> , and Amazon's Red Shift is >> > >> even based on Postgres. 100 million sales should be not problem at >> > >> all. My understanding is Greenplum also builds on top of Postgres, so >> > >> if you ever do outgrow your Postgres installation, that would be an >> > >> easy migration path. >> > > >> > > What's the benefit of GreenPlum for OLAP? Isn't it a columnar >> database? And a pretty old fork of Postgres at that? >> > > GreenPlum has a pretty steep price-tag too. >> > >> > Vertica is another case of an analytics focused platform that dirived >> > from Postgres, version 8.0 IIR >> >> vertica use a similar interface, but internally use nothing from pg. it >> was written from zero. >> >> > It was, by the time I first looked at it back about 4 years ago, only >> > superficially resembling Postgres. Performance was absolutely >> > shocking in terms of how quickly it processed queries over certain >> > kinds of data... and for which the set of expected queries to be run >> > over same was identifiable in advance. >> > >> > Sample queries are given to a moddeler which in turn creates a set of >> > "projections" which are physical manifestations of the backend storage >> > intended to optimize for this specialized workload. >> > >> > Vertica and I presume Green Plumb are *not* well suited for an OLTP >> > role so it takes a fair amount of learning to make good use of them. >> > >> > Just FWIW. >> > >> > > I didn't really look into Red Shift, perhaps I should… >> > > >> > > Anyway, I'm not at all sure I want to use some product that's heavily >> modified from Postgres. If it is, it has to be really really good. >> > > >> > >> One Postgres OLAP tool to consider is Pentaho. >> > >> That will save you lots of time around ETL, ad-hoc reporting, and >> > >> other standard OLAP functionality. >> > > >> > > How is Pentaho an OLAP tool? Aren't you mixing up a few things? >> > > We already use Pentaho for ETL, so I'm a bit familiar with it. Why do >> you consider it suitable for managing an OLAP database? >> > > >> > > How would Pentaho manage cube rollup triggers, business models, >> dimensions and stuff like that? >> > > We don't want to hand code those, that's far too error-prone and far >> too much work to keep track of. That stuff needs to be automated, >> preferably similar to what we're used to from Gentia (well, not me - I >> can't make heads or tails of Gentia, but the person who asked me about PG's >> suitability has been developing with it for years). That's what we're >> comparing to. >> > > >> > > Unfortunately, I can't find any decent information about Gentia for >> reference. Apparently these days they're all about NoSQL databases and >> such. That's not what we have - I guess the clunky GUI is a hint that it's >> something of the past... >> > > >> > > >> > > BTW, please don't top-post. >> > > >> > > >> > >> On Tue, Aug 27, 2013 at 8:12 AM, Alban Hertroys >> wrote: >> > >>> Hi all, >> > >>> >> > >>> At work we have a system that's built on top of a proprietary OLAP >> database >> > >>> system (Rocket Gentia). We're looking into replacing that system >> with
[GENERAL] Schema, databse, or tables in different system folder
Is there a way to create a database or a table of a database in its own folder? We are looking for ways to backup the sytem files of the database to tape and one to exclude some tables from this backup. We can selectively backup folders of the file system so we figure that if we can create a schema or database or table in its own folder, we can backup our database and exclude the tables selectively. We are using Linux RedHat. Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrading Database: need to dump and restore?
If I were to upgrade the database version, would I need to dump and restore all the data? -- 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] Upgrading Database: need to dump and restore?
For large databases or tables, this will take a long time to do. Are there any alternatives to benefit from the improved versions of PostgreSQL without having to dump some large tables that never change? "Bill Moran" wrote in message news:20090603114544.434edcce.wmo...@potentialtech.com... > In response to "Carlos Oliva" : > >> If I were to upgrade the database version, would I need to dump and >> restore >> all the data? > > If you upgrade patch releases (i.e. from 8.3.4 -> 8.3.5) then usually no, > but see the release notes for the versions you're upgrading, because there > are occasionally exceptions. > > If you're updating to a major release (8.2.x -> 8.3.x), then yes. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > 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] Schema, databse, or tables in different system folder
Thank you for your response Chris. It is helping us a great deal to understand the issues around backups. Would any of the pg_xlog, pg_clog, etc change for a table that has a stable structure and data? That is, the table undergoes several inserts and then it is never updated. "Chris Browne" wrote in message news:87prdlgurv@dba2.int.libertyrms.com... > "Carlos Oliva" writes: >> Would the backup be unrecoverable if I shutdown the databse first? > > If the backup includes pg_xlog and pg_clog, as well as all of the > database metadata files, then whatever portions *are* included are > likely to be somewhat usable. > > The portions not included in the backup will obviously not be usable. > > But supposing your backup does not include *all* metadata (e.g. - > pg_catalog), pg_xlog and pg_clog, then it is unlikely that you'll be > able to recover *anything at all* from it. > > The intent is that filesystem backups only work for complete backup > and restoration of an entire database cluster. > > Backing up *part* of your filesystem is Not Recommended in the > documentation. If you set a policy of doing so, you have to accept > that you are doing this contrary to documented recommendations, and at > considerable peril to your data. > -- > (reverse (concatenate 'string "ofni.secnanifxunil" "@" "enworbbc")) > http://cbbrowne.com/info/multiplexor.html > Rules of the Evil Overlord #25. "No matter how well it would perform, > I will never construct any sort of machinery which is completely > indestructible except for one small and virtually inaccessible > vulnerable spot." <http://www.eviloverlord.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] Upgrading Database: need to dump and restore?
Would I need to upgrade the entire cluster or can I just upgrade database wise? ""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906030845t526db574q464c17072fadd...@mail.gmail.com... > if you upgrade to different major version, yes. If between minor > releases (say 8.1.4->8.1.5) than no, with few exceptions. Make sure > you read release notes between versions to find out. > > -- > 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] Upgrading Database: need to dump and restore?
Woudl it be possible to keep the current postgresql version running in a different port, install a new version of postgresql, and copy the data from one version to the other while both versions are running? This might give us time to copy the tables and databases one at a time and reconfigure the database access for parts of the application until we complete the migration to the new version. ""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906031157v32fb9810j9476fd950e494...@mail.gmail.com... > On Wed, Jun 3, 2009 at 5:11 PM, Carlos Oliva wrote: >> Would I need to upgrade the entire cluster or can I just upgrade database >> wise? > Obviously whole installation has to be dumped and restored. Wether you > are going to dump database by database, or do pg_dumpall, is up to > you,and mainly depends on cluster size. > It's just that binary format of stored files changes from major > release to release, and postgresql will refuse to start with old > format data directory (where it stores all databases). > > -- > GJ > > -- > 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] Upgrading Database: need to dump and restore?
""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906031214k3dfaa4b2mae5733d7345f7...@mail.gmail.com... On Wed, Jun 3, 2009 at 8:03 PM, Carlos Oliva wrote: > Woudl it be possible to keep the current postgresql version running in a > different port, install a new version of postgresql, and copy the data > from > one version to the other while both versions are running? This might give > us time to copy the tables and databases one at a time and reconfigure the > database access for parts of the application until we complete the > migration > to the new version. Yes it is possible. You can setup new version of postgresql with new data directory, in different location (say /var/pg_new/data), run it on different port, and have multiple postgresql installations running side by side. What OS do you run, and where do you get postgresql binaries from. _Please stop replying on top_. Top posting is a baad thing on mailing lists. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Probably this will work for us. We are using RedHat and I think that we may be getting binaries from a couple of sources. -- 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] Upgrading Database: need to dump and restore?
Can the synchronization with Slony run while the old database is still being updated daily? I am wondering if we can just let Slony run until the databases are fully synchronized and then switch databases. "Bill Moran" wrote in message news:20090603153556.f05e6bd2.wmo...@potentialtech.com... > In response to Grzegorz Jaskiewicz : > >> On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran >> wrote: >> > In response to "Carlos Oliva" : >> > >> >> Woudl it be possible to keep the current postgresql version running in >> >> a >> >> different port, install a new version of postgresql, and copy the data >> >> from >> >> one version to the other while both versions are running? This might >> >> give >> >> us time to copy the tables and databases one at a time and reconfigure >> >> the >> >> database access for parts of the application until we complete the >> >> migration >> >> to the new version. >> > >> > Your best bet would be to install Slony-I. One of the main design goals >> > for Slony is to allow interruption-free upgrades. >> >> I don't think it is "easy", but will do if you need to synchronize >> data before switching. > > "easy" was not the point. I gathered from his comments that downtime is > an issue, and I know (from experience) that Slony provides the ability > to upgrade with almost no downtime, even with very large databases. > > His plan of migrating tables one at a time may work, but it's > frighteningly > error-prone. If he copies a table, how does he know the data hasn't > changed during the copy? What if he doesn't quite get all the clients > switched over all at once? How do you do a JOIN when one table is in > one database and the other somewhere else? > > Once the DBs are in sync with Slony, a single command will switch to the > new server. If it doesn't go well (because he has a client compatibility > problem, for example -- casts anyone?) it's a simple process to switch > back, all with a guarantee that his data will never be lost, out of sync > or corrupted. > > And if his application requires small downtime windows, this is a process > he will benefit from getting familiar with anyway. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > 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] Upgrading Database: need to dump and restore?
Thank you for your response Grzegorx. It is helping us a great deal to understand the issues around backups. Would any of the pg_xlog, pg_clog, etc change for a table that has a stable structure and data? That is, the table undergoes several inserts and then it is never updated ""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906031217h2a0bfe0t674f266d4397e...@mail.gmail.com... On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran wrote: > In response to "Carlos Oliva" : > >> Woudl it be possible to keep the current postgresql version running in a >> different port, install a new version of postgresql, and copy the data >> from >> one version to the other while both versions are running? This might give >> us time to copy the tables and databases one at a time and reconfigure >> the >> database access for parts of the application until we complete the >> migration >> to the new version. > > Your best bet would be to install Slony-I. One of the main design goals > for Slony is to allow interruption-free upgrades. I don't think it is "easy", but will do if you need to synchronize data before switching. -- GJ -- 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] Upgrading Database: need to dump and restore?
Thank you for your response Bill. It is helping us a great deal to understand the issues around backups. Would any of the pg_xlog, pg_clog, etc change for a table that has a stable structure and data? That is, the table undergoes several inserts and then it is never updated "Bill Moran" wrote in message news:20090603161817.131e706e.wmo...@potentialtech.com... > In response to "Carlos Oliva" : > >> Can the synchronization with Slony run while the old database is still >> being >> updated daily? I am wondering if we can just let Slony run until the >> databases are fully synchronized and then switch databases. > > Yes, and yes. That's exactly what Slony is for. > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > > -- > 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] Upgrading Database: need to dump and restore?
Thanks again Grzgorz for your expedicious reply. Would anything else change in the database for a table once it ceases to be updated? We have several tables for which a number of records is inserted and never again updated -- data is never updated, deleted, or inserted again. We are moving these tables into their own tablespace and getting some kind of snapshot copy of these tables. We were thinking that we can restore them in the future without having to back them up several times. ""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906040446s6b5a38b7ka1a4b1f3a4ce...@mail.gmail.com... pg_xlog and clog is something that is used during operation, and for point in time recovery. It doesn't go to database dump at all, not needed. On Thu, Jun 4, 2009 at 12:32 PM, Carlos Oliva wrote: > Thank you for your response Grzegorx. It is helping us a great deal to > understand the issues around backups. Would any of the pg_xlog, pg_clog, > etc change for a table that has a stable structure and data? That is, the > table undergoes several inserts and then it is never updated > ""Grzegorz Jaskiewicz"" wrote in message > news:2f4958ff0906031217h2a0bfe0t674f266d4397e...@mail.gmail.com... > On Wed, Jun 3, 2009 at 8:14 PM, Bill Moran > wrote: >> In response to "Carlos Oliva" : >> >>> Woudl it be possible to keep the current postgresql version running in a >>> different port, install a new version of postgresql, and copy the data >>> from >>> one version to the other while both versions are running? This might >>> give >>> us time to copy the tables and databases one at a time and reconfigure >>> the >>> database access for parts of the application until we complete the >>> migration >>> to the new version. >> >> Your best bet would be to install Slony-I. One of the main design goals >> for Slony is to allow interruption-free upgrades. > > I don't think it is "easy", but will do if you need to synchronize > data before switching. > > -- > GJ > > -- > 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 > -- GJ -- 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] Upgrading Database: need to dump and restore?
In which state do we need to put the db? We can use both types of backup strategy. We can pg_dump the table and copy the tablespace folder along with anyhting else that we may need. ""Grzegorz Jaskiewicz"" wrote in message news:2f4958ff0906040518l190af73dpff180755d567f...@mail.gmail.com... On Thu, Jun 4, 2009 at 1:07 PM, Carlos Oliva wrote: > Thanks again Grzgorz for your expedicious reply. Would anything else > change > in the database for a table once it ceases to be updated? We have several > tables for which a number of records is inserted and never again > updated -- > data is never updated, deleted, or inserted again. We are moving these > tables into their own tablespace and getting some kind of snapshot copy of > these tables. We were thinking that we can restore them in the future > without having to back them up several times. Well, if you do pg_dump - that data will be there. If you do copy of data directory, that isn't going to work, unless you put db in proper state. if you do that, you need to copy pg_xlog directory with it. So you have to specify which backup strategy you are going to use. -- GJ -- 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
