[GENERAL] NEW in after insert trugger contained incorrect data
Hello! I use Postgre-9.3.5 on windows7 x64. I use simple trigger for store some statistic data, it code: SELECT field IN variable FROM table WHERE ...; IF FOUND THEN UPDATE table SET field = ...; ELSE INSERT INTO table (field) VALUES(value); END IF; RETURN NULL; This trigger added as FOR EACH ROW on table2. In table2 I insert multiple data on one insert, e.g. INSERT INTO table2(field) VALUES(value0),(value1),(value2). Unfortunately trigger exit with error: ERROR: record new has no field value; Ok, for debug I add messages and what I see: NOTICE: *** START ***: (9994,5,2014-11-14 13:38:04.94,1970-01-02 06:02:38,0 ,6) // insert in table2 CONTEXT: SQL statement INSERT INTO trassa.cpu_load (device, device_timestamp, cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3) PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement // insert in table NOTICE: *** INSERT ***: (9994,5,2014-11-14 13:38:04.94,1970-01-02 06:02:38, 0,6) CONTEXT: SQL statement INSERT INTO trassa.cpu_load (device, device_timestamp, cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3) PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement NOTICE: *** START ***: (38,5,0,6,1970-01-02 06:02:38,2014-11-14 13:38:04.94 ,6,2014-11-14 13:38:04.94,6,1970-01-02 06:02:38,2014-11-14 13:38:04.94,6,1) Last START incorrect because NEW contained data from previews INSERT. Why and how can I solve this problem? Thank you and excuse me for my bad english. -- Best regards, Brilliantov Kirill Vladimirovich -- 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] Two instances of Postgres with single data directory
Michael, Appreciate your quick response. Thank you. I would like to know how is the Shared Disk Failover replication achieved if it is not possible to share the data directory ? I am referring to the 1st solution in the mentioned link, http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html Regards Dina -- View this message in context: http://postgresql.nabble.com/Two-instances-of-Postgres-with-single-data-directory-tp5826788p5826972.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Two instances of Postgres with single data directory
Hi, On Fri, 2014-11-14 at 02:57 -0700, dineshkaarthick wrote: I would like to know how is the Shared Disk Failover replication achieved if it is not possible to share the data directory ? I am referring to the 1st solution in the mentioned link, http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html It is not a replication -- it is HA solution. That particular one can be used along with Red Hat Cluster Suite (now called Red Hat High Availability Addon). A presentation is here: http://www.gunduz.org/download.php?dlid=208 Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Twitter: @DevrimGunduz , @DevrimGunduzTR signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Two instances of Postgres with single data directory
dineshkaarthick wrote: I would like to know how is the Shared Disk Failover replication achieved if it is not possible to share the data directory ? I am referring to the 1st solution in the mentioned link, http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html You have some cluster software that makes dead sure that one node is *not* running the database server before starting it on the other. Yours, Laurenz Albe -- 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] NEW in after insert trugger contained incorrect data
Brilliantov Kirill Vladimirovich wrote: I use Postgre-9.3.5 on windows7 x64. I use simple trigger for store some statistic data, it code: SELECT field IN variable FROM table WHERE ...; IF FOUND THEN UPDATE table SET field = ...; ELSE INSERT INTO table (field) VALUES(value); END IF; RETURN NULL; This trigger added as FOR EACH ROW on table2. In table2 I insert multiple data on one insert, e.g. INSERT INTO table2(field) VALUES(value0),(value1),(value2). Unfortunately trigger exit with error: ERROR: record new has no field value; You should post the table definition and the whole trigger; the error message seems to refer to things you omitted in your quote. Yours, Laurenz Albe -- 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] NEW in after insert trugger contained incorrect data
Albe Laurenz wrote on 11/14/2014 01:28 PM: You should post the table definition and the whole trigger; the error message seems to refer to things you omitted in your quote. Table with statistic: CREATE TABLE trassa.cpu_load_stat ( id serial NOT NULL, device integer NOT NULL, cpu smallint NOT NULL, min_value smallint NOT NULL, min_device_timestamp timestamp without time zone NOT NULL, min_timestamp timestamp without time zone, avg_value smallint NOT NULL, avg_timestamp timestamp without time zone NOT NULL, max_value smallint NOT NULL, max_device_timestamp timestamp without time zone NOT NULL, max_timestamp timestamp without time zone, total_value bigint NOT NULL, total_count integer NOT NULL, CONSTRAINT cpu_load_stat_pk PRIMARY KEY (id), CONSTRAINT cpu_load_stat_device_fk FOREIGN KEY (device) REFERENCES trassa.devices (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT cpu_load_stat_avg_value_check CHECK (avg_value = 0 AND avg_value = 100), CONSTRAINT cpu_load_stat_max_value_check CHECK (max_value = 0 AND max_value = 100), CONSTRAINT cpu_load_stat_min_value_check CHECK (min_value = 0 AND min_value = 100) ) Trigger: CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat() RETURNS trigger AS $BODY$ DECLARE line_id INTEGER DEFAULT 0; cpu_min_value SMALLINT DEFAULT 0; cpu_min_created_timestamp TIMESTAMP; cpu_min_device_timestamp TIMESTAMP; cpu_max_value SMALLINT DEFAULT 0; cpu_max_created_timestamp TIMESTAMP; cpu_max_device_timestamp TIMESTAMP; -- value BIGINT DEFAULT 0; -- number INTEGER DEFAULT 1; BEGIN -- RAISE NOTICE 'Device %', NEW.device; -- RAISE NOTICE 'Device timestamp %', NEW.device_timestamp; -- RAISE NOTICE 'CPU %', NEW.cpu; -- RAISE NOTICE 'Value %', NEW.value; SELECT id INTO line_id FROM trassa.cpu_load_stat WHERE device = NEW.device AND cpu = NEW.cpu; RAISE NOTICE USING MESSAGE = '*** START ***: ' || NEW; IF FOUND THEN RAISE NOTICE USING MESSAGE = '*** UPDATE ***: ID ' || line_id || ', data ' || NEW; SELECT created, device_timestamp, value INTO cpu_min_created_timestamp, cpu_min_device_timestamp, cpu_min_value FROM trassa.cpu_load WHERE trassa.cpu_load.device = NEW.device AND trassa.cpu_load.cpu = NEW.cpu ORDER BY value, created LIMIT 1; SELECT created, device_timestamp, value INTO cpu_max_created_timestamp, cpu_max_device_timestamp, cpu_max_value FROM trassa.cpu_load WHERE trassa.cpu_load.device = NEW.device AND trassa.cpu_load.cpu = NEW.cpu ORDER BY value DESC, created LIMIT 1; -- SELECT total_value, total_count -- INTO value, number -- FROM trassa.cpu_load_stat -- WHERE device = id; -- value = value + NEW.value; -- number = number + 1; UPDATE trassa.cpu_load_stat SET min_value = cpu_min_value, min_device_timestamp = cpu_min_device_timestamp, min_timestamp = cpu_min_created_timestamp, avg_value = CEIL((total_value + NEW.value) / (total_count + 1)), avg_timestamp = NOW(), max_value = cpu_max_value, max_device_timestamp = cpu_max_device_timestamp, max_timestamp = cpu_max_created_timestamp, total_value = (total_value + NEW.value), total_count = (total_count + 1) WHERE id = line_id; RAISE NOTICE '*** END UPDATE ***'; ELSE RAISE NOTICE USING MESSAGE = '*** INSERT ***: ' || NEW; INSERT INTO trassa.cpu_load_stat (device, cpu, min_value, min_device_timestamp, min_timestamp, avg_value, avg_timestamp, max_value, max_device_timestamp, max_timestamp, total_value, total_count) VALUES (NEW.device, NEW.cpu, NEW.value, NEW.device_timestamp, NOW(), NEW.value, NOW(), NEW.value, NEW.device_timestamp, NOW(), NEW.value, 1); RAISE NOTICE '*** END INSERT ***'; END IF; RAISE NOTICE USING MESSAGE = '*** END ***: ' || TG_NAME; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; -- Best regards, Brilliantov Kirill Vladimirovich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
Re: [GENERAL] NEW in after insert trugger contained incorrect data
Brilliantov Kirill Vladimirovich wrote: You should post the table definition and the whole trigger; the error message seems to refer to things you omitted in your quote. Table with statistic: CREATE TABLE trassa.cpu_load_stat ( id serial NOT NULL, device integer NOT NULL, cpu smallint NOT NULL, min_value smallint NOT NULL, min_device_timestamp timestamp without time zone NOT NULL, min_timestamp timestamp without time zone, avg_value smallint NOT NULL, avg_timestamp timestamp without time zone NOT NULL, max_value smallint NOT NULL, max_device_timestamp timestamp without time zone NOT NULL, max_timestamp timestamp without time zone, total_value bigint NOT NULL, total_count integer NOT NULL, [...] ) Trigger: CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat() RETURNS trigger AS $BODY$ [...] UPDATE trassa.cpu_load_stat SET min_value = cpu_min_value, min_device_timestamp = cpu_min_device_timestamp, min_timestamp = cpu_min_created_timestamp, avg_value = CEIL((total_value + NEW.value) / (total_count + 1)), avg_timestamp = NOW(), max_value = cpu_max_value, max_device_timestamp = cpu_max_device_timestamp, max_timestamp = cpu_max_created_timestamp, total_value = (total_value + NEW.value), total_count = (total_count + 1) WHERE id = line_id; I'd say that the error message refers to this or the following query: There is no field value in the table, and NEW represents a row in the table, so the expression NEW.value does not make sense. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Inconsistent results from HEX values in predicates
Hello
I have a table, and I want to always supply predicates in a BETWEEN format.
If all data is to be returned, then the FROM and TO values of the
BETWEEN are set to low hex value, and high hex value.
Sometimes this is not returning rows, here's an example:
e5iso=# create table t1 (col1 character(3));
CREATE TABLE
e5iso=# insert into t1 values('AAA');
INSERT 0 1
e5iso=# insert into t1 values('000');
INSERT 0 1
e5iso=# insert into t1 values(' ');
INSERT 0 1
e5iso=# insert into t1 values(' x ');
INSERT 0 1
e5iso=# select col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' ;
col1
--
AAA
000
x
(3 rows)
So where did my ' ' row go?
Am I missing something, are SPACES special in some way?
e5iso=# select * from t1 where col1 is null;
col1
--
(0 rows)
All my rows hex values:
e5iso=# select encode(col1::bytea,'hex') from t1;
encode
414141
303030
202020
207820
The database is a LATIN9 database.
Help please!
--
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] Inconsistent results from HEX values in predicates
Actually I find this returns my row:
select col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' or col1=E'\x20';
Why does x'202020' match an equality condition on E'\x20' ?
Is there some auto trimming going on?
This is PostgreSQL 9.3.5 on Linux
On 14 November 2014 11:41, Gary Cowell gary.cowell+pg...@gmail.com wrote:
Hello
I have a table, and I want to always supply predicates in a BETWEEN format.
If all data is to be returned, then the FROM and TO values of the
BETWEEN are set to low hex value, and high hex value.
Sometimes this is not returning rows, here's an example:
e5iso=# create table t1 (col1 character(3));
CREATE TABLE
e5iso=# insert into t1 values('AAA');
INSERT 0 1
e5iso=# insert into t1 values('000');
INSERT 0 1
e5iso=# insert into t1 values(' ');
INSERT 0 1
e5iso=# insert into t1 values(' x ');
INSERT 0 1
e5iso=# select col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' ;
col1
--
AAA
000
x
(3 rows)
So where did my ' ' row go?
Am I missing something, are SPACES special in some way?
e5iso=# select * from t1 where col1 is null;
col1
--
(0 rows)
All my rows hex values:
e5iso=# select encode(col1::bytea,'hex') from t1;
encode
414141
303030
202020
207820
The database is a LATIN9 database.
Help please!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Encrypting/Decryption
Hello, As i am new to postgresql and django. please help me to acheive the following. I have created a database with some tables and i want to encrypt the data in database tables, so please someone guide me how to do it.? i want to encrypt the data and also decrypt through django, so tell me which is right way either to use pgcrypto or pycryto, i am not understanding neither of them. please help. -- Regards : Venktesh Guttedar.
Re: [GENERAL] NEW in after insert trugger contained incorrect data
On 11/14/2014 03:24 AM, Albe Laurenz wrote: Brilliantov Kirill Vladimirovich wrote: You should post the table definition and the whole trigger; the error message seems to refer to things you omitted in your quote. Table with statistic: CREATE TABLE trassa.cpu_load_stat ( id serial NOT NULL, device integer NOT NULL, cpu smallint NOT NULL, min_value smallint NOT NULL, min_device_timestamp timestamp without time zone NOT NULL, min_timestamp timestamp without time zone, avg_value smallint NOT NULL, avg_timestamp timestamp without time zone NOT NULL, max_value smallint NOT NULL, max_device_timestamp timestamp without time zone NOT NULL, max_timestamp timestamp without time zone, total_value bigint NOT NULL, total_count integer NOT NULL, [...] ) Trigger: CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat() RETURNS trigger AS $BODY$ [...] UPDATE trassa.cpu_load_stat SET min_value = cpu_min_value, min_device_timestamp = cpu_min_device_timestamp, min_timestamp = cpu_min_created_timestamp, avg_value = CEIL((total_value + NEW.value) / (total_count + 1)), avg_timestamp = NOW(), max_value = cpu_max_value, max_device_timestamp = cpu_max_device_timestamp, max_timestamp = cpu_max_created_timestamp, total_value = (total_value + NEW.value), total_count = (total_count + 1) WHERE id = line_id; I'd say that the error message refers to this or the following query: There is no field value in the table, and NEW represents a row in the table, so the expression NEW.value does not make sense. Actually I think there is in the table the trigger is on. From the original post: SQL statement INSERT INTO trassa.cpu_load (device, device_timestamp, cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3) though what is showing up in the error is this: NOTICE: *** START ***: (9994,5,2014-11-14 13:38:04.94,1970-01-02 06:02:38,0 ,6) Not sure where the 9994, and 2014-11-14 13:38:04.94 are coming from, though I think it is related to this from the error message: PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement I am pretty sure the error is coming from a different function then the one we are being shown. So we would need to see the table the trigger is being run on as well as any other triggers and associated functions. Yours, Laurenz Albe -- Adrian Klaver adrian.kla...@aklaver.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] Inconsistent results from HEX values in predicates
Gary Cowell gary.cowell+pg...@gmail.com writes:
e5iso=# create table t1 (col1 character(3));
CREATE TABLE
e5iso=# insert into t1 values('AAA');
INSERT 0 1
e5iso=# insert into t1 values('000');
INSERT 0 1
e5iso=# insert into t1 values(' ');
INSERT 0 1
e5iso=# insert into t1 values(' x ');
INSERT 0 1
e5iso=# select col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' ;
col1
--
AAA
000
x
(3 rows)
So where did my ' ' row go?
Am I missing something, are SPACES special in some way?
In a char(N) column, certainly. Read the manual.
You could dodge that by using varchar or text instead. But you may have
another problem besides trailing blanks not being significant: you're
assuming that the sort order of text is pure bytewise, which would only
be true in C locale. So this coding technique is fragile as can be
and I don't recommend it.
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: service allowing arbitrary relations was Re: [GENERAL] hstore, but with fast range comparisons?
On 11/13/2014 5:56 PM, Guyren Howe wrote: On Nov 13, 2014, at 2:28 PM, Andy Colson a...@squeakycode.net wrote: I want to do something that is perfectly satisfied by an hstore column. *Except* that I want to be able to do fast (ie indexed) , etc comparisons, not just equality. From what I can tell, there isn’t really any way to get hstore to do this, so I’ll have to go to a key-value table. But I thought I’d ask just in case I’m missing something. I think your missing something. Is it one field in the hstore? Did you try an expression index? create index cars_mph on cars ( (data-'mph') ); thats a btree index, which should support and . (Although I've never tried it) Sorry I wasn’t clear. I need a fast comparison on whatever keys someone chooses to put in the hstore. I’m creating a web service where you can post whatever keys and values you wish. I am leaning toward having folks declare the relations they want to store and the field types, so I could just create a table for each one, but I want this to be able to scale to a lot of data for a lot of people. Perhaps I could give everyone their own schema and just create tables for their relations. How heavyweight are schemas? But if I don’t want to do that, I’m leaning toward a table with a relation name and a hstore, and then separate k-v tables for different types. I was thinking of keeping the hstore because they will often be searching on fewer fields than they’re retrieving, so this would avoid having to do a join for every field they need. Regards, Guyren G Howe Relevant Logic LLC Please keep the list cc'd, there are much smarter people there. -Andy -- 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] NEW in after insert trugger contained incorrect data
Adrian Klaver wrote on 11/14/2014 05:15 PM: Actually I think there is in the table the trigger is on. From the original post: SQL statement INSERT INTO trassa.cpu_load (device, device_timestamp, cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3) though what is showing up in the error is this: NOTICE: *** START ***: (9994,5,2014-11-14 13:38:04.94,1970-01-02 06:02:38,0 ,6) You can see what original data contained timestamp 1970-01-02 06:02:38, value 0 and 6, trigger start message also contain this values. Base on this I think trigger start message is correct and I get it after first insert, where 9994 is a inserted id. Not sure where the 9994, and 2014-11-14 13:38:04.94 are coming from, though I think it is related to this from the error message: PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement I am pretty sure the error is coming from a different function then the one we are being shown. So we would need to see the table the trigger is being run on as well as any other triggers and associated functions. Yours, Laurenz Albe -- С уважением, Бриллиантов Кирилл Владимирович … программист, технический отдел ООО «БайтЭрг» Видеокамеры МВК – Эффективность разумных решений … +7(495)221-66-22 http://www.byterg.ruhttp://www.bestdvr.ru -- 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] NEW in after insert trugger contained incorrect data
On 11/14/2014 07:23 AM, Brilliantov Kirill Vladimirovich wrote: Adrian Klaver wrote on 11/14/2014 05:15 PM: Actually I think there is in the table the trigger is on. From the original post: SQL statement INSERT INTO trassa.cpu_load (device, device_timestamp, cpu, value) VALUES(5,'1970-01-02 06:02:38',0,6),(5,'1970-01-02 06:02:38',1,0),(5,'1970-01-02 06:02:38',255,3) though what is showing up in the error is this: NOTICE: *** START ***: (9994,5,2014-11-14 13:38:04.94,1970-01-02 06:02:38,0 ,6) You can see what original data contained timestamp 1970-01-02 06:02:38, value 0 and 6, trigger start message also contain this values. Base on this I think trigger start message is correct and I get it after first insert, where 9994 is a inserted id. But the error message is coming from this: PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement Note different function name and the EXECUTE statement. There is no EXECUTE in the function you showed us. Not sure where the 9994, and 2014-11-14 13:38:04.94 are coming from, though I think it is related to this from the error message: PL/pgSQL function update_cpu_load_list(integer,integer,smallint[],smallint[]) line 19 at EXECUTE statement I am pretty sure the error is coming from a different function then the one we are being shown. So we would need to see the table the trigger is being run on as well as any other triggers and associated functions. Yours, Laurenz Albe -- Adrian Klaver adrian.kla...@aklaver.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] NEW in after insert trugger contained incorrect data
Albe Laurenz wrote on 11/14/2014 01:28 PM:
You should post the table definition and the whole trigger; the error
message seems to refer to things you omitted in your quote.
Yours,
Laurenz Albe
Table with original data trassa.cpu_load:
CREATE TABLE trassa.cpu_load
(
id serial NOT NULL,
device integer NOT NULL,
created timestamp without time zone NOT NULL DEFAULT now(),
device_timestamp timestamp without time zone NOT NULL,
cpu smallint NOT NULL,
value smallint NOT NULL,
CONSTRAINT cpu_load_pk PRIMARY KEY (id),
CONSTRAINT cpu_load_device FOREIGN KEY (device)
REFERENCES trassa.devices (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cpu_load_val CHECK (value = 0 AND value = 100)
)
WITH (
OIDS=FALSE
);
Function for save values in table trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_list(device_id
integer, device_timestamp integer, device_cpu smallint[],
device_cpu_load smallint[])
RETURNS boolean AS
$BODY$
DECLARE
val_len SMALLINT DEFAULT array_length($3, 1);
cmd TEXT DEFAULT 'INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES';
result SMALLINT;
ts TIMESTAMP DEFAULT to_timestamp($2);
BEGIN
IF val_len = array_length($4, 1) THEN
FOR i IN 1..val_len LOOP
cmd = cmd || '(' ||
$1::text ||
',''' || ts::text || ''',' ||
$3[i]::text || ',' ||
$4[i]::text || ')';
IF i != val_len THEN
cmd = cmd || ',';
END IF;
END LOOP;
EXECUTE cmd;
GET DIAGNOSTICS result = ROW_COUNT;
IF result = val_len THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSE
RETURN FALSE;
END IF;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
Table for save statistic trassa.cpu_load_stat:
CREATE TABLE trassa.cpu_load_stat
(
id serial NOT NULL,
device integer NOT NULL,
cpu smallint NOT NULL,
min_value smallint NOT NULL,
min_device_timestamp timestamp without time zone NOT NULL,
min_timestamp timestamp without time zone,
avg_value smallint NOT NULL,
avg_timestamp timestamp without time zone NOT NULL,
max_value smallint NOT NULL,
max_device_timestamp timestamp without time zone NOT NULL,
max_timestamp timestamp without time zone,
total_value bigint NOT NULL,
total_count integer NOT NULL,
CONSTRAINT cpu_load_stat_pk PRIMARY KEY (id),
CONSTRAINT cpu_load_stat_device_fk FOREIGN KEY (device)
REFERENCES trassa.devices (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cpu_load_stat_avg_value_check CHECK (avg_value = 0 AND
avg_value = 100),
CONSTRAINT cpu_load_stat_max_value_check CHECK (max_value = 0 AND
max_value = 100),
CONSTRAINT cpu_load_stat_min_value_check CHECK (min_value = 0 AND
min_value = 100)
)
WITH (
OIDS=FALSE
);
Trigger for update trassa.cpu_load_stat, values from trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat()
RETURNS trigger AS
$BODY$
DECLARE
line_id INTEGER DEFAULT 0;
cpu_min_value SMALLINT DEFAULT 0;
cpu_min_created_timestamp TIMESTAMP;
cpu_min_device_timestamp TIMESTAMP;
cpu_max_value SMALLINT DEFAULT 0;
cpu_max_created_timestamp TIMESTAMP;
cpu_max_device_timestamp TIMESTAMP;
BEGIN
SELECT id INTO line_id FROM trassa.cpu_load_stat
WHERE device = NEW.device AND cpu = NEW.cpu;
RAISE NOTICE USING MESSAGE = '*** START ***: ' || NEW;
IF FOUND THEN
RAISE NOTICE USING MESSAGE = '*** UPDATE ***: ID ' || line_id || ',
data ' || NEW;
SELECT created, device_timestamp, value
INTO cpu_min_created_timestamp, cpu_min_device_timestamp,
cpu_min_value
FROM trassa.cpu_load
WHERE trassa.cpu_load.device = NEW.device
AND trassa.cpu_load.cpu = NEW.cpu
ORDER BY value, created
LIMIT 1;
SELECT created, device_timestamp, value
INTO cpu_max_created_timestamp, cpu_max_device_timestamp,
cpu_max_value
FROM trassa.cpu_load
WHERE trassa.cpu_load.device = NEW.device
AND trassa.cpu_load.cpu = NEW.cpu
ORDER BY value DESC, created
LIMIT 1;
UPDATE trassa.cpu_load_stat
SET min_value = cpu_min_value,
min_device_timestamp = cpu_min_device_timestamp,
min_timestamp = cpu_min_created_timestamp,
avg_value = CEIL((total_value + NEW.value) /
(total_count + 1)),
Re: [GENERAL] NEW in after insert trugger contained incorrect data
On 11/14/2014 07:32 AM, Brilliantov Kirill Vladimirovich wrote:
Albe Laurenz wrote on 11/14/2014 01:28 PM:
You should post the table definition and the whole trigger; the error
message seems to refer to things you omitted in your quote.
Yours,
Laurenz Albe
Just approaching caffeine level required to follow this:)
Table with original data trassa.cpu_load:
CREATE TABLE trassa.cpu_load
(
id serial NOT NULL,
device integer NOT NULL,
created timestamp without time zone NOT NULL DEFAULT now(),
device_timestamp timestamp without time zone NOT NULL,
cpu smallint NOT NULL,
value smallint NOT NULL,
CONSTRAINT cpu_load_pk PRIMARY KEY (id),
CONSTRAINT cpu_load_device FOREIGN KEY (device)
REFERENCES trassa.devices (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cpu_load_val CHECK (value = 0 AND value = 100)
)
WITH (
OIDS=FALSE
);
FYI, in the function below you have declared aliases for the function
arguments e.g. device_id integer. You can use those aliases in the
function instead of $*. It would make things easier to follow.
http://www.postgresql.org/docs/9.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS
Function for save values in table trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_list(device_id
integer, device_timestamp integer, device_cpu smallint[],
device_cpu_load smallint[])
RETURNS boolean AS
$BODY$
DECLARE
val_len SMALLINT DEFAULT array_length($3, 1);
cmd TEXT DEFAULT 'INSERT INTO trassa.cpu_load (device, device_timestamp,
cpu, value) VALUES';
result SMALLINT;
ts TIMESTAMP DEFAULT to_timestamp($2);
BEGIN
IF val_len = array_length($4, 1) THEN
FOR i IN 1..val_len LOOP
cmd = cmd || '(' ||
$1::text ||
',''' || ts::text || ''',' ||
$3[i]::text || ',' ||
$4[i]::text || ')';
IF i != val_len THEN
cmd = cmd || ',';
END IF;
I have not thought this all the way through, but I see a potential
problem with the test above. It is not clear to me which version of cmd
you are using nor what exactly it returns. You might want to put a
NOTICE in there to see what you are actually building.
Also you might want to take a look at this section of the docs:
http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
In particular the following forms:
FOR target IN EXECUTE text_expression ...
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
END LOOP;
EXECUTE cmd;
GET DIAGNOSTICS result = ROW_COUNT;
IF result = val_len THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
ELSE
RETURN FALSE;
END IF;
END;$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
Table for save statistic trassa.cpu_load_stat:
CREATE TABLE trassa.cpu_load_stat
(
id serial NOT NULL,
device integer NOT NULL,
cpu smallint NOT NULL,
min_value smallint NOT NULL,
min_device_timestamp timestamp without time zone NOT NULL,
min_timestamp timestamp without time zone,
avg_value smallint NOT NULL,
avg_timestamp timestamp without time zone NOT NULL,
max_value smallint NOT NULL,
max_device_timestamp timestamp without time zone NOT NULL,
max_timestamp timestamp without time zone,
total_value bigint NOT NULL,
total_count integer NOT NULL,
CONSTRAINT cpu_load_stat_pk PRIMARY KEY (id),
CONSTRAINT cpu_load_stat_device_fk FOREIGN KEY (device)
REFERENCES trassa.devices (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cpu_load_stat_avg_value_check CHECK (avg_value = 0 AND
avg_value = 100),
CONSTRAINT cpu_load_stat_max_value_check CHECK (max_value = 0 AND
max_value = 100),
CONSTRAINT cpu_load_stat_min_value_check CHECK (min_value = 0 AND
min_value = 100)
)
WITH (
OIDS=FALSE
);
Trigger for update trassa.cpu_load_stat, values from trassa.cpu_Load:
CREATE OR REPLACE FUNCTION trassa.update_cpu_load_stat()
RETURNS trigger AS
$BODY$
DECLARE
line_id INTEGER DEFAULT 0;
cpu_min_value SMALLINT DEFAULT 0;
cpu_min_created_timestamp TIMESTAMP;
cpu_min_device_timestamp TIMESTAMP;
cpu_max_value SMALLINT DEFAULT 0;
cpu_max_created_timestamp TIMESTAMP;
cpu_max_device_timestamp TIMESTAMP;
BEGIN
SELECT id INTO line_id FROM trassa.cpu_load_stat
WHERE device = NEW.device AND cpu = NEW.cpu;
RAISE NOTICE USING MESSAGE = '*** START ***: ' || NEW;
IF FOUND THEN
RAISE NOTICE USING MESSAGE = '*** UPDATE ***: ID ' || line_id
|| ', data ' || NEW;
SELECT created, device_timestamp, value
INTO cpu_min_created_timestamp, cpu_min_device_timestamp,
cpu_min_value
FROM trassa.cpu_load
WHERE trassa.cpu_load.device = NEW.device
AND trassa.cpu_load.cpu = NEW.cpu
ORDER BY value, created
Re: [GENERAL] NEW in after insert trugger contained incorrect data
On 11/14/2014 07:32 AM, Brilliantov Kirill Vladimirovich wrote: Albe Laurenz wrote on 11/14/2014 01:28 PM: You should post the table definition and the whole trigger; the error message seems to refer to things you omitted in your quote. Yours, Laurenz Albe Turns out I was not at a sufficient caffeine level previously:( Trigger update_cpu_load_stat added to table trassa.cpu_load: CREATE TRIGGER update_cpu_load_stat_trigger AFTER INSERT ON trassa.cpu_load_stat FOR EACH ROW EXECUTE PROCEDURE trassa.update_cpu_load_stat(); Another run through showed that the issue is above. You have declared the trigger on trassa.cpu_load_stat instead of trassa.cpu_load. trassa.cpu_load_stat has no value field, hence the error. Thank you and excuse my big message. -- Adrian Klaver adrian.kla...@aklaver.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] Two instances of Postgres with single data directory
On 11/14/2014 2:24 AM, Albe Laurenz wrote: dineshkaarthick wrote: I would like to know how is the Shared Disk Failover replication achieved if it is not possible to share the data directory ? I am referring to the 1st solution in the mentioned link, http://www.postgresql.org/docs/9.3/static/different-replication-solutions.html You have some cluster software that makes dead sure that one node is*not* running the database server before starting it on the other. more specifically, that the data volume isn't even MOUNTED by the other server. typically this is enforced with hardware 'fencing' such that the standby server is blocked from even trying to mount the shared volume, until it decides to take over, then it fences the original 'master', unfences itself, and mounts the volume, then starts its copy of postgresql.this fencing is often done in a storage switch (fiberchannel most commonly). -- john r pierce 37N 122W somewhere on the middle of the left coast
[GENERAL] pg_upgrade and ubuntu
I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an 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] pg_upgrade and ubuntu
Jonathan Vanasco-7 wrote I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian It would make more sense for the Debian packagers people to write a pg_upgradecluster wrapper like they have done for the other key programs. David J. -- View this message in context: http://postgresql.nabble.com/pg-upgrade-and-ubuntu-tp5827035p5827039.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] pg_upgrade and ubuntu
On 11/14/2014 11:10 AM, Jonathan Vanasco wrote: I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. I believe there is: http://www.postgresql.org/docs/9.3/interactive/pgupgrade.html Notes If you are upgrading a pre-PostgreSQL 9.2 cluster that uses a configuration-file-only directory, you must pass the real data directory location to pg_upgrade, and pass the configuration directory location to the server, e.g. -d /real-data-directory -o '-D /configuration-directory'. -- Adrian Klaver adrian.kla...@aklaver.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] pg_upgrade and ubuntu
David G Johnston wrote Jonathan Vanasco-7 wrote I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian It would make more sense for the Debian packagers people to write a pg_upgradecluster wrapper like they have done for the other key programs. David J. Note it appears there is a pg_upgradecluster program, but it doesn't appear to a wrapper for the official program... David J. -- View this message in context: http://postgresql.nabble.com/pg-upgrade-and-ubuntu-tp5827035p5827041.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] pg_upgrade and ubuntu
David G Johnston wrote: David G Johnston wrote It would make more sense for the Debian packagers people to write a pg_upgradecluster wrapper like they have done for the other key programs. Note it appears there is a pg_upgradecluster program, but it doesn't appear to a wrapper for the official program... As far as I recall, pg_upgradecluster goes through a pg_dump / pg_restore cycle. It predates pg_upgrade itself. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] pg_upgrade and ubuntu
David G Johnston wrote David G Johnston wrote Jonathan Vanasco-7 wrote I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. https://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian It would make more sense for the Debian packagers people to write a pg_upgradecluster wrapper like they have done for the other key programs. David J. Note it appears there is a pg_upgradecluster program, but it doesn't appear to a wrapper for the official program... David J. should finish skimming before I post things Anyway, without self verification, it appears from here: https://bugs.launchpad.net/ubuntu/+source/postgresql-common/+bug/939260 that you can instruct pg_upgradecluster to use the official program to do the upgrade instead of its old method - whatever that was... David J. -- View this message in context: http://postgresql.nabble.com/pg-upgrade-and-ubuntu-tp5827035p5827044.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] pg_upgrade and ubuntu
On 11/14/14 2:10 PM, Jonathan Vanasco wrote: I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. On Debian/Ubuntu, use pg_upgradecluster --method upgrade. See the man page for details. -- 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] NEW in after insert trugger contained incorrect data
On 11/14/14, 10:09 AM, Adrian Klaver wrote: Trigger update_cpu_load_stat added to table trassa.cpu_load: CREATE TRIGGER update_cpu_load_stat_trigger AFTER INSERT ON trassa.cpu_load_stat FOR EACH ROW EXECUTE PROCEDURE trassa.update_cpu_load_stat(); Another run through showed that the issue is above. You have declared the trigger on trassa.cpu_load_stat instead of trassa.cpu_load. trassa.cpu_load_stat has no value field, hence the error. Something else to consider: using FOUND to decide whether to INSERT vs UPDATE is a race condition: you can do the SELECT, someone else can then insert or delete, and then you attempt to do the wrong thing. To handle this correctly, you need an appropriate UNIQUE constraint or primary key, and to follow the pattern in table 40-2 at http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING If you don't, and you have concurrent activity you can end up losing data (and in the case of a DELETE after your SELECT, the data loss will be completely silent). -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
