[GENERAL] Partitionning by trigger
Hi all, I sent the following mail yesterday but I do not see it in the list. So I retry == Hi, I have created a partitionned table and a Pl/PgSQL trigger exactly as explained herehttp://www.postgresql.org/docs/9.2/static/ddl-partitioning.htmlin the documentation. It works fine but the INSERT and COPY commands return zero instead of the number of the rows actually inserted. Worse : the RETURNING clause returns NULL when inserting through the trigger. I encounter the same problem with a C language trigger function. Is there anyway to obtain a behavior similar to that of the direct insertion ? I use PostgreSQL 9.2.3 in Linux Fedora 2.6 environment. I can send a simplified self contained test case if required. Thanks for your answer Best Regards Ali Pouya
Re: [GENERAL] Partitionning by trigger
Ali Pouya wrote: I sent the following mail yesterday but I do not see it in the list. So I retry It is there all right: http://www.postgresql.org/message-id/CAEEEPmwq_3=hGEC69-2EkCWTiwq0dme==8sou29e9k2dccg...@mail.gmail.com I have created a partitionned table and a Pl/PgSQL trigger exactly as explained here http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html in the documentation. It works fine but the INSERT and COPY commands return zero instead of the number of the rows actually inserted. Worse : the RETURNING clause returns NULL when inserting through the trigger. I encounter the same problem with a C language trigger function. Is there anyway to obtain a behavior similar to that of the direct insertion ? I don't think that is possible. 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] JDBC not returning update count from updateable view
As far as I remember this is an artifact of using rules to update a table.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Mon, Feb 25, 2013 at 2:52 PM, Russell Keane russell.ke...@inps.co.ukwrote:
Hi,
** **
We have a table which is inserted to and update via a view (using rules /
functions).
** **
We are trying to update this from JDBC but the view update command (on the
java side) doesn’t return the count of rows updated. I assume this is
because the postgres update function actually returns a tuple rather than a
single count.
** **
Any ideas?
** **
A simplified version of the java bit:
** **
JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;
** **
*public* *final* *int* updateTest(*final* String updateSQL,
*final*Object[] args) {
JdbcTemplate template = createJdbcTemplate();
*return* template.update(updateSQL, args);
}
** **
And the postgres object creation (again simplified):
** **
--PG START
** **
drop table if exists msg_table cascade;
drop sequence if exists msg_seq;
drop sequence if exists msg_aud_seq;
create sequence msg_seq;
create sequence msg_aud_seq;
** **
CREATE TABLE msg_table
(
aud_seq int default nextval('msg_aud_seq'),
status int default 1,
id int default nextval('msg_seq'),
val int
);
** **
create or replace view msg as
select
aud_seq,
id,
status,
val
from msg_table;
** **
-- audit the original record
CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$**
**
BEGIN
UPDATE msg_table
SET
status = 2
WHERE
aud_seq = $1.aud_seq;
END;
$$ LANGUAGE plpgsql;
** **
** **
-- insert function
CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$**
**
declare new_id integer;
BEGIN
INSERT INTO msg_table
(
val
)
SELECT
$1.val
RETURNING id INTO new_id;
return new_id;
END;
$body$ LANGUAGE plpgsql;
** **
-- update function
CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
BEGIN
INSERT INTO msg_table
(
id,
val
)
SELECT
$1.id,
$1.val;
** **
EXECUTE audit_original_record($2);
** **
END;
$body$ LANGUAGE plpgsql;
** **
-- insert to msg
create or replace rule msg__rule_ins as on insert to msg
do instead
SELECT process_insert(NEW);
** **
-- update to msg
create or replace rule msg__rule_upd as on update to msg
do instead
SELECT
COUNT(process_update(NEW, OLD))
WHERE
NEW.status = 1;
** **
** **
alter sequence msg_seq restart 1;
alter sequence msg_aud_seq restart 1;
** **
delete from msg_table;
** **
insert into msg
(val)
values
(1),
(2),
(66);
** **
select * from msg;
** **
update msg
set val = 5
where id = 1;
** **
select * from msg;
** **
--PG END
** **
** **
Thanks for any help you can give me.
** **
Regards,
** **
*Russell Keane***
*INPS*
Follow us https://twitter.com/INPSnews on twitter | visit www.inps.co.uk
** **
--
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended
solely for the addressee. Access, copying or re-use of information in it by
anyone else is not authorised. Any views or opinions presented are solely
those of the author and do not
Re: [GENERAL] JDBC not returning update count from updateable view
But this works just fine when using libpq via c++.
From: davecra...@gmail.com [mailto:davecra...@gmail.com] On Behalf Of Dave
Cramer
Sent: 26 February 2013 12:23
To: Russell Keane
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] JDBC not returning update count from updateable view
As far as I remember this is an artifact of using rules to update a table.
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Mon, Feb 25, 2013 at 2:52 PM, Russell Keane
russell.ke...@inps.co.ukmailto:russell.ke...@inps.co.uk wrote:
Hi,
We have a table which is inserted to and update via a view (using rules /
functions).
We are trying to update this from JDBC but the view update command (on the java
side) doesn't return the count of rows updated. I assume this is because the
postgres update function actually returns a tuple rather than a single count.
Any ideas?
A simplified version of the java bit:
JdbcTemplate is: org.springframework.jdbc.core.JdbcTemplate;
public final int updateTest(final String updateSQL, final Object[] args) {
JdbcTemplate template = createJdbcTemplate();
return template.update(updateSQL, args);
}
And the postgres object creation (again simplified):
--PG START
drop table if exists msg_table cascade;
drop sequence if exists msg_seq;
drop sequence if exists msg_aud_seq;
create sequence msg_seq;
create sequence msg_aud_seq;
CREATE TABLE msg_table
(
aud_seq int default nextval('msg_aud_seq'),
status int default 1,
id int default nextval('msg_seq'),
val int
);
create or replace view msg as
select
aud_seq,
id,
status,
val
from msg_table;
-- audit the original record
CREATE OR REPLACE FUNCTION audit_original_record(msg) RETURNS void AS $$
BEGIN
UPDATE msg_table
SET
status = 2
WHERE
aud_seq = $1.aud_seq;
END;
$$ LANGUAGE plpgsql;
-- insert function
CREATE OR REPLACE FUNCTION process_insert(msg) RETURNS integer AS $body$
declare new_id integer;
BEGIN
INSERT INTO msg_table
(
val
)
SELECT
$1.val
RETURNING id INTO new_id;
return new_id;
END;
$body$ LANGUAGE plpgsql;
-- update function
CREATE OR REPLACE FUNCTION process_update(msg, msg) RETURNS void AS $body$
BEGIN
INSERT INTO msg_table
(
id,
val
)
SELECT
$1.idhttp://1.id,
$1.val;
EXECUTE audit_original_record($2);
END;
$body$ LANGUAGE plpgsql;
-- insert to msg
create or replace rule msg__rule_ins as on insert to msg
do instead
SELECT process_insert(NEW);
-- update to msg
create or replace rule msg__rule_upd as on update to msg
do instead
SELECT
COUNT(process_update(NEW, OLD))
WHERE
NEW.status = 1;
alter sequence msg_seq restart 1;
alter sequence msg_aud_seq restart 1;
delete from msg_table;
insert into msg
(val)
values
(1),
(2),
(66);
select * from msg;
update msg
set val = 5
where id = 1;
select * from msg;
--PG END
Thanks for any help you can give me.
Regards,
Russell Keane
INPS
Follow ushttps://twitter.com/INPSnews on twitter | visit
www.inps.co.ukhttp://www.inps.co.uk/
Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.ukhttp://www.inps.co.uk
The information in this internet email is confidential and is intended solely
for the addressee. Access, copying or re-use of information in it by anyone
else is not authorised. Any views or opinions presented are solely those of the
author and do not necessarily represent those of INPS or any of its affiliates.
If you are not the intended recipient please contact
is.helpd...@inps.co.ukmailto:is.helpd...@inps.co.uk
Re: [GENERAL] JDBC not returning update count from updateable view
On 25 February 2013 19:52, Russell Keane russell.ke...@inps.co.uk wrote: Hi, We have a table which is inserted to and update via a view (using rules / functions). In PG 9.1 and later, the recommended way to do this is using INSTEAD OF triggers on the view. See the Notes section here: http://www.postgresql.org/docs/current/static/sql-createview.html and the example here: http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-VIEW-TRIGGER-AUDIT-EXAMPLE Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PosgreSQL Konfigürasyonu
Herkese merhaba, Saniyede 10 bin kayıt alacak bir sistem kurmam gerekiyor. Bunu PostgreSQL ile nasıl yapabilirim? Donanım konfigürasyonu, postgresql yapılandırması nasıl olmaldır? Devrim Bey'in postgresql konferansında saniyede 200.000 gibi bir rakamdan bahsettiğini hatırlıyorum ama detayları nasıldır bilmiyorum. -- Melik YÜCEL Araştırmacı Siber Güvenlik Enstitüsü TÜBİTAK BİLGEM 41470 GEBZE / KOCAELİ T +90 262 648 1567 F +90 262 648 1100 www.bilgem.tubitak.gov.tr melik.yu...@tubitak.gov.tr Sorumluluk Reddi
[GENERAL] broke postgres, how to fix??
I tried copying postgres over to a new directory. it was working until I deleted a bunch of databases from the old postgres. Lo and behold this somehow broke the new copy too. Now when I start with pg_ctl 2013-02-22 18:36:13 EST DETAIL: The database subdirectory base/1066060 is missing. 2013-02-22 18:36:25 EST FATAL: database wormmine does not exist 2013-02-22 18:36:25 EST DETAIL: The database subdirectory base/1027296 is missing. 2013-02-22 18:37:13 EST FATAL: database wormmine-dev does not exist and it won't start... How can I fix this? re-creating these databases with the old postgres didn't work... Also, why did this happen? I created a new data directory for the new postgres, this should be impossible I have no ideas left, can anyone help? Thanks in advance, -JD
Re: [GENERAL] PosgreSQL Konfigürasyonu
Hi, I asked OP to send this email to pgsql-tr-general mailing list. Regards, Devrim On Mon, 2013-02-25 at 20:42 +0200, Melik Yücel wrote: Herkese merhaba, Saniyede 10 bin kayıt alacak bir sistem kurmam gerekiyor. Bunu PostgreSQL ile nasıl yapabilirim? Donanım konfigürasyonu, postgresql yapılandırması nasıl olmaldır? Devrim Bey'in postgresql konferansında saniyede 200.000 gibi bir rakamdan bahsettiğini hatırlıyorum ama detayları nasıldır bilmiyorum. Herkese merhaba, Saniyede 10 bin kayıt alacak bir sistem kurmam gerekiyor. Bunu PostgreSQL ile nasıl yapabilirim? Donanım konfigürasyonu, postgresql yapılandırması nasıl olmaldır? Devrim Bey'in postgresql konferansında saniyede 200.000 gibi bir rakamdan bahsettiğini hatırlıyorum ama detayları nasıldır bilmiyorum. -- Melik YÜCEL Araştırmacı Siber Güvenlik Enstitüsü TÜBİTAK BİLGEM 41470 GEBZE / KOCAELİ T +90 262 648 1567 F +90 262 648 1100 www.bilgem.tubitak.gov.tr melik.yu...@tubitak.gov.tr Sorumluluk Reddi -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] broke postgres, how to fix??
Did you shut down the 'old' postgres before copying these files? Did you (re)configure the 'new' postgres to set its $PGDATA directory to the location of the 'new' files? On Fri, Feb 22, 2013 at 3:46 PM, JD Wong jdmsw...@gmail.com wrote: I tried copying postgres over to a new directory. it was working until I deleted a bunch of databases from the old postgres. Lo and behold this somehow broke the new copy too. Now when I start with pg_ctl 2013-02-22 18:36:13 EST DETAIL: The database subdirectory base/1066060 is missing. 2013-02-22 18:36:25 EST FATAL: database wormmine does not exist 2013-02-22 18:36:25 EST DETAIL: The database subdirectory base/1027296 is missing. 2013-02-22 18:37:13 EST FATAL: database wormmine-dev does not exist and it won't start... How can I fix this? re-creating these databases with the old postgres didn't work... Also, why did this happen? I created a new data directory for the new postgres, this should be impossible I have no ideas left, can anyone help? Thanks in advance, -- 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] broke postgres, how to fix??
On 02/22/2013 03:46 PM, JD Wong wrote: I tried copying postgres over to a new directory. it was working until I deleted a bunch of databases from the old postgres. Lo and behold this somehow broke the new copy too. Did you actually copy or symlink? Now when I start with pg_ctl 2013-02-22 18:36:13 EST DETAIL: The database subdirectory base/1066060 is missing. 2013-02-22 18:36:25 EST FATAL: database wormmine does not exist 2013-02-22 18:36:25 EST DETAIL: The database subdirectory base/1027296 is missing. 2013-02-22 18:37:13 EST FATAL: database wormmine-dev does not exist and it won't start... How can I fix this? re-creating these databases with the old postgres didn't work... Also, why did this happen? I created a new data directory for the new postgres, this should be impossible It happened so it was not impossible. You sure you deleted from the old directory? I have no ideas left, can anyone help? Thanks in advance, -JD -- Adrian Klaver adrian.kla...@gmail.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] broke postgres, how to fix??
On Tue, Feb 26, 2013 at 4:02 PM, JD Wong jdmsw...@gmail.com wrote: Hi Adrian, yes I completely copied the config-file and data directories over. Lonnie, I don't remember. I might not have shut down the old postgres, yes I set PGDATA accordingly. That's guaranteed to break everything badly. -- 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] broke postgres, how to fix??
On Tue, Feb 26, 2013 at 4:10 PM, JD Wong jdmsw...@gmail.com wrote: Hi Adrian, That's guaranteed to break everything badly. Even if I read only style copied the files? Do you mind elaborating on why this happens? ( or point me to relevant documentation ) What is read only style, and how does postgres know about this? http://www.postgresql.org/docs/9.2/static/backup-file.html Thanks, -JD On Tue, Feb 26, 2013 at 7:04 PM, Lonni J Friedman netll...@gmail.com wrote: On Tue, Feb 26, 2013 at 4:02 PM, JD Wong jdmsw...@gmail.com wrote: Hi Adrian, yes I completely copied the config-file and data directories over. Lonnie, I don't remember. I might not have shut down the old postgres, yes I set PGDATA accordingly. That's guaranteed to break everything badly. -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org -- 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] broke postgres, how to fix??
On 02/26/2013 04:12 PM, Lonni J Friedman wrote: On Tue, Feb 26, 2013 at 4:10 PM, JD Wong jdmsw...@gmail.com wrote: Hi Adrian, That's guaranteed to break everything badly. Even if I read only style copied the files? Do you mind elaborating on why this happens? ( or point me to relevant documentation ) What is read only style, and how does postgres know about this? http://www.postgresql.org/docs/9.2/static/backup-file.html I think we are going to need a more complete description of what happened. 1) The OS involved. 2) Postgres version of old and new directories 3) Location of old and new directories. 4) What exactly was done to get from old to new. In the event things are real and truly broken, do you have backups? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
