Re: Performance issues during backup

[Laurenz Albe]

Dylan Luong wrote:
> We perform nighty base backup of our production PostgreSQL instance. We have 
> a script that basically puts the instance
> into back mode and then backs up (tar) the /Data directory and then takes it 
> out of backup mode.
> Ie,
> psql -c "SELECT pg_start_backup('${DATE}');"
> tar -cvf - ${DATA_DIR} --exclude ${DATA_DIR}/pg_log | split -d -b 
> $TAR_SPLIT_SIZE - ${BACKUP_DIR}/${BACKUP_NAME}
> psql -c "SELECT pg_stop_backup();"
>  
> The size of our database is about 250GB and it usually takes about 1 hour to 
> backup.
> During this time, we have performance issue where queries can take up to 
> 15secs to return where normally it takes 2 to 3 seconds.
> During this time (1:30am) usage is low (less than 10 users) on the system.
>  
> Has anyone experience the same problem and any suggestions where to look at 
> to resolve the problem?

The "tar" is probably taking up too much I/O bandwidth.

Assuming this is Linux, you could run it with

  ionice -c 2 -n 7 tar ...

or

  ionice -c 3 tar ...

Of course then you can expect the backup to take more time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Performance issues during backup

[Rene Romero Benavides]

What about sending the backup to a different server? through ssh / rsync or
something, that would save lots of IO activity

2018-02-20 2:02 GMT-06:00 Laurenz Albe :

> Dylan Luong wrote:
> > We perform nighty base backup of our production PostgreSQL instance. We
> have a script that basically puts the instance
> > into back mode and then backs up (tar) the /Data directory and then
> takes it out of backup mode.
> > Ie,
> > psql -c "SELECT pg_start_backup('${DATE}');"
> > tar -cvf - ${DATA_DIR} --exclude ${DATA_DIR}/pg_log | split -d -b
> $TAR_SPLIT_SIZE - ${BACKUP_DIR}/${BACKUP_NAME}
> > psql -c "SELECT pg_stop_backup();"
> >
> > The size of our database is about 250GB and it usually takes about 1
> hour to backup.
> > During this time, we have performance issue where queries can take up to
> 15secs to return where normally it takes 2 to 3 seconds.
> > During this time (1:30am) usage is low (less than 10 users) on the
> system.
> >
> > Has anyone experience the same problem and any suggestions where to look
> at to resolve the problem?
>
> The "tar" is probably taking up too much I/O bandwidth.
>
> Assuming this is Linux, you could run it with
>
>   ionice -c 2 -n 7 tar ...
>
> or
>
>   ionice -c 3 tar ...
>
> Of course then you can expect the backup to take more time.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: stored procedure call is not working with "select procedure()" option

[Adrian Klaver]

On 02/19/2018 11:13 PM, Abhra Kar wrote:

Hi ,
          I have a stored procedure name "procedure()". Which I am 
calling by --


Session sess = (Session)entityManager.getDelegate(); //entityManager is 
javax.persistent.EntityManager[ Properly Initialise]

               sess.createSQLQuery("select procedure()");

procedure is containing some truncate queries like -- EXECUTE('truncate 
table abc');


It's not throwing any exception but not executing the procedure.Using 
oracle query -- sess.createSQLQuery("{ call procedure() 
}").executeUpdate();   procedure execution is working fine.


What's need to be change here.


More information would help:

1) Postgres version.

2) JDBC driver and version?

3) Any relevant information from the Postgres logs.

The forthcoming Postgres version 11 will have stored procedures, but at 
the moment Postgres has only stored functions.




Thanks and Regards,
Abhra



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: stored procedure call is not working with "select procedure()" option

[Adrian Klaver]

On 02/20/2018 07:35 AM, Abhra Kar wrote:

Please also reply to list so more people can see and answer.
Ccing list



On Tue, Feb 20, 2018 at 8:46 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 02/19/2018 11:13 PM, Abhra Kar wrote:

Hi ,
           I have a stored procedure name "procedure()". Which I
am calling by --

Session sess = (Session)entityManager.getDelegate();
//entityManager is javax.persistent.EntityManager[ Properly
Initialise]
                sess.createSQLQuery("select procedure()");

procedure is containing some truncate queries like --
EXECUTE('truncate table abc');

It's not throwing any exception but not executing the
procedure.Using oracle query -- sess.createSQLQuery("{ call
procedure() }").executeUpdate();   procedure execution is
working fine.

What's need to be change here.


More information would help:

1) Postgres version.

2) JDBC driver and version?

3) Any relevant information from the Postgres logs.

The forthcoming Postgres version 11 will have stored procedures, but
at the moment Postgres has only stored functions.





Postgres Version -- 9.5

JDBC Driver - org.postgresql.Driver

JDVC driver version and relavant postgres logs didn't get.

Regards,
Abhra



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: stored procedure call is not working with "select procedure()" option

[Abhra Kar]

On Tue, Feb 20, 2018 at 9:21 PM, Adrian Klaver 
wrote:

> On 02/20/2018 07:35 AM, Abhra Kar wrote:
>
> Please also reply to list so more people can see and answer.
> Ccing list
>
>>
>>
>> On Tue, Feb 20, 2018 at 8:46 PM, Adrian Klaver > > wrote:
>>
>> On 02/19/2018 11:13 PM, Abhra Kar wrote:
>>
>> Hi ,
>>I have a stored procedure name "procedure()". Which I
>> am calling by --
>>
>> Session sess = (Session)entityManager.getDelegate();
>> //entityManager is javax.persistent.EntityManager[ Properly
>> Initialise]
>> sess.createSQLQuery("select procedure()");
>>
>> procedure is containing some truncate queries like --
>> EXECUTE('truncate table abc');
>>
>> It's not throwing any exception but not executing the
>> procedure.Using oracle query -- sess.createSQLQuery("{ call
>> procedure() }").executeUpdate();   procedure execution is
>> working fine.
>>
>> What's need to be change here.
>>
>>
>> More information would help:
>>
>> 1) Postgres version.
>>
>> 2) JDBC driver and version?
>>
>> 3) Any relevant information from the Postgres logs.
>>
>> The forthcoming Postgres version 11 will have stored procedures, but
>> at the moment Postgres has only stored functions.
>>
>>
>
>> Postgres Version -- 9.5
>>
>> JDBC Driver - org.postgresql.Driver
>>
>> JDVC driver version and relavant postgres logs didn't get.
>>
>> Regards,
>> Abhra
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Please provide me the list email.I don't know about it.

Thanks

Re: stored procedure call is not working with "select procedure()" option

[Adrian Klaver]

On 02/20/2018 08:17 AM, Abhra Kar wrote:



On Tue, Feb 20, 2018 at 9:21 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:





Please provide me the list email.I don't know about it.


The list email is the one you used already:

pgsql-gene...@postgresql.org

I suspect the problem is that when you replied to my first post you did 
Reply not Reply All. This picked up my email, but not the list email.




Thanks



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: stored procedure call is not working with "select procedure()" option

[Abhra Kar]

On Tue, Feb 20, 2018 at 9:52 PM, Adrian Klaver 
wrote:

> On 02/20/2018 08:17 AM, Abhra Kar wrote:
>
>>
>>
>> On Tue, Feb 20, 2018 at 9:21 PM, Adrian Klaver > > wrote:
>>
>
>
>>
>> Please provide me the list email.I don't know about it.
>>
>
> The list email is the one you used already:
>
> pgsql-gene...@postgresql.org
>
> I suspect the problem is that when you replied to my first post you did
> Reply not Reply All. This picked up my email, but not the list email.
>
>
>> Thanks
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Postgres Version -- 9.5

JDBC Driver - org.postgresql.Driver

JDVC driver version and relavant postgres logs didn't get.

Regards,
Abhra

Re: stored procedure call is not working with "select procedure()" option

[Alban Hertroys]

> On 20 Feb 2018, at 8:13, Abhra Kar  wrote:
> 
> Hi , 
>  I have a stored procedure name "procedure()". Which I am calling by 
> -- 
> 
> Session sess = (Session)entityManager.getDelegate(); //entityManager is 
> javax.persistent.EntityManager[ Properly Initialise]
>   sess.createSQLQuery("select procedure()");

…

> It's not throwing any exception but not executing the procedure.Using oracle 
> query -- sess.createSQLQuery("{ call procedure() }").executeUpdate();   
> procedure execution is working fine.

Seems to me you are forgetting to execute the query, like in your Oracle 
example. IIRC, calling execute() is how to execute a statement that returns a 
ResultSet in Java.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Crear Una FUNTION usando ROW_NUMBER

[yeli]

Buen día, estoy intento crear un código concatenada a través de una función
que haga lo siguiente:

Tengo una base de datos postgresql que esta asociada a un software GIS.

Basicamente es una tabla donde se guardan datos sobre centros poblados,
estos centros poblados tienen datos que al concatenarlos se intenta crear
códigos únicos.

Id,ccentpob,nombre,cod_cent,sim_cent.

Los datos de las tabla son asi:

1,’’ , Estado Vargas,24,VAR;

2,,’’,Estado Vargas,24,VAR;

3,’’;Estado Amazonas,02,AMA;

4,’’;Estado Amazonas,02,AMA;

5,’’;Estado Amazonas,02,AMA;

6,’’;Estado Amazonas,02,AMA;

7,’’;Estado Anzoátegui,03,ANZ;


Utilizando una sentencia ROW_NUMBER, logro numerar de cada Centro Poblado
en función a la entidad federal que pertenecen:

SELEC Id,ccentpob,nombre,cod_cent,sim_cent, ROW_NUMBER ( ) OVER (PARTITION
BY cent) el cual me permite, llevar una secuencia de los datos en función a
sus diferentes centros poblados. Por lo que esta consulta queda asi:

Id,ccentpob,nombre,cod_cent,sim_cent,row_number

1,’’ , Estado Vargas,24,VAR,1;

2,,’’,Estado Vargas,24,VAR,2;

3,’’;Estado Amazonas,02,AMA,1;

4,’’;Estado Amazonas,02,AMA,2;

5,’’;Estado Amazonas,02,AMA,3;

6,’’;Estado Amazonas,02,AMA,4;

7,’’;Estado Anzoátegui,03,ANZ,1;

Hasta aquí todo funciona perfecto. Al probar esto como una consulta intento
ponerlo en un funcion que permita crear un trigger, que se va a activar
cada que que al insertar un dato desde el SIG, se enumere el dato siguiendo
la secuencia. Lo que me va a permita concatenar los datos para crear el
ccentpob que es el único dato que me falta.

La funcion que utilizo es la siguiente:


CREATE TRIGGER row_number AFTER INSERT

ON nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados

FOR EACH ROW EXECUTE PROCEDURE nombres_geograficos.row_number();


CREATE OR REPLACE FUNCTION nombres_geograficos.row_number() RETURNS TRIGGER
AS '

DECLARE

row_number numeric;

BEGIN

IF NEW.cent IS NOT NULL THEN

row_number := (SELECT ROW_NUMBER ( ) OVER (PARTITION BY cent)

FROM nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados);

END IF;

RETURN NEW;

END'

language PLPGSQL;


Pero cuando intento usar el SIG que es Qgis 2.18 me da un erro… y no me
almacena el dato.

Se y estoy consiente que el ROW_NUMBER es un dato temporal, pero necesito
que este dato sea parte de la tabla para así poder ejecutar mi trigger con
éxito.

Alguien tiene alguna sugerencia.

Se lo agradecería de verdad.

Re: stored procedure call is not working with "select procedure()" option

[Abhra Kar]

On Tue, Feb 20, 2018 at 10:45 PM, Alban Hertroys  wrote:

>
> > On 20 Feb 2018, at 8:13, Abhra Kar  wrote:
> >
> > Hi ,
> >  I have a stored procedure name "procedure()". Which I am
> calling by --
> >
> > Session sess = (Session)entityManager.getDelegate(); //entityManager is
> javax.persistent.EntityManager[ Properly Initialise]
> >   sess.createSQLQuery("select procedure()");
>
> …
>
> > It's not throwing any exception but not executing the procedure.Using
> oracle query -- sess.createSQLQuery("{ call procedure()
> }").executeUpdate();   procedure execution is working fine.
>
> Seems to me you are forgetting to execute the query, like in your Oracle
> example. IIRC, calling execute() is how to execute a statement that returns
> a ResultSet in Java.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
So What should be the query ---
sess.createSQLQuery("select procedure()").execute();

Something like this?

Thanks
Abhra

Re: Performance issues during backup

[Stephen Frost]

Greetings,

* Dylan Luong (dylan.lu...@unisa.edu.au) wrote:
> We perform nighty base backup of our production PostgreSQL instance. We have 
> a script that basically puts the instance into back mode and then backs up 
> (tar) the /Data directory and then takes it out of backup mode.
> Ie,
> psql -c "SELECT pg_start_backup('${DATE}');"
> tar -cvf - ${DATA_DIR} --exclude ${DATA_DIR}/pg_log | split -d -b 
> $TAR_SPLIT_SIZE - ${BACKUP_DIR}/${BACKUP_NAME}
> psql -c "SELECT pg_stop_backup();"

This doesn't do anything to ensure that the backup files were actually
written out to disk, meaning that you might lose files or portions of
files if the system crashed shortly after this completed.

You don't mention anything about archive_command or how you are handling
WAL?  Note that you must have the WAL generated between the start backup
and the stop backup for the backup to be valid.  If you do have an
archive command and it looks anything like what you have for your backup
command, you likely have the same issue there where the WAL isn't being
synced out to disk and therefore you might lose some WAL on a crash and
not be able to do PITR or possibly utilize a given backup.

You also don't meniton anything about checking that you have all of the
WAL needed between the start/stop before considering the backup valid.
I'd strongly suggest you look into a designed-for-PG backup solution
instead of trying to roll your own and trying to fix all of these
issues.  There's a few of them out there that I'd suggest considering-
pgBackRest being favorite (but I'm biased), barman, or wal-e (maybe
wal-g).

> The size of our database is about 250GB and it usually takes about 1 hour to 
> backup.
> During this time, we have performance issue where queries can take up to 
> 15secs to return where normally it takes 2 to 3 seconds.
> During this time (1:30am) usage is low (less than 10 users) on the system.
> 
> Has anyone experience the same problem and any suggestions where to look at 
> to resolve the problem?

Some of the backup tools for PG support throttling to slow down the
backup, but my recommendation here would be to stand up a replica and
have the bulk of the data pulled from the replica for a given backup
instead of doing it from the primary.  As mentioned down-thread, you're
likely saturating either your i/o bandwidth or your network bandwidth
(if you're going to an NFS mount or similar).

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Crear Una FUNTION usando ROW_NUMBER

[Hellmuth Vargas]

Hola Yeli

Respondo entre lineas

El 20 de febrero de 2018, 12:26, yeli escribió:

> Buen día, estoy intento crear un código concatenada a través de una
> función que haga lo siguiente:
>
> Tengo una base de datos postgresql que esta asociada a un software GIS.
>
> Basicamente es una tabla donde se guardan datos sobre centros poblados,
> estos centros poblados tienen datos que al concatenarlos se intenta crear
> códigos únicos.
>
> Id,ccentpob,nombre,cod_cent,sim_cent.
>
> Los datos de las tabla son asi:
>
> 1,’’ , Estado Vargas,24,VAR;
>
> 2,,’’,Estado Vargas,24,VAR;
>
> 3,’’;Estado Amazonas,02,AMA;
>
> 4,’’;Estado Amazonas,02,AMA;
>
> 5,’’;Estado Amazonas,02,AMA;
>
> 6,’’;Estado Amazonas,02,AMA;
>
> 7,’’;Estado Anzoátegui,03,ANZ;
>
>
> Utilizando una sentencia ROW_NUMBER, logro numerar de cada Centro Poblado
> en función a la entidad federal que pertenecen:
>
> SELEC Id,ccentpob,nombre,cod_cent,sim_cent, ROW_NUMBER ( ) OVER
> (PARTITION BY cent) el cual me permite, llevar una secuencia de los datos
> en función a sus diferentes centros poblados. Por lo que esta consulta
> queda asi:
>
> Id,ccentpob,nombre,cod_cent,sim_cent,row_number
>
> 1,’’ , Estado Vargas,24,VAR,1;
>
> 2,,’’,Estado Vargas,24,VAR,2;
>
> 3,’’;Estado Amazonas,02,AMA,1;
>
> 4,’’;Estado Amazonas,02,AMA,2;
>
> 5,’’;Estado Amazonas,02,AMA,3;
>
> 6,’’;Estado Amazonas,02,AMA,4;
>
> 7,’’;Estado Anzoátegui,03,ANZ,1;
>
> Hasta aquí todo funciona perfecto. Al probar esto como una consulta
> intento ponerlo en un funcion que permita crear un trigger, que se va a
> activar cada que que al insertar un dato desde el SIG, se enumere el dato
> siguiendo la secuencia. Lo que me va a permita concatenar los datos para
> crear el ccentpob que es el único dato que me falta.
>
> La funcion que utilizo es la siguiente:
>
>
> CREATE TRIGGER row_number AFTER INSERT
>
> ON nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados
>
> FOR EACH ROW EXECUTE PROCEDURE nombres_geograficos.row_number();
>
>
>
Creo que el Trigger debería ser BEFORE no AFTER si se quiere modificar un
campo para su inserción.


> CREATE OR REPLACE FUNCTION nombres_geograficos.row_number() RETURNS
> TRIGGER AS '
>
> DECLARE
>
> row_number numeric;
>
> BEGIN
>
> IF NEW.cent IS NOT NULL THEN
>
> row_number := (SELECT ROW_NUMBER ( ) OVER (PARTITION BY cent)
>


El  row_number es una función, no es una variable que  se pueda establecer


> FROM nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados);
>


nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados: la tabla esta
particionada?  todos los datos de  vargas  esta en una única tabla? no
tenida sentido estar enumerándolos


> END IF;
>
> RETURN NEW;
>
> END'
>
> language PLPGSQL;
>
>
> Pero cuando intento usar el SIG que es Qgis 2.18 me da un erro… y no me
> almacena el dato.
>
> Se y estoy consiente que el ROW_NUMBER es un dato temporal, pero necesito
> que este dato sea parte de la tabla para así poder ejecutar mi trigger con
> éxito.
>
> Alguien tiene alguna sugerencia.
>
> Se lo agradecería de verdad.
>
>


-- 
Cordialmente,
Hellmuth I. Vargas S.

Re: query performance

[PT]

On Sun, 18 Feb 2018 00:35:18 +0100
hmidi slim  wrote:

> Hi,
> I have two tables: establishment which contains these columns: id, name,
> longitude, latitude, geom (Geometric column)
> Product contains: id, name, establishment_id
> First of all I want to select the establishment within a radius.
> I run this query:
> select e.name, e1.name
> from establishment as e, establishment as e1
> where e.id <> e1.id
> and e1.id = 1
> and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
> 1000)
> 
> The result of this query returns all the establishment within a radius 1KM
> from from a given establishment which has an id = 1.
> 
> After that I want to get the product's name of each establishment from the
> query's result.
> 
> Is there an other optimized solution to make a query such this:
> select * from (
> select e.name, e1.name, e.id
> from establishment as e, establishment as e1
> where e.id <> e1.id
> and e1.id = 1
> and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography,
> 1000)) as tmp inner join product as p on p.establishment_id = tmp.id

Did you not see my earlier response?

-- 
Bill Moran

Re: Crear Una FUNTION usando ROW_NUMBER

[yeli]

Buen dia, ya corregui la primera parte, me fije después que envié el correo
y lo
Los datos en la tabla vargas, no necesariamente son solo de vargas, tenia
la intención de hacer que, como estos datos son datos de producción la
intención era que todo los datos que pertenecieran a la mismo Huso
estuviesen en la misma tabla estos son datos pertenecientes a un sistema de
información Geográfica.
Pero siento que me estoy dando cabezazos y como que es mejor separa los
datos por estados y así se hace mas fácil crear los condigo únicos que
identificarían los nombres geográfico o topónimos.
Entiendo que ROW_NUMBER es una función pero en mi tabla tengo una columna
que se llama row_number y lo que quería era que el dato expresado en la
función fuese tomado por la variable, así podía concatenar las columnas que
necesitaba y crear los código ccentpob, que debería ser únicos. ya que eso
le daría una identidad al topónimo. y no tenia que hacer 25 tablas para las
25 entidades federales que componen el País. Podía trabajarlas por bloque
en funcion a que todos los datos del Huso 18 juntos sin importar la entidad
federal, y mediante vista separa las entidades federales, menos tablas y
creo que mas efectivo. Ya que los usuario solo pueden tener acceso a los
datos mediante los servicios WFS y los editores son los únicos que pueden
manipular los datos.
Ese es mi interés de que todos los datos estén juntos. por esta razón me
parecí que la usar ROW_NUMBER podía controlar mejor esto.
De nuevo si surge una idea para mejorar esta locura que intento lo
agradezco.

El 20 de febrero de 2018, 17:28, Hellmuth Vargas escribió:

> Hola Yeli
>
> Respondo entre lineas
>
> El 20 de febrero de 2018, 12:26, yeli escribió:
>
>> Buen día, estoy intento crear un código concatenada a través de una
>> función que haga lo siguiente:
>>
>> Tengo una base de datos postgresql que esta asociada a un software GIS.
>>
>> Basicamente es una tabla donde se guardan datos sobre centros poblados,
>> estos centros poblados tienen datos que al concatenarlos se intenta crear
>> códigos únicos.
>>
>> Id,ccentpob,nombre,cod_cent,sim_cent.
>>
>> Los datos de las tabla son asi:
>>
>> 1,’’ , Estado Vargas,24,VAR;
>>
>> 2,,’’,Estado Vargas,24,VAR;
>>
>> 3,’’;Estado Amazonas,02,AMA;
>>
>> 4,’’;Estado Amazonas,02,AMA;
>>
>> 5,’’;Estado Amazonas,02,AMA;
>>
>> 6,’’;Estado Amazonas,02,AMA;
>>
>> 7,’’;Estado Anzoátegui,03,ANZ;
>>
>>
>> Utilizando una sentencia ROW_NUMBER, logro numerar de cada Centro Poblado
>> en función a la entidad federal que pertenecen:
>>
>> SELEC Id,ccentpob,nombre,cod_cent,sim_cent, ROW_NUMBER ( ) OVER
>> (PARTITION BY cent) el cual me permite, llevar una secuencia de los datos
>> en función a sus diferentes centros poblados. Por lo que esta consulta
>> queda asi:
>>
>> Id,ccentpob,nombre,cod_cent,sim_cent,row_number
>>
>> 1,’’ , Estado Vargas,24,VAR,1;
>>
>> 2,,’’,Estado Vargas,24,VAR,2;
>>
>> 3,’’;Estado Amazonas,02,AMA,1;
>>
>> 4,’’;Estado Amazonas,02,AMA,2;
>>
>> 5,’’;Estado Amazonas,02,AMA,3;
>>
>> 6,’’;Estado Amazonas,02,AMA,4;
>>
>> 7,’’;Estado Anzoátegui,03,ANZ,1;
>>
>> Hasta aquí todo funciona perfecto. Al probar esto como una consulta
>> intento ponerlo en un funcion que permita crear un trigger, que se va a
>> activar cada que que al insertar un dato desde el SIG, se enumere el dato
>> siguiendo la secuencia. Lo que me va a permita concatenar los datos para
>> crear el ccentpob que es el único dato que me falta.
>>
>> La funcion que utilizo es la siguiente:
>>
>>
>> CREATE TRIGGER row_number AFTER INSERT
>>
>> ON nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados
>>
>> FOR EACH ROW EXECUTE PROCEDURE nombres_geograficos.row_number();
>>
>>
>>
> Creo que el Trigger debería ser BEFORE no AFTER si se quiere modificar un
> campo para su inserción.
>
>
>> CREATE OR REPLACE FUNCTION nombres_geograficos.row_number() RETURNS
>> TRIGGER AS '
>>
>> DECLARE
>>
>> row_number numeric;
>>
>> BEGIN
>>
>> IF NEW.cent IS NOT NULL THEN
>>
>> row_number := (SELECT ROW_NUMBER ( ) OVER (PARTITION BY cent)
>>
>
>
> El  row_number es una función, no es una variable que  se pueda establecer
>
>
>> FROM nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados);
>>
>
>
> nombres_geograficos.ba_2202_100101_n_vargas_centros_poblados: la tabla
> esta particionada?  todos los datos de  vargas  esta en una única tabla?
> no tenida sentido estar enumerándolos
>
>
>> END IF;
>>
>> RETURN NEW;
>>
>> END'
>>
>> language PLPGSQL;
>>
>>
>> Pero cuando intento usar el SIG que es Qgis 2.18 me da un erro… y no me
>> almacena el dato.
>>
>> Se y estoy consiente que el ROW_NUMBER es un dato temporal, pero necesito
>> que este dato sea parte de la tabla para así poder ejecutar mi trigger con
>> éxito.
>>
>> Alguien tiene alguna sugerencia.
>>
>> Se lo agradecería de verdad.
>>
>>
>
>
> --
> Cordialmente,
> Hellmuth I. Vargas S.
>
>
>

Getting a primitive numeric value from "DatumGetNumeric"?

[Demitri Muna]

Hi,

I’m writing a C extension for PostgreSQL. One possible input datatype for my 
function is a numeric array, e.g. ARRAY[[1.5,2.5],[3.5,4.5]]. I can use 
“DatumGetNumeric” to extract a “Numeric” data type from the data, but at some 
point I need to convert this to a number (e.g. double) so that I can do mathy 
things with it. How does one convert a “Numeric” to, say, a double?

I have a workaround in that I can pass this to my function:

ARRAY[[1.5,2.5],[3.5,4.5]]::float8[]

but I’d rather have the code do that instead of bothering the user to remember 
that.

Thanks,
Demitri

Re: Getting a primitive numeric value from "DatumGetNumeric"?

[Tom Lane]

Demitri Muna  writes:
> I’m writing a C extension for PostgreSQL. One possible input datatype for my 
> function is a numeric array, e.g. ARRAY[[1.5,2.5],[3.5,4.5]]. I can use 
> “DatumGetNumeric” to extract a “Numeric” data type from the data, but at some 
> point I need to convert this to a number (e.g. double) so that I can do mathy 
> things with it. How does one convert a “Numeric” to, say, a double?

If you want to work with doubles, why don't you declare the function as
taking doubles?

> I have a workaround in that I can pass this to my function:
> ARRAY[[1.5,2.5],[3.5,4.5]]::float8[]
> but I’d rather have the code do that instead of bothering the user to 
> remember that.

Well, the implicit coercions work in your favor in this particular case.
You can just do, eg,

regression=# create function foo(float8[]) returns float8 as
regression-# 'select $1[1]' language sql;
CREATE FUNCTION
regression=# select foo(array[1.1,1.2]);
 foo 
-
 1.1
(1 row)

or to emphasize that it is doing a conversion:

regression=# select foo(array[1.1,1.2]::numeric[]);
 foo 
-
 1.1
(1 row)


regards, tom lane