pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger failed to identify the partions
4+ messages / 2 participants
[nested] [flat]
* trigger failed to identify the partions
@ 2009-09-09 09:26 Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sridhar Reddy Ratna @ 2009-09-09 09:26 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have created a table and partitions as below.
CREATE TABLE coll_fp_submission_details
(
rrid numeric NOT NULL,
sid numeric NOT NULL,
pfid numeric NOT NULL,
"timestamp" date NOT NULL,
schema_version numeric NOT NULL,
details character varying NOT NULL,
app_txn_id character varying NOT NULL,
CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)
)
WITH (OIDS=FALSE);
CREATE TABLE coll_fp_subdtls_01
(
CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)
)
INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts01;
CREATE TABLE coll_fp_subdtls_02
(
CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)
)
INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts02;
Now created a trigger as below
CREATE OR REPLACE FUNCTION ins_submission_details()
RETURNS TRIGGER AS $$
DECLARE
dateTable TEXT;
cmd TEXT;
BEGIN
IF ((NEW.rrid % 2)= 0) THEN
dateTable := coll_fp_subdtls_01;
ELSE
dateTable := coll_fp_subdtls_02;
END IF;
cmd := 'INSERT INTO ' || dateTable ||
'(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||
' VALUES (' || quote_ident(NEW.rrid) || ',' ||
quote_ident(NEW.sid) || ',' ||
quote_ident(NEW.pfid) || ',' ||
quote_literal(NEW.timestamp) || ',' ||
quote_ident(NEW.schema_version) || ',' ||
quote_literal(NEW.details) || ',' ||
quote_literal(NEW.app_txn_id) || ',';
EXECUTE cmd;
RETURN NULL;
END;
$$LANGUAGE 'plpgsql';
CREATE TRIGGER trig_ins_submission_details
BEFORE INSERT
ON coll_fp_submission_details
FOR EACH ROW
EXECUTE PROCEDURE ins_submission_details();
Now I am trying to insert data into table
INSERT INTO coll_fp_submission_details( rrid, sid, pfid, "timestamp",
schema_version, details, app_txn_id) VALUES (102, 101, 101, '2009-09-09',
1,'dtls', '1234');
The error is
ERROR: column "coll_fp_subdtls_01" does not exist
LINE 1: SELECT coll_fp_subdtls_01
^
QUERY: SELECT coll_fp_subdtls_01
CONTEXT: PL/pgSQL function "ins_submission_details" line 7 at assignment
********** Error **********
ERROR: column "coll_fp_subdtls_01" does not exist
SQL state: 42703
Context: PL/pgSQL function "ins_submission_details" line 7 at assignment
Can any body help me what is this problem and what is the solution.
Thanks in advance,
Sridhar Ratna
______________________________________________________________________________
DISCLAIMER
The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupport@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.
______________________________________________________________________________
This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
______________________________________________________________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger failed to identify the partions
@ 2009-09-09 10:05 Richard Huxton <dev@archonet.com>
parent: Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2009-09-09 10:05 UTC (permalink / raw)
To: Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>; +Cc: pgsql-sql
Sridhar Reddy Ratna wrote:
>
> dateTable := coll_fp_subdtls_01;
>
> ELSE
>
> dateTable := coll_fp_subdtls_02;
> ERROR: column "coll_fp_subdtls_01" does not exist
>
> ERROR: column "coll_fp_subdtls_01" does not exist
I think you missed the word "column" in the error message (easy to do,
you know you are naming tables). You've missed the quotes around the
partition-names so it's trying to find a column on a table that matches.
dateTable := 'coll_fp_subdtls_01';
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger failed to identify the partions
@ 2009-09-09 11:05 Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sridhar Reddy Ratna @ 2009-09-09 11:05 UTC (permalink / raw)
To: 'Richard Huxton' <dev@archonet.com>; +Cc: pgsql-sql
Hi Richard,
Thanks for your suggestion. It worked great.
But when I used table spaces for the inherited tables, data is being
inserted to the fpsdts01 or fpsdts02 along with the default table space.
So I am getting duplicate rows in select SQL.
I have created the table with default table space as below
CREATE TABLE coll_fp_submission_details(
rrid numeric NOT NULL,
sid numeric NOT NULL,
pfid numeric NOT NULL,
"timestamp" date NOT NULL,
schema_version numeric NOT NULL,
details character varying NOT NULL,
app_txn_id character varying NOT NULL,
CONSTRAINT coll_fp_submission_details_pkey PRIMARY KEY (rrid)
)WITH (OIDS=FALSE);
CREATE TABLE coll_fp_subdtls_01(
CONSTRAINT coll_fp_subdtls_01_pkey PRIMARY KEY (rrid)
)INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts01;
CREATE TABLE coll_fp_subdtls_02(
CONSTRAINT coll_fp_subdtls_02_pkey PRIMARY KEY (rrid)
)INHERITS (coll_fp_submission_details)
WITH (OIDS=FALSE)
TABLESPACE fpsdts02;
In the trigger
CREATE OR REPLACE FUNCTION ins_submission_details()
RETURNS TRIGGER AS $$
DECLARE
dateTable TEXT;
cmd TEXT;
BEGIN
IF ((NEW.rrid % 2)= 0) THEN
dateTable := 'coll_fp_subdtls_01';
ELSE
dateTable := 'coll_fp_subdtls_02';
END IF;
cmd := 'INSERT INTO ' || dateTable ||
'(rrid,sid,pfid,timestamp,schema_version,details,app_txn_id)' ||
' VALUES (' || quote_literal(NEW.rrid) || ',' ||
quote_literal(NEW.sid) || ',' ||
quote_literal(NEW.pfid) || ',' ||
quote_literal(NEW.timestamp) || ',' ||
quote_literal(NEW.schema_version) || ',' ||
quote_literal(NEW.details) || ',' ||
quote_literal(NEW.app_txn_id) || ')';
EXECUTE cmd;
RETURN NEW;
END;
$$LANGUAGE 'plpgsql';
If I changed the RETURN NEW to RETURN NULL its inserting only one row.
But to work with hibernate I need the return NEW statement.
Please help me in resolving this.
Thanks in advance,
Sridhar ratna
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Richard Huxton
Sent: Wednesday, September 09, 2009 3:35 PM
To: Sridhar Reddy Ratna
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] trigger failed to identify the partions
Sridhar Reddy Ratna wrote:
>
> dateTable := coll_fp_subdtls_01;
>
> ELSE
>
> dateTable := coll_fp_subdtls_02;
> ERROR: column "coll_fp_subdtls_01" does not exist
>
> ERROR: column "coll_fp_subdtls_01" does not exist
I think you missed the word "column" in the error message (easy to do,
you know you are naming tables). You've missed the quotes around the
partition-names so it's trying to find a column on a table that matches.
dateTable := 'coll_fp_subdtls_01';
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
______________________________________________________________________________
DISCLAIMER
The information contained in this e-mail message and/or attachments to it may
contain confidential or privileged information. If you are not the intended
recipient, any dissemination, use, review, distribution, printing or copying
of the information contained in this e-mail message and/or attachments to it
are strictly prohibited. If you have received this communication in error,
please notify us by reply e-mail or directly to netsupport@cmcltd.com or
telephone and immediately and permanently delete the message and any
attachments. Thank you.
______________________________________________________________________________
This email has been scrubbed for your protection by SecureMX.
For more information visit http://securemx.in
______________________________________________________________________________
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: trigger failed to identify the partions
@ 2009-09-09 14:35 Richard Huxton <dev@archonet.com>
parent: Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
0 siblings, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2009-09-09 14:35 UTC (permalink / raw)
To: Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>; +Cc: pgsql-sql
Sridhar Reddy Ratna wrote:
>
> cmd := 'INSERT INTO ' || dateTable ||
> EXECUTE cmd;
> RETURN NEW;
> If I changed the RETURN NEW to RETURN NULL its inserting only one row.
Yes. RETURN NEW allows the insert to procede normally so you end up with
the two rows.
> But to work with hibernate I need the return NEW statement.
Hmm - not sure I can see a way around this. Maybe someone else who knows
Hibernate can help.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-09-09 14:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-09-09 09:26 trigger failed to identify the partions Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
2009-09-09 10:05 ` Richard Huxton <dev@archonet.com>
2009-09-09 11:05 ` Sridhar Reddy Ratna <sridhar.ratna@cmcltd.com>
2009-09-09 14:35 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox