Re: [GENERAL] Postgresql 9.2 has standby server lost data?
On Fri, Jun 19, 2015 at 12:01 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 06/18/2015 05:45 PM, Paula Price wrote: I have Postgresql 9.2.10 streaming replication set up with log shipping in case the replication falls behind. I discovered that the log-shipping had been disabled at some point in time. I enabled the log shipping again. If at some point in time the streaming replication fell behind and the standby server was not able to retrieve the necessary WAL file(s) from the primary, would the standby server continue to function normally? Do I need to rebuild the standby server? I have restarted the standby server and it is up and running with no issues. Well that seems at odds with it being unable to retrieve the WAL files. This leads to these questions: 1) What makes you think it did not retrieve the WAL files via streaming? It *may* *not *have fallen behind via replication. We do have standby servers that fall behind, but since we have log-shipping it is not a concern. On this server, i have no idea how long we were running without log-shipping. I have no idea how many log files I would have to go through to find out when log-shipping stopped. My basic question is: If a standby server falls behind with streaming replication AND the standby server cannot obtain the WAL file needed from the primary, will you get an error from the standby server? Or does it just hiccup and try to carry on? 2) What does the postgres log show at the time you restarted the standby? 2015-06-18 01:12:41.871 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: database system was shut down in recovery at 2015-06-18 01:12:14 UTC 2015-06-18 01:12:41.871 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6298 2015-06-18 01:12:41.904 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: entering standby mode 2015-06-18 01:12:41.904 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6384 2015-06-18 01:12:41.987 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: redo starts at 867/FDF32E18 2015-06-18 01:12:41.987 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6855 2015-06-18 01:12:42.486 UTC::[unknown]@[unknown]:[28213]:2015-06-18 01:12:42 UTC: LOG: 0: connection received: host=[local] 2015-06-18 01:12:42.486 UTC::[unknown]@[unknown]:[28213]:2015-06-18 01:12:42 UTC: LOCATION: BackendInitialize, postmaster.c:3501 2015-06-18 01:12:42.486 UTC:[local]:postgres@postgres:[28213]:2015-06-18 01:12:42 UTC: FATAL: 57P03: the database system is starting up 2015-06-18 01:12:42.486 UTC:[local]:postgres@postgres:[28213]:2015-06-18 01:12:42 UTC: LOCATION: ProcessStartupPacket, postmaster.c:1792 2015-06-18 01:12:43.488 UTC::[unknown]@[unknown]:[28270]:2015-06-18 01:12:43 UTC: LOG: 0: connection received: host=[local] 2015-06-18 01:12:43.488 UTC::[unknown]@[unknown]:[28270]:2015-06-18 01:12:43 UTC: LOCATION: BackendInitialize, postmaster.c:3501 2015-06-18 01:12:43.488 UTC:[local]:postgres@postgres:[28270]:2015-06-18 01:12:43 UTC: FATAL: 57P03: the database system is starting up 2015-06-18 01:12:43.488 UTC:[local]:postgres@postgres:[28270]:2015-06-18 01:12:43 UTC: LOCATION: ProcessStartupPacket, postmaster.c:1792 2015-06-18 01:12:44.489 UTC::[unknown]@[unknown]:[28327]:2015-06-18 01:12:44 UTC: LOG: 0: connection received: host=[local] 2015-06-18 01:12:44.489 UTC::[unknown]@[unknown]:[28327]:2015-06-18 01:12:44 UTC: LOCATION: BackendInitialize, postmaster.c:3501 2015-06-18 01:12:44.489 UTC:[local]:postgres@postgres:[28327]:2015-06-18 01:12:44 UTC: FATAL: 57P03: the database system is starting up 2015-06-18 01:12:44.489 UTC:[local]:postgres@postgres:[28327]:2015-06-18 01:12:44 UTC: LOCATION: ProcessStartupPacket, postmaster.c:1792 2015-06-18 01:12:44.490 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: consistent recovery state reached at 868/112AF7F8 2015-06-18 01:12:44.490 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: CheckRecoveryConsistency, xlog.c:7405 2015-06-18 01:12:44.490 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: invalid record length at 868/112AFB00 2015-06-18 01:12:44.490 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: ReadRecord, xlog.c:4078 2015-06-18 01:12:44.490 UTC::@:[28166]:2015-06-18 01:12:41 UTC: LOG: 0: database system is ready to accept read only connections 2015-06-18 01:12:44.490 UTC::@:[28166]:2015-06-18 01:12:41 UTC: LOCATION: sigusr1_handler, postmaster.c:4314 I need to know if the data integrity has been compromised. I have run this query to determine the lag time for the standby(in case this tells me anything): SELECT now(), now() - pg_last_xact_replay_timestamp() AS time_lag; RESULT: 2015-06-19 00:40:48.83701+00;00:00:01.078616 Thank you, Paula P -- Adrian Klaver adrian.kla...@aklaver.com
Re: [GENERAL] WAL log archival on standby
On Fri, 19 Jun 2015 13:46 Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Jun 19, 2015 at 2:38 PM, James Sewell james.sew...@lisasoft.com wrote: Hey All, Is it possible to create WAL archive files on standby servers when using streaming replication? Yes and no, standbys do not archive WAL in ~9.4, but you could use archive_mode = 'always' with the upcoming 9.5. I know I can use archive_command or pg_receivexlog - but these will both result in me sending information to the standby servers which has already been sent via standard streaming replication. This would result in a doubling of my network traffic. Ideally I'd prefer to conserve this bandwidth and write the files to a local archive on any active standby. Both methods you mentioned are the way to go for now I am afraid, or you wait for 9.5. I have not tried it yet but should be possible to use pg_receivexlog from the standby. If I can use standby server to create a streaming replica and with pg_basebackup it should be possible to stream wal for archiving too. Regards Sameer
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. 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] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us wrote: David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. The use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Thanks for the quick response. David J.
Re: [GENERAL] Postgresql 9.2 has standby server lost data?
Adrian Klaver adrian.kla...@aklaver.com writes: On 06/19/2015 01:05 PM, Paula Price wrote: On Fri, Jun 19, 2015 at 12:01 PM, Adrian Klaver adrian.kla...@aklaver.com mailto:adrian.kla...@aklaver.com wrote: On 06/18/2015 05:45 PM, Paula Price wrote: I have Postgresql 9.2.10 streaming replication set up with log shipping in case the replication falls behind. I discovered that the log-shipping had been disabled at some point in time. I enabled the log shipping again. If at some point in time the streaming replication fell behind and the standby server was not able to retrieve the necessary WAL file(s) from the primary, would the standby server continue to function normally? Do I need to rebuild the standby server? I have restarted the standby server and it is up and running with no issues. Well that seems at odds with it being unable to retrieve the WAL files. This leads to these questions: 1) What makes you think it did not retrieve the WAL files via streaming? â It _may_ _not _have fallen behind via replication. We do have standby servers that fall behind, but since we have log-shipping it is not a concern. On this server, i have no idea how long we were running without log-shipping. I have no idea how many log files I would have to go through to find out when log-shipping stopped. My basic question is: If a standby server falls behind with streaming replication AND the standby server cannot obtain the WAL file needed from the primary, will you get an error from the standby server? Or does it just hiccup and try to carry on?â No it will fall over: I wouldn't describe it that way... To a user, the standby will function and appear normal, unless they notice that the data is not current. In the server logs, there will be indications that replication is stuck waiting for WAL. HTH http://www.postgresql.org/docs/9.2/interactive/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER wal_keep_segments (integer) Specifies the minimum number of past log file segments kept in the pg_xlog directory, in case a standby server needs to fetch them for streaming replication. Each segment is normally 16 megabytes. If a standby server connected to the sending server falls behind by more than wal_keep_segments segments, the sending server might remove a WAL segment still needed by the standby, in which case the replication connection will be terminated. Downstream connections will also eventually fail as a result. (However, the standby server can recover by fetching the segment from archive, if WAL archiving is in use.) This sets only the minimum number of segments retained in pg_xlog; the system might need to retain more segments for WAL archival or to recover from a checkpoint. If wal_keep_segments is zero (the default), the system doesn't keep any extra segments for standby purposes, so the number of old WAL segments available to standby servers is a function of the location of the previous checkpoint and status of WAL archiving. This parameter can only be set in the postgresql.conf file or on the server command line. When you started up if the necessary WAL files where not on the server you would have seen Postgres throwing errors in the log. I would check out the below to verify: http://www.postgresql.org/docs/9.2/interactive/warm-standby.html#STREAMING-REPLICATION 25.2.5.2. Monitoring 2) What does the postgres log show at the time you restarted the standby? â2015-06-18 01:12:41.871 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: database system was shut down in recovery at 2015-06-18 01:12:14 UTC 2015-06-18 01:12:41.871 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6298 2015-06-18 01:12:41.904 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: entering standby mode 2015-06-18 01:12:41.904 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6384 2015-06-18 01:12:41.987 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOG: 0: redo starts at 867/FDF32E18 2015-06-18 01:12:41.987 UTC::@:[28168]:2015-06-18 01:12:41 UTC: LOCATION: StartupXLOG, xlog.c:6855 2015-06-18 01:12:42.486 UTC::[unknown]@[unknown]:[28213]:2015-06-18 01:12:42 UTC: LOG: 0: connection received: host=[local] 2015-06-18 01:12:42.486 UTC::[unknown]@[unknown]:[28213]:2015-06-18 01:12:42 UTC: LOCATION: BackendInitialize, postmaster.c:3501 2015-06-18 01:12:42.486 UTC:[local]:postgres@postgres:[28213]:2015-06-18 01:12:42 UTC: FATAL: 57P03: the database system is starting up 2015-06-18 01:12:42.486
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
David G. Johnston david.g.johns...@gmail.com writes: On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us wrote: David G. Johnston david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. âThe use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. â It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Try this... sj$ psql -eqf q begin; create table foo as select 'here goes some text'::text as tf; create function foo () returns text as $$ begin return case when true then tf end from foo limit 1; end $$ language plpgsql; select foo(); foo - here goes some text (1 row) abort; sj$ HTH âThanks for the quick response. David J. -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. I thought there was an implicit variable available to me but cannot figure out what it is nor find it in the documentation. Using 9.3 but figuring if it is possible its likely the same in all supported releases... Thanks! David J.
Re: [GENERAL] Trying to avoid a simple temporary variable declaration in a pl/pgsql function
Hello The solution proposed by Tom works as long as you can make sure that your SELECT statement in the function will return a single row with a single column of type TEXT: CREATE TABLE test (id INTEGER, what_goes_here TEXT); INSERT INTO test values (1,'Text 1'); INSERT INTO test values (2,'Text 2'); CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN RETURN (SELECT what_goes_here FROM test LIMIT 1); END; $$; SELECT * FROM test_func(); test_func --- Text 1 (1 row) No need for INTO. Bye Charles On 6/20/2015 17:07, David G. Johnston wrote: On Sat, Jun 20, 2015 at 10:56 AM, Tom Lane t...@sss.pgh.pa.us mailto:t...@sss.pgh.pa.uswrote: David G. Johnston david.g.johns...@gmail.com mailto:david.g.johns...@gmail.com writes: I know this could be written quite easily in sql but was wondering if it is possible in pl/pgsql. CREATE FUNCTION test_func() RETURNS text LANGUAGE 'plpgsql' AS $$ BEGIN SELECT 'text_to_return' INTO what_goes_here?; --with or without a cast RETURN what_goes_here?; END; $$; The goal is to return the value of text_to_return without declaring an explicit variable to name in the INTO clause. INTO requires a declared variable as target. However, I'm wondering why you don't just use RETURN expression if this is all that will be in the function. The use of SELECT is required and will likely have a CTE and a set of SQL CASE expressions as part of it. It isn't a problem to declare it myself but I thought I had read about there being an implicit variable name that could be used instead. I guess I mis-remembered... Thanks for the quick response. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] INSERT a number in a column based on other columns OLD INSERTs
In PostgreSQL I have this table... (there is a primary key in the most left side timestamp02 which is not shown in this image) in the table above, all columns are entered via querrys, except the time_index which I would like to be filled automatically via a trigger each time each row is filled. This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel. *CREATE TABLE table_ebscb_spa_log02 ( pcnum smallint, timestamp02 timestamp with time zone NOT NULL DEFAULT now(), fn_name character varying, time time without time zone, time_elapse character varying, time_type character varying, time_index real, CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02) ) WITH ( OIDS=FALSE ); ALTER TABLE table_ebscb_spa_log02 OWNER TO postgres;* What I would like the trigger to do is: INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row. If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column, Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one before(above) in the time_index column. (pls look at the example table image, this trigger will produce every red highlighted square on it) I have tried so far this to create the function: CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix = 1; ELSE t_ix = t_ix + 1; END IF; END IF; NEW.time_index = t_ix; return NEW; END $$ LANGUAGE plpgsql; But when I manually insert the values in the table, nothing change (no error message) time_index column just remain empty, what am I doing wrong??? Please some good PostgreSQL fellow programmer could give me a hand, I really have come to a death point in this task, I have any more ideas. -- View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577.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] INSERT a number in a column based on other columns OLD INSERTs
On 06/20/2015 10:44 AM, litu16 wrote: In PostgreSQL I have this table... (there is a primary key in the most left side timestamp02 which is not shown in this image) in the table above, all columns are entered via querrys, except the time_index which I would like to be filled automatically via a trigger each time each row is filled. This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel. *CREATE TABLE table_ebscb_spa_log02 ( pcnum smallint, timestamp02 timestamp with time zone NOT NULL DEFAULT now(), fn_name character varying, time time without time zone, time_elapse character varying, time_type character varying, time_index real, CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02) ) WITH ( OIDS=FALSE ); ALTER TABLE table_ebscb_spa_log02 OWNER TO postgres;* What I would like the trigger to do is: INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row. If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column, Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one before(above) in the time_index column. (pls look at the example table image, this trigger will produce every red highlighted square on it) I have tried so far this to create the function: CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix = 1; ELSE t_ix = t_ix + 1; You need to use the assignment operator: http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT so: t_ix := 1 END IF; END IF; NEW.time_index = t_ix; Same here. return NEW; END $$ LANGUAGE plpgsql; But when I manually insert the values in the table, nothing change (no error message) time_index column just remain empty, what am I doing wrong??? Please some good PostgreSQL fellow programmer could give me a hand, I really have come to a death point in this task, I have any more ideas. -- View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] INSERT a number in a column based on other columns OLD INSERTs
On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: Hello I just made a short test with the code provided. As Bill mentioned the moment when the trigger is fired is essential. I made a test with both before (worked) and after (did not work because the row was already inserted and the returned new row is ignored). The assignment (= or :=) does not seem to play a role, but the correct version is as mentioned := Yea, I can't seem to remember this part of the docs: Equal (=) can be used instead of PL/SQL-compliant :=. Bye Charles -- 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] INSERT a number in a column based on other columns OLD INSERTs
On Sat, 20 Jun 2015 10:44:21 -0700 (MST) litu16 litumelen...@gmail.com wrote: In PostgreSQL I have this table... (there is a primary key in the most left side timestamp02 which is not shown in this image) in the table above, all columns are entered via querrys, except the time_index which I would like to be filled automatically via a trigger each time each row is filled. This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel. *CREATE TABLE table_ebscb_spa_log02 ( pcnum smallint, timestamp02 timestamp with time zone NOT NULL DEFAULT now(), fn_name character varying, time time without time zone, time_elapse character varying, time_type character varying, time_index real, CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02) ) WITH ( OIDS=FALSE ); ALTER TABLE table_ebscb_spa_log02 OWNER TO postgres;* What I would like the trigger to do is: INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row. If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column, Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one before(above) in the time_index column. (pls look at the example table image, this trigger will produce every red highlighted square on it) I have tried so far this to create the function: CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix = 1; ELSE t_ix = t_ix + 1; END IF; END IF; NEW.time_index = t_ix; return NEW; END $$ LANGUAGE plpgsql; But when I manually insert the values in the table, nothing change (no error message) time_index column just remain empty, what am I doing wrong??? Please some good PostgreSQL fellow programmer could give me a hand, I really have come to a death point in this task, I have any more ideas. Couple things. First off, you don't show your statement for creating the trigger. This is important. The trigger has to be a BEFORE trigger FOR EACH ROW. Otherwise, the returned value won't do anything. It should read like this: CREATE TRIGGER trigger_name BEFORE INSERT ON table_ebscb_spa_log02 FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable(); If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger won't work as desired. The other thing about assignment being := was already mentioned. -- Bill Moran -- 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] INSERT a number in a column based on other columns OLD INSERTs
Hello I just made a short test with the code provided. As Bill mentioned the moment when the trigger is fired is essential. I made a test with both before (worked) and after (did not work because the row was already inserted and the returned new row is ignored). The assignment (= or :=) does not seem to play a role, but the correct version is as mentioned := Bye Charles On 6/20/2015 21:37, Bill Moran wrote: On Sat, 20 Jun 2015 10:44:21 -0700 (MST) litu16 litumelen...@gmail.com wrote: In PostgreSQL I have this table... (there is a primary key in the most left side timestamp02 which is not shown in this image) in the table above, all columns are entered via querrys, except the time_index which I would like to be filled automatically via a trigger each time each row is filled. This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel. *CREATE TABLE table_ebscb_spa_log02 ( pcnum smallint, timestamp02 timestamp with time zone NOT NULL DEFAULT now(), fn_name character varying, time time without time zone, time_elapse character varying, time_type character varying, time_index real, CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02) ) WITH ( OIDS=FALSE ); ALTER TABLE table_ebscb_spa_log02 OWNER TO postgres;* What I would like the trigger to do is: INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row. If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column, Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one before(above) in the time_index column. (pls look at the example table image, this trigger will produce every red highlighted square on it) I have tried so far this to create the function: CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$ DECLARE t_ix real; n int; BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1 INTO t_ix; GET DIAGNOSTICS n = ROW_COUNT; IF (n = 0) THEN t_ix = 1; ELSE t_ix = t_ix + 1; END IF; END IF; NEW.time_index = t_ix; return NEW; END $$ LANGUAGE plpgsql; But when I manually insert the values in the table, nothing change (no error message) time_index column just remain empty, what am I doing wrong??? Please some good PostgreSQL fellow programmer could give me a hand, I really have come to a death point in this task, I have any more ideas. Couple things. First off, you don't show your statement for creating the trigger. This is important. The trigger has to be a BEFORE trigger FOR EACH ROW. Otherwise, the returned value won't do anything. It should read like this: CREATE TRIGGER trigger_name BEFORE INSERT ON table_ebscb_spa_log02 FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable(); If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger won't work as desired. The other thing about assignment being := was already mentioned. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
