Re: [GENERAL] File system level backup of shut down standby does not work?
On 02/19/2014 02:14 AM, Antman, Jason (CMG-Atlanta) wrote:
> Juergen,
>
> I've seen this quite a lot in the past, as we do this multiple times a day.
>
> Here's the procedure we use to prevent it:
> 1) read the PID from postmaster.pid in the data directory
> 2) Issue "service postgresql-9.0 stop" (this does a fast shutdown with
> -t 600)
> 3) loop until the PID is no longer running, or a timeout is exceeded (in
> which case we error out)
> 4) the IMPORTANT part: `pg_controldata /path/to/data | grep "Database
> cluster state: *shut down"` - if pg_controldata output doesn't include
> "shut down" or "shut down in recovery", then something's amiss and the
> backup won't be clean (error in shutdown, etc.)
> 5) `sync`
> 6) now take the backup
>
> -Jason
Jason,
Thanks a lot! That's exactly what I need.
Juergen.
>
> On 02/17/2014 08:32 AM, Jürgen Fuchsberger wrote:
>> Hi all,
>>
>> I have a master-slave configuration running the master with WAL
>> archiving enabled and the slave in recovery mode reading back the WAL
>> files from the master ("Log-shipping standby" as described in
>> http://www.postgresql.org/docs/9.1/static/warm-standby.html)
>>
>> I take frequent backups of the standby server:
>>
>> 1) Stop standby server (fast shutdown).
>> 2) Rsync to another fileserver
>> 3) Start standby server.
>>
>> I just tried to recover one of these backups which *failed* with the
>> following errors:
>>
>> 2014-02-17 14:27:28 CET LOG: incomplete startup packet
>> 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery
>> at 2013-12-25 18:00:03 CET
>> 2014-02-17 14:27:28 CET LOG: could not open file
>> "pg_xlog/000101E30061" (log file 483, segment 97): No such
>> file or directory
>> 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record
>> 2014-02-17 14:27:28 CET LOG: could not open file
>> "pg_xlog/000101E30060" (log file 483, segment 96): No such
>> file or directory
>> 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record
>> 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record
>> 2014-02-17 14:27:29 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:29 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:30 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:30 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:31 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:31 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:32 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:33 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:33 CET FATAL: the database system is starting up
>> 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated
>> by signal 6: Aborted
>> 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process
>> failure
>>
>>
>> So it seems the server is missing some WAL files which are not
>> in the backup? Or is it simply not possible to take a backup of a
>> standby server in recovery?
>>
>> Best,
>> Juergen
>>
signature.asc
Description: OpenPGP digital signature
[GENERAL] plans for plpython in RDS?
Does anyone know if there are plans to support plpython in Amazon's RDS? I (approximately) understand the issue, but I don't know if there's any effort to remedy the problem or, rather, I shouldn't bother hoping. Thanks, Reece
Re: [GENERAL] File system level backup of shut down standby does not work?
Juergen,
I've seen this quite a lot in the past, as we do this multiple times a day.
Here's the procedure we use to prevent it:
1) read the PID from postmaster.pid in the data directory
2) Issue "service postgresql-9.0 stop" (this does a fast shutdown with
-t 600)
3) loop until the PID is no longer running, or a timeout is exceeded (in
which case we error out)
4) the IMPORTANT part: `pg_controldata /path/to/data | grep "Database
cluster state: *shut down"` - if pg_controldata output doesn't include
"shut down" or "shut down in recovery", then something's amiss and the
backup won't be clean (error in shutdown, etc.)
5) `sync`
6) now take the backup
-Jason
On 02/17/2014 08:32 AM, Jürgen Fuchsberger wrote:
> Hi all,
>
> I have a master-slave configuration running the master with WAL
> archiving enabled and the slave in recovery mode reading back the WAL
> files from the master ("Log-shipping standby" as described in
> http://www.postgresql.org/docs/9.1/static/warm-standby.html)
>
> I take frequent backups of the standby server:
>
> 1) Stop standby server (fast shutdown).
> 2) Rsync to another fileserver
> 3) Start standby server.
>
> I just tried to recover one of these backups which *failed* with the
> following errors:
>
> 2014-02-17 14:27:28 CET LOG: incomplete startup packet
> 2014-02-17 14:27:28 CET LOG: database system was shut down in recovery
> at 2013-12-25 18:00:03 CET
> 2014-02-17 14:27:28 CET LOG: could not open file
> "pg_xlog/000101E30061" (log file 483, segment 97): No such
> file or directory
> 2014-02-17 14:27:28 CET LOG: invalid primary checkpoint record
> 2014-02-17 14:27:28 CET LOG: could not open file
> "pg_xlog/000101E30060" (log file 483, segment 96): No such
> file or directory
> 2014-02-17 14:27:28 CET LOG: invalid secondary checkpoint record
> 2014-02-17 14:27:28 CET PANIC: could not locate a valid checkpoint record
> 2014-02-17 14:27:29 CET FATAL: the database system is starting up
> 2014-02-17 14:27:29 CET FATAL: the database system is starting up
> 2014-02-17 14:27:30 CET FATAL: the database system is starting up
> 2014-02-17 14:27:30 CET FATAL: the database system is starting up
> 2014-02-17 14:27:31 CET FATAL: the database system is starting up
> 2014-02-17 14:27:31 CET FATAL: the database system is starting up
> 2014-02-17 14:27:32 CET FATAL: the database system is starting up
> 2014-02-17 14:27:33 CET FATAL: the database system is starting up
> 2014-02-17 14:27:33 CET FATAL: the database system is starting up
> 2014-02-17 14:27:33 CET LOG: startup process (PID 26186) was terminated
> by signal 6: Aborted
> 2014-02-17 14:27:33 CET LOG: aborting startup due to startup process
> failure
>
>
> So it seems the server is missing some WAL files which are not
> in the backup? Or is it simply not possible to take a backup of a
> standby server in recovery?
>
> Best,
> Juergen
>
>
>
--
Jason Antman | Systems Engineer | CMGdigital
jason.ant...@coxinc.com | p: 678-645-4155
--
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] automatically refresh all materialized views?
On Fri, Feb 14, 2014 at 10:15 AM, Merlin Moncure wrote: > yeah -- you could do this with some gymnastics and some dynamic SQL. > If I were lazy (check), I would just encode the order in the name of > the view somehow. > Thanks. That's exactly what I do already. Apparently, I'm even lazier than you! In case anyone out there is looking for a convention, here's one that I find useful: _v for views intended for interactive queries, _dv (defining views) for views that are too slow to use interactively, and _mv for the materialized _dv views.
Re: [GENERAL] Issues with patitionning and triggers
On 2014-02-18 17:59:35 Tom Lane wrote: > Samuel Gilbert writes: > > All of this was done on PostgreSQL 9.2.0 64-bit compiled from the official > > > source. Significant changes in postgresql.conf : > Why in the world are you using 9.2.0? You're missing a year and a half > worth of bug fixes, some of them quite serious. Yes. I know and I bear the pain and shame not running at least the latest revision of the 9.2 branch. Unfortunately, it's hard to get my manager to view the update of software that "just works" as something to prioritize. The good news is that your reply is a good argument to do so! :) Cheers! > > > INSERT ... RETURNING does not work with partitioned tables, since the > > trigger function on the parent that dispatches new rows to the children > > tables, must return NULL. If the trigger function on the parent ends > > with "RETURN NEW", INSERT ... RETURNING works, but new rows are > > duplicated; they are inserted both in the parent and child tables. > > > > Is there a way to make INSERT ... RETURNING work without duplicating the > > rows? > Fraid not --- it only shows what got inserted into the parent table, which > is nothing if you're using this technique. > > > The modification date must be updated if any row is modified in any way. > > I > > first tried to define the triggers on the parent table. This worked, but > > I > > realized that if a queries targets explicitly a child table, it could > > modify a row without the date being updated. I therefore dropped the > > triggers on the parent table and defined them for every child. To my > > great surprise, the insert below failed with a message saying that NULLs > > are not allowed in the modificationdate column. > > You'd have to provide a self-contained example for anyone to help you with > that. The most obvious explanation is that you forgot to attach the > trigger to the specific child table ... > > 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] Issues with patitionning and triggers
> "The modification date must be updated if any row is modified in any way." > > If that is the case shouldn't the trigger also cover UPDATE? You completely right about that! I actually have both configured, but I focused only on the INSERT to try keep the length of my post as short as possible. As Tom Lane pointed out, it's hard to get help without a complete self- contained example. I will work on writing that up tomorrow. Cheers! On 2014-02-18 15:02:41 Adrian Klaver wrote: > On 02/18/2014 02:42 PM, Samuel Gilbert wrote: > > On 2014-02-18 14:25:59 Adrian Klaver wrote: > >> On 02/18/2014 02:10 PM, Samuel Gilbert wrote: > >>> I have data warehousing DB 2 fairly big tables : one contains about 200 > >>> million rows and the other one contains about 4 billion rows. Some > >>> queries > >>> are now taking way too long to run (> 13 hours). I need to get these > >>> queries to run in an hour or so. The slowdown was gradual, but I > >>> eventually hit a wall, when the planner stopped using indexes. > >>> > >>> > >>> The other issue I'm encountering is that I also have very simple BEFORE > >>> UPDATE and BEFORE INSERT triggers that set the modification date on > >>> every > >>> single row > >>> > >>> > >>> CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$ > >>> BEGIN > >>> > >>> NEW.modificationDate := now(); > >>> RETURN NEW; > >>> > >>> END; > >>> $$ LANGUAGE 'plpgsql'; > >>> > >>> The modification date must be updated if any row is modified in any way. > >>> I > >>> first tried to define the triggers on the parent table. This worked, > >>> but > >>> I > >>> realized that if a queries targets explicitly a child table, it could > >>> modify a row without the date being updated. I therefore dropped the > >>> triggers on the parent table and defined them for every child. To my > >>> great surprise, the insert below failed with a message saying that NULLs > >>> are not allowed in the modificationdate column. > >>> > >>> INSERT INTO observation > >>> (dataset, station, method, startdate, duration, value) > >>> VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42); > >>> > >>> Why isn't the BEFORE INSERT trigger on the child table being executed? > >> > >> Constraints are checked before triggers are run. > >> > >>> Cheers, > >>> Samuel Gilbert > > > > I don't think that is the case since I currently have the BEFORE INSERT > > trigger working on the non-partitioned version of the table. > > Sorry for steering you wrong. I could have sworn I saw the behavior I > mentioned, previously, when trying to do what you have done. > > > The modificationdate field has a NOT NULL constraint. Even if I > > explicitly > > provide a NULL for the modificationdate column, a date gets written in the > > table. This leads me to believe that the BEFORE INSERT trigger is really > > executed before the constraint is checked. > > > > What I don't understand is why the trigger doesn't appear to be executed > > when it's defined on a child table. I'll add a RAISE NOTICE to the > > trigger function to makes sure it's not getting called. > > Still not sure what is going on, but I do have a question based on this > statement from your original post: > > "The modification date must be updated if any row is modified in any way." > > If that is the case shouldn't the trigger also cover UPDATE? -- 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] Issues with patitionning and triggers
On 02/18/2014 02:42 PM, Samuel Gilbert wrote: On 2014-02-18 14:25:59 Adrian Klaver wrote: On 02/18/2014 02:10 PM, Samuel Gilbert wrote: I have data warehousing DB 2 fairly big tables : one contains about 200 million rows and the other one contains about 4 billion rows. Some queries are now taking way too long to run (> 13 hours). I need to get these queries to run in an hour or so. The slowdown was gradual, but I eventually hit a wall, when the planner stopped using indexes. The other issue I'm encountering is that I also have very simple BEFORE UPDATE and BEFORE INSERT triggers that set the modification date on every single row CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$ BEGIN NEW.modificationDate := now(); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; The modification date must be updated if any row is modified in any way. I first tried to define the triggers on the parent table. This worked, but I realized that if a queries targets explicitly a child table, it could modify a row without the date being updated. I therefore dropped the triggers on the parent table and defined them for every child. To my great surprise, the insert below failed with a message saying that NULLs are not allowed in the modificationdate column. INSERT INTO observation (dataset, station, method, startdate, duration, value) VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42); Why isn't the BEFORE INSERT trigger on the child table being executed? Constraints are checked before triggers are run. Cheers, Samuel Gilbert I don't think that is the case since I currently have the BEFORE INSERT trigger working on the non-partitioned version of the table. Sorry for steering you wrong. I could have sworn I saw the behavior I mentioned, previously, when trying to do what you have done. The modificationdate field has a NOT NULL constraint. Even if I explicitly provide a NULL for the modificationdate column, a date gets written in the table. This leads me to believe that the BEFORE INSERT trigger is really executed before the constraint is checked. What I don't understand is why the trigger doesn't appear to be executed when it's defined on a child table. I'll add a RAISE NOTICE to the trigger function to makes sure it's not getting called. Still not sure what is going on, but I do have a question based on this statement from your original post: "The modification date must be updated if any row is modified in any way." If that is the case shouldn't the trigger also cover UPDATE? -- 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] Issues with patitionning and triggers
Samuel Gilbert writes: > All of this was done on PostgreSQL 9.2.0 64-bit compiled from the official > source. Significant changes in postgresql.conf : Why in the world are you using 9.2.0? You're missing a year and a half worth of bug fixes, some of them quite serious. > INSERT ... RETURNING does not work with partitioned tables, since the trigger > function on the parent that dispatches new rows to the children tables, must > return NULL. If the trigger function on the parent ends with "RETURN NEW", > INSERT ... RETURNING works, but new rows are duplicated; they are inserted > both in the parent and child tables. > Is there a way to make INSERT ... RETURNING work without duplicating the rows? Fraid not --- it only shows what got inserted into the parent table, which is nothing if you're using this technique. > The modification date must be updated if any row is modified in any way. I > first tried to define the triggers on the parent table. This worked, but I > realized that if a queries targets explicitly a child table, it could modify > a > row without the date being updated. I therefore dropped the triggers on the > parent table and defined them for every child. To my great surprise, the > insert below failed with a message saying that NULLs are not allowed in the > modificationdate column. You'd have to provide a self-contained example for anyone to help you with that. The most obvious explanation is that you forgot to attach the trigger to the specific child table ... 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] Issues with patitionning and triggers
On 2014-02-18 14:25:59 Adrian Klaver wrote: > On 02/18/2014 02:10 PM, Samuel Gilbert wrote: > > I have data warehousing DB 2 fairly big tables : one contains about 200 > > million rows and the other one contains about 4 billion rows. Some > > queries > > are now taking way too long to run (> 13 hours). I need to get these > > queries to run in an hour or so. The slowdown was gradual, but I > > eventually hit a wall, when the planner stopped using indexes. > > > > > > The other issue I'm encountering is that I also have very simple BEFORE > > UPDATE and BEFORE INSERT triggers that set the modification date on every > > single row > > > > > > CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$ > > BEGIN > > > > NEW.modificationDate := now(); > > RETURN NEW; > > > > END; > > $$ LANGUAGE 'plpgsql'; > > > > The modification date must be updated if any row is modified in any way. > > I > > first tried to define the triggers on the parent table. This worked, but > > I > > realized that if a queries targets explicitly a child table, it could > > modify a row without the date being updated. I therefore dropped the > > triggers on the parent table and defined them for every child. To my > > great surprise, the insert below failed with a message saying that NULLs > > are not allowed in the modificationdate column. > > > > INSERT INTO observation > > (dataset, station, method, startdate, duration, value) > > VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42); > > > > Why isn't the BEFORE INSERT trigger on the child table being executed? > > Constraints are checked before triggers are run. > > > Cheers, > > Samuel Gilbert I don't think that is the case since I currently have the BEFORE INSERT trigger working on the non-partitioned version of the table. The modificationdate field has a NOT NULL constraint. Even if I explicitly provide a NULL for the modificationdate column, a date gets written in the table. This leads me to believe that the BEFORE INSERT trigger is really executed before the constraint is checked. What I don't understand is why the trigger doesn't appear to be executed when it's defined on a child table. I'll add a RAISE NOTICE to the trigger function to makes sure it's not getting called. -- 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] syslog facilites and postgres ?
Tom, I will not claim I've totally observed all the fallout of attempting this. You may be correct about subsequent local0 output being bollixed but I certainly have seen some continued output to local0 after the trigger. I am not committed to this method. It was primarily an experiment for proof of concept. Based on your reservations, I will pursue some other ideas. Thanks much for the feedback and preventing me from wasting to much time on this avenue. Rgds Dave -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, February 18, 2014 4:02 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] syslog facilites and postgres ? "Day, David" writes: > Should I be able to run two syslog facilities simultaneously ( postgres > local0, and a trigger function to local3 ) successfully ? Probably not. libc's support for writing to syslog is not re-entrant. > I have created an insert trigger on one of my datatables using > plperlu that opens syslog to facility local3 which directs portions of that > newly inserted record to local3. ( see below). This works with the the > following issue. > I find occasional notifications in my local3 log file that should I believe > be in the local0 log file. E.g. I will see some of > my RAISE LOG output in the local3 output log file rather than in the local0 > output log file. I suspect this is happening > during the window in which local3 is open and closed in the trigger function. > I note that the errant notification I find in local3 is not also found > in local0. TBH, I'm astonished that this doesn't break logging to local0 entirely. Quite aside from the effect you're complaining of, I'd expect it to bollix subsequent syslog output. After your closelog(), the next syslog() call would do an implicit openlog(), but with default parameters --- or so I'd expect anyway. You could probably un-break that aspect by duplicating Postgres' normal openlog call after closing the local3 descriptor: openlog(syslog_ident ? syslog_ident : "postgres", LOG_PID | LOG_NDELAY | LOG_NOWAIT, syslog_facility); but this seems awfully fragile, and it certainly won't do anything for any messages Postgres tries to emit while you've got the syslog connection redirected to local3. Do you really need to have it work like that? 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] Issues with patitionning and triggers
On 02/18/2014 02:10 PM, Samuel Gilbert wrote: I have data warehousing DB 2 fairly big tables : one contains about 200 million rows and the other one contains about 4 billion rows. Some queries are now taking way too long to run (> 13 hours). I need to get these queries to run in an hour or so. The slowdown was gradual, but I eventually hit a wall, when the planner stopped using indexes. The other issue I'm encountering is that I also have very simple BEFORE UPDATE and BEFORE INSERT triggers that set the modification date on every single row : CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$ BEGIN NEW.modificationDate := now(); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; The modification date must be updated if any row is modified in any way. I first tried to define the triggers on the parent table. This worked, but I realized that if a queries targets explicitly a child table, it could modify a row without the date being updated. I therefore dropped the triggers on the parent table and defined them for every child. To my great surprise, the insert below failed with a message saying that NULLs are not allowed in the modificationdate column. INSERT INTO observation (dataset, station, method, startdate, duration, value) VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42); Why isn't the BEFORE INSERT trigger on the child table being executed? Constraints are checked before triggers are run. Cheers, Samuel Gilbert -- 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
[GENERAL] Issues with patitionning and triggers
I have data warehousing DB 2 fairly big tables : one contains about 200
million rows and the other one contains about 4 billion rows. Some queries
are now taking way too long to run (> 13 hours). I need to get these queries
to run in an hour or so. The slowdown was gradual, but I eventually hit a
wall, when the planner stopped using indexes.
All of this was done on PostgreSQL 9.2.0 64-bit compiled from the official
source. Significant changes in postgresql.conf :
shared_buffers = 8GB
work_mem = 8GB
maintenance_work_mem = 8GB
max_stack_depth = 2MB
Here is the information about the big tables :
SELECT
nspname || '.' || relname AS relation,
pg_stat_get_live_tuples(C.oid) AS live_tuples,
pg_size_pretty(pg_relation_size(C.oid, 'main')) AS relation_size,
pg_size_pretty(pg_indexes_size(C.oid)) AS indexes_size,
pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
nspname NOT IN ('pg_catalog', 'information_schema') AND
C.relkind <> 'i' AND
nspname !~ '^pg_toast' AND
relname IN ('obs', 'forecast');
relation | live_tuples | relation_size | indexes_size | total_size
-+-+---+--+
public.obs | 193235914 | 18 GB | 15 GB| 33 GB
public.forecast | 3914247064 | 425 GB| 148 GB | 573 GB
>From what I read, the planner probably stopped using indexes since they are so
big compared to the system RAM (64 GB). I therefore tried partitioning the
obs table. The forecast table is the biggest issue, but I wanted to be able
to try this out in a single day before tackling the real monster.
Here is the structure of the parent table :
Table "public.observation"
Column |Type | Modifiers
--+-+---
station | integer | not null
method | integer | not null
startdate| timestamp without time zone | not null
duration | interval| not null
value| real| not null
dataset | integer | not null
modificationdate | timestamp without time zone | not null
Check constraints:
"observation_check1" CHECK ((startdate + duration) < now())
"observation_duration_check1" CHECK (duration > '00:00:00'::interval)
Foreign-key constraints:
"observation_dataset_fkey1" FOREIGN KEY (dataset) REFERENCES dataset(id)
MATCH FULL
"observation_method_fkey1" FOREIGN KEY (method) REFERENCES method(id)
MATCH FULL
"observation_station_fkey1" FOREIGN KEY (station) REFERENCES station(id)
MATCH FULL
Triggers:
trigger_insert_00 BEFORE INSERT ON observation FOR EACH ROW EXECUTE
PROCEDURE observation_insert_trigger()
INSERT ... RETURNING does not work with partitioned tables, since the trigger
function on the parent that dispatches new rows to the children tables, must
return NULL. If the trigger function on the parent ends with "RETURN NEW",
INSERT ... RETURNING works, but new rows are duplicated; they are inserted
both in the parent and child tables.
Is there a way to make INSERT ... RETURNING work without duplicating the rows?
The other issue I'm encountering is that I also have very simple BEFORE UPDATE
and BEFORE INSERT triggers that set the modification date on every single row
:
CREATE FUNCTION set_modificationDate() RETURNS TRIGGER AS $$
BEGIN
NEW.modificationDate := now();
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
The modification date must be updated if any row is modified in any way. I
first tried to define the triggers on the parent table. This worked, but I
realized that if a queries targets explicitly a child table, it could modify a
row without the date being updated. I therefore dropped the triggers on the
parent table and defined them for every child. To my great surprise, the
insert below failed with a message saying that NULLs are not allowed in the
modificationdate column.
INSERT INTO observation
(dataset, station, method, startdate, duration, value)
VALUES (9, 2, 128, '2014-01-01 00:00:00', '24 hours', 42);
Why isn't the BEFORE INSERT trigger on the child table being executed?
Cheers,
Samuel Gilbert
--
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] syslog facilites and postgres ?
"Day, David" writes: > Should I be able to run two syslog facilities simultaneously ( postgres > local0, and a trigger function to local3 ) successfully ? Probably not. libc's support for writing to syslog is not re-entrant. > I have created an insert trigger on one of my datatables using plperlu that > opens syslog to facility local3 which directs > portions of that newly inserted record to local3. ( see below). This works > with the the following issue. > I find occasional notifications in my local3 log file that should I believe > be in the local0 log file. E.g. I will see some of > my RAISE LOG output in the local3 output log file rather than in the local0 > output log file. I suspect this is happening > during the window in which local3 is open and closed in the trigger function. > I note that the errant notification I find in local3 is not also found > in local0. TBH, I'm astonished that this doesn't break logging to local0 entirely. Quite aside from the effect you're complaining of, I'd expect it to bollix subsequent syslog output. After your closelog(), the next syslog() call would do an implicit openlog(), but with default parameters --- or so I'd expect anyway. You could probably un-break that aspect by duplicating Postgres' normal openlog call after closing the local3 descriptor: openlog(syslog_ident ? syslog_ident : "postgres", LOG_PID | LOG_NDELAY | LOG_NOWAIT, syslog_facility); but this seems awfully fragile, and it certainly won't do anything for any messages Postgres tries to emit while you've got the syslog connection redirected to local3. Do you really need to have it work like that? 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
[GENERAL] syslog facilites and postgres ?
Hi,
Should I be able to run two syslog facilities simultaneously ( postgres local0,
and a trigger function to local3 ) successfully ?
I have postgresql 9.3.1exit running on freebsd and sending errors to
"syslog_facility= local0".
That works fine.
I have created an insert trigger on one of my datatables using plperlu that
opens syslog to facility local3 which directs
portions of that newly inserted record to local3. ( see below). This works
with the the following issue.
I find occasional notifications in my local3 log file that should I believe be
in the local0 log file. E.g. I will see some of
my RAISE LOG output in the local3 output log file rather than in the local0
output log file. I suspect this is happening
during the window in which local3 is open and closed in the trigger function.
I note that the errant notification I find in local3 is not also found
in local0.
Any suggestions on this concept ?
Thanks
Dave Day
CREATE OR REPLACE FUNCTION log.connection_history_post_insert()
RETURNS trigger AS
$BODY$
use Sys::Syslog;
openlog('smdr', 'ndelay,pid', 'local3');
my $tmp = $_TD->{new} {orig_addr} .'->' . $_TD->{new} {term_addr} . '~' .
$_TD->{new} {answer_datetime}
syslog ("info", "data %s ", $tmp);
closelog();
return;
$BODY$
LANGUAGE plperlu VOLATILE
Re: [GENERAL] Is pgFoundry Down? (2/18/2014)
Thanks Magnus, I too checked that before coming here. I'm really looking for a reason from someone that may have some more information. Also potentially another download location for pg_bulkload 3.1.5+. http://ftp.postgresql.org/pub/projects/pgFoundry/pgbulkload/pg_bulkload-3.1/only goes to 3.1.4 *Kyle W. Purdon* Research Assistant | Center for Remote Sensing of Ice Sheets (CReSIS) https://www.cresis.ku.edu/~kpurdon/ Graduate Student | University of Kansas Geography (GIS) http://www.geog.ku.edu/ On Tue, Feb 18, 2014 at 11:21 AM, Magnus Hagander wrote: > > On Tue, Feb 18, 2014 at 6:07 PM, Purdon wrote: > >> It seems that pgfoundry.org is down? Is this the case for everyone? >> > > According to http://www.downforeveryoneorjustme.com/pgfoundry.org it is, > yeah. > > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ >
Re: [GENERAL] Is pgFoundry Down? (2/18/2014)
On Tue, Feb 18, 2014 at 6:07 PM, Purdon wrote: > It seems that pgfoundry.org is down? Is this the case for everyone? > According to http://www.downforeveryoneorjustme.com/pgfoundry.org it is, yeah. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
[GENERAL] Is pgFoundry Down? (2/18/2014)
It seems that pgfoundry.org is down? Is this the case for everyone? If so is there any other location to get pg_bulkload 3.1.5+ (It is not on the FTP mirror of pgFoundry) Thanks, *Kyle W. Purdon* Research Assistant | Center for Remote Sensing of Ice Sheets (CReSIS) https://www.cresis.ku.edu/~kpurdon/ Graduate Student | University of Kansas Geography (GIS) http://www.geog.ku.edu/
Re: [GENERAL] How do I track down a possible locking problem?
On Mon, Feb 17, 2014 at 8:45 AM, Herouth Maoz wrote: > I have a production system using Postgresql 9.1.2. > > The system basically receives messages, puts them in a queue, and then > several parallel modules, each in its own thread, read from that queue, and > perform two inserts, then release the message to the next queue for > non-database-related processing. > > Today, after we received complaints from a customer about delays, I > noticed odd behavior in the system. About 2 percent of the messages were > inserted into the tables more than an hour after they got into the system. > How do you know that? > > The queue never has more than 27,000 messages at the same time, and all > together, the parallel modules process about 5000 or 6000 messages per > minute. So basically, the delay for a single message should never be more > than a few minutes. Even if one module gets stuck, another will grab the > next message from the queue. I believe the only way for a message to be > stuck for so long would be for it to be grabbed by a module, and then for > the database write to be somehow delayed for a hour, although it's merely a > simple insert performed with a prepared statement. > > The database in production is very busy with millions of writes per hour. > Could there be a situation in which a particular connection gets "starved" > while other connections are able to run queries without noticeable delay? > If there is a delay like that, it would almost certainly be due to database locks that show up in pg_locks. http://www.postgresql.org/docs/current/static/view-pg-locks.html http://wiki.postgresql.org/wiki/Lock_Monitoring http://wiki.postgresql.org/wiki/Lock_dependency_information But, I doubt that that is your problem. > > How can I truck such locks down? Does anybody have any ideas other than > starvation? The system lets me view statistics of how many messages were > processed in each modules and the average latency. None of the four modules > running has long average latency or low number of messages processes, so I > don't think the issue is related to any particular thread in my (Java) > system being slow or locked away by the others. > If the insert into PostgreSQL was freezing, wouldn't that time get reflected in your latency monitoring? It sounds to me like your application has a bug in its queue, where it forgets about items on the queue for a while. Cheers, Jeff
Re: [GENERAL] Is PostgreSQL 9.3 using indexes for pipelined top-N window function queries?
On Fri, Feb 14, 2014 at 7:35 PM, Behrang Saeedzadeh wrote: > Hi, > > I just stumbled upon this article from 2012 [1], according to which > (emphasis mine): > > Window functions offer yet another way to implement pagination in SQL. This > is a flexible, and above all, standards-compliant method. However, only SQL > Server and the Oracle database can use them for a pipelined top-N query. > PostgreSQL does not use indexes for those queries and therefore executes > them very inefficiently. MySQL does not support window functions at all. > > > Is this still the case? Or is PostgreSQL 9.3 capable to execute suchlike > queries efficiently? oracle: SELECT * FROM ( SELECT sales.* , ROW_NUMBER() OVER (ORDER BY sale_date DESC , sale_id DESC) rn FROM sales ) tmp WHERE rn between 11 and 20 ORDER BY sale_date DESC, sale_id DESC; postgres: SELECT * FROM sales s WHERE (sale_date, sale_id) < (last_date, last_Id) ORDER BY sale_date DESC, sale_id DESC LIMIT 10; The postgres variant is superior in my opinion (it will be faster for large offsets). last_date, last_id are the lowest values you previously read off. It will use an index on those two columns if you have one. One interesting distinction is that the postgres variant will always move forward while the oracle variant can appear to move backwards if you are doing a non transactional scan. Also, you can always use a cursor in either database. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I track down a possible locking problem?
Is there a more appropriate place to ask this question? Or was my question unclear? I dug some data, and it seems that whenever messages come at a rate of 75,000 per hour, they start picking delays of up to 10 minutes. If I go up to 100,000, delays pick up to about 20 minutes. And for 300,000 in one hour, I get delays of up to 3 hours or so. Typically, during an hour in which 250,000 messages were processed, around 10% of them are delayed more than 20 minutes, and some for more than an hour. Can anybody offer any insight? Do any of you get delays in inserts performed at this rate (250,000 per hour), meaning that the insert transaction takes more than 10 minutes? Is it a matter of fine-tuning the server? Note that at the same time there are other processes who also perform updates on the same tables, at about the same rate. So each of the tables gets a write about 500,000 times per hour. The table normally contains around 2-3 million records, and has 3 indexes. Thank you, Herouth On 17/02/2014, at 18:45, Herouth Maoz wrote: > I have a production system using Postgresql 9.1.2. > > The system basically receives messages, puts them in a queue, and then > several parallel modules, each in its own thread, read from that queue, and > perform two inserts, then release the message to the next queue for > non-database-related processing. > > Today, after we received complaints from a customer about delays, I noticed > odd behavior in the system. About 2 percent of the messages were inserted > into the tables more than an hour after they got into the system. > > The queue never has more than 27,000 messages at the same time, and all > together, the parallel modules process about 5000 or 6000 messages per > minute. So basically, the delay for a single message should never be more > than a few minutes. Even if one module gets stuck, another will grab the next > message from the queue. I believe the only way for a message to be stuck for > so long would be for it to be grabbed by a module, and then for the database > write to be somehow delayed for a hour, although it's merely a simple insert > performed with a prepared statement. > > The database in production is very busy with millions of writes per hour. > Could there be a situation in which a particular connection gets "starved" > while other connections are able to run queries without noticeable delay? > > How can I truck such locks down? Does anybody have any ideas other than > starvation? The system lets me view statistics of how many messages were > processed in each modules and the average latency. None of the four modules > running has long average latency or low number of messages processes, so I > don't think the issue is related to any particular thread in my (Java) system > being slow or locked away by the others. > > TIA, > Herouth >
Re: [GENERAL] How to discard partially retrieved result set with the C API?
2014-02-18 11:53 GMT+01:00 Dmitriy Igrishin : > > > 2014-02-18 13:44 GMT+04:00 邓尧 : > >> When single row mode is enabled, after retrieving part of the result set, >> I'm no longer interested in the rest of it (due to error handling or other >> reasons). How can I discard the result set without repeatedly calling >> PQgetResult() in such situation ? >> The result set may be quite large and it's inefficient to call >> PQgetResult() repeatedly, so it's necessary to do so sometimes. >> >> Thanks >> Yao >> > I think you should use PQcancel(). > is not better to use a cursor for this use case? Pavel > > -- > // Dmitriy. > >
Re: [GENERAL] How to discard partially retrieved result set with the C API?
2014-02-18 13:44 GMT+04:00 邓尧 : > When single row mode is enabled, after retrieving part of the result set, > I'm no longer interested in the rest of it (due to error handling or other > reasons). How can I discard the result set without repeatedly calling > PQgetResult() in such situation ? > The result set may be quite large and it's inefficient to call > PQgetResult() repeatedly, so it's necessary to do so sometimes. > > Thanks > Yao > I think you should use PQcancel(). -- // Dmitriy.
[GENERAL] How to discard partially retrieved result set with the C API?
When single row mode is enabled, after retrieving part of the result set, I'm no longer interested in the rest of it (due to error handling or other reasons). How can I discard the result set without repeatedly calling PQgetResult() in such situation ? The result set may be quite large and it's inefficient to call PQgetResult() repeatedly, so it's necessary to do so sometimes. Thanks Yao
Re: [GENERAL] Deleted files still open long after droping a database
On Monday 17 February 2014 21:14:35 Tom Lane wrote: > Kevin Grittner writes: > > Perhaps we should arrange for a DROP DATABASE command to somehow > > signal all backends to close files from that backend? > > See commit ff3f9c8de, which was back-patched into 9.1.x as of 9.1.7. > > Unfortunately, the complainant is running 9.1.2. Thanks for the detailed info, great to see it's already fixed. As it happens, we're in the process of moving to new servers, where we'll strive to get a better update process going. -- Vincent de Phily -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
